joining >1 table to the same foreign key

[Italian Pete]

Hi,
I'm trying to link a "loans" table to an "equipment to be loaned" table
.. I'm doing this via an intermediary table called "loan line" so as to avoid
the many to many relationship which would exist between the direct join of
the "loans" and "equipment" tables.

All well and good.

However, the thing has grown and I now have various different equipment
tables each having different fields (e.g. there is a "laptops" table with
fields for RAM, HDD Size etc and a "Mobile Phone" table with fields like
"SIM Card", "No. Bands" etc) I want to link all of these different equipment
tables to the loan table via the lookup table.

However, Access gets upset when I try to do this with Referential Integrity.
I think it would like me to have the one generic "equipment" table but I
can't do this due to the different types of fields rrequired for each type of
equipment.

How can I get round this problem.? I thought of having another table with
two fields: "Equipment ID" and "TableSource" with bothe fields being the
primary key. This table would tell me which specific equipment table to look
in for the required piece of equipment but how would I then link that to the
loans table?

Am I thinking in the right direction?

Any help much appreciated.

 

[tina]

perhaps we can tweak your tables a bit. suggest

tblLoans
LoanID (primary key)
whatever other fields you need to describe a loan

tblEquipment
EquipID (primary key)
EquipName (such as laptop, mobile phone, etc)
any other fields that describe a piece of equipment *generically*, such as
date purchased, cost, etc.

tblLoanDetails
LoanID (foreign key from tblLoans)
EquipID (foreign key from tblEquipment)
any other fields that describe that specific piece of equipment in that
specific loan (*not* generic info about the piece of equipment, and *not*
generic info about the loan)
you can use the two foreign keys as a combination primary key here, or you
can add a LoanDetailID field (probably an Autonumber) as a "surrogate"
primary key field.

tblLoans is the "one" side of a one-to-many relationship with
tblLoanDetails, and tblEquipment is the "one" side of a one-to-many
relationship with tblLoanDetails.

to record the specifics of each piece of equipment, use as many tables as
you need, such as

tblLaptops
LaptopID (primary key)
EquipID (foreign key from tblEquipment)
RAM
HDDSize
whatever other fields you need to describe a specific laptop computer

tblMobilePhones
MPhoneID (primary key)
EquipID (foreign key from tblEquipment)
SIMCard
BandCount
whatever other fields you need to describe a specific laptop computer

each of these tables will have a one-to-one relationship with tblEquipment,
and each piece of equipment will need to have a Details record in only the
one appropriate table.

hth

 

[Italian Pete]

tina,
It wouldn't accept a one to one join but would accept a one to
many. I've put in a few lines of test data and it seems to work like this. Is
this right?

 

[tina]

in the equipment details tables (tblLaptops, tblMobilePhones, etc), is the
EquipID field's Indexed property set to No, or to Yes (Duplicates OK) ?
if so, open the Relationships window and delete the one-to-many relationship
between tblEquipment and tblLaptops. then open tblLaptops in design view.
change the EquipID field's Indexed property to Yes (No Duplicates). close
and save. go back to the Relationships window and create the link again; it
should default to "one-to-one". if this works, do the same thing to all the
other equipment details tables.

your relationships should match reality, so it's worth fixing. i can't think
of any reason you would actually need "many" equipment details records for a
single piece of equipment - unless you're intending to keep a "history" of
changes to equipment parts (and i'd rather not open that can of worms!)

btw, in the example tables i posted earlier, i named the foreign key fields
the same as the primary key fields they're linked to. my mistake; i usually
don't do that because having the same field names in different tables
becomes confusing when working with queries, code, etc. instead, i add an
"fk" suffix to the foreign key field names, and a prefix to identify the
table, as

tblLaptops
LaptopID (primary key)
LEquipIDfk (foreign key from tblEquipment)

tblMobilePhones
MPhoneID (primary key)
MPEquipIDfk (foreign key from tblEquipment)

that way all the field names in your database are unique, and you can tell
at a glance which table a foreign key field belongs to. sorry i didn't
include this originally, hope it's not too confusing.

hth