Max Query

[NPell]

Hi all,

I have 2 tables.
One holds account numbers.
One holds invoices, linked to account numbers.

Ive got a query to show the latest invoice for each account number,
via the MAX function.
However, once this has been done, due to it being an aggregate
function. There is no way of me editing this query once done.

Is there any way around this, as i would like to only show the latest
invoice, and edit information in that.

Regards,

 

[John Spencer]

A correlated sub-query in the WHERE clause might work for you if you don't
have a lot of records.

SELECT *
FROM Accounts INNER JOIN Invoices
ON Accounts.AccountNumber = Invoices.AccountNumber
WHERE Invoices.InvoiceDate =
(SELECT Max(InvoiceDate)
FROM Invoices as I
WHERE I.AccountNumber = Accounts.AccountNumber)

If you wish more help, you might post the SQL of what you currently have for a
query. Perhaps we can modify it to help you.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[NPell]

A correlated sub-query in the WHERE clause might work for you if you don't
have a lot of records.

SELECT *
FROM Accounts INNER JOIN Invoices
ON Accounts.AccountNumber = Invoices.AccountNumber
WHERE Invoices.InvoiceDate =
(SELECT Max(InvoiceDate)
  FROM Invoices as I
  WHERE I.AccountNumber = Accounts.AccountNumber)

If you wish more help, you might post the SQL of what you currently have for a
query.  Perhaps we can modify it to help you.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County








- Show quoted text -

Hi John, thanks for your help.
I found a solution on Google, and it seems to be the same theory you
are going for.
I havent a lot of records in the database at the moment, but time will
tell if in the future as more are added.
Thanks,