Advice on Database Design (1 Viewer)

ro801 · Oct 30, 2017

Hope someone can help, I've sweated over this all day.

I have 3 tables, Demographics, Admissions and Attendances.
Patient's records are stored in Demographics (obvs) and each can have more than one Admission and numerous Attendances within those Admissions.

I'd like to be able to represent each Admission on a form with each of the associated Attendances documented in a subform at the bottom.

I thought this would be easy, but its just not happening :banghead:

Any help would be appreciated

Mark_ · Oct 30, 2017

Thee forms should be involved;
Parent form (Demographics) used to identify one patient.
Sub form on Parent form for admissions.
Sub form on admissions for attendance.

Is this how you've set up your structure? Also have you established relationships for your data?

ro801 · Oct 30, 2017

Mark_ said:
Thee forms should be involved;
Parent form (Demographics) used to identify one patient.
Sub form on Parent form for admissions.
Sub form on admissions for attendance.

Is this how you've set up your structure? Also have you established relationships for your data?

That’s great thanks. I think I missed a step and had gone with an Attendance subform on the Admissions form only.

I constantly scratch my head with relationships TBH, what do you suggest would be the most efficient? Thanks for your help

Mark_ · Oct 30, 2017

Parent is your patient. Admissions is the child and would have a copy of the Patient's "ID" (Primary Key) and would be related as "One to Many" from the patient.

Attendances would be related the same way to Admissions.

NOTE: For sanity, you may want to spend some time working out exactly how you wish to name the tables. Demographics sounds like a subset of a patients record rather than the proper reference for those seeking treatment.

With regards to linking Attendants to a given admission, you would really want a linking file that holds both a primary key for "Attendance" and for "Admissions".

The following two links should help you understand setting up a many to many relationship. Many thanks to Pat for providing them!
https://www.access-programmers.co.uk/forums/showthread.php?t=228078
https://www.access-programmers.co.uk/forums/showthread.php?t=153458

Pat Hartman · Oct 30, 2017

You can't nest continuous forms so if you want to show all three on a single form, you'll need to use three subforms on a main form. I've attached a picture. In my picture, the list on the left is endorsements for a policy type. the upper right form is the details of the selected endorsement. The lower right form is the fillable data fields in the endorsement document. In your case, I would make the demographics single form the one on the left and the admissions and attendance list style forms stacked on the right.

ro801 · Nov 9, 2017

Thank for this Mark, I had to put it down for a week or so to preserve my sanity!
I think I've managed it now... not sure how I managed it... but I did nonetheless.

Mark_ said:
Parent is your patient. Admissions is the child and would have a copy of the Patient's "ID" (Primary Key) and would be related as "One to Many" from the patient.

Attendances would be related the same way to Admissions.

NOTE: For sanity, you may want to spend some time working out exactly how you wish to name the tables. Demographics sounds like a subset of a patients record rather than the proper reference for those seeking treatment.

With regards to linking Attendants to a given admission, you would really want a linking file that holds both a primary key for "Attendance" and for "Admissions".

The following two links should help you understand setting up a many to many relationship. Many thanks to Pat for providing them!
https://www.access-programmers.co.uk/forums/showthread.php?t=228078
https://www.access-programmers.co.uk/forums/showthread.php?t=153458

ro801 · Nov 9, 2017

Thanks for the help Pat. Would this then not require the Many to Many relationships that'd so doing my head in?.. I suspect not but I thought I'd cross my fingers.
How would you select one patient on the left and then show the Admissions on the right where one could then also be selected and then this shows the Attendances (for that patient and admission) below that?

Pat Hartman said:
You can't nest continuous forms so if you want to show all three on a single form, you'll need to use three subforms on a main form. I've attached a picture. In my picture, the list on the left is endorsements for a policy type. the upper right form is the details of the selected endorsement. The lower right form is the fillable data fields in the endorsement document. In your case, I would make the demographics single form the one on the left and the admissions and attendance list style forms stacked on the right.

Pat Hartman · Nov 9, 2017

Some relationships are many-to-many but we don't talk about them that way because of the larger context. For example Attendances I'm assuming means medical professionals who are working on the case. Technically the Attendances table is m-m between Admissions and Medical professionals because of the two foreign keys. But since the table is essentially about what is happening to the patient for this admission and the FK to the Medical Professionals is just another piece of data.

Demographics to Admissions is 1-m because one patient can have many admissions but each Admission is related to only one patient.

Does that help?

ro801 · Nov 9, 2017

Almost, there is one patient who would have multiple Admissions and each Admission might have multiple Attendances to Hospital. Awkward terminology I know, but I wanted to closely map the terminology of the main Hospital where Admissions have lots of little ‘episodes’ called Attendances hanging off them. For example a patient might be admitted as an outpatient on 01/01/17, they would have 3 separate Attendances to my unit on the 2nd, 3rd and 4th and I’d discharge the Admission on the 5th. If I needed to I would re-admit and repeat the cycle as many times as required. Does this change what you would recommend?

Pat Hartman said:
Some relationships are many-to-many but we don't talk about them that way because of the larger context. For example Attendances I'm assuming means medical professionals who are working on the case. Technically the Attendances table is m-m between Admissions and Medical professionals because of the two foreign keys. But since the table is essentially about what is happening to the patient for this admission and the FK to the Medical Professionals is just another piece of data.

Demographics to Admissions is 1-m because one patient can have many admissions but each Admission is related to only one patient.

Does that help?

Pat Hartman · Nov 10, 2017

Now I really don't know what an attendance is. If there are not two foreign keys in tblAddendances, then it isn't a junction table and it isn't m-m it is 1-m.

Advice on Database Design (1 Viewer)

ro801

Registered User.

Mark_

Longboard on the internet

ro801

Registered User.

Mark_

Longboard on the internet

Pat Hartman

Super Moderator

Attachments

ro801

Registered User.

ro801

Registered User.

Pat Hartman

Super Moderator

ro801

Registered User.

Pat Hartman

Super Moderator

Similar threads

Users who are viewing this thread