Display most recent entry when multiple entries for an item exist

[GregP]

Display most recent record when multiple records for an item exist

Hi,

I have a table which stores a list of all our loan computers and their loan history. Each time a computer is borrowed, a new entry is added to this table, and when it's returned, a checkbox is cleared to show it's no longer in use. Potentially, there could be many entries in this table for the same computer, but only one of these entries could ever have a tick to indicate 'borrowed'.

Now, what I want to be able to do is to generate a query which shows the most recent entry in the table for each computer, from which I can see its loan status. At the moment I'm seeing each entry for each computer. How do I do this?

As a sideline issue, how do I ensure that each computer only has one of its 'loaned?' fields ticked? At the moment there's nothing to stop operator error from issuing it to multiple borrowers.

Thanks!

Greg

 

[Len Boorman]

You could find this a little tricky. A while ago I did a database for a departmental library so problems are similar.

Basically I used a separate table to record items on loan

Primary key was Item loaned. Then if an item was already on loan you could not loan it out again without getting a primary Key Duplication error.

The loan out data was included and also a return data.

After return data entered I ran a couple of queries. First copied Item, date out and date in to a history table and second deleted the entry from the loan table.

This then meant I had a history of loans and also the item could be loaned again because PK would not be duplicated.

Needs a bit of thinking about but you can set up situations whereby

Only items not on loan are selectable for loaning out.
You can review loan status of an item by looking at Loan table and history table.

Hope this gives you some ideas

Len B