getting average value of purchases beetwen two dates

[dylan_dog]

Hi all,
I have a problem with a query. I have two tables, the first with customer ID's and contact data and related table with purchases for each customer. The second table has customer ID, date of purchase and amount.What I try to do is to display all the customers within given date range with average purchase above custom minimum. Purchase amount in my query is grouped by average. Query works fine regarding time frame, my problem is that I don't get just one single average value for each customer that meets the criteria. What I'm getting is a list of all the purchases for every customer that exceeds minimum value. How can I get just that one average value for each customer?

Regards, Samo

 

[recyan]

Just check out if below gives some guidelines :

SELECT 
	tblPurchase.Customer_ID
	,tblCustomer.customerName
	,Avg(tblPurchase.Amount) AS AvgOfAmount
FROM 
	tblCustomer 
	INNER JOIN 
	tblPurchase 
	ON tblCustomer.customerID = tblPurchase.Customer_ID
WHERE 
	(((tblPurchase.DateOfPurchase) Between #1/1/2012# And #1/10/2012#))
GROUP BY 
	tblPurchase.Customer_ID, 
	tblCustomer.customerName
HAVING 
	(((Avg(tblPurchase.Amount))>200));

Thanks

 

[dylan_dog]

Recyan, thank you.

Works like a charm.

 

[recyan]

Glad you found it helpful.

Thanks