Test Library Design

[P5C768]

I'm looking for advice on how to design a library database for tracking test materials. It needs to work like a lending library, which allows users to see current inventory and then check-out or check-in tests.



I have already created a table that has the master inventory list, including a TestCode (similar to ISBN) and a TestID, which specifices the individual copy of the test. I would like users to be able to check-out or check-in mulitple copies based on availability.

However, I'm stuck on the best way to do this. I started with a check-out table, but that needs to draw from a current inventory table, which needs to draw from the check-out and a check-in table.

Is there a simple way to do this through a union query or another method? Any advice is greatly appreciated.

[ConneXionLost]

Start with a basic data model:

Basic Library System

Cheers,