Access Query - Find most recent date in table

I have a table containing every invoice issued to a customer.  There are multiple records for each customer's business.

What I'm trying to produce is a query that will return a table containing only the most recent invoice record for each customer so I can follow-up customers who we've had no business with for more than 6 months.

The table has multiple fields but can you suggest a solution using the fields [Customer Ref#] and [Invoice_Date]?  I want to pull the whole record for their last business.

Normally I have no problem writing queries but this one has me stumped - I'm blaming it on a head cold :)

Thanks  M :o)
LVL 64
☠ MASQ ☠Asked:
Who is Participating?
 
rgn2121Connect With a Mentor Commented:
I don't do much with Access, but I have a site I got to if I do that might help...Here is a little code from it.

Select MAX(Invoice_Date), Customer Ref#  from Table1 group by Customer Ref#;

Now, that will only return thos 2 fields so you will have to link it back up.

With A as (
Select MAX(Invoice_Date), Customer Ref#  from Table1 group by Customer Ref#;
)

Select *
From Table1, A
Where A.Customer Ref# =Table1.Customer Ref#
AND  A.Invoice_Date = Table1.Invoice_Date

Something like that...the site should help lay it out better.  I generally work through the errors, but Since I don't have to data to work against I am just throwing it together....
http://www.techonthenet.com/access/queries/max_query2.php
0
 
☠ MASQ ☠Author Commented:
Thanks
Select MAX(Invoice_Date), Customer Ref#  from Table1 group by Customer Ref#;
Needed a little tweaking but fundamentally solved the issue.  It was MAX() I had forgotten!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.