MS Office Access 2007 (How to display multiple records.

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

Any help would be greatly appreciated.

 

[SkipVought]

Hi,

You might make out better posting in a forum that specialized in database design. Forum669, Forum700.

Consider a table for PersonelApplications. Should contain the total life cycle of a job application; start to finish.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[Dontremb]

Well, thank you for replying. I did some more research, and I found that I have to do a Many to Many relationship. I found out that I'll need an additional table to do that.

I'm going to revise my question, and try posting on both of those forums that you mentioned, because I messed around with a many to many, but still don't really know how to get it to display exactly what I want.

Thanks again for responding, and pointing me in the right direction instead of just screaming that I had posted in the wrong forum.

 

[Dontremb]

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

So thanks to Skip 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.

 

[Dontremb]

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.