Make-Tabel Query with running sum

[Guest]

I have a recipie database with many tables and I am trying to make a new
table based on a query. In this table I want only 1 row per ingredient.

this is a simplified eksample

Ingredient table

Name amount Supplier
Meat 4 100
Milk 2 100
Meat 2 100

Supplier table

Supplier SupName
100 Butcher

This is how I want the new table to look like

SupName Name Amount
Butcher Meat 6
Butcher Milk 2

Lines with the same ingredient is added together in one record


Any suggestions on how I can do this

___________
Regards
JaydDe
Norway

 

[John Spencer]

Instead of making a new table, just use a totals query.

Open a new query
-- Select the Ingredient table and the Supplier table
-- Join the two supplier fields together (Drag from one to the other)
-- Put SupName, Name, and Amount as fields
-- Select View: Totals from the menu
-- Change Group By to Sum under the Amount field

Run the query.

 

[Guest]

Thank you. In my simplified example it workes fine. However in my query I
have a caculated field that is based on 3 different fields. It is the
information in the caclulated field that needs to be summarized. But since
the information in the fields that goes into the calculation are different in
each record. This wont work. As I understand it, all the fields in the record
must be identical in order for sum to work, or is it something I can do?
--
___________
Regards
JaydDe
Norway


John Spencer skrev:

 

[John Spencer]

You could use the existing query as the source for a totals query.
Open a new query, add the existing query to the query window
-- Select View: Totals from the menu
-- Change Group by to SUM under the calculated field.

OR you might be able to modify the existing query directly. You can sum a
calculated field.

If you can't work this out, post the SQL of your existing query and tell us
which field(s) you want summed. Someone can suggest modifications to the
query and you can test the modifications.

Better yet, experiment and see what happens. I learn best by doing.

 

[Guest]

I tried your first solution and it worked fine.

Thank you

--
___________
Regards
JaydDe


John Spencer skrev: