Update checkbox on all but most recent Transaction

[8l2255]

I have a TransactionsTbl which records which Item is with which User
(TransactionID, ItemID and UserID). The problem I am having is that when a
new Transaction is created and an Item goes to a new user there is no way to
differentiate which transaction is the most recent when it is searched via
User.
*I still want to record the transfer history of each item - just have ONLY
the newest Transaction for each item marked with a checkbox on.

I am trying to create an update query that run will when I create a new
transaction. The update query should find all the older Transactions for the
particular item and render the checkbox off when the New Transaction is saved
(with the checkbox set on).

Any help or advice on how to better organise this data is welcome!

 

[Jeff Boyce]

If you want to track items over time, you need to record the time, also.
Where's your date/time field?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.