Eep! Many to Many Relationships!? (1 Viewer)

[SaraMegan]

Hi! I'm working on a database that tracks postings for vacant positions, and applications to those positions.

There are four main tables, plus a few lookup tables so far.

tblPositions
PositionNo (PK)
CostCenter (Office the position is in)
ClassCode (Title of Position)
VacancyDate (Date position vacated)

tblPostings
AnnouncementNo (PK)
PositionNo (looks up to another table all the details of each position)
Then a whole mess of dates tracking each phase of the posting process (approvals, etc.)

tblApplications
ApplicationID (PK)
SSN (Foreign key to tblApplicants)
AnnouncementNo
A mess of other fields that document the process the application goes through.

tblApplicants
SSN (PK)
Last Name, etc, contact information on the applicant.

Okay, so I'm trying to figure out the relationships.

Each applicant can have many applications, so that's a one-to many. I think I'm good there... BUT

Each posting can have many applications, and each application can have many postings. (One Posting can have many applicants and one applicant can respond to many postings, provided they have the same job title.)

I looked up Many to Many relationships in Access help and I'm a little confused. It says I have to make a table to join the two other tables, but what do I use as primary key? How do I work this out?

Is there a better way?

Thanks in advance. This is probably much simpler than I think it is, but I'd really like to get it right the first time.

--Sara

 

[Cosmos75]

Many-to-many relationship
http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=10587&highlight=Junction+Table

Example of using many-to-many relationship
http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=30787&highlight=Junction+Table

 

[Graham T]

Sara

Pat Hartman has posted a many-to-many sample in the forums

Check the following post:

http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=34541

HTH

Graham

 

[SaraMegan]

Thanks! I don't know why I didn't see these things when I did a search... I must have entered my criteria badly or something...

--Sara

 

[SaraMegan]

Cosmos,

You said to me:

So an applicant and submit an application for more that one posting? i.e. Application 1 is for Posting 1, Posting 2 and posting 3??

Yes, that is correct. Well, basically... Applicant is a different table, with a one to many relationship with tblApplications.
Application1 can apply to Position1, Position2, Position3. Position 1 can receive Application1, Application2, Application3.

The reason there is one Applicant to many applications is because the application only works for many positions if they have the same job title. If a person applies for "Secretary I" and "Clerk II" they'd have to submit two separate applications. But if there is a Secretary I in three different offices and the person wants to apply for them all, then only one application is needed.

Does that make sense to you?

--Sara

ps - your PM inbox is full

 

[SaraMegan]

I have downloaded Pat's sample, which makes everything much clearer... But as far as the joining table goes, I'm still a little confused.

She has three fields in her joining table, and each of them is a primary key. I can understand using the joining table, and using the other primary keys in the table, but am a little unclear about the third field, which does not appear in the other tables. Is it necessary to have that third field?

Well, I guess that's it for now. Any help from anyone is greatly appreciated.

--Sara

(EDIT: ) Oh, and, in Pat's example, the third primary key in the joining table is a date? What if you have two things going on in the same day.

Forgive me if I'm completely daft. After all, it is Monday...
(END EDIT)

 

[Graham T]

Sara

‘One’ Customer can book the same venue ‘Many’ times, therefore the BookingDt is also required to ensure that the record remains unique and that, that ‘One’ Customer can rebook the venue (on a different date) ‘Many’ times.

The customer would only book the same venue once for a particular date.

The date field is included to allow the same customer to book the same venue more than once.

Hopefully this is the correct definition, if not Pat will maybe put me right!

HTH

Graham

 

[SaraMegan]

All right, then I think that my situation is a little bit different because one application can be used for many postings, and one posting can receive many applications. BUT one application will not apply to the same posting more than once.

So can my junction table have just the primary keys from those two tables (tblPostings and tblApplications) and be fine, as there should be a copmletely unique combination every time?

Thanks again.

--Sara

 

[Graham T]

Sara

If the records in the link table will always be unique then you would not need any further fields and you can go with just the PK's from the Application table and the Posting table.

If there is any chance that these "maybe" repeated then you would need to include additional fields.

HTH

Graham

 

[sarahb845]

you should be fine with a junction table that contains only:

tblApplicationPosting
ApplicationID (PK)
PostingID (PK)

as long as the tblPosting has a different record for EACH cost center (location) and EACH open job (one cost center could have multiple openings of the same position, vacated on the same day), and the tblApplication has a different record for EACH applicant.

For example, let's say there are 2 positions for Computer Programmer open at the Boise, ID location and 1 position for Computer Programmer open at the Chicago, IL location. Sally Smith has applied for both locations.

Your tblPosting should look like:

PostingID---PositionID---CostCenterID
-------------------------------------------------
1---10---5
2---10---5
3---10---9

where PositionID of 10 = Computer Programmer
CostCenterID of 5 = Boise, ID (PostingIDs 1 & 2)
CostCenterID of 9 = Chicago, IL


Your tblApplication should look like:

ApplicationID---ApplicantID
----------------------------------
6---27

where ApplicantID of 27 = Sally Smith.


Your tblApplicationPosting would then look like:

ApplicationID---PostingID
--------------------------------
6---1 (Sally, Boise, first opening)
6---2 (Sally, Boise, second opening)
6---3 (Sally, Chicago)


hth

 

[SaraMegan]

Thanks for all the help! Just one more question (for now...)

Do I use a subform to populate my junction table? I've been looking at Pat's example to help me try to figure it out, but it's starting to confuse me. (Probably because I've been looking at it for too long.)

This is what I'm thinking:

My main form will be based on a query with all the Posting information.

My Subform will be based on a query that holds the junction table information and the application information.

But how do I get the information to plug in to the junction table? Use a lookup?

Never done a subform thing before. (Woah what a surprise )

Anyway, any help is wonderful. I'm still experimenting, but I'm in a little rut, so I thought I'd post to see if I can get a fresh view on this.

thanks in advance.

--Sara

 

[SaraMegan]

And Lo, there was singing

And the Sara did grin. Because she had figured it out.

Ahh.

Woo-Hoo.

Let me see if I can explain this, in case someone else has this confusion...

The queries are set up basically as I said. I made both forms separately.

In the subform portion, I made a combo box that selects the ApplicationID from a query bound to tblApplication and that populated the other fields in the subform and the ApplicationID was stored in my junction table.

When I put the subform in the main form, I used the wizard, and when it asked me, I used the PostingID to join the two, populating the other field in the junction table.

I'm not sure if that makes complete sense. But that's what I did. And it works.

--Sara

 

[Pat Hartman]

Hi,
I was away while this thread was active so I didn't pop in. I'm glad the many-to-many sample was helpful. Graham did an excellent job of explaining why the date field was included in the primary key of the junction table. Fields such as the booking date are referred to as intersection data. That means that they only have meaning at the intersection of two tables. The booking date "qualifies" the booking that joins the customer to the venue. Sometimes intersection data needs to become part of the primary key and other times it is just an additional data element. An example of this would be a class roster. The junction table joins the student and class tables but needs a third key - year/semester since the same student can take the same class more than one time. However, the grade the student earns would NOT become part of the junction table key because the student would earn only a single grade for each semester in which he took the class.