Table design and pulling in primary key values

[QntmPaige]

Hi - I have a database that stores data for a person's demographic/contact
info, the studies they've participated in, and details for each study. I
have my tables set up as follows:

Table Demographics
DemographID (primary key)
name
address, etc.

Table Studies
WhichStudyID (primary key)
DemographID (foreign key linked to table demographics)
Study
Enroll Date
Study number

Table Study A
StudyAID (primary key)
WhichStudyID (foreign key linked to table studies)
DemographID
information for study A

Table Study A set up will be repeated for numerous studies, each with its
own set of information, but always linked back to the Studies table through
WhichStudyID

The Studies Table is connected to the Studies Form, which is a continuous
subform on the Demographics form. So a person can be in multiple studies,
each will have its own WhichStudyID. For each study, there is a separate
form that opens when a user hits a command button - this form is dependent on
the study name selected from a drop-down menu. So if the user selects StudyA
in the first section of the Studies continuous subform, the Study A form
opens to the record for that person. They can then enter a second study in
the next section of the continuous subform and click the button to go to that
studies form.

Up to that point, things are working fine. What I'm having trouble with is
getting the WhichStudyID to carry over into the StudyA table. The form opens
to the correct record for that person, but when I go to the table for StudyA,
the data on the form is there but not WhichStudyID.

Question 1 - are my tables set up in a way that makes sense?
Question 2 - how do I get WhichStudyID to populate into the Study A table?

Please let me know if you need additional information (the VBA code,
properties, etc.) - thanks so much for your help!!

 

[Jeff Boyce]

I'm a little puzzled why you are putting the DemographID in each of the
other tables.

Here's another way to conceptualize the entities/relationships:

* One Person can participate in many Studies
* One Study can include many Participants

tblPerson
PersonID
LastName
FirstName
... (other person-specific info)

tblStudy
StudyID
StudyName
... (other study-specific info)

trelParticipant
ParticipantID
PersonID
StudyID
DateOfParticipation
... (other participation-specific info)

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[QntmPaige]

Hi Jeff,

Thanks for your reply! Basically I'll want to know three pieces of
information -
1. Who the person is (captured by tblDemographics),
2. What studies they've participated in and on which date they enrolled
(captured by tbl whichstudy),
3. What occured in that study on that date (what procedures did they
undergo, etc.) - study specific and person specific info

One person can participate in many studies (but will not participate in all)
Each study will have many participants
For each person, a specific study will only have one set of data (once
someone is enrolled in StudyA, they will not be enrolled in StudyA again)

As I understand it, your tblPerson = my tblDemographics. Your
tblParticipant = my tblWhichStudy, and your tblStudy = my tblStudyA, StudyB
etc. So you're proposed layout removes the primary key from the Person table
(your tblPerson or my tblDemographics) from the Study specific table.

I don't know that each table needs DemographID in it, I suppose it makes
more sense not to have it. My problem still remains though that I can't get
WhichStudyID to populate into tblStudyA, etc. I had thought that linking
tblWhichStudy to tblStudyA through WhichStudyID would pull that information
into tblStudyA...I guess I'm misunderstanding that process?

Thanks again!

 

[Jeff Boyce]

Notice that the third table is use as a "junction/resolver/relationship"
table to show valid combinations of Person and Study. The PersonID in
tblPerson is the Primary Key. Likewise, the StudyID in tblStudy.

You could either use the pair PersonID + StudyID as a multi-column Primary
Key in the trelParticipation table, or you could use a unique index on that
combination (to prevent duplicate 'enrollments'), and use, as I did, a
Primary Key of ParticipantID.

To get the information back out (remember, Access is a relational database
product), you would create queries that join the tables on their related key
fields. Care to find all the folks who participated in StudyA? Use a
query.

All the studies participated in by Person17? Again, use a query.

Regards

Jeff Boyce
Microsoft Office/Access MVP