Relating one Primary Key to multiple tables - Trickier than itsounds?!

[jsmith]

Hi. I have the following tables:

StaffID (PK)
LastName
FirstName

CMLID
StaffID (FK)

RAID (PK)
StaffID(FK)

CTAID (PK)
StaffID(FK)

TrialID (PK)
TrialNo
CMLID (FK)
RAID (FK)
CTA (FK)

Okay, so each StaffID will be assigned to one of three functions (CML,
RA, CTA). Each Trial has one CML, one RA and one CTA each. Are my
relationships okay so far?

What I need to do is insert a subdatasheet on the Staff table (this is
for my use, not the users') that will show what trial each StaffID is
working on, and who is working on that trial with them. The problem
is, the underlying query for the subdatasheet must include a "StaffID"
field from the tblStaff, because that's the field on the table that
will act as the "Link Master Fields" field (with tblCML.StaffID;
tblRA.StaffID and tblCTA.StaffID as the corresponding "Link Child
Fields"), but if I include it in the query, it prompts the user for
the StaffID. I just want it to show for whatever StaffID I expand on
the parent tblStaff.

If this is a really simple fix, I'm very sorry. I think I've been
working on this for days now and perhaps I've made it more complex in
my mind than it needs to be. I do believe it's a little trickier than
it sounds, though...

Please help so I don't waste any more sleepless nights! All
assistance is appreciated.

JS

[tina]

well, your tables may be normalized, but the information you provided about
your process is sketchy, at best. i did notice that you say that CML, RA,
and CTA are all *functions*, so i have to wonder why you've set up a
different table for each one. perhaps with a more in-depth explanation of
the entities and the process...

hth
<js****@bur.boehringer-ingelheim.comwrote in message
news:55**********************************@r66g2000 hsg.googlegroups.com...

Hi. I have the following tables:

StaffID (PK)
LastName
FirstName

CMLID
StaffID (FK)

RAID (PK)
StaffID(FK)

CTAID (PK)
StaffID(FK)

TrialID (PK)
TrialNo
CMLID (FK)
RAID (FK)
CTA (FK)

Okay, so each StaffID will be assigned to one of three functions (CML,
RA, CTA). Each Trial has one CML, one RA and one CTA each. Are my
relationships okay so far?

What I need to do is insert a subdatasheet on the Staff table (this is
for my use, not the users') that will show what trial each StaffID is
working on, and who is working on that trial with them. The problem
is, the underlying query for the subdatasheet must include a "StaffID"
field from the tblStaff, because that's the field on the table that
will act as the "Link Master Fields" field (with tblCML.StaffID;
tblRA.StaffID and tblCTA.StaffID as the corresponding "Link Child
Fields"), but if I include it in the query, it prompts the user for
the StaffID. I just want it to show for whatever StaffID I expand on
the parent tblStaff.

If this is a really simple fix, I'm very sorry. I think I've been
working on this for days now and perhaps I've made it more complex in
my mind than it needs to be. I do believe it's a little trickier than
it sounds, though...

Please help so I don't waste any more sleepless nights! All
assistance is appreciated.

JS

[jsmith]

Hey, thanks both, for taking the time to respond.

Say you have a list of 6 staff (all fictitious):
StaffID 1 - Mary Smith
StaffID 2 - Paul Perry
StaffID 3 - David Brown
StaffID 4 - Jessica King
StaffID 5 - Fiona Matthews
StaffID 6 - Leslie Campbell

Each one of these has a different function, or title : Mary and Paul
are normally CMLs (Clinical Scientists), David and Jessica are
normally RAs (Research Associates) and Fiona and Leslie are normally
CTA (Trial Administrators). But let's say David is also an CML for
one trial.

Now, say we have 5 trials:
TrialID 1 - 159.46
TrialID 2 - 549.51
TrialID 3 - 784.61
TrialID 4 - 366.89
TriaID 5 - 987.42

Each trial has one CML, one RA and one CTA assigned, and ONLY one of
each. The problem is, in rare occasions, one staff member MAY be a
CML on one trial and an RA on another trial. THAT's why I had to make
CML, RA and CTA tables, instead of just assigning them their titles on
the Staff table. Therein lies the problem. So, each CML, RA and CTA
is associated with a Staff ID, and each Trial is associated with a
CMLID, RAID and CTAID.

When I want to look at say, David Brown on the staff table (staff ID
3), and press the + to see the subdatasheet, I want to see all the
trials he is workin on, in what capacity (CML, RA, or CTA), as well as
the remaining members of his team. But there's no staffID field on
the Trial table to be the Child field to link to the master field of
staffID on the staff table.

My sub-dataset would be (for David):

Trial ID 2 Trial ID 4
Trial No. 549.51 Trial No. 366.89
CML Mary CML Paul
RA David RA Jessica
CTA Leslie CTA Fiona

Does that help?
Kind regards,
JS

[jsmith]

Hey, thanks both, for taking the time to respond.

Say you have a list of 6 staff (all fictitious):
StaffID 1 - Mary Smith
StaffID 2 - Paul Perry
StaffID 3 - David Brown
StaffID 4 - Jessica King
StaffID 5 - Fiona Matthews
StaffID 6 - Leslie Campbell

Each one of these has a different function, or title : Mary and Paul
are normally CMLs (Clinical Scientists), David and Jessica are
normally RAs (Research Associates) and Fiona and Leslie are normally
CTA (Trial Administrators). But let's say David is also an CML for
one trial.

Now, say we have 5 trials:
TrialID 1 - 159.46
TrialID 2 - 549.51
TrialID 3 - 784.61
TrialID 4 - 366.89
TriaID 5 - 987.42

Each trial has one CML, one RA and one CTA assigned, and ONLY one of
each. The problem is, in rare occasions, one staff member MAY be a
CML on one trial and an RA on another trial. THAT's why I had to make
CML, RA and CTA tables, instead of just assigning them their titles on
the Staff table. Therein lies the problem. So, each CML, RA and CTA
is associated with a Staff ID, and each Trial is associated with a
CMLID, RAID and CTAID.

When I want to look at say, David Brown on the staff table (staff ID
3), and press the + to see the subdatasheet, I want to see all the
trials he is workin on, in what capacity (CML, RA, or CTA), as well as
the remaining members of his team. But there's no staffID field on
the Trial table to be the Child field to link to the master field of
staffID on the staff table.

My sub-dataset would be (for David):

Trial ID 2 Trial ID 4
Trial No. 549.51 Trial No. 366.89
CML Mary CML David
RA David RA Jessica
CTA Leslie CTA Fiona

If I base the subdatasheet on a query that involves the staff and the
trial tables, it prompts the user for the StaffID.

Does that help?
Kind regards,
JS

[paii, Ron]

<js****@bur.boehringer-ingelheim.comwrote in message
news:66**********************************@t65g2000 hsf.googlegroups.com...

Hey, thanks both, for taking the time to respond.

Say you have a list of 6 staff (all fictitious):
StaffID 1 - Mary Smith
StaffID 2 - Paul Perry
StaffID 3 - David Brown
StaffID 4 - Jessica King
StaffID 5 - Fiona Matthews
StaffID 6 - Leslie Campbell

Each one of these has a different function, or title : Mary and Paul
are normally CMLs (Clinical Scientists), David and Jessica are
normally RAs (Research Associates) and Fiona and Leslie are normally
CTA (Trial Administrators). But let's say David is also an CML for
one trial.

Now, say we have 5 trials:
TrialID 1 - 159.46
TrialID 2 - 549.51
TrialID 3 - 784.61
TrialID 4 - 366.89
TriaID 5 - 987.42

Each trial has one CML, one RA and one CTA assigned, and ONLY one of
each. The problem is, in rare occasions, one staff member MAY be a
CML on one trial and an RA on another trial. THAT's why I had to make
CML, RA and CTA tables, instead of just assigning them their titles on
the Staff table. Therein lies the problem. So, each CML, RA and CTA
is associated with a Staff ID, and each Trial is associated with a
CMLID, RAID and CTAID.

When I want to look at say, David Brown on the staff table (staff ID
3), and press the + to see the subdatasheet, I want to see all the
trials he is workin on, in what capacity (CML, RA, or CTA), as well as
the remaining members of his team. But there's no staffID field on
the Trial table to be the Child field to link to the master field of
staffID on the staff table.

My sub-dataset would be (for David):

Trial ID 2 Trial ID 4
Trial No. 549.51 Trial No. 366.89
CML Mary CML David
RA David RA Jessica
CTA Leslie CTA Fiona

If I base the subdatasheet on a query that involves the staff and the
trial tables, it prompts the user for the StaffID.

Does that help?
Kind regards,
JS

Don't store any staff information in the trial table.
You need a TrialStaff table
TrialID RoleID StaffID
2 CML 1
2 RA 3
2 CTA 6
4 CML 3
4 RA 4
4 CTA 5

[Phil Stanton]

I suggest you look at a different table Structure

TblStaff
StaffID 1
StaffName Mary Smith
etc

TblJobs
JobID 1
Job Research Assistant

TblTrials
TrialID 1
Trial TestTrial
TrialResult 159.46

TblJnTrailJobStaff
TrialID
JobID
StaffID

In this last table you need a Key of TrialJob using their respective IDs
and set to Unique. This ensures a job can only occur once per trial
A second Unique Key of TrialStaff using their respective IDs and set to
Unique. This ensures a Staff member can only occur once per trial

I suspect that you put the information in with an unbound form where you
enter the trial details, the three jobs are pre-set up and you use 3 combo
boxes ( 1 against each job) to select the staff. Then you need an update
button to add the info to the TblTrialJobStaff

Phil

<js****@bur.boehringer-ingelheim.comwrote in message
news:66**********************************@t65g2000 hsf.googlegroups.com...

Hey, thanks both, for taking the time to respond.

Say you have a list of 6 staff (all fictitious):
StaffID 1 - Mary Smith
StaffID 2 - Paul Perry
StaffID 3 - David Brown
StaffID 4 - Jessica King
StaffID 5 - Fiona Matthews
StaffID 6 - Leslie Campbell

Each one of these has a different function, or title : Mary and Paul
are normally CMLs (Clinical Scientists), David and Jessica are
normally RAs (Research Associates) and Fiona and Leslie are normally
CTA (Trial Administrators). But let's say David is also an CML for
one trial.

Now, say we have 5 trials:
TrialID 1 - 159.46
TrialID 2 - 549.51
TrialID 3 - 784.61
TrialID 4 - 366.89
TriaID 5 - 987.42

Each trial has one CML, one RA and one CTA assigned, and ONLY one of
each. The problem is, in rare occasions, one staff member MAY be a
CML on one trial and an RA on another trial. THAT's why I had to make
CML, RA and CTA tables, instead of just assigning them their titles on
the Staff table. Therein lies the problem. So, each CML, RA and CTA
is associated with a Staff ID, and each Trial is associated with a
CMLID, RAID and CTAID.

When I want to look at say, David Brown on the staff table (staff ID
3), and press the + to see the subdatasheet, I want to see all the
trials he is workin on, in what capacity (CML, RA, or CTA), as well as
the remaining members of his team. But there's no staffID field on
the Trial table to be the Child field to link to the master field of
staffID on the staff table.

My sub-dataset would be (for David):

Trial ID 2 Trial ID 4
Trial No. 549.51 Trial No. 366.89
CML Mary CML David
RA David RA Jessica
CTA Leslie CTA Fiona

If I base the subdatasheet on a query that involves the staff and the
trial tables, it prompts the user for the StaffID.

Does that help?
Kind regards,
JS

[tina]

i agree with both Phil and Ron re the tables design. for the data entry -
and basing my remarks on Phil's table layout - i'd probably use a mainform
bound to tblTrials, and a subform bound to tblJnTrialJobStaff. a little code
would append the required job records to that table when a new trial record
is entered in the mainform, and requery the subform to show those records.
then the user can move through the job records in the subform, entering a
staff member for each job, from a combobox control with RowSource using
tblStaff.

hth
"Phil Stanton" <ph**@myfamilyname.co.ukwrote in message
news:-s******************************@posted.plusnet...

I suggest you look at a different table Structure

TblStaff
StaffID 1
StaffName Mary Smith
etc

TblJobs
JobID 1
Job Research Assistant

TblTrials
TrialID 1
Trial TestTrial
TrialResult 159.46

TblJnTrailJobStaff
TrialID
JobID
StaffID

In this last table you need a Key of TrialJob using their respective

IDs

and set to Unique. This ensures a job can only occur once per trial
A second Unique Key of TrialStaff using their respective IDs and set

to

Unique. This ensures a Staff member can only occur once per trial

I suspect that you put the information in with an unbound form where

you

enter the trial details, the three jobs are pre-set up and you use 3 combo
boxes ( 1 against each job) to select the staff. Then you need an update
button to add the info to the TblTrialJobStaff

Phil

<js****@bur.boehringer-ingelheim.comwrote in message
news:66**********************************@t65g2000 hsf.googlegroups.com...

Hey, thanks both, for taking the time to respond.

Say you have a list of 6 staff (all fictitious):
StaffID 1 - Mary Smith
StaffID 2 - Paul Perry
StaffID 3 - David Brown
StaffID 4 - Jessica King
StaffID 5 - Fiona Matthews
StaffID 6 - Leslie Campbell

Each one of these has a different function, or title : Mary and Paul
are normally CMLs (Clinical Scientists), David and Jessica are
normally RAs (Research Associates) and Fiona and Leslie are normally
CTA (Trial Administrators). But let's say David is also an CML for
one trial.

Now, say we have 5 trials:
TrialID 1 - 159.46
TrialID 2 - 549.51
TrialID 3 - 784.61
TrialID 4 - 366.89
TriaID 5 - 987.42

Each trial has one CML, one RA and one CTA assigned, and ONLY one of
each. The problem is, in rare occasions, one staff member MAY be a
CML on one trial and an RA on another trial. THAT's why I had to make
CML, RA and CTA tables, instead of just assigning them their titles on
the Staff table. Therein lies the problem. So, each CML, RA and CTA
is associated with a Staff ID, and each Trial is associated with a
CMLID, RAID and CTAID.

When I want to look at say, David Brown on the staff table (staff ID
3), and press the + to see the subdatasheet, I want to see all the
trials he is workin on, in what capacity (CML, RA, or CTA), as well as
the remaining members of his team. But there's no staffID field on
the Trial table to be the Child field to link to the master field of
staffID on the staff table.

My sub-dataset would be (for David):

Trial ID 2 Trial ID 4
Trial No. 549.51 Trial No. 366.89
CML Mary CML David
RA David RA Jessica
CTA Leslie CTA Fiona

If I base the subdatasheet on a query that involves the staff and the
trial tables, it prompts the user for the StaffID.

Does that help?
Kind regards,
JS

[jsmith]

Excellent ideas! Thanks very much. I will implement these
suggestions and let you know how it goes...
JS