Linking 2 fields in Table A to 2 fields in Table B

[HowieG]

Assuming the following tables (fields):

[tt]tblJobs (Job#, Closed, JobName, ...)
tblPhases (Job#, Phase, PhaseName, PctComp, ...)
tblCats (Job#, Phase, Category, MonthEnd, PctComp, ...)[/tt]

There is a 1-M relationship between tblCats and tblPhases based on (Job# and Phase), and between tblPhases and tblJobs on Job#.

Can you create a relationship linking two fields in each table? (Job#&Phase => Job#&Phase)?

 

[930driver]

Yes you can link on two fields, but I doubt this is correct for your situation.

I'm guessing that tblJobs is what everything else is based on.

Does this describe it? Each Job has Phases, and each Phase has Cats.

You have Job# in tblPhases. So Job# in tblJobs is the primary key and Job# in tblPhases is the foreign key (in other words the Job# in tblPhases says which Job that Phase belongs to). This makes Phase the primary key of tblPhases.

You have Phase in tblCats. So it is the foreign key (in other words Phase in tblCats says which Phase that Cat belongs to).

You can dump Job# in tblCats and link as follows:

tblJobs to tblPhases via Job#
tblPhases to tblCats via Phase

If I'm wrong and tblCats is the top Dog, then reverse the logic. In any case, although you can link on two tables it is not necessary for your situation.
"The Key, The Whole Key, and Nothing But The Key, So Help Me Codd!"

 

[HowieG]

The link between tblPhases and tblJobs as you state is correct, and I understand that part.

It's the link between tblPhases and tblCats that is in question. I think I didn't explain the data well enough. Each Job can have between 1 and 50 phases numbered 1-50. Each of these phases can have one or more categories out of a list of about 20 (this list is in another table).

The data I'm storing in tblCats is not just related to that particular category code in a generic sense, but to that category, within that phase for that job. For example, materials costs for Category X in Phase 2 of Job# 123.

Similarly, the data I'm storing in tblPhases is not the list of 50 generic phases that can be assigned to a job, but the list of 10 specific phases applied to job 1, the 14 to job 2, etc., and some information related to that entire phase within the context of that job.

So the Primary Key within tblCats is the combination of Job+Phase+Category, and the rest of the fields supply information about that combination.

Now that you have 'the rest of the story,' does it sound like I'm on the right track?

As for my original question, I did manage to find out how to link multiple fields between tables.

I appreciate your help.

 

[930driver]

OK, now I understand a bit better.

Each Job has Phases and each Phase can be assigned to more than one Job.

Each Job/Phase has Categories and each Category can be assigned to more than one Job/Phase.

You don't have 1 to Many relationships; you have Many to Many relationships! You're basicly on the right track in using combination keys but it needs to be implimented a bit differently. For the sake of this explanation I will assume the following primary keys in your tables (and I encourage you to do it this way, although the subject of keys is often debated and others may have other opinions). Your existing tables will have no foreign keys (at least not to each other).

tblJobs: JobID - Autonumber
tblPhases: PhaseID - Autonumber
tblCats: CategoryID - Autonumber

Then you will need two other tables to represent the relationships that you need. The first table will have a foreign key for tblJobs and tblPhases and will record each Job/Phase combination.

lktblJobPhases
Primary Key: JobPhaseID - Autonumber
Foreign Key: JobID - Long Integer
Foreign Key: PhaseID - Long Integer

Now we need to set up the other relationship. Since we have a handy record of each JobPhase in our new link table it is simple to put a another link table between it and your Categories.

lktblJobPhasesCategories
Primary Key: JobPhasesCategoryID - Autonumber
Foreign Key: JobPhaseID - Long Integer
Foreign Key: CategoryID - Long Integer

The way you make this all work on a form is with subforms. On your Job form you will have one subform for Phases which will be based on lktblJobPhases. JobID will be the link field for that subform and you will select the Phase with a combo.

Then you can either have a sub-subform for the Categories for each Phase, linked on JobPhasesID, or (my preference) you can have a second subform on the Job form which is dependent on the first form. In other words it will display only Categories that apply to the currently selected Phase. That link is a bit more difficult and will involve a hidden unbound field on your Job form and a smattering of VBA code, but it's not too difficult.

However, I'm not going to get into it in this message. If you understand all this and want to impliment it post a new message asking about the subform issue, probably in the forms forum. If you even need that help that is. "The Key, The Whole Key, and Nothing But The Key, So Help Me Codd!"

 

[HowieG]

I understand the issue of meaningful vs. arbitrary primary keys. Although I usually tend towards the arbitrary (autonumber) type approach, in this case I'm using the meaningful key (that being the Job #) because my work is drawing information from an existing system than I am unable to change. The job #'s are fixed and unchangable.

Anyways...

I don't agree that these are M-M relationships. Within tblJobs, there can only be one record for each Job#, and within tblPhases, there can be many records for each Job#. Likewise in tblPhases there can only be one record for each (Job+Phase) combination, and in tblCats there can be many records for each (Job+Phase) combination.

I do, however, like your idea of using a single autonumber key for the combinations of (Job+Phase). So, I'm going to try adding an autonumber field (PhaseID) to tblPhases, and use that as the foreign key in tblCats in place of the Job and Phase fields.

Thanks again!

 

[Survane]

930driver - I have a similar issue with normalizing a database tables. I was wondering if I could send you my relationships and tables info.

Thanks,
Survane.

 

[930driver]

Survane,

Your email is not exposed so I can't contact you directly. Perhaps the best thing to do would be to start a new thread and explain what you need to do and ask for help. I haven't been active here for several months but there are lots of people with database normalization skills that post here.

If you reply to this message and give your email address, supposedly the board will email me and let me know (as it did for your last message). "The Key, The Whole Key, and Nothing But The Key, So Help Me Codd!"