Many to many relationships in MS Access

[akasmia]

I am new to this so please forgive my simplicity!

I have the following tables:
Patients
Meds
Hospitals
Providers
Diagnoses
Visits

I have edtablished many-to-many relationships as follows:
Patients-Providers (Subform esablished)
Patients-Meds (Subform esablished)
Patients-Diagnoses (Subform esablished)

I am trying to achieve the following simple database:
Patients as a main form with the subforms listed above
- Patient cane be seen at diffirents dates (one vist per date)
- Patients can be seen by different provider at on differents dates
- Patients can have one main diagnosis per visit per date
- Patients can have many secondary diagnoses as a separete list (see subforms mentioned above)
- Providers can see different patients on different dates
- Patients can be seen at differents hospital on different dates by different providers
- Visits subform to shaw visits (dates)

 

[AccessMoron]

What is your Question?

 

[akasmia]

I am sorry that the question was left out!

QUESTION:
How can I establish a relationship between PATIENTS, PROVIDERS, NOTES, HOSPITALS, and DATES OF VISIT such that when you click a DATE OF VISIT you will see: PROVIDER, NOTE, HOSPITAL(or office), and the MAIN DIAGNOSIS?. NOTES are stored in a memo and there is one note per date of visit per provider. Usually a PROVIDER sees a PATIENT only once per DATE OF VISIT. Different PRVIDERS can see a PATIENT on the same date.

I imagine a main form to be based on the PATIENTS TABLE, and a Subform based on DATES OF VISIT. Each time you click next record it will take you to the next PATIENT and her/his DATES OF VISIT. Clicking on a DATE OF VISIT will give you the visit details (PROVIDER, NOTE, MAIN DIGNOSIS, and HOSPITAL (or office). List of his/her DIAGNOSES and MEDICATIONS can be two additional subforms on the main form.


Sounds confusing, sorry!!.

In real life a provider (e.g. nurse procitioner) sees a patient at hospital(or office x), makes a diagnosis (main diagnosis), lists all medications, and writes a note (consult) on that date.

 

[demoman]

Howdy

This is not confusing. A very standard database design, and you must complete a good design before embarking on form creation.

What you need is a transaction table to record each unique event, which you briefly describe:

"In real life a provider (e.g. nurse procitioner) sees a patient at hospital(or office x), makes a diagnosis (main diagnosis), lists all medications, and writes a note (consult) on that date."

Is this the table you are calling 'visit'? If so, you should first make sure your :

Patients
Meds
Hospitals
Providers

tables have a key reference column (i.e., PatientID, MedicineID, HospitalID, ProviderID), becuase these are what you will actually store in your 'visits' table. In addition, you will need the VisitID (a unique ID), VisitDate and VisitTime. You will ultimately find MANY additional columns to add.

Now, each visit may have more than one purpose, and resulting diagnosis. So, you need a table for Diagnosis which has the VisitID as a foriegn key.

Finally, since a diagnosis may have more than one prescription (love that word), you need an additional table for that. It will use the unique key for your Diagnosis table as it's foriegn key reference.

I have developed one medical billing system, so I do have some practical experience. I would highly recommend you read much of the very good info on this forum. There are some very experienced pros, who have posted a lot of help on design issues. Many are better written than mine.

 

[akasmia]

demoman
thanks for your help. This will certainly give me a good starting point!

I am not sure that every diagnosis will need a prescrition, and in this case the table "Medications" or "Meds" will be used instead of prescritions.

Each patient may have several diagnoses, several visits, several medications none, and be seen by several providers even on the same day.

The number of medications and number of diagnoses do not necessarily have to be equal.

The number of visits is not necessarily eqaul to the number of diagnoses because a visit does not gecessarily generate a new diagnosis.

 

[demoman]

Let me know how you do.