I have a report that lists all of the inventory items in a table. No
biggy. Now I need to add the last purchased date column to the report. I

have a table ictran that contains a record for every purchase for each
item. I've figured out the query "SELECT stockno, MAX(tdate) FROM
ictran  GROUP BY stockno ORDER BY stockno" that will give me the most
recent purchase date for each item. Now, how do I alter the Query
attached to the
report to use this query?

Or is there a way to rewrite the main query to include this column?

Report:
item no.   description         cost   last purchased
--------   ---------------   -----   -------------
abc1       some part           1.99    1/22/2002

Main query:
SELECT i.stockno,i.descript,i.onhand,i.unitcost,e.name AS equipment,
i.location,c.name AS classification,as.tdate
FROM items i LEFT JOIN equipment e ON i.equip_id = e.id
LEFT JOIN classification c ON i.class_id = c.id
LEFT JOIN datasource1 as on i.stockno = as.stockno
ORDER BY 5,7,1

- Gary

Subselect the main + subselect the MAX query, join them on stock_no and
select fields you need. Something like:

    select a.stock_no, a.description, a.unitcost, b.lastpurchased
    from items a
    inner join ( select stock_no, max( tdate )
                    from ictran
                    group by stock_no) on a.stock_no = b.stock_no
    order by a.description

rb

news:3C4E17C0.334AA452@edisoninfo.com...