Want to return result of Max query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a query that contains 3 fields.
ItemID, ItemName, ItemQuantity.

How do I set up a Select query that finds the MAX ItemQuantity and also
return the corresponding ItemName.
 
You may not need a Select query to do so. If you use a "totals" query that
returns GroupBy(ItemName) and Max(ItemQuantity), you can use this -- and if
you need a specific ItemName, use that as a criterion.
 
I have tried this but it just gives me every ItemName with each corresponding
ItemQuantity that I already have in my query.
What I want to produce is only one single record with a field that gives the
name and the other field the largest Quantity out of all the records in the
query.

You've indicated that if I need a specific ItemName that I can use that as a
criteron but I won't know what the ItemName is untill I have checked to see
which Quantity is the largest and can then tell what the corresponding Name
is.

Am I misunderstanding your advice? I have also tried to put criteria in the
ItemName field such as 'WHERE ItemQuantity = Max(ItemQuantity)' but this
gives an error message about 'item not include in aggregate function'
 
You didn't include the SQL statement of the query you are using, but from
the description, it sounds like you've clicked the "Totals" button, but not
changed the "aggregation" row. Open the query in design mode and see if it
doesn't say Group By for both the ItemName and the ItemQuantity. You want
to select the Maximum instead of Group By for the ItemQuantity.
 
The SQL sataement I am using is :

SELECT MyQuery.ItemName, Max(MyQuery.ItemQuantity) AS MaxOfItemQuantity
FROM MyQuery
GROUP BY MyQuery.ItemName

Basically
MyQuery = ItemID ItemName ItemQuantity
1 Apples 15
2 Oranges 7
3 Pears 12
4 Peach 4

So what I want to return from my query would be :-

ItemName ItemQuantity
Apples 15.

What criteria do I need to achieve this or do I need to create a
second/third query.
 
As one of my earlier responses indicated, add your criterion to the query.
This is very easy to do in the query design window. If you are only working
in SQL, you'd use something like:

SELECT ...
FROM ...
GROUP BY ...
WHERE MyQuery.ItemName = "Apples"
 
Back
Top