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