Multi categories question (1 Viewer)

[cluendo]

Hello. I have one question for you guys. I am not sure how approach the problem I have so I hope someone will be able to help me.

Here it is:
I have one table for "Patients" (ID, Name, bla, bla, bla)
I have another table "Visits" which is in one-to-many relationship with table "Patients", so that each individual patient can have multiple visit dates.
Now I want to have a categories of different doctors, lets say 4 doctors.

I am not quite sure how it would be logical to approach this problem?
What is not clear to me:
1. Do I need 1 or 4( each table for each doctor) tables?
1.1. How many tables for my "Categories" must be?
2. What relationship between tables must be(and fields)
3. How can I have multiple categories(doctors) for the one individual patient?
4. How my form should look like: I mean, do I need to use "tick" boxes or something else?


Hope someone who will read this understands what I want to achieve.
Thank you !

 

[MSAccessRookie]

Below is an approach that uses only three Tables. Note that this approach not only allows a Doctor to be a Patient of another Doctor, but also allows more than one Doctor to handle the same Reason for a visit. You will need to fill in any of the additional details.

tblPeople - Table With Primary Key PersonID along with any other information that is related to people.
tblVisitReasons - Table With Primary Key ReasonID along with any other information that is related to reasons for Visits
tblVisits - Junction Table With Primary Key VisitID along with a Foreign Key to tblPeople for the Patient, a second Foreign Key to tblPeople for the Doctor, and a Foreign Key to tblVisitReasons for the reasons for the Visit, along with any other information that is related to to the particular Visit.

Your Forms and reports can be designed once the Required Data Tables have been established.

 

[cluendo]

Thank you! But it's not completely clear to me, how I am going to select Which patient belongs to which doctor(s) ?

So far I have "Patients" table one to many relationship to table "Visits". I've attached image to better see.

Now I just need to be able to select the "tick box" on form and so select the Doctor(s) to which that patient belongs.

If you be kind, can you explain further as I am a little bit confused among many relationships between tables..

Thanx again!

 

[cluendo]

Anyone ? sorry for impatience.

 

[MSAccessRookie]

Thank you! But it's not completely clear to me, how I am going to select Which patient belongs to which doctor(s) ?

So far I have "Patients" table one to many relationship to table "Visits". I've attached image to better see.

Now I just need to be able to select the "tick box" on form and so select the Doctor(s) to which that patient belongs.

If you be kind, can you explain further as I am a little bit confused among many relationships between tables..

Thanx again!

The reason that I named the proposed Table tblPeople and not tblPatients, was that it can be used as the source for any Field that represents a person.

In your Visits Table, Add another Field for the Doctor's Name.
In the Design View of the Query, Add a second copy of tblPeople and Link the new Copy to the Doctor Field.
In your Form, you should avoid using anything Static like Radio Buttons. After all, what will happen if a Doctor Leaves the Service, ort if a New Doctor Joins the Service? Adding a Dropdown or similar method to select the Doctor is a good alterrnative.

You may need to have a way to identify your Doctors. One way is to create a new Table tblDoctors that contains the PersonID values for people that are Doctors. There are other ways, but you might want to try this one first.

 

[cluendo]

Thank you! I'm on the track now. I am almost done, but one thing is still not clear.
Images how it looks now:
Query:

tables relationships:

What do you actually mean by "In the Design View of the Query, Add a second copy of tblPeople and Link the new Copy to the Doctor Field."

Is my arrangement OK now(see images) or do I need something else?
As far as I understand, I will need to use Queries to be able to sort out which patient belongs to which doctor, am I right? Use a listbox or smth.

Also, I abandoned idea to use radio buttons or tick boxes. I will use only combo box on my "Visit form". (correct? ) For example, if the same Patient went to 2 different doctors on the same day, I can use "Visit form" to manually add 2 same dates but for each date choose a different doctor.

When you said "After all, what will happen if a Doctor Leaves the Service, ort if a New Doctor Joins the Service? " I will created a new form which will be used to add new Doctor or remove the current one(correct?)


Your answers are just brilliant, thank you for your help and your time! Hope you will answer this question too!

Looking forward to hear from you

 

[MSAccessRookie]

I strongly recomment that you use a more Generic name (like tblPeople) for the Person related Information, to avoid confusion inthe future. Below are answers to your Questions.

--------------------------------------------------------------------------------

What do you actually mean by "In the Design View of the Query, Add a second copy of tblPeople and Link the new Copy to the Doctor Field."

I presumed that from your original responses, that your Query was made using Design View as opposed to SQL View.

Is my arrangement OK now(see images) or do I need something else?
As far as I understand, I will need to use Queries to be able to sort out which patient belongs to which doctor, am I right? Use a listbox or smth.

What you have displayed represents what I was talking about, with the exception of the fact that the tblDoctors would not require the name of the Doctor. Since you will already have the name in tblPeople, you can get it there when you need it. When Desiging a Database, remember to try as much as possible to keep it NEAT (Never Enter Anything Twice).


Also, I abandoned idea to use radio buttons or tick boxes. I will use only combo box on my "Visit form". (correct? ) For example, if the same Patient went to 2 different doctors on the same day, I can use "Visit form" to manually add 2 same dates but for each date choose a different doctor.

As long as you are satisfied with that approach, I believe that it will fulfill the needs you have outlined.

When you said "After all, what will happen if a Doctor Leaves the Service, ort if a New Doctor Joins the Service? " I will created a new form which will be used to add new Doctor or remove the current one(correct?)

A new Query/Form/Report would not be needed. For a new Doctor, a new Record would need to be added to tblPeople, and a new Record would need to be added to tblDoctors. If a Doctor Leaves the Service, then a Record would need to be removed from tblDoctors. A record would not need to be removed from tblPeople.