Hey guys.
I have a boolean expression which I want to calculate the percentage of.
I have the field 'Regular Booking' which is either true or false. I have 6 true and 5 false, equalling to 11 in total. I've tried using: 100*[CountOfRegular Booking] / [TotalRegular Booking] but this gives me the same percentage for both true and false. So if I enter 5 I get 45.54% for both true and false... why is this? I've had trouble with this for ages now and I'm pulling my hair out, lol. :(
Thanks guys!
I am using following function to return percentage spend by testing against number of months elapsed. I am unable to show the CSpend as percentages and I cannot seem to get the percentages in the query.
Public Function CSpend(mMonths) If (mMonths) = -4 Then CSpend = 1 ElseIf (mMonths) = -3 Then CSpend = 0.99956 ElseIf (mMonths) = -2 Then
Just started doing access at work and have a problem.
I have a database. Each record has a bunch of different fields.
In one field, the record can either be DC or DS (different classifications).
I would like to run a query which would tell me the percentage of DCs and DSs.
I have managed to create a query that counts the total of each one, but for the life of me i cannot figure out how to do the percentages.
Can anyone help?
I am using Access 2000 and my query is being made in design view
The Query has 2 columns:
Column 1 Field: DC/DS Table: Main Total: Group By Show: yes
Column 2 Field: DC/DS Table: Main Total: Count Show: Yes
Can I add the percentage section as another column (using an expression?) or will it have to be another query, that runs the above query then uses data from that?
Simple question: how do you convert vlaues into percentages? I have a boolean expression so when it's ticked the bookings are regular, and if they're false they are occasional bookings. I have used 10 bookings as test data (5 regular - true, 5 occasional - false). How would I convert these into percentages? For example, I've used the "Count" total to fint the number of regular and occasional booknigs, which are both 5. This is 50%, but how would I do that? Thanks for your help guys. :)
ok, so i have to work out week by week percentage uses i have a query that already finds the bookings in the week already, that works fine, but then i need to find the % use for that week, which is the count of the number of bookings in the week / 21 ive tried doing this in an update query, but it doesnt like me :) any help would be great :) i have a % use field in my table that it updates to, but im flexible :) thanks
I have a requirement to give a % figure from 2 time fields. I would have thought this was simple, but when I divide the fields I keep getting #Error. Its not an error caused by a divide by zero, i can recify those. Can anyone point me in the right direction to get a % from 2 time fields.
This is probably simple but it's something I haven't had to do before.
I have a main table. I have a filter query based on this table. I have a report, based on the query, which displays the total number of records in the query. In the same report, I would like to display this total as a number (already done, obviously) and also as a percentage of the total number of records in the main table.
Been searching for an answer to this one but still cant quite get it.
I am using an option group to subtract and add percentages on an amount in a text box. This is the code i am using but there is something wrong. My syntax is out.
Is there a data type that I can use that will handle both text and numbers such as percentages? Or is there a way I can set the field type to text then convert the text to a percentage? I plan on using this field in a form so when the user inputs the info I will want to display it in the correct format. Any ideas or suggestions?
I'm setting up a database for student evaluations. Students have several options as to how well the instructor did for each survey question. I've been able to set up the data entry form to my liking, and I can use a query to average the total answers to each question, including a count of how many people responded. HOWEVER, I need to calculate the percentage of responses for each option. For example, I need to know the percentage of students who picked "Excellent" for question 1, how many students chose "Very Good", how many chose "Good", etc., so on and so on for every question. How can I do that? I'm completely stuck and a novice user. HELP!!:eek:
can anyone explain how to use a percentage in a table.
i have a field designated as data type "number" and format set to Percentage.
when i go to datasheet view and attempt to enter in these percentage (1%,50%, 34%, etc) it is multiplying the data entered by 100 (100.00%,5000.00%,3400.00%, etc).
I'm trying to create a table in an Access Form or create a block of code to export the table with query results to Excel or PDF. In the end I would like to run a query or queries to populate the table or export the query results creating the table. I'm not sure the best way to create the table.
I'm creating a report that list multiple users providing an input of "approved" or "not approved" for a plurality of proposals. I'm trying to create a report that lists the percentage for each user that calculates the number of times the user inputs "not approved" over the total number of proposals that particular user reviewed.
For example,
Steve reviews 50 proposals, and of the 50 proposals Steve inputs 10 of those proposals to be "not approved". I need a calculated field that counts the number of times that Steve inputs "not approved" and calculates that 20% of proposals reviewed are not approved, of all the proposals he reviewed. The number of proposals are continuously being added so the number 50 will not always be the denominator.
There are at least 10 other users that I have to do the same calculation but if I'm able to do the example above.
We have a model at my job which shows every job code one can do (there are only about 25 of these jobs) For example, let's say one job is called "Trade Settlements" and it's been estimated that we spend about 1000 minutes a day doing this particular job across the entire floor.
Well, I want to come up with a way to allocate the minutes of this to certain hour blocks and determining whether our group is over/understaff given the results.
So let's say I want, 30% of it to be done from 9-10 am, 10% done over the next 4 hour time blocks, and 2-3 pm for the remaining 30%.
Thus, we'd get something something like 300, 100, 100, 100, 100, 300 minutes and 0's for other time blocks.
These percentages would be input fields so if anyone types in a percentage, they get # of minutes spent for that hour on that job. Ultimately we would add these numbers up with other jobs and be able to easily update from there for any job type we wanted.
I created a database to record time logged per workorder for each employee on my job. Each time log has a specific "Trade" attached to it along with a number of hours the employee spent on that workorder. I've created a report to display how much time the selected employee spent on each workorder (within a date range) and now I want to see what percentage of their time was spent on a particular "Trade" (for instance, during September Employee "name" spent "percentage" of their time on Electric, "percentage" on HVAC, "percentage" on Plumbing...[and so on])
I have trades listed in the table and in the time log, the form writes to the trades area of the table (probably very elementary for this discussion) and the report lists the name and grand totals with percentage of total time on each workorder, but does not list any trade information.
How can I add this into my report, preferably at the end (Report Footer?)
I have a database that I'm building off of a process that is currently done in excel. I want my percentages calculations to match what I get in excel but I'm finding the numbers to be off by as much as .4%. I'm pretty sure they issue has to do with the precision of its calculations but what the best settings are.
My percent numbers are currently set to the field size "Double" with a percent formatting. My currency numbers are set to the format Currency and decimal places on auto.
I am currently using Access 2007 and have created a huge database for our investment managers to calculate the income based on percentages. The percentages are created in Excel. I have uploaded into the database both the June and the July percentages. When I run the query for my report using the date range for June, it works fine. When I request just the July information (entering the date range for July) the June data is doubled on the report.
What is the best way to impliment a query in a form so that the user can view the query records, and have the option to print or save the selected record using command buttons?
I tried subforms but I could not get the command buttons to work in the subform after it went into the form, it wanted to print the entire form instead of the selected record from the subform.
So in a nutshell I have 3-4 queries that are built, and I want to have them show up on my form in a format that the user can scroll through the results and select a single record of the results and then print or save that individual record from the form, if such a thing is possible.
In some cases I create pass-through queries and use these in an Append or Make-table query to bring data locally.
All is well and fine until source data changes and the pass-through query runs too long and times out.
If needed, I can extend the timeout value in the Parameters of the pass-through query no problem, but when I try to open the Append or Make-table query in Design view to do the same, the pass-through query is first triggered and then throws the timeout, and I cannot access the Design view of the Append or Make-table
Is there a way to open an Append or Make-table query in Design view without invoking the source query?
I'm trying to run a UNION query that joins five queries through a MS WorkSpace into a DAO.recordset in VB. I'm pulling the data from a SQL Server Database through VB in Access. I'm attempting to open a recordset with a query passed to it as a string. The query is below. For some reason, I'm receiving a message: "MS Jet database engine cannot find the input table or query. Runtime Error 3078".
Here's what's puzzling. When I run a single query without any UNION statement, the code finds the table and runs fine without error, but anytime I join two or more queries with a UNION statement in the VB, it gives me the error.
I've executed the same UNION query in both Access Query Builder and SQL Server's Query Analyzer and they work fine in both environments. It's only when I call the query from a DAO.Recodset with VB that it causes this problem. The following is a sample of the UNION query joining two of the five queries. Does anyone have any idea what could be the problem? The following query executes in about 5 seconds so I don't think there's a "time-out" issue. I'm thinking that the UNION statement may be the culprit. Maybe there's another way to approach joining these separate queries? Any help would be most appreciated. Thanks.
SELECT SalespersonID, Sum([SlsPrice]-[RtnPrice]-[SlsDiscnt]+[RtnDiscnt]) AS fldPrice FROM MyTable WHERE (((Source)='d') AND ((DistrictID)='01') AND ((CategoryID) = 'HCPROD') AND ((BrandID)<>'CSS')) AND (((BrandID)<>'1356')) AND (((BrandID)<>'1400')) AND (((BrandID)<>'1551')) AND (((BrandID)<>'555')) AND (((BrandID)<>'66')) AND (TransDate >= 07/01/2005) AND (TransDate <= 07/31/2005) GROUP BY SalespersonID UNION SELECT SalespersonID, Sum([SlsPrice]-[RtnPrice]-[SlsDiscnt]+[RtnDiscnt]) AS fldPrice FROM MyTable WHERE (((Source)='d') AND ((DistrictID)='01') AND ((ProductID) = '0029800')) AND (TransDate >= 07/01/2005) AND (TransDate <= 07/31/2005) GROUP BY SalespersonID
Set wrkJet = CreateWorkspace("", "pw", "", dbUseJet) Set db = wrkJet.OpenDatabase("DW", _ dbDriverNoPrompt, True, _ "ODBC;DATABASE=DW;DSN=DW2") 'Set rs1 = db.OpenRecordset(strSQL)
I am wondering if there is a quicker way to export a query to excel then have the data in that query removed from the original table. (effectively cutting the data from the table and exporting to excel)
I understand that this can be done by exporting the query to excel then running the same query as a delete query to remove the data but I just wondered if this is the most efficient way.
I have experience of VB in excel but currently only use the basic macro builder in Access though if Access VB is more efficient I can easily learn.
I run a physical therapy office and patients come in for treatment either 3, 4 or 5 times per week. My database is used to track these frequencies (among other things).
I have 3 queries which count how many patients come in 5, 4 and 3 times/week.
In my main table I have fields called "how many 5's", "how many 4's" and "how many 3's".
I have tried to design an update query which will update those fileds in my main table to reflect the counts in the 3 queries mentioned above.
(I'm not using SQL view, I'm using the query design view)
In the "update to:" row, I use the Build function and locate the count I'm looking for.
Problem: when I run the query I get the error: Operation must use an updateable query.
The ProjRevMRC field is an expression that reads:ProjRevMRC: IIf([ProjRevDate]>=DateSerial(Year(Date()),Month(Date()),1),[CurrentMRC]*[qry303a_ SFADetailMRC_ONLY]![Rev Flow Through],0)
When I run the query, it works perfectly, but when I created a crosstab query to show totals by month, I wanted the totals to be zero for the months less than the current month. Is there a way for the crosstab query to execute the expression and put zeroes for those months?
I have a field that is giving me the number of business days between a period of time and then I want to subtract that number - the person's PTO time to see the actual days they were available...when I simply type the number in (see below) it works great but I want to set up a prompt that will ask me how many PTO Days to calculate as it will be different for each person I am quering...is this possible?
