I have a table with these fields: ProdCode, Price, and Vendor. For each ProdCode, I am trying to determine the vendor with the highest price.

I am able to find the highest price for each product code but am having problems associating the vendor.

I am using one query to find the highest price for the product code and a table with the prices, codes, & investors.

When I try to link to my "highest price" query to add the vendor, my new query returns multiple instances of the product code with each vendor instead of just one product code, one price, and one vendor.

How do I find the highest price for each product code and associate the vendor to that price and product code?

Thanks!

JB

you need to nest two queries to get the correct answer. The inner query finds the Max() price, the outer obtains the related data.

Query1:
Select Product, Max(Price) As MaxPrice
From YourTable
Group By Product;

Query2:
Select q.Product, q.MaxPrice, t.Vendor
From Query1 as q Inner Join YourTable as t On q.Product = t.Product And q.MaxPrice = t.Price;

Pat,

Thank you for your precise suggestion - it works like a charm. I had not seen an inner join before & really learned something here. Thanks again!

JB

You're very welcome. Learning is the best part of this forum http://www.access-programmers.co.uk/ubb/smile.gif