Relationship between tables ? (1 Viewer)

Emma35 · Jan 3, 2013

Hi All,
I'm normally pretty ok with tables but seem to be having a bad day of it today

. I'm just beginning a staff training database and have two tables so far. My problem is that one employee may have many jobs and each job has many SOP's associated with it. I'm trying to create a relationship between the two tables (attached pic)
I'm then going to create a data entry form for filling in employee training details where the parent form will be based on tblMaster and the child based on tblTrainedSOPData....which will allow me to add several SOP's to each record using a combo box on the subform. Hope i've explained this ok. Is this a many-to-many relationship ?

spikepl · Jan 3, 2013

Training requirements go with a position. Actual training history goes with the physical employee filling that position. All that needs to go into a DB woith a normalized data sturcture. Google, read and assimilate "database normalization".

Also, google the Access World Forums:

site:www.access-programmers.co.uk training
site:www.access-programmers.co.uk SOP
site:www.access-programmers.co.uk GMP

Emma35 · Jan 3, 2013

I've looked up normalisation and haven't been able to come up with a solution. When i connect these tables and try to add data i'm getting the PK violation error. Do i need a bridge table ?....if that's what it's called.....

GSSDevelopment · Jan 3, 2013

When you have a many to many relationship, you need an intermediate table which links the PKs of each of the tables.

The general example is a store. An inventory of items, and orders. An item may be on multiple orders and an order may have multiple items. Create a table called OrderLineItems to handle this.

Another example is teachers and students. Create a table called ClassRosters or something that links students to teachers

Emma35 · Jan 4, 2013

Ok thanks...i'll try and come up with something at the weekend and get back to you. Thanks for the help

Emma35 · Jan 6, 2013

Haven't been having much luck with this. I'm using the EmployeeID information to uniquely identify each member of staff but everytime i add a multiple record for that employee i'm violating the PK rule. How can i get round this ?

Thanks,
Em

GSSDevelopment · Jan 6, 2013

Your new table should have a new PK, then have a new field for each of the two other tables' PKs

jdraw · Jan 6, 2013

Emma,

Get your tables designed before trying to add information. Normalization and business rules are key.
This link may be useful - the first few links on the page are great for normalization; the entity relationship drawing article leads through an example.
http://www.rogersaccesslibrary.com/forum/topic238.html

RainLover · Jan 7, 2013

tblMaster is not a good name. Table names should be more descriptive. I would prefer tblEmployees. This table should only have information about the Employee. Things like FirstName, LastName not Name as you may need to search on the last name sometime. Other things could be Address, PayrollNumber, NextofKin. All of these do not change.

A second Table might be tblEmployeeDetails.

Another Table would be tblTraining which lists all the Training/SOPs.

You would then create a Join Table which is a Many to Many Relationship.

Include TtblEmployees.PrimaryKey and tblTraining.PrimaryKey.

There is more to this than what I have just written but I hope it helps a little. You need to read the links as posted by the others.

Emma35 · Jan 7, 2013

Thanks for the help guys i really appreciate it. I've made some changes to the database and hopefully improved the whole normalization thing (attached).

I've changed the name of tblMaster to tblTrainingRecords and taken the fields EmployeeID and EmployeeName and put them in a table on their own. I think i'll keep EmployeeName as one field as we dont have a lot of staff and i can use EmployeeID if i need to search for somebody.

Creating the Join Table is still confusing me though

RainLover · Jan 7, 2013

I think i'll keep EmployeeName as one field as we don't have a lot of staff and i can use EmployeeID

I think you should reconsider this. If you want to learn Access you need to consider the advise given. Not all advise is correct but this one is.

Suggest you look at the Blue link in my Signature. There is a good article of Naming Conventions. Also (not written by me) is an article on RDMS Principals. The last two or three pages describe a Many to Many relationship which is part of a Join Table.

Hope this helps.

Emma35 · Jan 7, 2013

Thanks RainLover....that Skydrive tutorial is probably the best explanation of relationships i've seen. Off home for the night but i'll get back on it tomorrow morning and hopefully sort this out.
I'll break the EmployeeName field into 2 separate fields......might as well not get into any bad habits.

Thanks for the help (for the moment

)

Em

RainLover · Jan 8, 2013

I like the attitude.

Emma35 · Jan 8, 2013

Ok i've made an attempt to get things normalised (attached). The data types are

tblStaff (StaffID Autonumber)

tblStaffTraining (StaffID Number ) (TrainingID Number)

tblTrainingRecords (TrainingID Autonumber) (SOPID Number)

tblTrainedSOPData (SOPID Autonumber)

Can someone have a look and see if this looks ok ?. I now need to create a data entry form to allow users to enter training information.

Thanks

RainLover · Jan 8, 2013

Emma

Things look a lot better.

Just to clarify my thinking, do you have a Table that records the employees and the basic information about them. I assume tblStaff is the one for this. Most likely you will add additional field to this table later. Things like DOB, Next of Kin, Phone Number etc. Not important just now until you sort out your relationships.

Next do you have a Table that stores information about the various types of Training. Things like, driving a Truck, operating a Back Hoe using a computer.

If you have the above two then add a third table as a join table which will give you a many to many relationship. This table stores the Primary Key from both the above Tables.

It would look like this.

tblStaffTraining.

StaffTrainingPK Autonumber.
StaffFK which is Type long and joins to StaffPK from tblStaff.
TrainingFK which is Type long and joins to TrainingPK from tblTraining.

Result is that you have

Staff Trained in TrainingCourse.

Fred Truck Driving
Fred Back Hoe
Fred Computers
George Computers
John Back Hoe
John Computers.

Many Staff joined to Many Courses

I think you have this but I am not sure.

Hope this helps.

Emma35 · Jan 8, 2013

Hi RainLover,
Yes i have these tables now as you described. My tblStaff will only contain names and an employee number which uniquely identifies each member of staff.

tblTrainingRecords will contain the details for each job that a member of staff is trained on.

Each job has several SOP's associated with it which must be learned before a person is deemed competent on that job so tblTrainedSOPData is for saving the list of SOP's each staff member has learned for each job.

In your opinion, when i create my data input form, should i create the master form based on tblStaff and then add 2 subforms based on the other 2 tables ?

Thanks again for your help.

RainLover · Jan 8, 2013

Emma

If you are still havingproblems with Many to Many then have a look at the link in my signature.

I have just added a Many to Many Demo Database. There are no instructions but scroll through the various forms for different types of view.

There is no error trapping so if you find a fault please PM me and I will fix.

I decided to attach it to make it easier for you.

RainLover · Jan 8, 2013

My last post was before I saw your last post.

I will read yours better and get back to you.

Besides I need time to think about it.

RainLover · Jan 8, 2013

Emma35 said:
Hi RainLover,
Yes i have these tables now as you described. My tblStaff will only contain names and an employee number which uniquely identifies each member of staff.

tblTrainingRecords will contain the details for each job that a member of staff is trained on.

Each job has several SOP's associated with it which must be learned before a person is deemed competent on that job so tblTrainedSOPData is for saving the list of SOP's each staff member has learned for each job.

In your opinion, when i create my data input form, should i create the master form based on tblStaff and then add 2 subforms based on the other 2 tables ?

Thanks again for your help.

I am thinking that you need a Many to Many Relationship between the current Join and the SOP Table.

The current Join says John was trained in Trucks and he underwent 10 SOPs

So the new Join would be John who is listed in 10 Records and he did each of the 10 SOPs. You could add a Date, Trained by, Approved by for each record.

Does this make sense to you.

Best thing is to create this in a dummy DBase and enter some records to see if it works. Post the DB when you finish.

I believe this will work.

Others may wish to comment. I hope.

BTW

As a tool, when finished print your relationships on A3 if you can. It is worth printing to PDF if you can't print directly in your office. Take this to your local Photo Copy shop and have them print a few copies.

Then pin this to your wall. It makes for a great Road map of your design.

Emma35 · Jan 8, 2013

Thanks RainLover that database should be a help when i can get the hang of what's going on. I see all your forms are based on queries ?....i've never done it this way before.

I'm attaching my database as it stands at the moment (very raw). The form i've set up to add training records is called frmAddNewRecord. The main form is based on tblStaff and there is a subform based on tblTrainingRecords and another subform based on tblTrainedSOPData. The subforms don't seem to be linked to the parent forms for some reason (maybe the relationships aren't set up properly

I'd appreciate your opinion or suggestions.

Cheers,
Em

Relationship between tables ? (1 Viewer)

Registered User.

Attachments

Eledittingent Beliped

Registered User.

PHP Guru

Registered User.

Registered User.

PHP Guru

Super Moderator

VIP From a land downunder

Registered User.

Attachments

VIP From a land downunder

Registered User.

VIP From a land downunder

Registered User.

Attachments

VIP From a land downunder

Registered User.

VIP From a land downunder

Attachments

VIP From a land downunder

VIP From a land downunder

Registered User.

Attachments

Similar threads

Users who are viewing this thread