many-many-many (1 Viewer)

[titus]

i know how to do a many-many with a junction table but i have a database for tracking job interviews

1. job opening info
2. appplicants general info
3. applicants status for the job

one applicant - job opening is many-many

Q- what to do with the applicant status table? this info is unique to the applicant BUT for a specific Job opening.

 

[Dreamweaver]

The Status Is Just A Lookup Table which has it's ID Assigned to The Applicants Record so you can see it they have been rejected, Accepted, Employed, Second interview Act.

If it's not used leave blank.

 

[grnzbra]

On the other hand, if it is not a look up table, the applicant's status for the job should be part of the table that join's the applicants table and the jobs table, since each record in that table represents one applicant's relation to one particular job. You should still have a lookup table, but that's where it should be connected - to an StatusID field in the join table. What fields are in the ApplicantsStatusForJob table?

 

[SRAB]

i know how to do a many-many with a junction table but i have a database for tracking job interviews

1. job opening info
2. appplicants general info
3. applicants status for the job

one applicant - job opening is many-many

Q- what to do with the applicant status table? this info is unique to the applicant BUT for a specific Job opening.

I think you need a Applicant/Job Opportunity intersection table.
Jobs have 1-M applicants
Applicants have 1-M job opportunities (applicant status would be here)
Jobs have no direct relationship to applicant status

 

[Moniker]

See the attached for an example of how to do an one-to-many jobs to applicants and then a one-to-many applicants to status using a status lookup table, all in one query.

I wrote two quick queries for you. The first one, "q_ApplicantStatus_INNER", uses inner joins and will only show records where a job has an applicant and that applicant has a status of some sort.

The second query, "q_ApplicantStatus_LEFT", uses left joins and it will show all the jobs regardless of if they have applicants or an applicant status has been set.

Both queries are identical except for the join types.

The tables should be pretty self-explanatory.

~Moniker