By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,286 Members | 1,632 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,286 IT Pros & Developers. It's quick & easy.

Calculate Percentages

P: 6
I created a table in Access 2007 called "Accidents" . I have a field called
"Fault Description" which allows the user to select the option of "Fault" or
"No fault" from a list. In the report footer, I want a number to reflect the percentage of records that have the option of "Fault" selected. I want to run this report by date range, i have already added the date field.
Apr 26 '10 #1
Share this Question
Share on Google+
3 Replies


P: 47
create a query with parameter to restrict the dates then based on this query create a second query to obtain the "fault" count , and other query to "No fault" count, then create a form with 2 unbound date fields that ask for initial and end dates, and a button to run a report based on the parameter query, you can now see all the registers within your dates window and in the report footer you can write down the "fault" and "No fault" total counts and create a new calculated field that give you a percent of "Fault" over "No fault" or over Total register count
Apr 26 '10 #2

ADezii
Expert 5K+
P: 8,698
Here is the general idea without the Date Criteria included. To include the Percentage of Faults in the [Fault Description] Field for all Records in the Accidents Table, place the following Expression in the Control Source of a Text Box in the Report's Footer:
Expand|Select|Wrap|Line Numbers
  1. =Format(DCount("*","Accidents","[Fault Description] = '" & "Fault" & "'")/DCount("*","Accidents"),"Percent")
Apr 27 '10 #3

NeoPa
Expert Mod 15k+
P: 31,768
If your data is shown in a control in the Detail section of the report, then these values can be aggregated directly (You can sum the value in the detail rather than having to use a Domain Aggregate function - DSum() - to process through the data again).

Something like :
Expand|Select|Wrap|Line Numbers
  1. =Format(Sum(IIf([txtFaultDescription]='Fault',1,0)/Count([txtFaultDescription]),'Percent')
This will automatically incorporate any date criteria applied to the report.
Apr 27 '10 #4

Post your reply

Sign in to post your reply or Sign up for a free account.