Linking two primary keys to the same foreign key

[Aenon]

Hey everyone!

I have an issue with building an asset tracking database. I will try to describe my situation as detailed as possible in hopes that someone can help me figure out how to solve my problems.

I have two asset types, Asset1 and Asset2, which each have their own serial number mask. I dont know if this will matter for what i am trying to achieveve, but lets call the masks "Asset-1-"#### and "Asset-2-"#####. Each of these assets have their own table because their serial no are built up differently and I thought it would be more tidy to seperate the tables. Now, what these assets do is transfer material. I wish to track how much is transferred with each asset on each project they are sent to. The projects each have their own project number which i store in yet another table, called "projects". In the "projects" table i insert a lookup column with the project number, a lookup column with the asset serial number, and a third column which lists the amount of material transfered by that asset on that project. My issue is that i would like to link the primay key in both the "Asset1" and "Asset2" tables to the same "asset" foreign key column in the "projects" table.

I have tried using the "Add Existing Fields" button on the "Fields and Columns" ribbon under the "Datasheet" tab and adding the "Asset1" ID in. To also include the "Asset2" ID i tried to modify the Raw Source by going into Design view, but my options in the cells in the "asset" lookup column in the "projects" table were screwed up. Either it listed single digits, or repeated the same serial numbers about 20 times before moving on the the next.

The simple solution would be to just have one table with all my assets regardless of type, but i was hoping i could split them up.

I will greatly appreciate all help and suggestions!

 

[spikepl]

Your structure needs rework

tblProjects
-----------
ProjectID (autonumber) PK
ProjectIdentifier (this one is for human consumption)
other project data

tblAssets
---------
AssetID (autonumber) PK
AssetSerialNo (your number )

if multiple assets can work on multiple projects, then you have a many-to-many relation

tblProjectTransfers
------------------
ProjectTransferID autonumber (PK)
ProjectID( FK)
AssetID (FK)
TransferAmount
other data relatiing to THis project and This Asset

and also, do not use Lookups in tables, http://access.mvps.org/access/lookupfields.htm

 

[Aenon]

Thanks for your reply spikepl! I do believe we have the same current solution to the problem, as a single asset table "tblAssets", with both types of assets would solve the problem. However, I was hoping i could keep a single table for each asset type. My relations would then be:

1. Asset1.AssetID (PK) ONE -----> MANY (FK) ProjectTransfers.Asset
2. Asset2.AssetID (PK) ONE -----> MANY (FK) ProjectTransfers.Asset
3. Project.ProjectID (PK) ONE -----> MANY (FK) ProjectTransfers.Project

Making the tblProjectTranfers the link in the many to many relationship, but with one foreign key linked to two different primary keys in two different tables. I suspect, however, that because you drew up the solution you did, that this is the actualy way i will have to do it.

A second query then, as I didnt really get the problem with lookup columns. The only way i know how to do that would be to directly link the autonumber ID which doesnt really say much. And without the tblProjectTransfers listing the actual project number and asset numbers it doesnt really serve any purpose. As a result, I do not know how to create good relations in my tables without using lookups, please help?

 

[spikepl]

Tables are for storing and managing data. Queries are for extracting it. Forms are for displaying info in user's language.

Relations between tables ara managed using the keys, but that is NOT supposed to be visible to users. If you need to show, eg a given AssetSerialNo, then in the Form where you display the results can have a combo box, that is bound to the AssetID in the underlying data. But, the rowsource of the combo would be something like SELECT AssetID , AssetSerialNo FROM tblAssets ... and it would to the user show the AssetSerialNo.

Users must not have direct access to tables - only via forms /reports.

How all this is done can be best illustrated by some examples. Download some smaller template from here http://office.microsoft.com/en-us/templates/CT010117248.aspx?av=ZAC and see how comboboxes are used to display human-readable values instead of keys

 

[Aenon]

Thanks again spikepl. Ill try to create the database as you describe with forms/reports as the only way to interact with my data. I just have to get better at access before i can create the database perfectly.