query to pull most recent record by date

[spence]

I have the following two tables:

tblVendor (key field vendorID)
tblInvoice (key fiend invID)

I have a simple query that pulls all the invoices I've ever paid, the vendor
to whom they were paid, and the date they were paid. What I would like to do
is to alter the query to show me only the most recently paid bill for each
vendor. I've been trying to understand the Max function but am in over my
head.

 

[Ken Snell [MVP]]

Using generic fieldnames:

SELECT Max(I.InvoiceDate) AS LastPaidDate,
First(I.InvoiceNumber) AS InvNumber,
First(V.VendorName) AS VendName, V.VendorID
FROM tblInvoice AS I INNER JOIN tblVendor AS V
ON I.VendorID = V.VendorID
WHERE I.InvoicePaid = True
GROUP BY I.VendorID;


or


SELECT I.InvoiceDate, I.InvoiceNumber, V.VendorName,
I.VendorID
FROM tblInvoice AS I INNER JOIN tblVendor AS V
ON I.VendorID = V.VendorID
WHERE I.InvoiceDate =
(SELECT Max(TI.InvoiceDate) AS IDt
FROM tblInvoice AS TI
WHERE TI.VendorID = I.VendorID
AND TI.InvoicePaid = True);


Note that the first example will not show you all invoices with most
recently paid date for a vendor, should you have more than one invoice
number paid on same date. The second example will show all invoice numbers
that have the most recently paid date.