Asset ID Table / Tables

[scouser]

Hi guys. I have been attempting to add an asset tracking function to my database. I have a table 'tblAssetIdList' which holds all the available asset ID's. Asset ID's can be allocated to either Computers from tblComputers, Software from tblSoftware and finally Hardware from tblHardware.

I initially created additional tables to hold details of the allocations: tblComputerAssetIdAllocation, tblHardwareAssetIdAllocation & tblSoftwareAssetIdAllocation. I then created queries from the above tables and then forms from the queries to allow for data input.

The one flaw (quite obvious really) is that asset ID's should be unique. Although unique Asset ID's are required in the individual tables I am able to allocated the same Asset ID to the different tables. (i.e. A20222 can be allocated to only one computer in tblComputers but can also be allocated to both Hardware & Software once).

I then tried creating a single table tblAssetIdAllocation with fields:

ID
AssetID
ComputerID
SofwareID
HardwareID

These linked to the appropiate tables. However if I allocated an asset ID for computers then the same asset id was then allocated to hardware & software.

I must be close (only taken 2 days to get it wrong!! )
Help Appreciated,
Phil.

 

[Dennisk]

What you can do in queries is to set a constant, either in a column of its own or concatenated to an ID.

So if you uses a unnio quiery to view hardware Assets and Software Assets you could use something like this

SELECT "S" as Code, ID from tblSoftware
UNION ALL
SELECT "C" as Code, ID from tblComputers

 

[DRathbone]

I've just undertaken a very similar project, designing the tables using normalisation was a crucial process, I had a Asset_general table with AssetID as primary key - within that table are a list of defined fields that computers, software and hardware share such as 'manufactuer', 'purchase date','order number','addedby' then in computer/software/hardware table a AssetID as a number with referential integerity to asset_general. then within the computer/software/hardware tables their are fields which are more specfic for each catagory.

 

[scouser]

Thanks

Thanks for the replies. I will have a play & post back.
regards,
Phil.

 

[scouser]

Sample DB

I have implemented DRathbone suggestion (see attached) as I was unsure regards the query option

Issue: I would like to make it so users can only assign an AssetID once and once only. So if they assign AssetID A20100 to Computer A they are unable to then assign the same AssetID to Harware A or Software A.

Any questions regards the sample DB post back.
DB created in Access 2000 format.
Thanks for your help,
Phil.

Update: Sample database removed 14/03/2007.

 

[scouser]

Resolved

I have resolved this issue (to suit my requirements at least!!).
I will post the database in the appropiate forum for others to make use of once the project is complete.
Thanks for your help,
Phil.