Tabe linking

[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,

Here are a few pointers that might help...

1. If you only want to store one employment record per employee (i.e.
if the person changes jobs, the new "Employment Information" record
will replace the old, and if you only ever want to store one emergency
contact per employee... then, there is absolutely no reason at all to
use three separate tables. All the information should be in one
table.

2. If you do insist on doing it the hard way by using three separate
tables, you are correct that they will ber related via the
PKEmployNatINS field in each. If this field is an autonumber data
type in one of the tables, it will need to be a number data type in
the other two.

3. If you make three separate forms based on these tables, and there
is data already entered in the three tables for an employee, then you
will be able to link from one form to another for the same employee,
by using code similar to...
DoCmd.OpenForm "OtherForm", , , "PKEmployNatINS=" & Me.PKEmployNatINS
However, for your initial data entry, you will have to enter the
PKEmployNatINS manually, or set up some routines to make sure this is
done. The alterrnative is to use the Personal Information form as a
main form, and place the other two forms on it as subforms.

- Steve Schapel, Microsoft Access MVP

 

[Griffij]

I would like to thank you for the help that you have shown their.



I would however like to clear a few things up if I may? PKEmpNatIns is a
text field, a mixutre of text and numbers.



Question 1. If I was to create three seperate tables, for them how do I
go about filling in data in all three forms, I do not wish to enter
EmpNatIns three times. so how would I link all three table?



Question 2. I am beging to see that creating a Personal form then having
two sub forms coming off that one may be easier. How do I create the sub
forms to look like they are parent ones. I have already made up those
forms how do I go about turning them in to sub Forms, or do I need to
create new ones



Question 3. In order so I do not have to bug you guys forever would you
be able to commend a book that I could reacd or study that would show me
how relationships work as I need a real case study to work on.



Many thanks Joel