I'm fairly new to this so sorry if I get some of the terminology wrong.  I'm trying to design a simple library system to keep track of hard drives in our company. These are often sent out of the office to collaborators/clients and we need to log their movements.   So far, I have three forms within the library application.

Form 1 - (Drive Catalogue) records the information about each individual drive (a unique ID number, the name of project it's being used on, whether it's a master or back-up drive, etc..)

Form 2 -  (Drive Check-out Log) - should be filled in every time someone sends a drive out of the office. Fields are:

DRIVE ID# [a Lookup field linked to form 1]

CHECK-OUT DATE/TIME

CHECKED OUT BY (Name)

SENT TO [the place it was sent to]

RECIPIENT'S NAME (if known)

COURIER REF (if sent by courier)

Form 3 (Drive Check-in log)  - should be filled in every time a drive comes back into the office. Fields are:

DRIVE ID# [a Lookup field linked to form 1]

CHECK-IN DATE/TIME

CHECKED OUT BY

The whole point of this library system is to be able to see easily where any given drive is at any given point in time..   

I want to create a "LOCATION" field in Form 1 that pulls data  from either Form 2 or Form 3, depending which was entered more recently - the latest check-in date or the latest check-out date. If a drive has been checked out but not yet checked in, I would like the LOCATION field to show the data from the "SENT TO" field in form 2. If a drive has been checked back into the office, I would like the LOCATION field to read "Office"

Does this make sense? Is this possible? If so, is it too complicated for a newbie to do? Would anyone be able post a link to instructions on how to set up something like this?

Thank you!