Linking tables in a one-to-many relationship

[jamilian]

Hi

I have a problem with setting up relationships to enable me to view information in subforms.

I followed the Help in MS Access,
'Create a form that contains a subform (a one-to-many form)'
but I cannot get it to work, even though the what I am trying to do is exactly the same as in the example in Help.

As I only want to enter the qualifications once, I created the qualifications table for that.
I also have a table with employee contact details. Each qualification in the qualifications table could be linked to one or more employees if they have the same qualifications.

Therefore, I need to know how to achive the following:
1. Correctly create the one-to-many relationship.
2. Create a subform from the employee details form that will permit me to view and associate the qualifications to each employee.

I've tried this is several ways and am almost there but must be missing something. For example, from the employee contact form, I can create a subform of qualifications. I can enter data but I want to be able to list and choose the qualifications to associate to each employee from the qualifications table.

I'm new to this and am usng the Help guide to get me through. The example they give is exactly the same as what I am trying to achieve but with different data but it doesn't show how the table relationships are set up. Any ideas.

 

[HiTechCoach]

Hi

I have a problem with setting up relationships to enable me to view information in subforms.

I followed the Help in MS Access,
'Create a form that contains a subform (a one-to-many form)'
but I cannot get it to work, even though the what I am trying to do is exactly the same as in the example in Help.

As I only want to enter the qualifications once, I created the qualifications table for that.
I also have a table with employee contact details. Each qualification in the qualifications table could be linked to one or more employees if they have the same qualifications.

Therefore, I need to know how to achive the following:
1. Correctly create the one-to-many relationship.
2. Create a subform from the employee details form that will permit me to view and associate the qualifications to each employee.

I've tried this is several ways and am almost there but must be missing something. For example, from the employee contact form, I can create a subform of qualifications. I can enter data but I want to be able to list and choose the qualifications to associate to each employee from the qualifications table.

I'm new to this and am usng the Help guide to get me through. The example they give is exactly the same as what I am trying to achieve but with different data but it doesn't show how the table relationships are set up. Any ideas.

You will need a third table that is a junction table between the employee and the qualifications. It is this table that will be displayed in the sub form.

Example:

tblEmployeequalifications
- EmployeequalificationsID - autonumber primary key
- EmployeeID - foreign key - link to employees table
- QualificationsID - foreign key - link to Qualificationstable

If you are showing thistable in a form on the employees form, you would use EmployeeID as the master/child linking fields. I would use a combo box to select the QualificationsID.

 

[jamilian]

Hi

I followed your recommendations and think this is working...to some extent but not sure it is working 100%.

The problem I encounter now is the following.
On the form, I can enter new employee profiles etc and it registers in the fields. However, when I close and re-open the contact details form, it does not show the data previously entered, it only allows me to enter new information.

I've attached the following screenshots:

1. relationships
2. contact details form and table -> here you can see the problem
3. employeequalifications table
4. qualifications table.

Current Attachments (408.1 KB)

contact details.jpg (101.6 KB)

Employeequalifications.jpg (100.9 KB)

form.jpg (82.3 KB)

qualifications.jpg (100.7 KB)

relationships.png (22.6 KB)


I appreciate your help, I feel that I'm almost there

 

[HiTechCoach]

Hi

I followed your recommendations and think this is working...to some extent but not sure it is working 100%.

The problem I encounter now is the following.
On the form, I can enter new employee profiles etc and it registers in the fields. However, when I close and re-open the contact details form, it does not show the data previously entered, it only allows me to enter new information.

I've attached the following screenshots:

1. relationships
2. contact details form and table -> here you can see the problem
3. employeequalifications table
4. qualifications table.

Current Attachments (408.1 KB)

contact details.jpg (101.6 KB)

Employeequalifications.jpg (100.9 KB)

form.jpg (82.3 KB)

qualifications.jpg (100.7 KB)

relationships.png (22.6 KB)


I appreciate your help, I feel that I'm almost there

Looking at the contacts detail form ( form.jpg ), the sub form for qualifications has the EmployeeID as a combo box. This should not be visible on the form. You will set the master/child linking field properties for the sub form control to use this field as the child linking filed. The Master linking field will be the EmployeeID on the parent form. This way Access can automatically link the the records. It will also allow Access to automatically filter the records in the sub form to only show the related records to the record in the parent form.

 

[jamilian]

Hi Boyd

If I understood you corrctly, isn't that what I've done in the relationships? Please check the relationships.png. Or is it incorrect?

 

[jamilian]

The qualificationsID is where I created the combo box, not under Emloyee ID.

 

[jamilian]

HI

I managed to make it work but have encountered another problem.

Some tables are already populated with data, running into hundreds of records. When I try to link the employee profile to the related record through a form (relationships are working fine), it will let me add new records but not link the employee profile to an existing record unless I populate either the fields in the datasheet table for that record or the fields in the joining table for that record.

What I would like to do (because it's more user friendly) is to populate vis the form, by entering the id for the employee profile and the id for that specific existing record. However, it refuses to let me add teh id for the existing record.

Any ideas on what I need to change?

 

[HiTechCoach]

HI

I managed to make it work but have encountered another problem.

Some tables are already populated with data, running into hundreds of records. When I try to link the employee profile to the related record through a form (relationships are working fine), it will let me add new records but not link the employee profile to an existing record unless I populate either the fields in the datasheet table for that record or the fields in the joining table for that record.

What I would like to do (because it's more user friendly) is to populate vis the form, by entering the id for the employee profile and the id for that specific existing record. However, it refuses to let me add teh id for the existing record.

Any ideas on what I need to change?

I base "user-friendly" on work flow. Based on the work flow it may make sense to have multiple ways to do the same thing. Since I have not doe a work flow analysis I can not give you any suggestion on how to make it user-friendly.

Your current issues seam to be at the form design level. Without seeing the actual forms, it is hard to say where to look for an issue.

If I understand you correctly, you should be able to make a form that is based on just the Employee Qualifications junction table. use two combo box on the form to select the two foreign keys in each record.

 

[jamilian]

HI HiTechCoach

Well, I've tried just about every combination I can think of following your advice but still cannot get the database to present the data in the way I like. This includes using subforms, linked forms, forms created with the wizard etc etc.

The core problem is that when I have information in existing tables and try to link more than one record, i.e. knowledge sharing session, with more than one employee, it refuses to work and gives me the error message in the attachment.

Example:
I created three tables:

Contact Details - master table - primary key = ContactDetailsID
EmployeeKnowledgeSharing - linking table - primary key = EmployeeKnowledgeSharingID
Knowledge Sharing - child form - primary key = KnowledeSharingID

Forms:
Contact Details created from the Contact Details Table
Knowledge Sharing created from the EmployeeKnowledgeSharing table.

I created a form, called Contact Details, where I have a button I click on to bring up a form with the information about the knoweldge sharing sessions that that indiviudal employee has done over the course of the year.

All this works fine if I use the create -> form > wizard and only use the three fields from EmployeeKnowledgeSharing.

But the problem is that this form does not show the actual knowledge sharing details, like description, reference, name, date etc, only the IDs from the above three tables....butit works because it links each record to each employee and more than one record to one employee.

However, if I add a subform or additonal fields to the form to have the Knowledge Sharing details, then when I link more than one employee to one session it refuses and gives me the error message attached unless I update it first in the linking table.

Attached are the tables, relationships and forms.


It's probably something very basic or the way in which I set up the forms.
Any ideas?

 

[jamilian]

I would like to add that by creating a table from the EmployeeKnowledgeSharing table using the primary and two foreign keys, I can add more than one employee to more than one record. However, I would like ot see the detail of each record in a subform or in the detailed section of the form. When I add fields from the Knowledge Sharing table to the form created from EmployeeKnowledgeSharing, it breaks the above, i.e. I cannot add more than one employee to each record.

If I use a subform, I can see the records but only one row at a time and need to click the arrow button to go to the next one. Also, I cannot add to it.

I would like to be able to able ot do the following. Fron the Main Contact Details form, click a button for Knowledge sharing, and see all the records related to that individual. Also, if possible, add new records to this individual, either through a form or subform.

I believe the tables are correct but as you mentioned before, the way in which I create the linked forms or subforms is wrong. I've tried all types of combinations to no avail. I've also exhausted the Help in Access and my Access book for Dummies.
Is this something that cannot be done in Access 2007?