Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2005
    Location
    Utah
    Posts
    136

    Smile Unanswered: Sums in Report Footer

    I have a report that is based on a query that has a one to many relationship between two tables.

    The table on the one side is called Authorizations, the table on the many side is called Visits.

    I'm comparing the numeric value in the #Auth field in the Authorizations table to the number of records in the Visits table so I can get a percentage of how many visits were done out of the number authorized.

    Example: 6 were authorized but only 3 visits were done, the percentage used is 50%.

    I have the percentage calculated at group level and again at report level.

    The calculations at group level work fine. But I can't get the calculations at the report level to work correctly.

    In the report footer I have a control that displays the total sum of all visits done which works correctly. Another control that displays the total sum of authorizations, not working correctly. Another control that displays the percentage used, which is incorrect because of the total sum of authorizations being incorrect.

    To narrow this issue down, the problem is with the control in the report footer that displays the total sum of authorizations.

    If I have only one group of records (I really have multiple groups but to simplify the issue, I'm using one group to explain what is happening) and in that group if the numeric value in the #Auth field is 6 and there are 3 records in the Visits table related to that authorization, the control is multiplying the #Auth (6) by the number of records in the Visits table(3). So instead of the control in the footer displaying the number 6, it's displaying the number 18 (6x3).

    So in short, the # visits authorized (6) is being multiplied by the number of visits used (3). I need the # visits authorized (6) to display in the footer instead of the number 18. (Keep in mind, I really have several groups in which I would like to add the # visits authorized in all the groups and have it display in the footer.)

    If I use just the name of the field in the control source, I get only the #Auth from the first group. If I use the word SUM in front of the name of the field, it multiplies the #Auth by the number of records in the Visits table.

    I'm not sure how to rectify the problem. Any help is greatly appreciated.
    Thanks,
    Zenaida


    - If you've never made a mistake, you've never made anything.

  2. #2
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    Well, doing math in a report beyond the "first level" is always a bit "fun" (read: annoying to get just right).

    To ensure you get the correct value, do the math in queries. Unless there are a LOT of records, there isn't generally a performance hit to doing multi-level queries, especially since the report probably spawns the extra queries anyway. Run a "totals" query that provides the count of visits in each group. Add that query to the current query and you now have the number of visits for each group counted ahead of time and you can either calculate the percentage in the query, or in the report, without relying on the report to do it for you.

    I know of al least six people already forulating their response - yes, this is less efficient. But, it is easy to debug and the performance hit will not be noticable. Sometimes, the "less efficient way" is better as you get to your desired outcome faster and you don't lose as much hair trying to get the "correct" way to work.

    tc

  3. #3
    Join Date
    Sep 2005
    Location
    Utah
    Posts
    136
    Thanks so much for your reply. I'm fairly new with Access and have never used "totals" queries before. I looked in to them and did not realize how much you can do with them. Your idea worked great for me. Thanks so much for your time.
    Thanks,
    Zenaida


    - If you've never made a mistake, you've never made anything.

Posting Permissions

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