Show me the last 4 invoices for every customer

Stuart Green

Registered User.
Local time
Today, 17:18
Joined
Jun 24, 2002
Messages
108
I have a table with every invoice I have raised to my customers on it (160,000 records so far). Every record identifies the customer and the date of the invoice. I would like a query that returns details of the last 4 invoices raised to each customer based on the date of the invoice. So, if I have 100 customers I would expect 400 records returned. I have spent ages searching and reading the forums re this and have seen several post that I "think" are similar, but I think I'm too daft to understand the solutions put forward. I "think" this involves a module to count but as I have never written a module in my life am a bit confused. Any pointers would be greatly appreciated.
 
Use the TOP operator on the dates. I presume the latest 4 invoise are the ones with the latest dates.
 
Hi Neil, sorry, this only returns any record who's date is within the top 4, ie is quite recent. I want the last 4 records for EVERY customer that I have whether the invoice date was this year, last year or 10 years ago
 
Hi Stuart,

actually, Neil's approach works.
Of course you need to use a query that joins customers and invoices.

RV
 
Sorry folks, I must be out of my depth here. I have tried using TOP as per the Microsoft help and can only get 4 records... those invoices that have the top 4 invoice dates. I have 12,214 customers so am looking to get a query that gives me 48,856 records, 4 for every customer. This is the sort of thing I have in the SQL statement

SELECT TOP 4
customer_name, invoice_number
FROM tbl_sales
ORDER BY invoice_date DESC

It gives me the last 4 invoices I happened to raise and that's it.

It's Friday, nearly home time and the pub calls. I could have more beer than is good for me if I can't crack this lol. Your patience is much appreciated
 
Sorry, mate, I'm in California and was working, it's 9:30 am here and you are probably already pinted out. :p

Here's yer consolation. Hope you get better.

Code:
SELECT tbl_Sales.customer_name, tbl_Sales.invoice_date
FROM tbl_Sales
WHERE ((((SELECT  Count(*)
FROM tbl_Sales AS tbl_Sales_1
WHERE tbl_Sales.invoice_number <= tbl_Sales_1.invoice_number
AND tbl_Sales.customer_name = tbl_Sales_1.customer_name
))<=4))
ORDER BY tbl_Sales.customer_name, tbl_Sales.invoice_date DESC;

NOTE: this uses a subquery which uses a *lot* of time. If you need more speed for all those thousands of records, break up the subquery into it's own query, or even faster, use VBA (Jon K has a GREAT function here. Have a good weekend.
 
Last edited:
Too late!! Got drunk and the wife was not impressed with my new excuse..blame Access. But many many thanks, your solution was just the job and I would never have got there on my own.
 
What about 2 tables?

What would the SQL be for 2 tables?
I'm selecting CompanyName from TBLProspects and Note and NoteDate from TBLNotes. I want to show only the latest Note for each company.
 

Users who are viewing this thread

Back
Top Bottom