Selection criteria

[Guest]

Hopefully not very complex and I could explain .

I have a table consists of four fields .

Country / Product / Product Type / Qty
X A 01 7
X A 02 6
X B 03 14
X B 04 14
Y C 11 15
Y C 15 10
Z D 20 4
Z D 30 3
Z E 40 10

For each country I would like to keep only ONE product type by product with
the highest volume .

And if there is more than one with equal highest volume , I would like to
keep both.

Besides for the quantities lower than 5 , I would like all them to be listed
regardless of quantities.

Ie . On above table the query should then list

Country / Product / Product Type / Qty
X A 01 7
X B 03 14
X B 04 14
Y C 11 15
Z D 20 4
Z D 30 3
Z E 40 10

I wonder if that possible by writting a query in access and i
very much appricate any help on this.

Steve

 

[Steve Schapel]

Steve

First of all, make a query like this...

SELECT Country, Product, Max(Qty) AS MaxOfQty
FROM YourTable
GROUP BY Country, Product

Let's say you call this query CountryMax.

Then, make another query including YourTable and also CountryMax query,
like this...

SELECT YourTableCountry, YourTable.Product, YourTable.[Product Type],
YourTable.Qty
FROM YourTable LEFT JOIN CountryMax ON (YourTable.Qty =
CountryMax.MaxOfQty) AND (YourTable.Product = CountryMax.Product) AND
(YourTable.Country = CountryMax.Country)
WHERE (((YourTable.Qty)<5) Or ((YourTable.Qty)=[MaxOfQty]))