Linking more than 2 tables together

[Griffij]

I was a wondering if anyone could help me in the quest
for understanding.

I Have a database which is supposed to store on
employees. with three tables:

Personnel Information Table
Emergency Information Table
Employment Information Table

What I am tryng to do is ensure that I get the
relationships right.

What I am wanting to achieive is when the tables are made
into forms I want to be able to click on any of the forms
and be able to access that form with the related data
being attached.

I would say the above tables al have a one to one
relationship becacuase one Person can oly have one
Employment and Emergency information record.

Let me demonstrate:

Personal Information Emergency Information Employment Info

PKEmployNatINS ----- PKEmployNatINS ----- PKEmployNatINS
Employdob EmployRelationship EmpJobTitle
Employsurname EmployRelSurname EmployJobStart
Employfirstname EmployRelFirstnme EmployJobEnd
Employtitle EmployRelTitle EmployJobNotes
Employhousename EmployRelhousename
Employaddress Employaddress
Employtown EmployReltown
Employcity EmployRelcity
Employcounty EmployRelcounty
Employpostcode EmployRelpostcode
Empoloytelnumber EmpoloyReltelnumber
Employmobile Employmobile
Employsecondtelnumber EmployRelMobile
Employfaxnumber EmployRelCompany
Employemail EmployRelCompanyNumber
Employnotes EmployRelExtensionNumber
EmployRelnotes


The Above is my tables I would like to link them together
so there are connected together.
Please help regards Joel

 

[Steve Schapel]

Joel,

The way these tables are designed at the moment, means you can only
show one Emergency record for each Employee, and one Employment record
for each Employee. Is this what you intend? So if a person changes
jobs within the company, you don't want to retain a historical record?
If so, there is no reason to keep this information in separate tables.
Combine the three tables into one table. You can still use different
forms for different purposes, i.e. by only showing some of the fields
from the table as applicable.

- Steve Schapel, Microsoft Access MVP

 

[Guest]

Dear Steven you have a great point their I will make
Employment information a one to many relationship thanks

I would Make the link from Employee Personnel Informaton
to Employee Employment a One to Many Relationship by
makinkin the Emplnatins a Foreign key in the Employment
information table and creat an EmploymentID as the new PK.

The Question Still remains on how I link the Employee
Emergency Information to the Employee Personnel
Information?

My thought on that where to make it a one to one
relationship as it is now. because an Employye Private
information is unlikly to change.

Regards Joel

The Question

 

[John Vinson]

The Question Still remains on how I link the Employee
Emergency Information to the Employee Personnel
Information?
My thought on that where to make it a one to one
relationship as it is now. because an Employye Private
information is unlikly to change.

And you will never have more than one Emergency Information record? If
not, as Steve suggests - just put that information into the Personnel
Information table!

If you really want a one to one relationship, store the EmployeeID in
both tables as the primary key and create a relationship from
EmployeeID to EmployeeID.

To enter data you would typically use a Form based on the Personnel
Information table, with Subforms on it based on the other two tables.

 

[Griffij]

I completely understand what you are saying and I thank
you for your input. When the user is in any form what I
wish for them to be able to do is click on any of the
forms and that it take them there I do not want a preset
route.

I want to link all the tables to each other to anabler
the user to form hoping.

Many thanks Joel

 

[John Vinson]

I completely understand what you are saying and I thank
you for your input. When the user is in any form what I
wish for them to be able to do is click on any of the
forms and that it take them there I do not want a preset
route.

I want to link all the tables to each other to anabler
the user to form hoping.

The Relationships between tables have ABSOLUTELY NOTHING TO DO with
form navigation. They are altogether separate problems!

Table relationships should be based on logical connections between the
entities represented by the tables.

Form navigation would best be done with VBA code - you could, for
instance, have an unbound Listbox or Option Group on your form's
header with a list of all the forms to which the user can navigate,
with code in its AfterUpdate event to open the selected form.

 

[Guest]

Dear John would it be possible to email you direct

Joel

 

[John Vinson]

Dear John would it be possible to email you direct

Certainly, at my standard consulting fees; should I send you a resume
and my terms?

I'm a self-employed consultant, volunteering my time on these
newsgroups. As such I must reserve private EMail support for paying
customers.

If you're interested in paid consulting, my email address is jvinson
<at> WysardOfInfo <dot> com. If you'ld like free support, I'd suggest
explaining a bit more what you're trying to accomplish; I or one of
the many other volunteers here should be able to help.