Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    New Lounger
    Join Date
    Feb 2005
    Location
    Plymouth, Michigan
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Grand Totals in Cross Tab Query (Access '03)

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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Grand Totals in Cross Tab Query (Access '03)

    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.

  3. #3
    New Lounger
    Join Date
    Feb 2005
    Location
    Plymouth, Michigan
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Grand Totals in Cross Tab Query (Access '03)

    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.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Grand Totals in Cross Tab Query (Access '03)

    Referring back to that report (<post#=455173>post 455173</post#> 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.

  5. #5
    New Lounger
    Join Date
    Feb 2005
    Location
    Plymouth, Michigan
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Grand Totals in Cross Tab Query (Access '03)

    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.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Grand Totals in Cross Tab Query (Access '03)

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

  7. #7
    New Lounger
    Join Date
    Feb 2005
    Location
    Plymouth, Michigan
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Grand Totals in Cross Tab Query (Access '03)

    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.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Grand Totals in Cross Tab Query (Access '03)

    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.

  9. #9
    New Lounger
    Join Date
    Feb 2005
    Location
    Plymouth, Michigan
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Grand Totals in Cross Tab Query (Access '03)

    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,

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Grand Totals in Cross Tab Query (Access '03)

    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.

  11. #11
    New Lounger
    Join Date
    Feb 2005
    Location
    Plymouth, Michigan
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Grand Totals in Cross Tab Query (Access '03)

    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.

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Grand Totals in Cross Tab Query (Access '03)

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

  13. #13
    New Lounger
    Join Date
    Feb 2005
    Location
    Plymouth, Michigan
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Grand Totals in Cross Tab Query (Access '03)

    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..

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Grand Totals in Cross Tab Query (Access '03)

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

  15. #15
    New Lounger
    Join Date
    Feb 2005
    Location
    Plymouth, Michigan
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Grand Totals in Cross Tab Query (Access '03)

    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.

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •