Report "Percentage claculation of Totals and Grand Totals"

[Micheal]

Greetings Access Group,

Being relatively new to Access, I try to work through problems on my own and have been very successful, although I have a conundrum that I have been working on for two days (total 10 hours) and I have yet to resolve it. please excuse me for my lack of terminology, I will try to provide you with the best of information.

I currently have a report that contains 2 totals and the percentage of the difference of the 2 totals. I will refer to the totals as: total "A and "B", and the percent calculation I will refer to "C".
The Report is based off a query and results in multiple records being viewed on the Report. The "Detail" section of the Report returns the actual amounts for "A" and "B". "C" is the calculated percentage difference B & A: (Query field) Expr1 SUM([b]/[A]). The report DETAIL percentage returns the correct percentage, the problem is:
The "Footer" section of the report and the "Report Footer" are not returning the correct percentage. I need the reports "Footer" section to return the % difference of the "Sum" section of the "Footer" and "Report Footer". My logic is such: (Footer), "C" equals: the sum of all "B" records - divided by - the sum of all "A" records. I also need to return the same result in the "Report Footer" "Grand Totals”, my logic being, (Report Footer), "C" equals: the Grand Total for all "B" records - divided by - the Grand Total for all "A" records.
I have exhausted my efforts and realize that I am clearly moving in the wrong direction. Your help would be greatly appreciated, and please remember my knowledge of Access is limited.
Thank you for assistance

[MGrowneyARSI]

I’ve done something similar in the past you could try setting the field C on your report equal to a Dlookup of A / Dlookup of B you can do this in the properties box or the on open event for the report don’t know if this will help but that is the way I would do it
DLookup("[FIELD]","TABLE/QUERY","CRITERIA")
Me.CompanyID = DLookup("[companyID]", "Company", "[company]=" & [Forms]![Login]![Company])
Me.CompanyID = the field you are setting
("[companyID]"= the data you are feeding to the field
"Company",= the table/query you are looking in
"[company]=" & [Forms]![Login]![Company]) = in this example you are finding the record were company in the table/query = company on the login form you do not need to use criteria if there is only one record at a time and there are other ways to do dlookups

If you knew all of this already then just do something like this
C = DLookup("[FIELD]","TABLE/QUERY","CRITERIA") / DLookup("[FIELD]","TABLE/QUERY","CRITERIA")

[mlcampeau]

Greetings Access Group,

Being relatively new to Access, I try to work through problems on my own and have been very successful, although I have a conundrum that I have been working on for two days (total 10 hours) and I have yet to resolve it. please excuse me for my lack of terminology, I will try to provide you with the best of information.

I currently have a report that contains 2 totals and the percentage of the difference of the 2 totals. I will refer to the totals as: total "A and "B", and the percent calculation I will refer to "C".
The Report is based off a query and results in multiple records being viewed on the Report. The "Detail" section of the Report returns the actual amounts for "A" and "B". "C" is the calculated percentage difference B & A: (Query field) Expr1 SUM([b]/[A]). The report DETAIL percentage returns the correct percentage, the problem is:
The "Footer" section of the report and the "Report Footer" are not returning the correct percentage. I need the reports "Footer" section to return the % difference of the "Sum" section of the "Footer" and "Report Footer". My logic is such: (Footer), "C" equals: the sum of all "B" records - divided by - the sum of all "A" records. I also need to return the same result in the "Report Footer" "Grand Totals”, my logic being, (Report Footer), "C" equals: the Grand Total for all "B" records - divided by - the Grand Total for all "A" records.
I have exhausted my efforts and realize that I am clearly moving in the wrong direction. Your help would be greatly appreciated, and please remember my knowledge of Access is limited.
Thank you for assistance

You could duplicate your "B" records and "A" records textbox in your details section. In the properties, change Visible to No and Running Sum to Over Group. Name each of these textboxes appropriately. In your footer, create a textbox for "C" with the expression: =[NotVisibleBTextboxName]/[NotVisibleATextboxName]. Follow this same logic for your Report footer.

[Micheal]

I could not get the DLookup to work correctly, so I kept Reviewing formula's then noticed that I my calculation was; =Sum([b])/([A]), which was incorrect, it should have been: =Sum([b])/Sum([A]). Which means I kind of misdirected you in my first question. Thanks for your help.
Michael

[MGrowneyARSI]

Well that will work LOL but I would keep the Dlookup in your bag of tricks they can be a pain but they're very usfull as well good luck