Should be easy for someone - show only last customer order, by date?

[Garindan]

Hi all, i have a query which will be used for reports, and only the last order placed by a customer is needed to be shown. Here's the table used in the query:-

tblCustomerPurchases
CustomerNumber
PurchaseNumber - PK,Autonumber
DateOfPurchase
SeasonID
TotalCost

So obviously for one customer there can be several orders, but i just want to show the latest one by the date field. I have tried grouping by the date field on both Last and Max, but with all the other fields from the table in the query (i need to have all the fields from the table for the report by the way) it still shows all the orders for a customer.

If i only include the customernumber field and dateofpurchase field and then group by date i have 4923 records, which is the correct amount, only one order per customer (the latest one) but adding any other fields to the query gives me the full 7000 odd orders (several per customer).

Any help would be much appreciated, many thanks in advance.

 

[MStef]

Hello Garindan!

I think I have solved your problem.
Look at "DemoLastOrderA2000.mdb".
Look at Table1, Query1,
Open Report1.

 

[Garindan]

Sorry MStef but i can't see how you did that I know it must be something on the report but i can't see where?

I would actually rather it was done in the query if it was possible as i am going to be making the report a dynamic report with filtering and maybe sorting, so if it's something that would affect that then it won't be possible.

 

[FoFa]

Try this:
SELECT CP1.yada, CP1.yaday, CP1.yada
FROM tblCustomerPurchases CP1
WHERE CP1.DateOfPurchase = (SELECT MAX(CP2.DateOfPurchase) from tblCustomerPurchases CP2 Where CP2.CustomerNumber = CP1.CustomerNumber)

 

[Garindan]

Sorry FoFa, i'm not that intelligent obviously I can't work out what to do with that? Whats CP1 and CP2?

 

[Garindan]

Can't anybody help? I'm running out of time and getting stressed with it :'(

 

[FoFa]

Replace the yada's with the columns you want. Put this in the SQL window of a query. Verify all names are correct. CP1 and CP2 are table aliase names is all.

 

[Garindan]

So do u mean to have 2 copies of tblCustomerPurchases in the same query?

Edit: No it's ok, i worked out what i'm supposed to do with it It works fairly well but for some reason it shows 4935 records, where it should be 4923? Do you think that might be because a few customers might have ordered more than once on one day? I'm trying to work it out but its confusing.

 

[Garindan]

Yep i've done it now and it works fine, thanks for your help FoFa. It was just that i wanted to know where the extra numbers were coming from, and i worked out that obviously when only using two fields there are less unique records than in the full query with five fields. All the extra ones were just from when customers have ordered more than once on one day, and thats ok. Many thanks

 

[MStef]

Hello Garindan!

If yo still want to know how the report work,
look at VBA in Report1.