lowest cost vendor

[robbarkley]

I'm trying to set up a query to identify the lowest cost vendor. I have set up three tables: 1. a product table, 2. a vendor product table and 3. a Vendor Table. The vendor products are linked to the vendor table via the vendor ID. The vendor products are linked to the Products in the product table off the Product ID in the Vendor Product table. Each Product can have multiple vendors. I want to identify the Primary vendor using a couple of criteria but primarily using the lowest cost. What is the best equation for building this query?

thanks

RB

[June7]

Use TOP 1 and sort by. Review http://allenbrowne.com/subquery-01.html#TopN

[robbarkley]

i would be lieing if i had any clue what you were talking about. i'm a ridiculously inexperienced with access

[June7]

You want the lowest cost vendor for each product.

Build a query following Allen Browne's 'TOP N for Each Group' example - your 'group' is product.

Assuming there are Cost and ID fields in VendorProduct, something like:

SELECT * FROM VendorProduct WHERE ID IN
(SELECT TOP 1 ID FROM VendorProduct AS Dupe WHERE Dupe.ProductID = VendorProduct.ProductID ORDER BY Dupe.Cost);