I have 2 tables. I need to be able to get the most recent item cost and from what method it was from (purchase order, credit, etc.). I get all sorts of jumbled and mixed up info. Tried Max, grouping, MostRecent and no luck. I just can't get it correct.
Here's what I have:
Table 1: dbo_icitem
Fields needed: item, itmdesc, and price
Linked with table 2 via item
Table 2: dbo_icytrn
Fields needed: tcost, tdate, appid (this is the filed that indicates how cost determined PO, AR, SO, IC).
When I run the query I get multiple entries for each item because it lists the most recent entry for each appid per item. I only want one entry - the most recent regardless of appid.
HELP!!
Thanks