I need the last purchase cost

[UT227]

I'm trying to find the cost of ammunition by rounds. I need the last purchase only of certain types of ammunition. However, all of the purchases appear repeating the types of ammunition bought. For instance, I have CaliberID and CaliberTypeID (Primary Keys). If I have CaliberID=1 and CaliberTypeID=1, or CaliberID=1 and CaliberTypeID=2, I don't need the older purchases of those to come up with those same primary keys. I only need the last purchase with those primary keys. With the query I wrote, all of the purchases show up. I'm not sure what I'm doing wrong.

[June7]

You want the most recent record for each CaliberID and CaliberTypeID pair?

Review http://allenbrowne.com/subquery-01.html#TopN

Or try:

SELECT tblAmmoInv.* FROM tblAmmoInv INNER JOIN (SELECT CaliberID, CaliberTypeID, Max(ChangeDate) AS MaxDate FROM tblAmmoInv GROUP BY CaliberID, CaliberTypeID) AS Q ON tblAmmoInv.CaliberID = Q.CaliberID AND tblAmmoInv.CaliberTypeID = Q.CaliberTypeID AND tblAmmoInv.ChangeDate = Q.MaxDate;

[UT227]

Yes. But what was hanging me up was the cost. When I put the cost in, it would show all purchases. I took it out and made another query with the original table, and it worked.

[ArviLaanemets]

On fly:

SELECT inv.*
FROM
(Select invi00.CalibreID, inv00.CalibreTypeID, MAX(inv00.AmmoInvID) AS LastInvID FROM tblAmmoInv inv00 WHERE inv00.ChangeDate = (SELECT MAX(CahngeDate FROM tblAmmoInv WHERE CalibreID = inv00.CalibreID and InvTypeID = invoo.InvTypeID) GROUP BY invi00.CalibreID inv00.CalibreTypeID) inv0
LEFT JOIN tblAmmoInv inv ON inv.AmmoInvID = inv0.LastInvID