Relationship Help - no, not THAT kind

[joy]

Hi All! A little guidance, please
This is a long explanation, but it's a lot of data, so bear with me.
I'm trying to set up a client database. Each client (~50) has a long list of daily data readings for each day that they are with us. The data is separated into 8 headings with several items under each.

Example: Under the heading of CR, there can either be NoProblems (everything under it closed) or Problems. Under Problems, there are subheadings of Machines, Course, and Plans. Under Machines, there are several options, and each of the options has a setting. If they are using one machine, i want the other machines and their settings to disappear, and if they are not using machines, I want the entire subheading to not show. Same idea with Course and Plans.

Questions:
Is there a better way to set up the data base than making separate tables for each heading, subheading and (settings for each machine be separate tables as well), and just have ON/OFF switches at each level to relat the tables?

If I use this setup, what can i use as the key index in each table that will make the distinction between one day's data and the next for a single client, but not mix up the clients because they are in on the same day?

Whoever can understand/help me with this is a GOD!
Joy

 

[Andrew Fergus]

Hello Joy
I am having some difficulty understanding the 'problems' part - setting up the tables and relationships for the clients and daily readings won't be difficult but you lost me with the problems and the machines / plans / course.

Can you explain a little more about the 'problems'? What is the industry you are working in (to help us put it into context), what are the 'sub-problems' and what are the 8 headings you mentioned?
Andrew

 

[joy]

Ok, so I guess it would help if I properly identified this as a medical records database. For this type of patient, there are 8 major foci (the headings). Under each heading, the patient can either have no problems (ie, don't need to see any of the subtopic stuff), or problems (ie, need details - this is another place I could use help with an On/Off switch).

An example of a heading would be Respiratory stuff: you have several general readings that need to be shown regardless (medications, status, plan of action, etc). In addition, they may be using one of several ventilators that each have different types of settings. Since you cannot use all the ventilators at once, I am trying to get the others to go away if one is already selected.

Now multiply this by 8 headings with 10 subheadings a piece, with changes recorded on a daily basis and you can see my predicament in the Relationships Window. I'm back to the drawing board even to the level of each table. My last attempt was like an MC Escher drawing - visually stunning, but logially impossible.

Thanks,
Joy

 

[Andrew Fergus]

Hi Joy

Apologies in advance for the long answer.

I have had a quick look at this and whilst I think I understand how the (minimum) data structures should be set up, I haven't tested this so please view this as a guideline rather than a definitive answer on how the tables etc should be set up.

I would expect the minimum fields & tables necessary for this project would be as follows :

Note : where I have indicated a 'link' between tables I have only shown the link on the many side of the relationship (please excuse my table or field names if I haven't used the medical terms in the right context).

tbl_Patients
Patient_id (key)
Patient_details
etc

tbl_Patient_Visits
Visit id (autonumber, key)
Visit_Patient_id (linked to tbl_Patients.Patient_id)
Visit_Admit_Date
Visit_Discharge_Date
Prognosis / Diagnosis etc?
Ward or assigned doctor / nurse etc?
Other visit details?

tbl_Foci
Foci_id (key)
Foci_Description
other foci details etc?

NB : assuming I am reading your example correctly, this table holds the 8 main foci.

tbl_Sub_Foci
Sub_Foci_id (key)
Sub_Foci_Description
Sub_Foci_Link (linked to tbl_Foci.Foci_id)
other sub-foci details etc?

NB : this is where you set up, what I refer to as, the 'sub-foci' and you can have many 'sub-foci' for each of the 8 'foci'

tbl_Patient_Visit_Foci
Patient_Visit_Foci_id (autonumber, key)
PVF_Visit_id (link to tbl_Patient_Visits.visit_id)
PVF_Foci_id (link to tbl_Foci.Foci_id)
PVF_Status (Yes / No field?, Problem / No Problems per your example)

NB : If I am reading your example correctly, this table will have 8 records (one for each foci) for each patient for each visit (assuming a patient visits your facility more than once) and each 'foci' will have a status of either 'problems' or 'no problems'

tbl_Readings
Reading_id (autonumber, key)
Date
Time
PVF_Link_id (linked to tbl_Patient_Visit_Foci.Patient_Visit_Foci_id)
Sub_Foci_Link (linked to tbl_Sub_Foci)
Reading_Result
Reading_Taker details?
Other reading details?

NB : this table records the many daily readings for patients currently admitted, for each of the sub-foci related to each 'foci' with a status of 'No Problems' - this might take some fancy subforms and VB programming to work but there are experts in this forum whom I sure can help. We might need to watch the relationships here given there is a 'loop' in the relationships.

I'm still not 100% clear on the 'machines' aspect, but you will need the following (as a minimum) :

tbl_Machines
Machine_id
Machine_Description
other machine details etc

And I'm not sure if the machines should link to the PVF file or the readings file (or both?). If a machine is permanently and exclusively assigned to a patient during their stay, then it should link to the PVF table. If however, one machine is wheeled around the ward and is used on multiple patients each day, then it should link to the readings table. Combined with the fact that multiple machines could be assigned to one patient then I think you need a many-to-many relationship between the machines table and the other table you link it to, like this :

tbl_Machine_Deployment
Deployment_id (autonumber, key - or just use joint key with the machine id and other id? not sure...)
Machine_id (linked to tbl_Machines.Machine_id)
Other_Link_Id (linked to either the tbl_Patient_Visit_Foci.Patient_Visit_Foci_id or to the tbl_Readings.Reading_id, not sure...)
Date_Deployed?
Date_Undeployed?
Other deployment details? (ward etc.?)

NB : This table records where a machine is assigned to either a patient_visit or a reading - beware that this is going to increase the workload of the db administrator enormously unless the machine details are captured on the readings. {Light bulb going on moment}If the machine details are captured at the time of the reading, then you could do away with this deployment table and just have another field in tbl_Readings which is a machine_id linked back to tbl_Machines - much simpler!

There are a few many-to-many relationships which may require a bit of grappling with to get your queries and forms to work as intended but I'm sure they can be resolved given time (and a question or two on MrExcel).

Please please treat this as a guideline only and not necessarily a definitive answer. I would be more than happy for others to critique this so that we can get it right for Joy.

Let us know how you get on.

HTH, Andrew.

 

[joy]

Thank you so much, i think this is going to work for the most part.

Now that i have the subforms almost complete, i'm trying to figure out how to relate them to the main form. I have considered a few ways so far:
1) Make each subheading as a line in the main table with a yes/no box that relates the YES to the subheading tables.
2) make the DATE and Patient# as both primary keys in each of the subheading tables
Another way that was suggested was to forget altogether about making things disappear and only worry about it when i get to report-making time. But then that leaves the front-end user to wade through all the extra boxes, which i can't have.

Are any of these the wisest course of action?
Thanks
Joy

 

[Andrew Fergus]

Hi Joy, in response to this:

Another way that was suggested was to forget altogether about making things disappear and only worry about it when i get to report-making time. But then that leaves the front-end user to wade through all the extra boxes, which i can't have.

Are you referring to the readings or the sub-foci? (for want of a better word). If it is the sub-foci, then you *should* be able to show just the records where the foci has a status of "Not Ok" (or whatever status you chose to have) in the linked foci. If however it is the readings, then you should only show / get readings for the sub-foci that are still "open" or unresolved. As I mentioned in my earlier post, this will probably require a bit of VB coding (not my forte) with the forms and sub-forms to only show the records you want.

You may wish to post a new topic detaling what the relevant table structures are, what you want to show on your form & subform, and what sort of "filtering" etc that you want.

Sorry for the vague answer.
Andrew.