asked on Find minimum cost for records
i have the following table called tblPNPrice and am looking for the lowest cost for the PN_id
tblPNPrice
idn PN_id Cost
1 123 $1.20
2 154 $7.23
3 123 $2.15
4 278 $11.78
5 189 $63.75
6 278 $15.11
so that the result would be:
qryPNPrice_MinCost
PN_id MinCost
123 $1.20
154 $7.23
278 $11.78
189 $63.75
thank you!
tblPNPrice
idn PN_id Cost
1 123 $1.20
2 154 $7.23
3 123 $2.15
4 278 $11.78
5 189 $63.75
6 278 $15.11
so that the result would be:
qryPNPrice_MinCost
PN_id MinCost
123 $1.20
154 $7.23
278 $11.78
189 $63.75
thank you!
Microsoft Access
Last Comment
Patrick Matthews
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
matthews was the simplest to follow and worked right off the bat...
quick followup question...
is it possible to get a result of:
qryPNPrice_MinCost
idn PN_id MinCost
1 123 $1.20
2 154 $7.23
4 278 $11.78
5 189 $63.75
when i try to include the idn in the query it messes up the query and ALL records are shown instead of just the minimum cost for PN_id
quick followup question...
is it possible to get a result of:
qryPNPrice_MinCost
idn PN_id MinCost
1 123 $1.20
2 154 $7.23
4 278 $11.78
5 189 $63.75
when i try to include the idn in the query it messes up the query and ALL records are shown instead of just the minimum cost for PN_id
SELECT t1.idn, t1.PN_id, t1.Cost
FROM tblPNPrice t1 INNER JOIN
(SELECT t2.PN_id, Min(t2.Cost) AS MinCost
FROM tblPNPrice t2
GROUP BY t2.PN_id) AS x ON t1.PN_id = x.PN_id And t1.Cost = x.MinCost
ORDER BY t1.idn, t1.PN_id
SELECT blPNPrice.PN_id, Min(blPNPrice.Cost) AS MinOff2
FROM blPNPrice
GROUP BY blPNPrice.fPN_id;