Linking more than one table to a table 1

[Wobert]

Hi there,

Maybe a silly question but here I go:

I have a table that I want to link to various other tables depending on the data in a field. Is this possible? If so how?

Cheers,

Robert Canavan.

 

[LittleSmudge]

Yes possible

I'd go as far as to say VITAL

Any two tables can be linked together in a One-to-Many relationship

Eg two tables for Houses and People that live in them

tblHouse Table Name
HouseId Table's Primary Key
Address1
Address2
Town
County
Postcode


tblPerson Table Name
PersonId Prime Key
FirstName
Surname
DoB

Assume here a person ONLY lives in one house but a house can have many occupants so we have a One-to-Many relationship between House and Person
So we need to add a field in the tblPerson called a Foreign Key to hold the information about which house they live in. Call this field HouseRef ( Useful convention : Foreign Key is name of PrimeKey linked to it but with Id replaced by Ref )

So tblPerson becomes
tblPerson
PersonId
HouseRef
FirstName
Surname
DoB

So for each person's record the HouseRef field will contain the value of the HouseId for the house that they live in.

You can have as many tables hanging off tblHouse as you like

Eg List of Cars and which household they belong to
tblCar
RegNo
HouseRef
Make
Model
Colour

Similarly with Pets, Bicycles etc

You can then have a tblEmployer to record who all of the people in tblPerson work for

You'll need to add an EmployerRef to the tblPerson to do this

tblPerson
PersonId
HouseRef
FirstName
Surname
DoB
EmployerRef

For kids - a table containing information about which school they go to - tblSchool
And tblPerson will then need a SchoolRef

tblPerson
PersonId
HouseRef
FirstName
Surname
DoB
EmployerRef
SchoolRef

etc.. ..
etc..
etc.

In the last two cases unemployed and non-school age people will have Null in the EmployerRef and SchoolRef respectivly. -



'ope-that-'elps.


G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.