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 7 3926
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.boe hringer-ingelheim.comwr ote in message
news:55******** *************** ***********@r66 g2000hsg.google groups.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
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
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
<js****@bur.boe hringer-ingelheim.comwr ote in message
news:66******** *************** ***********@t65 g2000hsf.google groups.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
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
TblJnTrailJobSt aff
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 TblTrialJobStaf f
Phil
<js****@bur.boe hringer-ingelheim.comwr ote in message
news:66******** *************** ***********@t65 g2000hsf.google groups.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
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 tblJnTrialJobSt aff. 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**@myfamilyn ame.co.ukwrote in message
news:-s************** *************** *@posted.plusne t...
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
TblJnTrailJobSt aff
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 TblTrialJobStaf f
Phil
<js****@bur.boe hringer-ingelheim.comwr ote in message
news:66******** *************** ***********@t65 g2000hsf.google groups.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
Excellent ideas! Thanks very much. I will implement these
suggestions and let you know how it goes...
JS This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Philip Yale |
last post by:
I'm probably going to get shot down with thousands of reasons for
this, but I've never really heard or read a convincing explanation, so
here goes ...
Clustered indexes are more efficient at returning large numbers of
records than non-clustered indexes. Agreed? (Assuming the NC index
doesn't cover the query, of course)
Since it's only possible to have one clustered index, why is this
almost always used for the primary key, when by...
|
by: 101 |
last post by:
Taking a course on SQL. They are saying you can get better performance by
having multiple files for a group.
They then graphically show an example of "Primary" with multiple data files.
I have tried altering PRIMARY to have multiple data files and I get and
error. I have tried creating a new database with multiple PRIMARY files and
get an error.
I can ALTER and CREATE secondary files with multiple data files with no
|
by: Fred |
last post by:
Hi.
What is the usual and what are the possible fields to use for the primary
key of an intersecting table of a many-to-many relationship?
I would think the typical, most common fields would be to set the
intersecting table to have its own unique primary key. Right?
OR
|
by: BlackFireNova |
last post by:
I have an existing database, and I need to add another table to it. The
database tracks equipment, however I have a need to track ancillary
items which are purchased or added to some of the equipment separately.
In the existing table there is a serial number field, which is indexed
with no duplicates. In the new table there will be multiple entries
which must be related to the individual piece of equipment which is
entered in the...
|
by: JumpinJeff |
last post by:
I am about to revamp the most referenced table in my whole database and
am unsure the best way to approch it. The table's primary key is also
used as a part number for all the items in the table.
My first problem is that I need to seperate the single part number
field into multiple (3 or more). I could make multiple primary key's
for each of the new fields, but some would be null, and I hate dealing
with multiple primary keys. Plus this...
| |
by: MP |
last post by:
Hi
trying to begin to learn database using vb6, ado/adox, mdb format, sql
(not using access...just mdb format via ado)
i need to group the values of multiple fields
- get their possible variations(combination of fields),
- then act on each group in some way ...eg ProcessRs (oRs as RecordSet)...
the following query will get me the distinct groups
|
by: Chirag |
last post by:
I want to create a table with
member id(primary key for Students,faculty and staff )
and now i want to create issues with foreign key as member id
but in references i could not able to pass on reference as or
condition for students, faculty and staff.
Thank You,
Chirag
|
by: jcocomo |
last post by:
Hi there. I am quite new to MS-Access and databases in general, so please bear with me if this is a very basic question.
I am trying to create a database in Access 2000 that will track patients as they move from room to room in a hospital setting. I have a table for patient demographics, called “Patients”, one for room numbers and information, called “Rooms”, and one for information on movements from room to room, called “Movement.”...
|
by: newbtemple |
last post by:
New to programming and got a question. I'm trying to add a primary key in vb.net to a table I made in SQL. The table in SQL does not have a primary key. I pull that table into my program and concatenate two columns and assign a series of numbers from a for loop to rows with nulls, to make a third column that is unique enough to be a primary key.
This is where i'm getting stuck. I'm trying to figure out how to assign a primary key in VB...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
| |
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |