Help need creating a join between 3 tables

[spnz]

Hi there

I have made a form I am trying to bound 3 tables to.

The 3 tables are

tblPersonalInfo
tblBookingInfo
tblSkills

How I am trying to set it up is that I enter someones personal details in once. But using the same personal details someone can have more then one booking.
With the skills....each person can have lots of different skills.

Basically I work for a recruitment agency and I have been asked to build a database that can add new people.add skills to that person.....then add different jobs to the same person.

I have made 3 tables

tblPersonalInfo
PersonalID
chrForename
chrSurname
dtmDOB
chrEmail

tblBookingInfo
BookingID
chrJobTitle
curPayRate
PersonalID
SkillsID

tblSkills
SkillsID
Skills


I have joined all tables togeather (I think wrong but trying) the tblbookinginfo is joined to tblbooking using a 1 - Many relationship using the primary key of tblPersonalInfo to the field PersonalID in the table tblBookingInfo
I have joined tblSkills to the table tblbookingID through SkillsID

I created a query using all 3 tables and have used this to bound the form....

The form contains 3 pages and the 1st page works ok (it only contains info from the tblPersonalInfo) the 2 other pages don't seem to work as I want them to.


I would like to be able to click to add a new person and also new booking and new skills

Currently I am able to add new personal details but nothing else.

Can anyone see what I am doing wrong?

Thanks for taking the time to read this

 

[RV]

A skill comes with a person, not with a booking.
So you should join tblPersonalInfo and tblSkills on skills, and not join tblbookingID and tblSkills.

Furthermore, a person can have multiple skills and a skill can be owned by multiple persons.
Therefore, you've got a many to many relationship between tblPersonalInfo and tblSkills.
A many to many relationship needs to be cut down to 2 one to many relationships, by introducing a junction table tblPersonalSkills, consisting of the primary keys of both parent tables.
(query in the forum on "junction table" for more info).

As for persons, in tim they can practice multiple jobs.
Which you' also like to register so you'd need an additional table, to be joined in a 1 to many relationship with tblPersonalInfo.

As for Bookings, it's quite common that a booking start with a request ("can you get me someone who").
A request includes requestor data, job description plus requirements.
Meaning, you'd need at least one new table tblRequestor.
And you could consider a new table tblRequirement as basic requirements will be repetative....

So, the sky's the limit

What I'd suggest is you first take a piece of paper, write down your daily operations, translate them towards entities (in such a way that an entity can be addressed uniquelly), translate your entities into tables and thus create your database structure.

RV

 

[spnz]

HI RV

Thanks for such a detailed response...Very Helpful!!!

Ok please tell me if I have got this wrong.

I need to make 2 more tables

tblPersonalSkills
PersonalSkillsID (Primary Key)
PersonalID
SkillID

tblPersonalBookings
PersonalBookingsID (Primary Key)
PersonalID
BookingInformationID


1.Do I create a 1 to many relationship between tblPersonalInformation & the new table tblPersonalSkills
2. Do I then create a 1 to many between tblPersonalSkills & tblActualSkills

3.Do I create a 1 - many relationship between tblBookingInformation & tblPersonalBookings

4. Finally... Do I create a 1 - many relationship between tblPersonalBookings & tblPersonalInformation.



I know I am asking for a heap of help but I am very grateful!!!


Thank-you

 

[Pat Hartman]

You don't define the cardinality of the relationship - Access does. But, yes, you do need to create relationships and enforce referential integrity and possibly cascade delete.

Once you've gotten the tables and relationships sorted out, you can create your forms. You will not be able to create a single query with all these tables since there is more than one 1-many relationship involved. You will need to create a form or report with several subforms or subreports to work with the data.

 

[spnz]

Thanks Pat & RV

I have changed it around a little now is this correct??

tblPersonalInformation contains all the persons personal details

tblPersonaleInformation
PersonalInformationID
chrSurname
chrForename
etc
etc

A person may have many skills so I thought of making a seperate table (other
temps will have the same skills)
tblActualSkills
ActualSkillID
chrSkill

A person may have many different job bookings so I made a seperate table for
bookings
tblBookingInformation
BookingInformationID
chrJobTitle
curSalary
etc
etc
etc

Joining them is now where I get a little confused. This is what I was
thinking would work but it doesn't seem to work.

1. Adding the field PersonalInformationID to the tbl tblBookingInformation
and then creating a relationship between tblPersonalInformation &
tblBookingInformation.

2. Creating a new tbl called tblPersonalSkills adding the fields
PersonalSkillsID (Primary Key)
BookingInformationID
ActualSkillID

Then creating a relationship between the tblPersonalSkills & tblBookingInformation &
then creating another join between tblPersonalSkills & tblActualSkills.

My reason for thinking that I need to create a new table is that lots of
bookings can share skills.

Am I going in the right direction or should I replan how to create these
relationships??

Part 2.

There will be a number of different companies so I have created a tbl for
them

tblCompanyInformation
CompanyInformationID
chrCompanyName
chrCompanyContact
chrAddress1
etc
etc

I don't know if I should bother with the next part but I was thinking that it
might be a must.

A company may employee a number of different temps.
Do I need to create a relationship now between tblBookingInformation &
tblCompanyInformation?

 

[spnz]

I have attached a screen shot of my relationship so far as it might be easier to understand.

Thanks again!

 

[spnz]

Right........now I think I am getting somewhere.

I have managed to get tblPersonalInformation & tblSkills joined correctly. I have a 3rd table that is used to join the 2 of them togeather.

This is the part im really stuck on and have spent all weekend trying to solve it

I want to join my tbl tblBookingInformation & also tblCompanyName so they are all joined as the all are related.

I am not sure now how to create another 1-to-many relationship so I can join them all togeather.

I have attached a small copy of my db.
If anyone could take a look and let me know if they can see anything I am doing wrong I would be most grateful.

Thank-u

 

[Pat Hartman]

I don't see CompanyID in any of the other tables so I don't know what you want to relate it to.

Find my ManyToMany sample database in the samples forum. It will show you how to work with these relationships.

 

[spnz]

Hi Pat

Thanks for pointing me in the direction of your sample db.

I have taken the CompanyID out of the picture as I don't think I really need it in the relationship.

All works well when I am entering information into the database. The problem is when I go to delete information off it.
For example I enter a persons name and there different skills and then enter their booking information. All the tables look correct with the relevant data in them. When I go to the tblPersonalInformation and delete a record. It only deletes information from that table and the tblPersonalSkills.
tblBookingInformation details are still left on the table. I don't think it effects anything I just don't think its right that the information is left there.
I have checked and all the relationships I have ensured that 1.I have enforced referential integrity 2. I have ensured that the cascade delete related records is selected.

What do I have to do to get the data that is left on tblBookingInformation is deleted automatically?

Can anyone let me know whats going wrong?

Thanks everyone for your help!!!

 

[Pat Hartman]

It looks like the tblPersonalBooking is not needed. Delete it. PersonalID should go in tblBookingInformation. Given the information in that table, it looks like the relationship is 1-m with tblPersonalInformation. You should also add companyID to tblBookingInformation since that table represents a person's assignment with a company.

FYI - using prefixes on column names, especially data types, is non-standard. I recommend removing them.

Also, using a single column to hold ContactName will lead to future problems. Whenever you need to store a name, you should use a minimum of two columns. It is also advisable to store a title such as Mr., Mrs., etc to facilitate communication via email or snail mail.

 

[spnz]

Thank you so much Pat!!!
Got it finally working the way I wanted it to!!

quick question about using prefixes on column names......Do you mean when I name a column in the table design only to use the name I want e.g Surname not chrSurname?

Thanks for all your help

 

[Pat Hartman]

That's it. Surname rather than chrSurname.