Access Query to show last entry only

[YK]

Can anyone help...
I have a table with supplier code, invoice number, invoice date. How to
create a query only to show the "Last" invoice date.
Thanks!

 

[Stefan Hoffmann]

hi,

Can anyone help...
I have a table with supplier code, invoice number, invoice date. How to
create a query only to show the "Last" invoice date.

Your queries SQL statement should look like this

SELECT *
FROM [yourTable]
WHERE [invoiceDate] =
(
SELECT MAX([invoiceDate])
FROM [yourTable]
)



mfG
--> stefan <--

 

[John W. Vinson]

Can anyone help...
I have a table with supplier code, invoice number, invoice date. How to
create a query only to show the "Last" invoice date.
Thanks!

Don't use the "LAST" option in a totals query: it's misleading, it doesn't
mean the most recent entry or the entry with the most recent invoice date; it
means "the last record in disk storage order", an arbitrary record.

Use a Subquery to find the Max (not Last) value of InvoiceDate as Stefan
suggests, or use a query sorted by InvoiceDate and set its TOP VALUES property
to 1.

 

[YK]

Thanks for your reply.
The SQL statement only show the latest invoice overall.
Sorry that I didn't state clearly, I need the query to show the latest
invoice per supplier.
I'll try the method suggested by Ken.

hi,

Can anyone help...
I have a table with supplier code, invoice number, invoice date. How to
create a query only to show the "Last" invoice date.

Your queries SQL statement should look like this

SELECT *
FROM [yourTable]
WHERE [invoiceDate] =
(
SELECT MAX([invoiceDate])
FROM [yourTable]
)



mfG
--> stefan <--
.

 

[YK]

Thank you.
I got it.

Should you want to return the latest invoice per supplier rather than the
latest invoice overall, correlate the subquery with the outer query on the
supplier code, e.g.

SELECT *
FROM [Invoices] AS I1
WHERE [invoice date] =
(SELECT MAX([invoice date])
FROM [Invoices] AS I2
WHERE I2.[supplier code] = I1.[supplier code]);

Ken Sheridan
Stafford, England

Can anyone help...
I have a table with supplier code, invoice number, invoice date. How to
create a query only to show the "Last" invoice date.
Thanks!