If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Max Query
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, |
#2
|
|||
|
|||
Max Query
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 wrote: 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, |
#3
|
|||
|
|||
Max Query
On 11 May, 17:03, John Spencer wrote:
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 wrote: 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,- Hide quoted text - - 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, |
Thread Tools | |
Display Modes | |
|
|