Access Query to show last entry only

  • Thread starter Thread starter YK
  • Start date Start date
Y

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!
 
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.
Click to expand...
Your queries SQL statement should look like this

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



mfG
--> stefan <--
 
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!
Click to expand...

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.
 
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.

Stefan Hoffmann said:
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.
Click to expand...
Your queries SQL statement should look like this

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



mfG
--> stefan <--
.
Click to expand...
 
Thank you.
I got it.

KenSheridan via AccessMonster.com said:
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!
Click to expand...
Click to expand...
 
Back
Top