Prev: address in one line
Next: How can I refresh the master form from a subform?
From: NPell on 11 May 2010 11:05
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,
From: John Spencer on 11 May 2010 12:03
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,
From: NPell on 17 May 2010 08:11
On 11 May, 17:03, John Spencer <spen...(a)chpdm.edu> 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,
 | 
Pages: 1
Prev: address in one line
Next: How can I refresh the master form from a subform?