Want to return result of Max query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

AdeyS - 28 Mar 2005 13:35 GMT

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.

Reply to this Message

Jeff Boyce - 28 Mar 2005 14:04 GMT

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.

Signature

Good luck

Jeff Boyce
<Access MVP>

> 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.

Reply to this Message

AdeyS - 28 Mar 2005 15:01 GMT

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 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
[quoted text clipped - 5 lines]
> > How do I set up a Select query that finds the MAX ItemQuantity and also
> > return the corresponding ItemName.

Reply to this Message

Jeff Boyce - 28 Mar 2005 16:07 GMT

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.

Signature

Good luck

Jeff Boyce
<Access MVP>

> I have tried this but it just gives me every ItemName with each corresponding
> ItemQuantity that I already have in my query.
[quoted text clipped - 20 lines]
> > > How do I set up a Select query that finds the MAX ItemQuantity and also
> > > return the corresponding ItemName.

Reply to this Message

AdeyS - 29 Mar 2005 01:25 GMT

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.

Reply to this Message

Jeff Boyce - 29 Mar 2005 13:54 GMT

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"

Signature

Good luck

Jeff Boyce
<Access MVP>

> The SQL sataement I am using is :
>
[quoted text clipped - 16 lines]
> What criteria do I need to achieve this or do I need to create a
> second/third query.

Reply to this Message