average purchase value between 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. I also un-ticked date field in query design so dates don't show when I run the query. How can I get just that one average value for each customer?

Regards, Samo

[pbaldy]

What's the SQL of the query? It sounds like you're still grouping on the date field. It should just be in the WHERE clause (or nowhere).

[dylan_dog]

SELECT baza2.ID, baza2.Ime, baza2.Priimek, tblTHW.datum, Avg(tblTHW.THW) AS AvgOfTHW
FROM baza2 INNER JOIN tblTHW ON baza2.ID = tblTHW.ID
GROUP BY baza2.ID, baza2.Ime, baza2.Priimek, tblTHW.datum
HAVING (((tblTHW.datum) Between [forms]![frmIznadX]![txtDatumZadnjegTrekanjaOd] And [forms]![frmIznadX]![txtDatumZadnjegTrekanjaDo]) AND ((Avg(tblTHW.THW))>[forms]![frmIznadX]![txtTHWIznad]));


datum means date, THWIznad is the target min average value, Ime is name, Priimek is second name and the other two are dates

[pbaldy]

As I suspected:

SELECT baza2.ID, baza2.Ime, baza2.Priimek, tblTHW.datum, Avg(tblTHW.THW) AS AvgOfTHW
FROM baza2 INNER JOIN tblTHW ON baza2.ID = tblTHW.ID
GROUP BY baza2.ID, baza2.Ime, baza2.Priimek, tblTHW.datum

Having it in the GROUP BY clause is telling Access you want to see it grouped on date.

[dylan_dog]

First, thank you for the answers. I tried to remove group by date in SQL view and it replaced "group by" with "expression" in design view and the query won't run. How do I remove date from grouping?

Regards, Samo

[pbaldy]

It appears you need it in there for the criteria, so change Group By to Where in design view.

[dylan_dog]

It doesn't run :-). Maybe the easiest solution would be to make two queries, the first to just filter by date and the second based on the first to calculate averages.

[pbaldy]

If you're still stuck, post a sample db here we can play with and sort out the problem.

[dylan_dog]

Hi,

I tried with two queries and it works fine. Than I found this and it does exactly what I need:

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));

Thank you

[pbaldy]

Glad you got it sorted out.