Hi,

I have a table that contains the following fields:
production_Item_Id (autoinecrement)
lookup_key (numeric)
order_number (numeric)
item_date (date())

Each vehicle repair order might go through 10 production steps. As the vehicle enters for example the body shop they enter the date it went in. The lookup_key corresponds to a field in a lookup table that contains the name of the production step (body shop, mechanic, etc.) The date for each step on a repair order will be unique. As they enter a step, it is appended to the table.

I'm trying to retrieve the latest(lookup_key, order_number and item_date) for a report. If I have only 2 repair orders in the system, I should get only the two latest production items. I've tried the max aggregate on item_date but that only works if I only retrieve 2 fields (order_number, item_date). Add the third field (now you have to group by lookup_key and order_number) and you get all the rows again. I've tried DISTINCT, self joins but can't get it to work.

Any suggestions?

Thanks,

Marcel