Pull most current Invoice

[Kelli]

I have a query that I am trying to pull the most current
invoice where all the criteria matches except for the
Invoice number and the last invoice date.

Example:
Inv # = 12345
date = 1/1/2004
client = john doe
topic = abc123
amt due = 1234.56

Next record would be (and this is the one I want)
Inv # = 56789
date = 6/1/2004
client = john doe
topic = abc123
amt due = 1234.56

As I have it my query written now, my query pulls both
records because the invoice numbers and the dates are
different. This is for an outstanding invoice report so I
need to show all the data.

How do I pull the latest record?

Thank you in advance for your assistance.
Kelli

 

[Allen Browne]

See:
Getting a related field from a GroupBy (total) query
at:
http://www.mvps.org/access/queries/qry0020.htm
for 4 possible approaches.

 

[Tom Ellison]

Dear Kelli:

I take it you want to retrieve only the most recent invoice, as
defined by [date], for each client/topic. I'm going to assume that
[amt due] and [Inv #] do not figure into the criteria.

SELECT client, topic, [Inv #], [date], [amt due]
FROM YourTable T
WHERE [date] = (SELECT MAX([date]) FROM YourTable T1
WHERE T1.client = T.client AND T1.topic = T.topic)

Not that, if there are two invoices for a client/topic that have the
same [date] you will get both. It is ambiguous which one is the most
recent. We could give you the one with the greater invoice number of
the two if that is important. Or you could have the one with the
greater amount due. The point is to have some combination or criteria
that is unique. The best way to do that is to constrain the data with
a unique index, probably on the invoice number, and then use that to
filter this query uniquely.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts