Hi All,

I am very new to Access but have a project to create a inventory with checkin/checkout system for our internal helpdesk.

The system needs to record basic hardware inventory (laptops,desktops, monitors, d-stations, k/b, mouse etc. We use asset numbers for laptops and desktops and each user (employee) has a unique user number. Stock items are checked out to users and checked in when device is retired or user leaves. We would nee to keep a history of each device , who it was checked out to, when etc. Finally the device is retired, either through being returned to the vendor at the end of the leasing period, or due to broken. We also have a number of devices that are used as a temporary loan, such as laptops while the users primary laptop is being repaired, or if they need an additional device for some testing.

I have setup tables as such:

employees
department codes
inventory
inventory transactions
suppliers
transaction types
user device subform

Are these enough to capture the required information for checkin/checkout? Would I need a separate table for the loaner or device retirement? I am also confused for the relationships and how they should be designed. My understanding is that I would need a many to many relationship to record multiple devices checked to employees? Also how would the checkin/checkout work? Attached is the relationship model i have setup.

I would really appreciate any comments or pointers for the design and thanks for reading this post.

There is a free existing data model for an IT inventory at
http://www.databaseanswers.org/data_models...ssets/index.htm

It may not be exactly what you are looking for, but may have sufficient detail to show relationships relevant to your project.

As for multiple assets assigned to multiple employees, Google "junction tables" for a discussion and example of solving the M:M relationship.

This video may be helpful to you
http://office.microsoft.com/en-us/access-h...=CH100739911033

This post has been edited by orange999: May 6 2012, 11:03 AM

The vast majority of your post is a red herring. For the sake of simplicity, I'm going to be ruthless and strip out all the excess stuff, because if you don't understand the core problem, you won't be able to solve it. If all you are doing in this part of the application is essentially a "loaner pool" type thing, then you don't need that many tables. Basically, you are modeling this: "Person borrows Item".

So you have a table for Person (or employee or whatever) - includes "Person/EmployeeID" as primary key. Then for Items, you have "ItemID" as primary key. It may be a parent record for subclassed items (printers, computers, etc). See Rebecca Riordan's article on subclassing on Access Web. www.mvps.org/Access. Then the rest is pretty simple.

Person---(1,M)---Borrows---(M,1)---Item

Borrows(PersonID, ItemID, OutDate, ReturnDate)


Then the rest is pretty straightforward... you may want to have a field in your Items table to indicate if an item is "out of service" just to simplify filtering etc.