Starting an inventory database

[Metty]

I'm trying to design a database in access that will allow me to track my computer inventory. Mainly, I want to track the PC and Monitor by serial number. I also want to assign each to a user (cubicle). I am not sure how to setup the tables and relationships in the best way possible. Any suggestions would be greatly appreciated.<br>
<br>
Thanks,<br>
<br>
Metty

 

[culshaja]

You need an inventory table, a staff table, and an equipment assignment table. You need a 1 to many relationship going from equipment to the equipment assignment table, and a one to many relationship on the staff to the equipment assignment realationship. You then need to build some business logic in to the database to allow a piece of equipment to be assigned only once - a status flag on the equipment table might be the way to go, coupled with assigment start and end dates on the equipment assignment table. I don't think that you can just use realtionships to inforce data integrity on this one, you'll have to use some code to inforce the rules.<br>
<br>
James <br>

 

[elizabeth]

You can avoid coding by creating an index on the serial number field in the &quot;equipment-assignment&quot; table and choosing &quot;Duplicates not allowed&quot;.<br>

 

[culshaja]

You would still have to do some coding on the 'assigned to' table as indexes won't always guarantee that it meets your business needs. What if you want to keep a history in that table, tats why you would have a one-to-many relationship. You could use a equipment, assignee, assigned date unique index but I still have this nagging feeling that things could slip through. I would like to find a way that does not need code though.<br>
<br>
james

 

[WP]

Why start from scratch ! This has been done many times before. There is even an ACCESS Wizard to create one.<br>
<br>
Other places of reference are some of the ACCESS sites that have sample databases.<br>
<br>
I'm sorry that I don't know of any sites off hand.<br>
<br>
Have a look at the ACCESS 97 Wizard.<br>
<br>
WP <p>Bill Paton<br><a href=mailto:wpaton@neptune400.co.uk>wpaton@neptune400.co.uk</a><br><a href=

http://www.neptune400.co.uk>www.neptune400.co.uk</a><br>

 

[Metty]

Thanks everyone for their help. I have searched for sample inventory databases, but couldn't find much. The wizard in Access for inventory databases creates one that has way too much detail than I am wishing. Here are most of the things I want to track:<br>
<br>
employee name<br>
employee cubicle<br>
employee dept.<br>
<br>
computer serial num<br>
computer make<br>
computer model<br>
RAM<br>
Hard drive size<br>
<br>
monitor serial num<br>
monitor make<br>
monitor model<br>
<br>
<br>
any suggestions on how to setup the tables would be greatly appreciated!!<br>
<br>
Thanks again,<br>
<br>
Metty

 

[MChancellor]

You might try giving each employee an ID number (if they aren't already using one) and use that number to link the inventory tables to the employee information.<br>
<br>
Have one table for the equipment, and one table for the employee information, and a table (sometimes called a &quot;junction&quot; table) containing the primary keys of both tables to link them together (based on the assumption that there could be more than one employee to a machine and more than one machine to an employee). This would create a one to many relationship between each of the main tables and the junction table. If you wanted to keep computer and monitor information in separate tables, you could create a table with computer information and one with monitor information, (rather than just an inventory table), and then include each of their keys in the junction table.<br>
<br>
Just a couple of ideas... hope it helps.<br>
M. Chancellor