Most Recent Date

[Pharaoh]

Hi,

I am trying to produce a query to show the last time a customer bought and item and how many they bought.

The fields I am using are a date field a "customer number" field and a "volume bought" field. The problem is that I do not know how to set the query to only show the last batch bought by each customer.

Each customer can buy as many of the item as they wish at any time but if I use a date range say for the last financial year, it will show all transactions by all customers instead of only showing the last transaction by each customer whether it be in april last year or the end of march this year.

Regards

Pharaoh

 

[namliam]

Split it into 2 parts
1)
Find the last transaction date:
Select CustomerNumber, max(buydate) MaxBuydate from yourtable

2)
Get the data you want by joining yourtable back into above query

Good luck.

 

[MStef]

Look at "DemoMostRecentDA2000.mdb" (attachment, zip).
Look at Table, Query. Run Query.
I think it is what you need.

 

[Pharaoh]

Ah thank you both. Got it working now.

Regards

Pharaoh