get totals within query 3

[ksbrace]

Hello, I would like to get the TOTAL quantity sold from the week based on product code. I would like it to list the description, TOTAL Quantity
and total sales value for each product code. Any help would be greatly apprecaited on getting the totals part to work.

table:

Product Code
Description
Transaction Number
Quantity
Sales Value
Cost
Transaction Date
Transaction Time
Department
Type Code
Cashier
Computer Name
Customer Code

query I am working with:

select [product code], [description], [quantity], [sales value] from product_history where [Transaction Date]<= Date()-7 and [Transaction Date]>=Date() order by [description];

 

[Remou]

How about:

select [product code], [description],
Sum([quantity]), Sum([sales value])
from product_history
where [Transaction Date]<= Date()-7 and [Transaction Date]>=Date()
Group By [product code], [description]

 

[PHV]

I'd replace this:
where [Transaction Date]<= Date()-7 and [Transaction Date]>=Date()
with this:
where [Transaction Date] Between Date()-7 And Date()

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[BigRed1212]

I had to try that syntax out because I would have used:

WHERE [transaction date] <=now() And [transaction date]>DateAdd('ww',-1,Now())

doing the query in Access. The "between Date()-7 And Date()" seems to work just fine and is a lot cleaner.