Asset ID Table

[scouser]

I have decided to futher develop / normalize a db I have been working on for well over a year now (keep promising to post the updated version when complete!!).

I currently have a number of tables that may or may not require an Asset ID.

i.e.
tblComputers
tblSoftware
tblHardware

I have seperate tables:
tblComputerAssetID
tblSoftwareAssetID
tblHarwareAssetID

Can any one advise a better approach? If I have only 1 table tblAssetID, then I am unable to enforce referential integrity as values must exist in all related tables.
Thanks,
Phil.

PS: There is more but I will post back with further questions when I resolve the above!!

 

[scouser]

Asset Id

Hi Pat, it has been a while. I have been modifying other tables also. For instance I had:

tblComputerMakes
tblComputerModels
tblHardware

I used to store say Dell in both tblComputerMakes & tblHardware

I normalized to have a tblSupplier + tblProducts (have also done this for RAM & Operating Systems, all in individual tables now).

I have a table tblSoftware. This holds both application manufacturer & application type fields. I have thought about normalizing here also. So I would add Microsoft to tblSupplier then Office 2007 to tblProducts.

I have a slight issue though. I have a number of forms that use queries to look up values, for example frmCOMPUTER_ENTRY

I select Computer Make from a combo (value from tblVendor, just gave field on form a more descriptive name). Then when I select Computer Model the combo list is filtered to only display products for that vendor. If I normalize as above when I select 'Computer Make' it will display 'Microsoft' or Adobe' where as I would only like to see 'Dell' or 'Hewlett Packard' etc......

Also after normalizing tblHardware when I select Computer Model 'Dell' from the combo it lists all PC Models & Printer Models. Wanted to filter to jsut show Computer Models.....am working on an additional field to filter on (tblType). So PC is Type1 Laptop Type2 Server Type3 Printer Type4. However when I applied to query used on combo to lookup computer models (qryLOOKUP_COMPUTER_MODELS) it did not do as expected.

Any way as usual I waffle.

I will post a sample database as a picture paints a thousand words. When complete I WILL POST so others can beneift.
Thanks,
Phil.

 

[scouser]

Sample DB

I have attached a sample DB (access 2002-2003).

Ignore all the stuff that does not work ( I have changed a fair bit but have not updated).

So what to look at. tblAssetID and frmENTRY_ASSET_ID

It would be good if I could link the asset to the equipment. i.e. If I looked at Computers via ENTRY_COMPUTER_FORM there would be a field showing the asset? I thought of adding a Yes/No as not all will have asset?

Hope I am making sense!!

Phil.

 

[scouser]

AssetID

Any ideas out there regards best way to track Assets?

tblAssetID:
ID
AssetID
Type ID
Supplier ID
Product ID
Date Purchased

Example
AssetID = A0001
TypeID = 1 (Desktop PC)
SupplierID = 11 (Hewlett Packard)
ProductID = 38 (dc7700p)
Date Purchased = 01/01/2007

So the above table lists assets. I would like to link asset to say a Computer or piece of hardware or Software (tblComputers / tblHardware / tblSoftware)

So AssetID A0001 would be related to PC001 / AssetID 0002 would be related to HardwareID 5 etc.....

In another database version I NO tblAssetID. Instead have seperate tables (tblComputerAssetID, tblHardwareAssetID, tblSoftwareAssetID).

Any thoughts?
Regards,
Phil.