Selecting Minimum Value

  • Thread starter Thread starter jsccorps
  • Start date Start date
J

jsccorps

For three vendors (A, B, C), need to find minimum price based on Product ID
(PID). For same price, use VID A’s price.

Before:
VID PID Price
A 1 9.88
B 1 8.33
C 1 7.87
A 3 2.79
B 3 4.89
C 5 12.87
A 5 14.31
B 5 5.36
B 8 14.73
A 8 14.73
C 8 14.73
A 9 2.22
C 10 3.33
B 11 4.44

After:
VID PID Price
C 1 7.87
A 3 2.79
B 5 5.36
A 8 14.73
A A 2.22
C B 3.33
B B 4.44
 
Correction for After:

After:
VID PID Price
C 1 7.87
A 3 2.79
B 5 5.36
A 8 14.73
A 9 2.22
C 10 3.33
B 11 4.44
 
You can try the following.


Query to get the lowest price and associated vendor(s)
SELECT VID, PID, Price
FROM tblPrices INNER JOIN
(SELECT PID, Min(Price) as LowPrice
FROM tblPrices
Group By PID)
As qLprice
ON tblPrices.PID = qlPrice.PID
AND tblPrices.Price = qLPrice.LowPrice

Use that saved query as the source for this query
SELECT VID, PID, Price
FROM QSaved
WHERE qSaved.VID =
(SELECT Top 1 Temp.VID
FROM qSaved as temp
WHERE Temp.Price = qSaved.Price
AND TempPID = qSaved.PID
ORDER BY temp.VID)

If one vendor has the lowest price this will return that vendor.
In case of ties for the lowest price, the first vendor with that lowest
price in alpha order will be returned.

This will return Vendor A if A has the lowest price.
If Vendor B and C have the lowest price this will return B


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
For three vendors (A, B, C), need to find minimum price based on Product ID
(PID). For same price, use VID A’s price.

Before:
VID PID Price
A 1 9.88
B 1 8.33
C 1 7.87
A 3 2.79
B 3 4.89
C 5 12.87
A 5 14.31
B 5 5.36
B 8 14.73
A 8 14.73
C 8 14.73
A 9 2.22
C 10 3.33
B 11 4.44

After:
VID PID Price
C 1 7.87
A 3 2.79
B 5 5.36
A 8 14.73
A A 2.22
C B 3.33
B B 4.44
Click to expand...

A Totals query will do this:

SELECT VID, PID, Min([Price])
FROM tablename
GROUP BY PID, VID;
 
Thanks for the response.

Getting error message: " The specified PID could refer to more than one
table listed in the FROM clause of the SQL statement"
 
I should have specified the table along with the fields in the SELECT
clause.

SELECT tblPrices.VID, tblPrices.PID, tblPrices.Price
FROM tblPrices INNER JOIN
(SELECT PID, Min(Price) as LowPrice
FROM tblPrices
Group By PID)
As qLprice
ON tblPrices.PID = qlPrice.PID
AND tblPrices.Price = qLPrice.LowPrice

SELECT QSaved.VID, QSaved.PID, QSaved.Price
FROM QSaved
WHERE qSaved.VID =
(SELECT Top 1 Temp.VID
FROM qSaved as temp
WHERE Temp.Price = qSaved.Price
AND TempPID = qSaved.PID
ORDER BY temp.VID)
'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
I just realized in the shower this AM, that you could probably use this
one query to get the desired result. AND it might be faster.

SELECT Min(tblPrices.VID) as Vendor
, tblprices.PID, tblPrices.Price
FROM tblPrices INNER JOIN
(SELECT PID, Min(Price) as LowPrice
FROM tblPrices
Group By PID)
As qLprice
ON tblPrices.PID = qlPrice.PID
AND tblPrices.Price = qLPrice.LowPrice
GROUP BY tblprices.PID, tblPrices.Price

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
Back
Top