Report only the most current record a report grouping

[guytarson]

I am trying to produce a report to list items we track through our company. Each item's history is recorded. I can do a simple report that shows a given items history with the header displaying the most recent location. When I try doing the report to only show the most current location, the info in the report is incorrect. I have to include ever movement of the item in the "Detail" section of the report to get correct information in the header. With that said... I just want a report that will show me the most current location of said items not their history. I hope this makes sense and you can help. Thank you in advance for any help.

 

[lupins46]

So how do you identify 'most recent'?

 

[GingerR]

Do you have a Date field or Record ID field that determines which is "last"? If so you can then use a totals query and group on MAX of that field.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[guytarson]

Each items is barcoded. Each barcoded item is connected to a job number. Each barcoded item's transaction is date stamped as it moves through the system to each station it must pass. The grouping in the report is based on job number. The header reflects the last station location (grouping = JOB NUMBER sorted desending. Header info is the last transaction (current location) for that "JOB". I just want to report the current location of a job.

CURRENT LOCATION = LAST TRANSACTION FOR JOB

I hope this is clearer.

 

[guytarson]

GingerR
I have a autonumber column running in the "History" table for each JOB'S transaction. The report is based on the transactions in the HISTORY table.

 

[GingerR]

Try a TOTALS query, grouping on MAX.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[guytarson]

Thanks GingerR!
Sorry for the slow reply (weekend worrior here)
The Totals Query worked fine with little ajustment.
thanks again for all your help!