Template for a Check-Out System?

[kelemvor]

I'm just wondering if anyone has or knows of a template I could download that would be for a Check Out system. We have some items in our PC lab that users can check out (laptops, power cords, etc) and we'd like some nice way to keep track of them and run reports to see what's in and out and such.

I saw the Inventory Management template on Microsoft's site which is sort of similar to what we are looking for but we're looking for something suited for a Library where you have specific predefined items with either a checked IN or checked OUT status and info on who has it.

Anyone know of anything?
Thanks.

 

[Pat Hartman]

I don't have any samples to offer but keywords like rental, lease, or library will probably produce better results than check-out. Many posters here are students and one of their frequent projects is a video store rental db. Search for that sample. It may get you started. Also the templates dealing with books/videos are probably more suitable than the inventory db.

 

[kelemvor]

I looked at some info on the Video Rental type things and they are actually way more advanced than we need.

Here's what I'm envisioning.

ITEM TABLE
ItemID
TagNumber
SerialNumber
Description
Status (Available / Checked Out)

RENTAL TABLE
RentalID
ItemID
UserID
FirstName
LastName
Action (Check In / Check Out)

We don't need to have any sort of customer table to track user info at all. We're just concerned about the items and who has/had them with a history.

So I'm thinking that with the above two tables, the Item table would be populated with all our items manually. Then we'd have some sort of form we could fill out and Select the Item from a dropdown box and then enter the user's info and have that create an entry in the Rental Table.

Then we could look at each item and pull in any history that had that Item ID and see who had each item, when they had it, etc.

Does that sould like it would work or am I missing anything?

 

[Pat Hartman]

Omitting the "customer" table does not simplify the application, it simply makes for more data entry and less integrity. Use the correct table schema. You have a one-to-many relationship between items and customers. The relation is implemented with a relation table. You need three tables.

tblCustomer
CustomerID (autonumber primary key)
FirstName
LastName
etc.

tblItem
ItemID (autonumber primary key)
ItemDescription
etc.

tblActivity
ItemID (primary key fld1, foreign key to tblItem)
DateOut (primary key fld2)
CustomerID (primary key fld3 - foreign key to tblCustomer)
DateIn

You don't need a flag in the Item table to identify Item status. You do it by joining to the Activity table. Having a flag would be redundant.

Select tblItem.ItemID, Max(tblActivity.DateOut) As CheckoutDate, IIf(IsNull(tblActivity.DateIn), "On Loan", "Available") as ItemStatus
from tblItem Left Join tblActivity On tblItem.ItemID = tblActivity.ItemID
Group by tblItem.ItemID, IIf(IsNull(tblActivity.DateIn), "On Loan", "Available);

The above query returns the last activity record for each item.

If you want to see a simplified example of a working many-to-many relationship that needs a date in the relation table, download my example db.

Many-to-Many