Correct design and primary keys for linking multiple tables

[ScottMSP]

Hello,

I am struggling with table design and what primary keys to set up for
multiple tables that I need to link together and ultimately be able to edit
through a query/form.

tJobCode:
JobCode (unique field)
JobTitle
Grade

tPrimaryJobCodes
PrimaryJobCode
JobCode (unique field)

tSurveyMatches
PrimaryJobCode
SurveyID
SurveyJobNumber
Breakout

(I have tried composite primary key for SurveyID, SurveyJobNumber, Breakout)

tSurveyData
SurveyID
SurveyJobNumber
Breakout

(I have tried composite primary key for SurveyID, SurveyJobNumber, Breakout)


I need these four tables to interact with each other so that when I bring
them into a query or a form, that any of the fields can be edited. I have
had some success with the primary keys if I use two of the tables, but I know
I am not setting up the design correctly with the correct primary key(s) that
will allow me to edit this data when it is all on one query or form.

Thanks in advance.

-Scott

 

[Steve]

Hi Scot,

Please explain the function of your database and the meaning of the fields
then we can help you.

Steve

 

[John... Visio MVP]

Hi Scot,

Please explain the function of your database and the meaning of the fields
then we can help you.

Steve

Are you planning on behaving and NOT pester posters for work?

These newsgroups are provided by Microsoft for FREE peer to peer support.
Stevie is famous for ignoring that fact and likes to harrass posters
forwork.

John... Visio MVP

 

[Sylvain Lafontaine]

Easy: as you are a newbie, I would suggest that you stop using composite
primary keys and to set up a unique primary key based on an autonumber for
each table:

tJobCode:
JobCodeId (autonumber)
JobCode (unique field)
JobTitle
Grade

tPrimaryJobCodes
PrimaryJobCodesId (autonumber)
PrimaryJobCode
JobCode (unique field)

tSurveyMatches
SurveyMatchesId (autonumber)
PrimaryJobCode
SurveyID
SurveyJobNumber
Breakout

(I have tried composite primary key for SurveyID, SurveyJobNumber, Breakout)

tSurveyData
SurveyDataId (autonumber)
SurveyID
SurveyJobNumber
Breakout

You definition of fields like JobCode is not clear because it's mentionned
in two tables tJobCode and tPrimaryJobCodes, each time with the annotation
(unique field); this is why I've keep them without any change in the
previous exemple.

If you need to etablish a relationship between two tables, you add the
primary key of the first table as a foreign key to the second table; for
exemple we could add the field JobCodeId to the second table
tPrimaryJobCodes:

tJobCode:
JobCodeId (autonumber)
JobCode (unique field)
JobTitle
Grade

tPrimaryJobCodes
PrimaryJobCodesId (autonumber)
JobCodeId (int)
PrimaryJobCode
JobCode (unique field)

However, it's quite possible that depending on what's the exact definition
of a JobCode and a PrimaryJobCode, that we must do the contrary; ie, to add
the PrimaryJobCodesId to the table tJobCode instead. From your explanation,
it's impossible to tell which one is good.

The interesting thing is when you have a third table: do you etablish a
relationship between the first table and the third or between the second and
the third or even between all of them: between the first and the third AND
between the second and the third, too.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain aei ca (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[ScottMSP]

Based on your response, I have thrown out the tPrimaryJobCodes table as I
think it was unnecessary.

I have written below examples of my tables so that you can see what I am
trying to do

tJobCode:

JobCode PrimaryJobCode JobCodeTitle
00001 00001 Accountant
00002 00001 Assistant Accountant
00003 00001 Accountant II
00004 00004 Administrative Assistant
00005 00004 Sr. Admin Assistant
00006 00004 Executive Assistant
00007 00007 Manager I
00008 00007 Manager II
00009 00007 Manager III
00010 00007 Manager IV

(One unique job code, duplicate primary job codes)

tSurveyData

SurveyID SurveyJobNumber Breakout Salary
010108 21500 NY $5
010108 21500 CT $4
010108 21505 NY $6
020108 0170 CT $3
020108 0200 NY $6
020108 0200 CT $4
040108 100130 NY $7
040108 100130 CT $3

(duplicate SurveyID, duplicate SurveyJobNumber, duplicate Breakout.
Combination of three together, equals unique value which allows me to get the
corresponding salary)

tSurveyMatches
(Through a form, I am trying select a primary job code (i.e., 00001
Accountant) and match survey data from the tSurveyData and record those
matches in this table.

PrimaryJobCode SurveyID SurveyJobNumber Breakout
00001 010108 21500 NY
00001 020108 0200 NY
00001 040108 100130 NY


qJobCodeData
Once the matches have been made on the primary job codes, I will have
matched all of the job codes because all of the job codes have a
primaryjobcode. Once this is done, I still need to be able to edit the
information through a query. For instances, maybe I decide that the primary
job code for 00002 Assistant Accountant is not 00001 Accountant, but instead
00004 because it is more an administrative position. I need to be able to
make that change on the query immediately without having to open up a
different form just for matching primary jobs with job codes.


PrimaryJobCode PrimaryJobCodeTitle JobCode JobCodeTitle SurveyID SurveyJobNumber Breakout Salary
00001 Accountant 00001 Accountant 010108 21500 NY $5
00001 Accountant 00001 Accountant 020108 0200 NY $6
00001 Accountant 00001 Accountant 040108 100130 NY $7
00001 Accountant 00003 Accountant II 010108 21500 NY $5
00001 Accountant 00003 Accountant II 020108 0200 NY $6
00001 Accountant 00003 Accountant II 040108 100130 NY $7


Obviously there is other data that I will be pulling in, but hopefully this
gives you an idea of what I am trying to accomplish.

Thanks in advance to everyone.
-Scott

 

[Sylvain Lafontaine]

As I said, you should add an autonumber to each of your tables and use it as
the primary key. When you need to etablish a relationship with a foreign
table, you add this field as a foreign key to your foreign table. This is
not the only way to etablish a relationship between two tables but by far
and large, this is the easier way:

Table tJobCode:

JobCodeId JobCode PrimaryJobCodeId JobCodeTitle
1 00001 1 Accountant
2 00002 1 Assistant Accountant
3 00003 1 Accountant II
4 00004 2 Administrative Assistant
5 00005 2 Sr. Admin Assistant
6 00006 2 Executive Assistant
7 00007 3 Manager I
8 00008 3 Manager II
9 00009 3 Manager III
10 00010 3 Manager IV


Table tPrimaryJobCode:

PrimaryJobCodeId PrimaryJobCode
1 00001
2 00004
3 00007

If you really want to, you can drop the JobCodeId and the PrimaryJobCodeId
and use the columns JobCode and PrimaryJobCode as the primary keys.
However, you'll get soon into the position of having to use composite keys
and this will make your model much more complicated.

Also, you should make a clear distinction between declaring and using a
primary key for editing and etablishing relationships and the fact of using
a WHERE statement to filter/get one or more values. When you say something
like « ... duplicate SurveyID, duplicate SurveyJobNumber, duplicate
Breakout. Combination of three together, equals unique value which allows
me to get the corresponding salary », you are saying nothing explicit to me:
I have absolutely no idea if you're talking about either a tri-fields
composite primary key or of a Select filter on three columns or about both.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain aei ca (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)