Link Tables in Database

[Ravi Sandhu]

Hi all

I am hoping you can help with this...

I have two tables in Microsoft Access



1.. Staff Information, and
2.. Training
In Staff Information I have fields such as:

a.. First name
b.. Surname
c.. Date of Birth
d.. Home Telephone Number
e.. Home Address
f.. (and more)
In Training I have fields such as:

a.. First Name
b.. Surname
c.. Training Received
d.. Training Result
e.. Trainer
f.. Training Duration
I want to create two separate forms.

I have create two forms, but can't get the 'relationships/links' to work..

I want the following to be achieved:



a.. One form to display/Enter the Staff Information and
b.. One form to Display/Enter Training Information
I also want it so that:

If:

a.. A new record is created in Staff Information,
b.. Then a record is automatically created in Training, with the same
fields (Same Fields are First Name, or surname ------ Fields such as
Training Received would be unpopulated)
Perhaps there is a tutorial, or a guide that can help me with this,
somewhere?

Thanks in advanced

 

[tina]

your tables are not normalized. it's not good to duplicate data - in this
case, first name and surname - in separate tables. before working on data
entry forms, you need to redesign your Training table (and possibly Staff
Information table)and set the relationship between the two tables. Note: if
you have spaces in your table and/or field names, suggest you remove them;
also, remove any special characters - except the underscore ( _ ), that's
okay.

tblStaffInformation: if you don't have a primary key field, you need to add
one. i'll call it StaffID.

tblTraining
TrainingID (if you don't already have a primary key field, add one)
StaffID (foreign key from tblStaffInformation)
TrainingReceived
TrainingResult

in the Relationships window, set a one-to-many relationship between the two
tables, on the StaffID field in each table. suggest you Enforce Referential
Integrity.
now you can express the relationship in a main form (tblStaffInformation) /
subform (tblTraining). build a form, and set its' RecordSource to
tblStaffInformation. build another form and set its' RecordSource to
tblTraining. open the main form in design view, and add a subform control.
set the control's SourceObject property to the name of the Training form.
Access should set the LinkChildFields property and the LinkMasterFields
property for you, automatically.
now when you open the main form, you can add/change/delete a Staff record.
to enter training information for a specific staff member, go to that Staff
record, then enter the training information in the subform. the two records
will be linked automatically, by the StaffID - you won't have to enter that
value manually in the subform.

hth

 

[Ravi Sandhu]

Brilliant

Thanks Tina

your tables are not normalized. it's not good to duplicate data - in this
case, first name and surname - in separate tables. before working on data
entry forms, you need to redesign your Training table (and possibly Staff
Information table)and set the relationship between the two tables. Note: if
you have spaces in your table and/or field names, suggest you remove them;
also, remove any special characters - except the underscore ( _ ), that's
okay.

tblStaffInformation: if you don't have a primary key field, you need to add
one. i'll call it StaffID.

tblTraining
TrainingID (if you don't already have a primary key field, add one)
StaffID (foreign key from tblStaffInformation)
TrainingReceived
TrainingResult

in the Relationships window, set a one-to-many relationship between the two
tables, on the StaffID field in each table. suggest you Enforce Referential
Integrity.
now you can express the relationship in a main form (tblStaffInformation) /
subform (tblTraining). build a form, and set its' RecordSource to
tblStaffInformation. build another form and set its' RecordSource to
tblTraining. open the main form in design view, and add a subform control.
set the control's SourceObject property to the name of the Training form.
Access should set the LinkChildFields property and the LinkMasterFields
property for you, automatically.
now when you open the main form, you can add/change/delete a Staff record.
to enter training information for a specific staff member, go to that Staff
record, then enter the training information in the subform. the two records
will be linked automatically, by the StaffID - you won't have to enter that
value manually in the subform.

hth