Unanswered: Problem with LAST aggregate in a query
I have a requisition database for requesting and tracking parts ordered. When I first set the db up, I pre-populated a History table with quite a large amount of previous data (from paper records which didn't include some of the information now required). For this reason, whenever I am searching previous orders, I merge the information from the History table with that of my active Order_List table in a query. This master query has every part ordered and is sorted by Company, PartNumber, DateOrdered
Because of the amount of previous orders, I group the information into parts in another query, which then shows how many times each part has been ordered. You can then expand a part to see all the orders of that part in a separate form. The field I am having trouble with is my LastCost field. Basically, I want to display the last price paid for each part (not necessarily the most expensive, as some things actually get cheaper!!). I am trying to use the LAST aggregate total in my second query - ie Last([Cost]), but it doesn't return the last value that the parent query has listed (ie in date order). This is driving me mad!!
Can anyone advise on a way around this?
Is it possible, say, in the criteria of the second query for the LastCost field to base it on the Max of the DateOrdered?