Simple for experts...

[Rubi]

Hi all,

I have the feeling that this is a simple thing to do but i just can't figure it out, here's the thing:

my DB collects orders in a resturant.

I have 2 tables: tempSubtotal (itemNumber, Date, Quantity...) and Archive (ItemNumber, Date, Quantity...)

I want to move the records from tempSubtotal to Archive each time.
the thing is that i want to sum the "Quantity" in the archive for each "itemNumber" and "Date".

the result Archive should contain the entire resturants menu (all the itemNumbers that where ordered) and how many orders there where (sum of "Quantity") each day.

can anyone help ?

 

[Fuga]

Are you sure you can´t do this with only one table and then a query to group/sum?

Fuga.

 

[Rubi]

I prefere to have the 2 tables (I have only 3 tables in the DB)

what kind of sum query are you talking about ?
can i run a query on the "Archive" table to sum up its values ?

thanks for your help...

 

[Fuga]

Yes.

When you design your query, hit the summation button. Group by Itemnumber and then date. For the quantity field, choose "sum".

Or, try pasting this in SQL view:

SELECT Archive.Itemnumber, Archive.[date], Sum(Archive.quantity) AS SumOfquantity
FROM Archive
GROUP BY Archive.Itemnumber, Archive.[date];

I havent tried it, but I think it should work.

Also, consider changing the name date to something like "archivedate" to avoid problems later on.

Fuga.

 

[Rubi]

thanks alot

gone to try it...