What is your table structure like? It sounds like your table is as follows;
tblData
Product (String)
COmpanyA (Number) - Company A's price
COmpanyB (Number) - Company B's price
COmpanyC (Number) - Company C's price
If so, you can use three queries.
qryPriceData
Code:
SELECT Product, CompanyA as Price, "CompanyA" as Company FROM tblData;
UNION SELECT Product, CompanyB as Price, "CompanyB" as Company FROM tblData;
UNION SELECT Product, CompanyC as Price, "CompanyC" as Company FROM tblData;
qryPriceMin
Code:
SELECT Product, Min(Price) AS MinOfPrice
FROM quniPriceData
GROUP BY Product;
qryCompanyCheapest
Code:
SELECT qryPriceMin.Product, qryPriceMin.MinOfPrice, quniPriceData.Company
FROM qryPriceMin INNER JOIN quniPriceData
ON (qryPriceMin.MinOfPrice = quniPriceData.Price)
AND (qryPriceMin.Product = quniPriceData.Product);
HOwever, I think you it would be better to have three tables instead of one.
Also, create a unique multi-field index using COmpanyID and ProductID. This wil make it so that you can't have a multiple company-product combinations (One price for each unique Company-Product). Alternatively you can use the unique multi-field index as your primary key instead of an Autonumber.
This would allow you to have two queries instead.
qryPriceMinV2
Code:
SELECT tblPrice.ProductID, strProduct, Min(curPrice) AS MinOfcurPrice
FROM tblProduct
INNER JOIN tblPrice
ON tblProduct.ProductID = tblPrice.ProductID
GROUP BY tblPrice.ProductID, tblProduct.strProduct;
qryCompanyCheapestV2
Code:
SELECT qryPriceMinV2.strProduct, qryPriceMinV2.MinOfcurPrice, tblCompany.strCompany
FROM qryPriceMinV2
INNER JOIN (tblCompany INNER JOIN tblPrice ON tblCompany.CompanyID = tblPrice.CompanyID)
ON (qryPriceMinV2.MinOfcurPrice = tblPrice.curPrice)
AND (qryPriceMinV2.ProductID = tblPrice.ProductID);
But I am assuming that you are looking for the minimum price. However, your thread title is "Query for Lowest 2nd lowest and 3rd lowest value" which doesn't seem to match the query result example that you provided.
At any rate, I am attaching an Access 97 file with examples of what I have discuss. If it isn't what you are looking for, please post back and I will try to help as my time and skill permit!
