# 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!
Microsoft Access

Last Comment
Patrick Matthews

8/22/2022 - Mon
Patrick Matthews

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.
babesia

Do a group by Query

SELECT blPNPrice.PN_id, Min(blPNPrice.Cost) AS MinOff2
FROM blPNPrice
GROUP BY blPNPrice.fPN_id;
Jose

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
Patrick Matthews

``````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
``````