sort and grouping with sum problem in a query?

[Gustavson]

I'm using access97. I have a query in which I have many fields. Building, room number, chemical, quantity are four of the many fields that I have in the query. As of right now I am grouping by buildings , room numbers and chemicals. That works just fine but I can't figure out how to keep a running sum of each chemical in a building.

Here's my example of how the query looks now:
Onyx (building), 12 (room#), boric acid, 200g
Onyx (building), 110 (room#), boric acid, 300g
Cascade (building), 23 (room#), acetone, 500ml
Cascade (building), 56 (room#), acetone, 1000ml

Here's what I want:
Onyx (building), 12 (room#), boric acid, 200g
Onyx (building), 110 (room#), boric acid, 300g
total boric acid in Onyx = 500g
Cascade (building), 23 (room#), acetone, 500ml
Cascade (building), 56 (room#), acetone, 1000ml
total acetone in Cascade = 1500ml

Here's the problem:
I can't get the query to display individual chemicals and the total amount of those chemicals in the same query. It will work fine if I remove the room number field but that defeats the purpose of my query. I want to be able to display all the chemicals in a building and the running total quantities of individual chemicals. Is there a way to do this? Will some code be required here?

Bye the way, I want to be able to do this so I can print the chemicals and running total in a report. This seems to work really well in Excel but can't do it in Access.

Hope this is enough info...
Thanx
Gustavson

 

[thornmastr]

Since you have stated this query will drive a report, you have all the information you need in your query. You need do nothing else to it.

You want to take the time, now, to read about the grouping/sorting ability in the Access report facility. I don’t think you need group headings, but you will want to use group footers. You want to group on chemical within room number within building. You will be summing and printing within each group footer.

This should get you started. If you need help as you go, post back stating where you are. Someone will help you through that stage.

Robert Berman

 

[Gustavson]

Robert thanx for your reply. I read into grouping and using running sums. This is what I did so far. Added a new footer based on Building. I set the properties to Group On Each Value and Keep Together Whole Group. Added a text box to the footer and set the source to quantity, and set the Running Sum property to Over Group. I'm one step closer but the total is displayed after each chemical.

Here's how it looks:

Onyx, 10, boric acid, 10g
total 10g
Onxy, 10, boric acid, 20g
total 20g

So it's halfway there and I'm not sure where to go from here.

Any ideas?
Thanx
Gustavson

 

[thornmastr]

It’s closer. Notice that the grouping capability also gives you sorting in the group as well. It looks as if you printing a total after each line of print. Remember you do not have to do anything with a group. You can define a group primarily to allow you to sort and specify neither header or footer which in effect simply makes it a sort field. You do want to sort, from minor to major, chemical, then building. This will put your chemicals in a group but you can ignore the group, this will force all the chemicals found in a building to fall under that building. Until you get the report to where you want it you might want to let the Keep Together option to its default which is no. if you still have a stumbling block, if you wish, you can zip your database and mail it to me. I will be happy to look at it. Email address under my sig.

Robert Berman
thornmastr@yahoo.com

 

[Gustavson]

okay, I just had to mess around with some properties within the Headers and Footers on my report and used the =Sum([Quantity]) expression to run the sum.. Works good now.. Thanx for your help
Gustavson