Many to Many Relationship help.

[Dontremb]

Ok, first thanks for reading. This is my problem:

I'm making a database, and I've created many databases, but this one requires something slightly different, and I absolutely can't figure it out.

Basically, I need to link two tables.

Table 1: Job Vacancies.

Simple table, it just has fields like: Vacancy numbers, job title, location, and things like that.

Table 2: Personnel

Also a simple table, this just lists First and last names and Socials, Racial background.


My problem is this: In Job Vacancies, I need to list people from Personnel who have applied, and I also need to list who was selected. (Both of these fields can have multiple people.)

In addition to that, I need to be able to run reports which simply compiles the data to show the number of people who applied to certain or all jobs. It also needs to break down how many of which race/handicap applied for or were hired for a specific/all jobs.

I know how to do all the calcualations and stuff, what I'm having problems doing is linking the two tables together.

I did some research and found that I will need to have a many to many relationship, which will require me to have a 3rd table with the primary keys of both of my other tables.

What I can't figure out, is how to get it to display properly in a form or the table, or report, for that matter.

Ultimately, I need to be able to display a job vacancy in a form, and have it list all the people who have applied. I also need to be able to have personnel specific fields (like qualifications) that would only be applicable to THAT job vacancy.

Then, I want to be able to look at individual persons, and see what jobs they have applied for.

I realize this is a lot of stuff, and it may not be an easy answer, but I think I'm just missing something simple. I'm mostly a self-taught Access user of about 8 years, but I've never had to do a many to many relationship before.

Any help would be greatly appreciated.

 

[johnherman]

Quite simply, you have two bridge tables: one for applicants, and one for hires, which is also a child table of applicants.

Vacancy Table
Vacancy ID
Vacancy Name
Vacancy Open Date
Vacancy Close Date
etc

Personnel Table
Personnel ID
Personnel First Name
Personnel Last Name
etc

Vacancy-Personnel (Bridge) Table
VPBridge ID
Personnel ID
Vacancy ID

Vacancy-Hire Table
VHBridge ID
VPBridge ID
Personnel ID (optional but recommended)
Vacancy ID (optional but recommended)

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

 

[Dontremb]

I felt I should respond one last time to say that I DID figure it out.

So thanks to John for replying, and if anyone else has any nuggets of wisdom, feel free to add, but I think I pretty much have it figured out.

 

[r937]

dispense with the unnecessary ID column in Vacancy-Personnel

and if a person can only be hired after having applied, then the Vacancy-Hire table rows would be a subset of the Vacancy-Personnel rows, and all you need is a WasHired column

Vacancy-Personnel (Bridge) Table
[red]VPBridge ID[/red]
Personnel ID
Vacancy ID
[blue]WasHired[/blue]

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[johnherman]

I disagree with r937. The although the WasHired column could be convenient, it violates 3NF since the existence (WHERE EXISTS) or non-existence (MINUS query in Oracle) of a Vacancy-Hire record indicates whether the position was filled by that particular Personnel ID. Additionally, the VPBridge ID (as a single column primary key) could be useful for performance if the concatenated key of Personnel ID and Vacancy ID was large or of mixed data types.

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

 

[r937]

i disagree with johnherman

time to re-check what 3NF actually means



r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[alvechurchdata]

time to re-check what 3NF actually means

At the risk of starting an argument I'd say that 3NF is an academic concept conceived in the days when disk space was expensive. I'd keep the VPBridgeID column. Technically it's wrong but it's not doing any harm.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[Dontremb]

Ok, thank you both. This is what I have now:

tblPersonnel:
IDPersonnel (Primary Key)
LName
FName
etc.

tblVacancies:
IDVacancy (Primary Key)
VacancyNumber
JobTitle

tblCompilation: (I know, silly name.)
IDCompilation (Primary Key)
IDPersonnel (Foreign Key)
IDVacancy (Foreign Key)
Hired Y/N


Ok, so, when I made a form with Vacancies, with a subform from Personnel in it, it worked perfectly. (Before the original question, I didn't know how to do many-to-many relationships)

Anyway, my problem NOW, is when I'm on the form (which shows Vacancies, and subform with Personnel info, I can't put the same person in two Vacancies.

Example: Job 1: Applicants: John and Jake. I fill in the info for the vacancy, then fill in the infor for John and Jake in the subform. Everything works perfectly. The problem is that on Job 2, if the SAME John applies, my tables consider him a NEW John, with a new IDPersonnel Number. Is there any way to check to see if the name is already in the table, or something? The types of jobs I do, I CONSTANTLY get repeat applicants.

Thanks for the assistance in advance.

 

[Olaf Doschke]

on Job 2, if the SAME John applies, my tables consider him a NEW John, with a new IDPersonnel Number

I think nobody can tell you this, unless we know how your form is constructed and what controls show what data. And this would rather be an access question, than a general database question.

In a generelisation of the problem, the main form should show records of the vacancies table and the subform should list entries of a View parameterised with the current vacancie joining the compilation table and personnel table. Adding a applicant would mainly add a "compilation" record and you'd choose via a combobox a personnal, and that way would be able to choose the same John for a different vacancy. Becaus if you change vacancy in the main part of the form the subform should requery it's view data.

Bye, Olaf.

 

[ceh4702]

You can handle some of these items in differrent ways. For instance the people that apply are not personnel but just people who applied in many instances.

I work for a school and all people are in the person file, Students, Employees, contractors, and other relations like staff (paid and unpaid), are in the person file then we have applicant (students applying for programs) and applicants (for work) also. Then there are Students also.

There is a kind of Class relationship in the file structure between Person and Applicants. The same could be said for Person and Employee. In our case the Employees and the
Applicants and the Persons share a common File key. However, the file could be conceptual in that they are all in the same file, but when you say Applicant file it just refers to a subset of the file person with a conceptual kind of parent child relationship or saying Applicant is a sub-class of Person, but they are both technically the Person Class.

Probably not what you want to consider. However, Person, Applicant and Employee could be said to share a lot of specific values. Where as Applicant and Employee only add specific fields or values that are unique to them.

If you do not like my post feel free to point out your opinion or my errors.

 

[mjt39]

r937 already suggested dispensing the with the >artificial< pimary key column tblComposite.IDCompilation. Instead IDPersonnel-IDVacancy could become the composite primary key. To me that would make things easier, but or because I am not a pro.
I fear putting 2 rows for one and the same John1 in tblPersonell - John11 (John1 for job1) and John12 (John1 for job2) - could cause problems. 2 rows <John1,job1> and <John1,.job2> in tblComposite might be safer.
If I didn’t understand properly, because job1 and job2 are identical jobs in tblVacancies for which John1 applied at different dates. Then a new column tblComposite.IDHiredate could be added with IDPersonnel-IDVacancy-IDHiredate as primary key.

 

[mjt39]

Sorry, I forgot to add: entering John1 twice in tblPersonnel is a violation of the 1NF.