Results 1 to 10 of 10
  1. #1
    2 Star Lounger
    Join Date
    May 2001
    Location
    indiana
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Report Subtotals (XP)

    I have read many of the posts about getting a subtotal to display in a report. For some reason i can't get things to work... I have "departments" that are broken down into "clusters" (i.e. Surgery may have different clusters of rooms in different locations around the building). Each cluster has an area square footage. I want to sum the areas of the "clusters" for each "department" WHen i follow the instructions of the other posts i have read, i can only get the sum to display one of the areas, not the sum. I have no idea what i'm doing wrong! I cannot get the database down to less that 150 KB, so i would be happy to email it to anyone that wants to help. The problem is that i need this by lunch tomorrow! THanks in advance for your help!

  2. #2
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    La Verne, California, USA
    Posts
    313
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report Subtotals (XP)

    Use sorting and grouping for the departments and the clusters. Use rthe show footer option for those groups. In the footer create a text box and set its data source to =Sum([fieldname]). Set this to sum over group.

  3. #3
    2 Star Lounger
    Join Date
    May 2001
    Location
    indiana
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report Subtotals (XP)

    I'm coming up with numbers like 10 million! not quite right... In my tables, i have one with department clusters that each have an area square footage. I combine these in a query so each room shows up in it's appropriate cluster. In the query, the area of the cluster displays for each room. I think this is what the total area is adding. Am i doing something wrong? thanks for your help!

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

    Re: Report Subtotals (XP)

    See <post#=401925>post 401925</post#> for instructions.on how to reduce the size of the database for posting it (and remember, you must zip it)

  5. #5
    2 Star Lounger
    Join Date
    May 2001
    Location
    indiana
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report Subtotals (XP)

    here is the attachment... I guess i should have tried zipping it. After 12 hours of work, sometimes your brain just gets fried! I have included the two reports that I need done. There area labels indicating where and what sums need to be calculated. Thank you for everyone who is trying to help me out!

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

    Re: Report Subtotals (XP)

    Why is there a Floor field both in tblRooms and in tblDepartments? If all rooms in a department are on the same floor, the field should be in tblDepartments only. If rooms in the same department can be on different floors, the Floor field should be in tblRooms only.

    Why is the Department Name field in tblRooms and not in tblDepartments?
    Same question for the Building Name field.

  7. #7
    2 Star Lounger
    Join Date
    May 2001
    Location
    indiana
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report Subtotals (XP)

    It's been a while since i was heavy into Access.. assume i will make those changes and see if the report totals will work. Thanks for the tip!

  8. #8
    2 Star Lounger
    Join Date
    May 2001
    Location
    indiana
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report Subtotals (XP)

    Well, I've almost got it working.... After moving the fields around as Hans suggested, i get the sums to total, but they are not quite right. Since the square footage is tied to the clusters, in which are made up of several rooms (without individual square footages), the query displays the cluster square footage for each room. This means the report is trying to sum all the instances of the square footages. If there is only one room in the cluster, then the sums work correctly. If there are three rooms in a cluster, and the cluster area is 400 s.f., then the sum is 1200 (3 x 400). It is summing the area for each room, not the overall area of each cluster. What can i do to fix this? I only have 2 hours to get this completed. Thanks so much for your help!

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

    Re: Report Subtotals (XP)

    You should use a subreport for the rooms - see the attached version.

  10. #10
    2 Star Lounger
    Join Date
    May 2001
    Location
    indiana
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report Subtotals (XP)

    I think that will work! Thanks a bunch Hans!

Posting Permissions

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