Problem with group by query over date range

[George]

Hi there,
background:
I have a list of transactions for different clients and different products.
important colums are transaction date, product name, client name, transaction
type (buy or Sell) and Amount.

What i want is to create a summary of transaction total at the product
level. So i get a list of each product and total transactions as opposed to
each individual client transaction.

The problem is i have to enter a transaction date range (Between
[Forms].[date].[start] And [Forms].[date].[end]) which makes it difficult.

WHen i try to group by product name and sum by amount the query returns the
total of all transactions for each client on that day and so on.

Any ideas would be greatly appreciated?

Thanks
George

 

[Michel Walsh]

SELECT product, SUM(amount)
FROM tableName
WHERE transactionDate BETWEEN FORMS![date]![start] AND FORMS![date]![end]
GROUP BY product


(in SQL view. Change the fields names and table name as it fits; all cap
words are keywords and to be typed as they appear here.).


Vanderghast, Access MVP