Multimedia Inventory and Checkout

[ITChevyUSSNY]

I am on a US Navy Ship and we have a Library on board that lends all sorts of things to the troops. I have created a data base to track these items, I have a table for each different category of item Books, Movies, Games, CDs, and so on. I also have a table with all the crew on it with there email. Now what I want to do is create a form so I can select the username and select from our inventory of items what they checked out and have access track it. I know this i possible I just can't wrap my head around a solution. Anyone have any ideas?

[jzwp11]

I think it would be best to have all items in 1 table and then just have a field within the table to distinguish the category. I would still have a table to hold the various categories.

tblCategory (1 record for each category)
-pkCatID primary key, autonumber
-txtCatName


tblItems
-pkItemID primary key, autonumber
-txtItemNo
-fkCatID foreign key to tblCategory
-txtItemName

tblPeople (your crew members)
-pkPeopleID primary key, autonumber
-txtFName
-txtLName
-txtEMail

Since a person can check out many items and an item can be check out by many people over time, that describes 2 one-to-many relationships between the same two tables. When this happens, you have a many-to-many relationship which requires the use of a junction table as follows:

tblPeopleItems
-pkPeopleItemsID primary key, autonumber
-fkPeopleID foreign key to tblPeople
-fkItemID foreign key to tblItems

The above table associates the person and the item, now you have to track what happens with that combination. Assuming that you want to record when the person checks out the item and when they return it, that describes 2 events or transactions for each combination of person/item or a one (combination) to many (transactions) relationship.

tblPeopleItemTransactions
-pkPeoItemTransID primary key, autonumber
-fkPeopleItemID foreign key to tblPeopleItems
-dteTrans (transaction date)
-fkTransTypeID (transaction type) foreign key to tblTransactionTypes

tblTransactionTypes (2 records: checked in, checked out)
-pkTransTypeID primary key, autonumber
-txtTransType

In terms of forms, you would have a main form based on your crew members table (tblPeople in my example) with a subform based on the
tblPeopleItems. In that subform, you would have a combo box based on the items. Now for the combo box, I assume that you will want to select only from those items that are not already checked out, so the row source of the combo box will have to look at the items but also the transaction table to know which are in stock and which are not. So in other words, you will need a query to supply the list of available items to the combo box.

[ITChevyUSSNY]

Thanks for the info I am going ot give that a shot I will ahve to recreate the database but when I am done I will let you know how it went.

[jzwp11]

You're welcome. Please post back with any questions.

[ITChevyUSSNY]

Ok so I rebuilt the database like you said but I am not sure on how to build these forms. I have tried a couple times and failed miserably.

[RuralGuy]

Each Username has a 1:m relationship with inventory checked out. This is best displayed with a Form/SubForm arrangement with the Form bound to the Username and the SubForm displaying the many side of the relationship. Properly constructed, this arrangement will maintain the relationship for you and allow you to add or delete from the inventory assigned to the Username.