Query to show only most recent

[rvil460]

Hello,

I have a transaction data table where information is put in through a form. The transaction data table includes: Date, Equipment Number, Category, Pick up Location, Pick up Job Number, Current Location, and Current Job Number. This information is typed in daily, and the same equipment number is used several types as it moves from location to location. I need to create a report that shows the Current Location of an Equipment Number, but when I do there are Equipment Numbers that show up in multiple locations. How can I create a query and report that will filter the Equipment Number duplicates to show only the most recent Current Location, and also show Equipment Numbers that are not duplicates?

 

[pbaldy]

Does this technique work for you?

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

 

[MarkK]

It looks suspicious to me that you have pick-up and current locations in the same record. Is it so that for a piece of equipment the pick-up location is the same as the previous current location, and if so you don't need both.

 

[rvil460]

Lagbolt,

Yes, that's the reason for having both, and now that you mention it, it does seem a little redundant. However, if I don't have the pick up field can I still be able to see where the equipment has been?

Pbaldy,

I think that solution would be suitable, but I am new to Access and to SQL so I'm not sure what to substitute the example fields with.

I really appreciate both of your replies.

 

[rvil460]

Pbaldy,

I figured out what to put in place of your example, and it worked.

But, now I have come to another problem. If the same piece of equipment moves multiple times in on day, it still shows in all the locations rather than just the most recent. I'm pretty sure it's because the MaxDate is the same...since it moved the same day. Is there anything I can do to filter it even more?

Thank you very much

 

[pbaldy]

I would probably including the time, so the date field would include both the date and time. If you're using Date() to populate it, changing that to Now() would include the time.