Solved

Access Query - Find most recent date in table

Posted on 2008-10-14
2
1,549 Views
Last Modified: 2013-11-27
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)
0
Comment
Question by:☠ MASQ ☠
2 Comments
 
LVL 12

Accepted Solution

by:
rgn2121 earned 500 total points
ID: 22709508
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
 
LVL 62

Author Comment

by:☠ MASQ ☠
ID: 22711317
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

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

The System Center Operations Manager 2012, known as SCOM, is a part of the Microsoft system center product that provides the user with infrastructure monitoring and application performance monitoring. SCOM monitors:   Windows or UNIX/LinuxNetwo…
This collection of functions covers all the normal rounding methods of just about any numeric value.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now