Asset List Database

[nhmy]

I have a table of staff and various tables f all the different types of assets. I would like to build a relationship between the staff and the assets deployed to the staffs. The primary key would either be the User ID or User Name. The outcome is to list all of the assets deployed to the staff in a form. We would search using the primary key.

Please help.

[davegri]

Your design is wrong. You don't need separate tables for each type of asset. All assets should be in same table with an AssetType_ID that links into a new table, tblAssetType.

[orange]

You might get some ideas from this generic data model from Barry Williams' site.
Good luck

[davegri]

Here's a sample of your tables consolidated. Added Primary and foreign keys. Set up the relationship diagram. Included a couple of queries to show how it ties together.

AssetMasterList-davegri-v01.zip

[davegri]

Update to above. Added form/subform with junction table to hold the assignments.

AssetMasterList-davegri-v02.zip