Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

MS Access 2003 Report Help Calculation

Posted on 2012-08-22
8
Medium Priority
?
351 Views
Last Modified: 2012-08-23
I received some help eariler to help me with a calculation on a report which works until I add more records to my table. I have a simple form that ties to one table. The form has two drop down lists. One is for area audited (example: FWV, CVT) the other is a choice of audits (Completed Audits, Unsafe Audits, Safe Audits). I have a query that will count the total number of audits which is tied into a report. The report has a calculation to give a percentage of unsafe audits / completed audits. If I enter four records for say CVT and based on my query it calculates 3 Completed Audits and 1 Unsafe Audit the percentage is correct on my report (33%). The problem is if I introduce more records but add FWV my calculation for the query remains correct but the report to get the percentage is calculating all the records. I am looking for I guess what I would call sub totals. So I would expect to see something like this.

CVT  Unsafe Audits 1
        Completed Audits 3
        Percentage of Unsafe audits 33%

FWV33 Unsafe Audits 1
             Completed Audits 2
            Percentage of Unsafe auditis 50%

Right now on my report I get a percentage for both as a grand total. I have attached my db In the hopes that somebody can help me fill in the blanks as I'm sure this is a simple fix.
Safety-new.mdb
0
Comment
Question by:tmaususer
  • 5
  • 3
8 Comments
 
LVL 31

Expert Comment

by:hnasr
ID: 38324427
Check your data, In query:
Status      Items      Area Audited
Audits Completed      3      CVT
Unsafe Audits      2      CVT
Audits Completed      1      FWV33
Unsafe Audits      3      FWV33

Last two values seems reversed.

Is Ratio calculated like unsafe / Completed?
0
 

Author Comment

by:tmaususer
ID: 38324559
May have been a type o. Yes The calculation is correct.
0
 
LVL 31

Expert Comment

by:hnasr
ID: 38324614
Try this:
Safety-new-2.mdb
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:tmaususer
ID: 38324732
hnasr: That is awesome. One more question and if it can be done I will up the points. On the report I get something like this

FWV33               Unsafe Audits              2
FWV33               Audits Completed       3

Is there anyway on the report to only show FWV33 (and future line entries) once. In other words something like this

FWV33               Unsafe Audits              2
                           Audits Completed       3

If not its not a show stopper.
0
 
LVL 31

Expert Comment

by:hnasr
ID: 38324876
Yes!
A moment please.
0
 
LVL 31

Accepted Solution

by:
hnasr earned 1200 total points
ID: 38324945
Try this: Field Duplicates: No
Safety-new-3.mdb
0
 

Author Comment

by:tmaususer
ID: 38325210
THanks. That did it but I think I may have an issue with the counts and introducing dates. I'll see if I can figure it out on my own and if not I'll post another question. Thanks for the help.
0
 
LVL 31

Expert Comment

by:hnasr
ID: 38326157
Welcome!
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

572 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question