Can't figure out Access query

[luderbeck1]

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

[pbaldy]

Is this what you're after?

http://www.baldyweb.com/LastValue.htm

[luderbeck1]

Is this what you're after?

http://www.baldyweb.com/LastValue.htm

Almost. I still get multiple records for each item number because of the different costs and type of application. I just want 1 item number, the most recent date, cost, and type of application all associated to that item.


SELECT dbo_icytrn.*
FROM dbo_icytrn INNER JOIN qryMaxDates ON (dbo_icytrn.tdate=[qryMaxDates].MaxDate) AND (dbo_icytrn.Item=[qryMaxDates].Item);

[pbaldy]

Can you post a sample db here? Might help to see example data and your expected result.