A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Max Query



 
 
Thread Tools Display Modes
  #1  
Old May 11th, 2010, 04:05 PM posted to microsoft.public.access.queries
NPell
external usenet poster
 
Posts: 91
Default 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  
Old May 11th, 2010, 05:03 PM posted to microsoft.public.access.queries
John Spencer
external usenet poster
 
Posts: 7,815
Default 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  
Old May 17th, 2010, 01:11 PM posted to microsoft.public.access.queries
NPell
external usenet poster
 
Posts: 91
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:25 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.