Every customer's last invoice

[Herman Beeksma]

Hi there! I have two tables:

Customer (ID, Name)
Invoice (ID, Date, Customer, Amount)

and want to select only the *last* invoice for each customer. It's easy
to get each customer's last invoice date:

SELECT Customer.Name, MAX(Invoice.Dat e)
FROM Customer INNER JOIN Invoice ON Custimer.ID = Invoice.Custome r
GROUP BY Customer.Name

but I run into trouble when I try to include the Invoice.Amount. How to
do this in one SQL statement? I came up with:

SELECT Customer.Name, Invoice.Date, Invoice.Amount
FROM Customer INNER JOIN Invoice ON Custimer.ID = Invoice.Custome r
WHERE Invoice.ID IN
(
SELECT MID(key, INSTR(key, ":") + 1) FROM
(
SELECT Customer, MAX(CLNG(Invoic e.Date) & ":" & CLNG(Invoice.ID ))
AS key
FROM Invoice
GROUP BY Customer
)
)

which leaves me somewhat unsatisfied. (And it won't work if the same
customer is invoiced twice on the same day.) There's got to be a more
elegant way to do this. Any ideas?

[Allen Browne]

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Herman Beeksma" <he****@opticro p.nl> wrote in message
news:9d******** *************** ****@news.conce pts.nl...

Hi there! I have two tables:

Customer (ID, Name)
Invoice (ID, Date, Customer, Amount)

and want to select only the *last* invoice for each customer. It's easy to
get each customer's last invoice date:

SELECT Customer.Name, MAX(Invoice.Dat e)
FROM Customer INNER JOIN Invoice ON Custimer.ID = Invoice.Custome r
GROUP BY Customer.Name

but I run into trouble when I try to include the Invoice.Amount. How to do
this in one SQL statement? I came up with:

SELECT Customer.Name, Invoice.Date, Invoice.Amount
FROM Customer INNER JOIN Invoice ON Custimer.ID = Invoice.Custome r
WHERE Invoice.ID IN
(
SELECT MID(key, INSTR(key, ":") + 1) FROM
(
SELECT Customer, MAX(CLNG(Invoic e.Date) & ":" & CLNG(Invoice.ID )) AS
key
FROM Invoice
GROUP BY Customer
)
)

which leaves me somewhat unsatisfied. (And it won't work if the same
customer is invoiced twice on the same day.) There's got to be a more
elegant way to do this. Any ideas?