Finding the field value for a select query total

[nichski]

Hello all,

I have a set of data where I am trying to find the max price listed
from each vendor for each item (part number) then find the location
where this max price occurs. I have grouped the on the vendor name,
part number and listed the max price. The data also has a site name
where this max price occurs but I do not want to know the max price for
each location, rather I want to know the value for the site name where
the max price occurs.

Can someone tell me how I find the value of the site name field for the
max price for each unique item (part number) and vendor.

Thanks in advance for your assistance.

Tony

 

[Guest]

Use two queries --

nichski_1 ---
SELECT nichski.[Part number], Max(nichski.Price) AS MaxOfPrice
FROM nichski
GROUP BY nichski.[Part number];

SELECT nichski.[Part number], nichski.Vendor, nichski.Price, nichski.Location
FROM nichski INNER JOIN nichski_1 ON (nichski.Price = nichski_1.MaxOfPrice)
AND (nichski.[Part number] = nichski_1.[Part number]);