Help with my schema (1 Viewer)

FlagDay1987

New member
Local time
Today, 10:54
Joined
May 27, 2020
Messages
20
Hello all,

I’m going round in circles trying to design a schema to allow me to build a database for a series of medical studies.

For each study I will receive an excel table like the two examples below (simplified version of a real one). For now I’m not thinking about how I will import them into Access but will do once I’ve got the Access principle sorted. I know that I will have to split the tables into normal form.

Every patient who is recruited to each study WILL attend all the appointment (cycles) and all the specified Activities that pertain (ie the Y’s) to that appointment. Each study could have a different number of appointments and activities.

For example, every patient who is recruited to the Diabetes study will have all 7 appointments (cycles) and at appointment 3 will undergo ‘Medical History’ and ‘ECOG Status’.

Excel tables.jpg


Each patient could be on more than one study.

I have used a variety of junction tables to achieve what I think does the job for me, although I do wonder if I am over complicating it. Could anyone help or give feedback? (I have been reading up about ternary relationships but don’t fully understand how they work. Maybe these could work?)


schema.jpg


Once I am satisfied that this schema does will work. I need to find a way of when I add a patient to a study, that they inherit the pre-determined appointments and activities. Am I right in saying that this would be a series of append queries? Or could I use a form which when I add a patient to a study they automatically get populated in the ‘tbl_Patient_Study_Appointment’ and ‘tbl_Patient_Study_Appointment_Activity’

Once I’ve done that I need to work out how I will upload a new study and schedule into the database.

If anyone could help I would be most grateful. I know there are some gapping holes in my understanding of Access.

Thankyou!
 

CJ_London

Super Moderator
Staff member
Local time
Today, 10:54
Joined
Feb 19, 2013
Messages
16,553
looks reasonable to me but some general comments

not sure of the benefit of tblAppointments - presume this is populated with 'Cycle 1 Day 1'. Either way better to store Cycle and Day in separate fields, otherwise you will not be able to sort effectively - sorting 'Cycle 1 Day 15' and 'Cycle 1 Day 8' will put Day 15 before Day 8. Will use less space as well. You can concat the values together when required

not sure what 'activity_required' means - the fact the record exists implies it is required.

You may want an ordering field in tblActivity to ensure you can list them in the required order. Ditto for tblStudy

Am I right in saying that this would be a series of append queries?
Yes

Or could I use a form which when I add a patient to a study they automatically get populated in the ‘tbl_Patient_Study_Appointment’ and ‘tbl_Patient_Study_Appointment_Activity’
possibly - is it one workbook per patient? or one worksheet? Or per study? But usually better to get everything in from the get go, otherwise confusion may reign

You may want a 'patient zero' so you can populate with all the different study profiles (in which case include a 'template' boolean field in the patient table).

Then when you get a new patient you simply have some code to create new records, substituting the new patientID and PatientStudyID for the 'patient zero' ones
 

FlagDay1987

New member
Local time
Today, 10:54
Joined
May 27, 2020
Messages
20
Hi Gasman,

Can i assume that i shouldn't have posted the same question on a different forum? Apologies if so.

I have received useful and differing help from both.
 

Gasman

Enthusiastic Amateur
Local time
Today, 10:54
Joined
Sep 21, 2011
Messages
14,038
Hi Gasman,

Can i assume that i shouldn't have posted the same question on a different forum? Apologies if so.

I have received useful and differing help from both.
No, just that you should advise members when asking for help and the same query has been posted elsewhere? That is just being polite/courteous.?
Quite often the same advice is given in different forums, and then members are just wasting their time duplicating solutions.

Then there are times where a poster does not like the answers in one forum and goes elsewhere, only to get the same advice. :D
 

FlagDay1987

New member
Local time
Today, 10:54
Joined
May 27, 2020
Messages
20
not sure of the benefit of tblAppointments - presume this is populated with 'Cycle 1 Day 1'. Either way better to store Cycle and Day in separate fields, otherwise you will not be able to sort effectively - sorting 'Cycle 1 Day 15' and 'Cycle 1 Day 8' will put Day 15 before Day 8.
Thanks for the quick reply CJ_London!

I think that I should have named tbl_Appointments as tbl_Cycles instead as it will hold the names of the appointment eg Cycle1 Day1 etc. Point taken regarding about splitting up Cycle and Day.

not sure what 'activity_required' means - the fact the record exists implies it is required.
'Activity required' exists because for example the 'Physical Examination' activity is required for 'Cycle1 Day 1' but not for 'Cycle 1 Day 8' for all patients on the study. However, there may be exceptions and one patient may need that activity, so I need to be able to turn this to 'Y'.
possibly - is it one workbook per patient? or one worksheet? Or per study? But usually better to get everything in from the get go, otherwise confusion may reign
The example that I've given is actually a really simplified version. Each study will actually have it's own workbook with multiple worksheets within. (Multiple patients will inherit the schedule from that workbook.

I think I may come back to you when I start looking at importing a new schedule if that is ok? Otherwise I will trip myself up with too much information. My first worry was that my schema was flawed but from what you say I may well be on the right lines. Thankyou!
 

FlagDay1987

New member
Local time
Today, 10:54
Joined
May 27, 2020
Messages
20
No, just that you should advise members when asking for help and the same query has been posted elsewhere? That is just being polite/courteous.?
Quite often the same advice is given in different forums, and then members are just wasting their time duplicating solutions.

Then there are times where a poster does not like the answers in one forum and goes elsewhere, only to get the same advice. :D
Ok thankyou Gasman. I was unaware of that etiquette but i will regard myself as told (y):). I shall do that in future.

I assumed the community was so vast that it was unlikely that the same people would read the question. Fortunately I have received good and differing advice from both. Plus i feel like I'm badgering the same people on one forum.
 

Gasman

Enthusiastic Amateur
Local time
Today, 10:54
Joined
Sep 21, 2011
Messages
14,038
I assumed the community was so vast that it was unlikely that the same people would read the question. Fortunately I have received good and differing advice from both. Plus i feel like I'm badgering the same people on one forum.
A good few members partake in several forums.

I would have thought this etiquette would be the same for any forum?, but at least you are now aware. :)

My post was mainly to advise other members, so they did not waste their time offering duplicate advice. This way they can see (should they wish to) what has already been offered.?
 

CJ_London

Super Moderator
Staff member
Local time
Today, 10:54
Joined
Feb 19, 2013
Messages
16,553
so I need to be able to turn this to 'Y'.

better to use a boolean field to return true or false

with regards cross posting, first offenders are forgiven, second offenders are usually ignored, your choice. There is a relatively small pool of knowledgeable responders (perhaps 1% of total members) and most work across a number of forums. They give their time for free and don't want to see it wasted
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 06:54
Joined
Feb 19, 2002
Messages
42,970
Do not use embedded spaces or special characters in your object names. A simply way to indicate a question is AttendedYN. I would use PaidDT rather than PaidYN since it provides more information. Maybe even include check or CC information.

Based on one of your answers, it appears that activity is related to the appointment so Activity needs to be a child of Appointment. Then the junction table connects. Then the junction table links PatientStudyID with ActivityID. This seems to create a circular relationship but actually it is used to limit the combo which selects activities to show only those for this particular appointment. If the relationship isn't that rigid and an activity might happen at multiple appointments, then what you have would be OK. However, I would be consistent with how PK/FK pairs are named and they are not consistent in this table.

You are using both CamelCase and the_underscore names. Consistency is better. My choice is CamelCase because I don't like to shift when I type and the the_underscore requires using the shift key. Too many years of typing COBOL which was all upper case and used the-dash as a separator which did not require using the shift key. Even today, when I print something, I always use upper case as if I were writing in the boxes on a coding form. Old habits die hard:)
 

FlagDay1987

New member
Local time
Today, 10:54
Joined
May 27, 2020
Messages
20
better to use a boolean field to return true or false

with regards cross posting, first offenders are forgiven, second offenders are usually ignored, your choice. There is a relatively small pool of knowledgeable responders (perhaps 1% of total members) and most work across a number of forums. They give their time for free and don't want to see it wasted
Ok thankyou CJ_London. I will take that on board. The free advice is certainly appreciated!

Just out of interest, if I was to post the same question on two or more fora and posted a link to each other, would that be regarded as acceptable? The reason I ask is that I have received differing and very useful advice from different fora. Obviously, I'm happy accept whatever the etiquette is.

Thankyou.
 

FlagDay1987

New member
Local time
Today, 10:54
Joined
May 27, 2020
Messages
20
Do not use embedded spaces or special characters in your object names. A simply way to indicate a question is AttendedYN. I would use PaidDT rather than PaidYN since it provides more information. Maybe even include check or CC information.

Based on one of your answers, it appears that activity is related to the appointment so Activity needs to be a child of Appointment. Then the junction table connects. Then the junction table links PatientStudyID with ActivityID. This seems to create a circular relationship but actually it is used to limit the combo which selects activities to show only those for this particular appointment. If the relationship isn't that rigid and an activity might happen at multiple appointments, then what you have would be OK. However, I would be consistent with how PK/FK pairs are named and they are not consistent in this table.

You are using both CamelCase and the_underscore names. Consistency is better. My choice is CamelCase because I don't like to shift when I type and the the_underscore requires using the shift key. Too many years of typing COBOL which was all upper case and used the-dash as a separator which did not require using the shift key. Even today, when I print something, I always use upper case as if I were writing in the boxes on a coding form. Old habits die hard:)
Thank you Pat.

I will have a good read and digest your advice :)
 

FlagDay1987

New member
Local time
Today, 10:54
Joined
May 27, 2020
Messages
20
Based on one of your answers, it appears that activity is related to the appointment so Activity needs to be a child of Appointment. Then the junction table connects. Then the junction table links PatientStudyID with ActivityID. This seems to create a circular relationship but actually it is used to limit the combo which selects activities to show only those for this particular appointment.
Hi Pat,

I've given this some thought and I think I need to implement your suggestion. Study is a parent of Appointment, and Appointment is a parent of Activity. Therefore I would like each appointment to be specific to a study and an activity be specific to an appointment.

At the moment when I use the form that allows me to choose the appointments for the patient on the study, I am presented with all the appointments for all studies. This is using a combo box with row source:

Code:
SELECT tblAppointment.AppointmentID_PK, tblAppointment.AppointmentName FROM tblAppointment;

I've attached my database in case anyone has the time or inclination to see what I've done. The forms are only quite basic until i know they work correctly. The main form is frm_Stud, with sub forms: frm_pat, frm_appts, frm_activity

Below is my schema which i have tried to tidy up from the original one and I've added the junction tabled jnctStudyAppointment, but am struggling to work out how to use this to limit the choices in my combo box (on frm_appts) to only the appointments on that study (I know that the row source code above doesn't do the job). Could you help? ( I know that once i crack this, i will need to do the same between Appointment and Activity).


Schema_20210217.jpg


Thankyou
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 06:54
Joined
Feb 19, 2002
Messages
42,970
You need to include the junction table and a where clause

Where jnctStudyAppointment = Forms!yourparentform!StudyID_FK
 

FlagDay1987

New member
Local time
Today, 10:54
Joined
May 27, 2020
Messages
20
Thanks Pat,

Does that go in the row source query of the Appointment combo box on my frm_appts? If so, i can get it to work. Also I forgot to upload my database.

The parent form of frm_App is frm_pat (ie patients). Am i right in saying that frm_pat needs StudyID_FK on it?
 

Attachments

  • Option2_2_3.zip
    144.6 KB · Views: 325

Pat Hartman

Super Moderator
Staff member
Local time
Today, 06:54
Joined
Feb 19, 2002
Messages
42,970
Yes, the query goes in the RowSource of the combo.
 

FlagDay1987

New member
Local time
Today, 10:54
Joined
May 27, 2020
Messages
20
I'm afraid I'm doing something wrong or misunderstanding.

The form i am working on is called frm_appts. The combo box within this form has a row source query as below. It's parent form is frm_pat and i have put the StudyID_FK on that form. When i run the query it is asking for the parametre value. Is there anything obvious that I'm doing wrong here?


error.jpg
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 06:54
Joined
Feb 19, 2002
Messages
42,970
The form needs to be open when you run the query. If it is, check the spelling. You should be getting intellisense which helps get the spelling right.
 

FlagDay1987

New member
Local time
Today, 10:54
Joined
May 27, 2020
Messages
20
Thanks Pat,

I've tried that and used the builder to make sure i get the spelling correct.

Then I've opened the But I've opened the main form - frm_Stud, then opened the patients (frm_pat) on the Diabetes stud, then when i try to expand the patients to see the appointments (frm_appts) i get the parameter message. I'm obviously misunderstanding something here but can't get to the bottom of it.

error_210218_1135.jpg
 

Users who are viewing this thread

Top Bottom