Max aggregate function in Query

[DaveTappenden]

Please help, I have a severe case of brain fade!.

I am trying to create a query based on 2 tables using an inner join, but only selecting the record in the 2nd table with the highest value (Invoice Number).

The following query will give me all Invoices for a customer:
SELECT Cust.CustName, Inv.InvNr
FROM Cust INNER JOIN Inv ON Cust.CustID = Inv.CustID
WHERE (((Cust.CustName)="Smith&quot);

- but I only want to extract a single row from the 2nd table, which is the last (highest) invoice number.
So, I add the Max aggregate function:

SELECT Cust.CustName, MAX(Inv.InvNr)
FROM Cust INNER JOIN Inv ON Cust.CustID = Inv.CustID
WHERE (((Cust.Name)="Smith&quot);

However, this causes the query to fail with an Access error "You have tried to execute a query that does not include the specified expression 'CustName' as part of an aggregate function"

Can any kind person explain what I am doing wrong please?

Thanks.
David

 

[cmmrfrds]

If you aggregate any one column in the query, you need to aggregate them all. You could add a group by to customer name and that would fullfil the rule.

WHERE (((Cust.Name)="Smith&quot)
group by Cust.Name

 

[jjonesal]

What about using a subquery in your WHERE clause....I haven't tested the query, but would something like this work?

SELECT Cust.CustName, Inv.InvNr
FROM Cust INNER JOIN Inv ON Cust.CustID = Inv.CustID
WHERE (((Cust.Name) = "Smith&quot AND (Inv.invNr)=(SELECT max(invNR) from [Inv] WHERE CustID = Cust.CustID)));

JJ

J. Jones
jjones@cybrtyme.com

 

[jebry]

Hi!

Another possibility would be:

SELECT Top 1 Cust.CustName, Inv.InvNr
FROM Cust INNER JOIN Inv ON Cust.CustID = Inv.CustID
WHERE (((Cust.Name)="Smith&quot)
Order By Inv.InvNr;

hth
Jeff Bridgham
bridgham@purdue.edu