Grand Totals in Cross Tab Query (Access ’03)

Topic

New Reply

Is there any way to get grand totals (sum of columns) into a cross tab query?

Reply | Quote

Replies

No, but you can

– calculate the column totals in the report footer of a report based on the crosstab query, or
– create a separate query to calculate the column totals, and display subforms based on the queries on a main form.

Reply | Quote

That would work if I just wanted to display them but I want to be able to use them in a calculation. Remember the report you so kindly helped me with? What I want to do is for each Division, use the sum of its montly totals as the divisor in the percentage calculation for that Division so that each segment shows a a percentage of the annual total for that Division.

Reply | Quote

Referring back to that report (post 455173 for others reading this thread), do you want to do this for each individual payment category (greater than or equal to $100K, between $75K and $100K etc.), or only for the row total?

I have attached a rather messy version of the report from the post mentioned above. There are two extra queries and an extra subreport.

Reply | Quote

I would like to do it for each individual payment category. Showing the categories by month but having the percent be calculated by dividing each of the monthly category totals by the sum of the totals by month for the division.

Reply | Quote

OK, you’ll find that in the database attached to my previous reply.

Reply | Quote

I looked at that and unless I’m mistaken, the percents are correct in the total row and the total column but the monthly detail breakdown is not correct. It appears as though the monthly detail is still using the monthly total as the divisor to calculate the percentages.

Sorry to be such a pain in the butt.

Reply | Quote

Below each amount in the detail section of the report, there are two percentages. The first indicates the percentage of the row total, i.e. the amount for that category as a percentage of the total amount for all categories (for that month and division). The second indicates the percentage of the column total for that company, i.e. the amount for that month as a percentage of the total amount for all months (for that category and division).
If that is not what you want, please explain in detail what you need.

Reply | Quote

I apologize profusely. I looked but I did not see. I believe that is a perfect solution. Now all I have to do is figure out what you did.

Thank you,

Reply | Quote

The crosstab query qrySumDivision calculates the column totals per division and per category.
The query qrySumTotal combines qrySum and qrySumDivision (joined on Division), and calculates the percentages.
The query qrySumTotal is used as Record Source for sbrSum; text boxes for the percentages were added to the detail section of this subreport.
The subreport sbrSumDivision is based on qrySumDivision. It is used in the group footer for Division on the main report, to show the column totals.

Reply | Quote

Hans. I am returning your database solution to my problem including a much larger data set (it’s actually my real data with everything changed to protect the innocent or guilty as the case may be). It appears that something gets out of sync between the qrySum and the qrySumTotal. The payment month order and therefore the record order is not consistent between the two which I believe is causing erroneous results but I’m not smart enough yet to understand how it is happening. It is also confusing to me that the record order is identical between the two query results for Bach and Beethoven beginning to get out of sync with Brahms. If you look at the first instance of Beethoven the percentages add up to 163.81%. so somehow the 972.99 is being divided by itself instead of the line total of 7818.31. Hopefully this will be simple for you. It is Dutch to me.

Reply | Quote

With your “real” data, the report takes minutes to open on my PC, so I’ll see if I can find a different approach.

Reply | Quote

I had that same problem but for my part, it could run all day as long as I get the results I’m looking for..

Reply | Quote

Here is a slightly faster version. The calculations are still the same; frankly I don’t see what’s wrong.

Reply | Quote

Wow. This one is quite a bit faster.

Aha! I see the problem and to quote Strothers Martin from Cool Hand Luke, “What we have here is a failure to communicate” for which I take full responsibility and do apologize. Due to my faulty specification, I believe the problem was made more difficult than it should have been. What I was looking for in the “% of Total” row was that sorted cumulative entry divided by the sum of all of the lease payments (grand total) for that division. I believe that what this report does for that row is divide the sorted cumulative entry by the sum of all lease payments in that sort column for that division. I doesn’t appear to make much sense until you add 2 decimal places to the percent fields, then it quickly shows the relative importance of the leases for that division for the year.

Reply | Quote

Try this one. The main change is in the query qrySumTotal.

Reply | Quote