get a max number

[Guest]

Hi,

I have the three columns as follow:
Product Ingredient# Qty
PPP 316 100
PPP 315 70
PPP 317 80
SOS 316 100
SOS 320 60
SOS 315 60
SOS 317 90

I need to get the ingredients that have the max qty among the same
ingredients and for what Product those ingredients are used. If this max
exist for several products it would be nice to have all these products.

In our sample the result would be as follow:
Product Ingredient# Qty
PPP 316 100
SOS 316 100
PPP 315 70
SOS 317 90
SOS 320 60

How could I create this query?

Thanks,

Alex

 

[Michel Walsh]

Hi,



SELECT LAST(a.Product), a.Ingredient, a.qty
FROM myTAble As a INNER JOIN myTable As b
ON a.Ingredient = b.Ingredient
GROUP BY a.Ingredient, a.qty
HAVING a.qty = MAX(b.qty)




sounds to be a possible answer. Three other solutions are presented at
http://www.mvps.org/access/queries/qry0020.htm




Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

Thanks a lot Michel.

I've just taken off LAST to have all the same max for the different products.

 

[Guest]

I get this result --
Product Ingredient# QTY
PPP 315 70
PPP 316 100
PPP 317 80
SOS 315 60
SOS 316 100
SOS 317 90
SOS 320 60

SELECT MyTable.Product, MyTable.[Ingredient#], Max(MyTable.QTY) AS QTY
FROM MyTable
GROUP BY MyTable.Product, MyTable.[Ingredient#];