Summary from detail table to new table

[T.J.]

I'm stuggling with how to approach the following situation. On the forums I've looked up moving summary data between tables and

also looked at self-join tables and trying to use the Get(Summary) function.

I have a table with detail information.

whse - cust - prod - price - cost - count

ny - fred - rock1 - 10 - 5 - 1

sf - wilma - rock1 - 12 - 4 - 1

la - barney - rock2 8 - 2 - 1

ny - wilma - rock2 9 - 2 - 1

ny - barney - rock1 9 - 3 - 1

la - betty - rock2 10 - 3 - 1

I'd like to move the summary of some of this data to some new tables.

I have a table prod_sum which I'd like to end up with the following from the detail information

prod - tot.price - tot.cost - tot.count

rock1 - 31 - 12 - 3

rock2 - 27 - 7 - 3

I assume some sort of loop script would do the trick here but I'm not sure of the script steps on how to accomplish.

Next I'd like to sum the information by two fields and move that to a new table.

I have a table whse_prod_sum so the data would look like this.

whse - prod - tot.price - tot.cost - tot.count

la - rock2 - 18 - 5 - 2

ny - rock1 - 19 - 8 - 2

ny - rock2 - 9 - 2 - 1

sf - rock1 - 12 - 4 - 1

I'm also assuming a loop script would work for this but because the data is summarized for two fields I would think this might be a

little different from the first loop script maybe even requiring a concatenation.

I have a table cust_prod_sum but would think that if someone can show me the loop script for the whse_prod_sum table, then

I should be able to figure it out from there.

summary_tables.zip