Asset form with multiple combo box criteria

[eburgtech]

I have an asset tracking database that I've made and it works wonderfully now. Currently I have one form that has all fields for a workstation (pc, monitors, printers, etc.) and it's all tied to a user on the Users table. This is fine but as I got to thinking about it, it would be a better design if I had separate tables for each type of asset (pc table, monitors table, etc.) and then tied that to a user on a form since it's possible that a monitor may be switched with someone or a printer, etc. I've scoured the intarwebz and tried many things and I'm not sure if it's possible. Anyone have any ideas to offer? I can upload a sample db if necessary. Any help would be great.

 

[Steve R.]

Its a question of normalizing your database. As you indicated, you need to be able to move equipment from one workstation to another and keep track of it. Normalizing your database will solve that problem

 

[boblarson]

And Steve's response INCLUDES that you don't make a single table and you don't have separate tables for each piece of equipment.

 

[eburgtech]

Honestly, I thought I had it pretty normalized. But I am self-taught and maybe I didn't understand the documentation I read on normalization.

I'm using Access 2003 SP3. I've attached a copy of the db. Currently everything resides on the Assets table. I put the brokedown tables I want to use in there as well. Is there a way on one form to pull from each of those tables?

 

[eburgtech]

Bob, that's confusing. Don't make a single table and don't make multiple tables...then what would I make?

 

[boblarson]

Bob, that's confusing. Don't make a single table and don't make multiple tables...then what would I make?

No, you didn't read it properly. You don't have a SINGLE table to store everything. But you don't have a table for each piece of equipment.

I was searching for the Asset Management sample that is somewhere around in the forum but haven't found it yet.

A quick and simple structure (not saying this is complete, mind you) is:

Equipment Table
EquipID - Autonumber (PK)
EquipDesc - Text
AcquireDate - Date


EquipmentAttributes (junction table)
EquipmentAttributesID - Autonumber (PK)
EquipID - Long Integer (FK)
AttributeID - Long Integer (FK)

Attributes Table
AttributeID - Autonumber (PK)
AttributeDescr - Text

Employees Table
EmpID - Autonumber (PK)
LName - Text
FName - Text


EquipmentAssignments Table
EquipmentAssignmentID - Autonumber (PK)
EquipID - Long Integer (FK)
EmpID - Long Integer (FK)
AssignDate - Date


And that is just a quick, simple sample to show you approximately how you can do it and then have any number of attributes for a piece of equipment depending on what it is.

 

[eburgtech]

Thanks Bob. I'm working on normalizing my database. I've saved a copy of what I have so I don't have to reinvent the wheel. I'm making a new database with new tables and once I get it functioning the way I want it, I'm going to import the data from from my saved original. Thanks again. )