Vendor Selection

[DampFloor]

Hello,
I'm pretty new to Access so really need help. I am making a a database for a restaurant which needs to be able to select the cheapest vendors for all ingredients. Quotes are received weekly from three seperate vendors. I cannot figure out how to get access to identify which vendor is the cheapest for each ingredient. If any more info is required please ask!

 

[arnelgp]

if you have a separate table for ingredients and quotations, you can
use query to list all ingredients then only get the least price
from quotations table:

select ingredientName, T1.quotationNumber, T1. qoutationDate, T1.quotationPrice FROM ingredients LEFT JOIN
(SELECT TOP 1 qoutationNumber, quotationDate, quotationPrice FROM quotations AS T1 WHERE T1.ingredientname = ingredients.ingredientName ORDER BY T1.quotationPrice DESC) AS T2 ON ingredients.ingredientName = T2.ingredientName

 

[DampFloor]

Ok great, thanks for the help!