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.

 

[Dontremb]

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

If anyone else has any nuggets of wisdom, feel free to add, but I think I pretty much have it figured out.

 

[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.