Need help with relationship????

I am not sure if I need to use a junction table or not. I have the following
tables:

Doctor ID (PK)
DocLNM
DocFNM
etc.

PtID(PK)
PTFNM
PTLNM
etc.

DISCD(PK)
DISNM
StartDT
etc

Doctor can have many patients and patients can have many doctors as well as
many diseases. Do I have to have a junction table? Thank you.

Yes: you will have a PatientDoctor table with fields like this:
- PtID relates to the p.k. of your patient table
- DoctorID relates to the primary key of your doctor table

So if a patient has 3 doctors, their PtID will appear in 3 records in this
table.

You could use the combination of both field (PtID + DoctorID) as the primary
key of this table if you wish.

To interface this, your patient form will have a subform bound to the
PatientDoctor table. Show the subfom in Continuous Form view. Use a combo
box for the DoctorID. Now you can add as many doctors as you need in the
subform for the patient in the main form (one per row.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"TotallyConfused" wrote in
message
...
I am not sure if I need to use a junction table or not. I have the
following
tables:

Doctor ID (PK)
DocLNM
DocFNM
etc.

PtID(PK)
PTFNM
PTLNM
etc.

DISCD(PK)
DISNM
StartDT
etc

Doctor can have many patients and patients can have many doctors as well
as
many diseases. Do I have to have a junction table? Thank you.

Can my DisCd table be the junction table? DisCD can ref many doc and many
pts. Can the DIScd tble have a PK of autonumber and pT ID and DOctor ID be
in this DISCD table.

"Allen Browne" wrote:

Yes: you will have a PatientDoctor table with fields like this:
- PtID relates to the p.k. of your patient table
- DoctorID relates to the primary key of your doctor table

So if a patient has 3 doctors, their PtID will appear in 3 records in this
table.

You could use the combination of both field (PtID + DoctorID) as the primary
key of this table if you wish.

To interface this, your patient form will have a subform bound to the
PatientDoctor table. Show the subfom in Continuous Form view. Use a combo
box for the DoctorID. Now you can add as many doctors as you need in the
subform for the patient in the main form (one per row.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"TotallyConfused" wrote in
message
...
I am not sure if I need to use a junction table or not. I have the
following
tables:

Doctor ID (PK)
DocLNM
DocFNM
etc.

PtID(PK)
PTFNM
PTLNM
etc.

DISCD(PK)
DISNM
StartDT
etc

Doctor can have many patients and patients can have many doctors as well
as
many diseases. Do I have to have a junction table? Thank you.

You actually need another table and possibly TWO junction tables. A patient
sees a doctor(s) for a disease so you need a disease table:
TblDisease
DiseaseID
DiseaseName
etc

A patient could have multiple diseases so you need a patient-disease
junction table:
TblPatientDisease
PatientDiseaseID
PatientID
DiseaseID

You're not done yet! If in ALL cases a patient only sees one doctor for each
disease he has, TblPatientDisease needs to include DoctorID:
TblPatientDisease
PatientDiseaseID
PatientID
DiseaseID
DoctorID

If there's a possibility a patient may see more than one doctor for a
disease he has, TblPatientDisease needs to be:
TblPatientDisease
PatientDiseaseID
PatientID
DiseaseID

and you need a Patient-Disease-Doctor junction table:
TblPatientDiseaseDoctor
PatientDiseaseDoctorID
PatientDiseaseID
DoctorID

Steve




"TotallyConfused" wrote in
message ...

I am not sure if I need to use a junction table or not. I have the
following
tables:

Doctor ID (PK)
DocLNM
DocFNM
etc.

PtID(PK)
PTFNM
PTLNM
etc.

DISCD(PK)
DISNM
StartDT
etc

Doctor can have many patients and patients can have many doctors as well
as
many diseases. Do I have to have a junction table? Thank you.

Diseases are not related to doctors (unless doctors specialise in certain
diseases, or unless the doctors HAVE the diseases, in which case they would
be patients!)

A simple setup for you would be to have a fourth table for Cases:
CaseID (PK)
CasePatient (foreign key in a 1-M relationship with Patients)
CaseDisease (FK in a 1-M relationship with Diseases)
CaseDoctor (FK in a 1-M relationship with Doctors)
CaseOpenDate
CaseCloseDate
... etc

This design would not allow for such complexities as a patient presenting
with more that one disease, or more than one doctor being involved with a
case, but I think that trying to handle those complexities at this stage
would make you even more than TotallyConfused!

BTW, if you did want to relate doctors with their specialty diseases, then
that would be another many-many relationship and would therefore require a
junction table:
SpecID
SpecDoctor
SpecDisease

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

"TotallyConfused" wrote in
message ...
Can my DisCd table be the junction table? DisCD can ref many doc and many
pts. Can the DIScd tble have a PK of autonumber and pT ID and DOctor ID
be
in this DISCD table.

"Allen Browne" wrote:

Yes: you will have a PatientDoctor table with fields like this:
- PtID relates to the p.k. of your patient table
- DoctorID relates to the primary key of your doctor table

So if a patient has 3 doctors, their PtID will appear in 3 records in
this
table.

You could use the combination of both field (PtID + DoctorID) as the
primary
key of this table if you wish.

To interface this, your patient form will have a subform bound to the
PatientDoctor table. Show the subfom in Continuous Form view. Use a combo
box for the DoctorID. Now you can add as many doctors as you need in the
subform for the patient in the main form (one per row.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"TotallyConfused" wrote in
message
...
I am not sure if I need to use a junction table or not. I have the
following
tables:

Doctor ID (PK)
DocLNM
DocFNM
etc.

PtID(PK)
PTFNM
PTLNM
etc.

DISCD(PK)
DISNM
StartDT
etc

Doctor can have many patients and patients can have many doctors as
well
as
many diseases. Do I have to have a junction table? Thank you.

Oooh! oooh! What about a doctor that specializes in a disease that s/he
has?!

Jeff B.

"Graham Mandeno" wrote in message
...
Diseases are not related to doctors (unless doctors specialise in certain
diseases, or unless the doctors HAVE the diseases, in which case they
would be patients!)

A simple setup for you would be to have a fourth table for Cases:
CaseID (PK)
CasePatient (foreign key in a 1-M relationship with Patients)
CaseDisease (FK in a 1-M relationship with Diseases)
CaseDoctor (FK in a 1-M relationship with Doctors)
CaseOpenDate
CaseCloseDate
... etc

This design would not allow for such complexities as a patient presenting
with more that one disease, or more than one doctor being involved with a
case, but I think that trying to handle those complexities at this stage
would make you even more than TotallyConfused!

BTW, if you did want to relate doctors with their specialty diseases, then
that would be another many-many relationship and would therefore require a
junction table:
SpecID
SpecDoctor
SpecDisease

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

"TotallyConfused" wrote in
message ...
Can my DisCd table be the junction table? DisCD can ref many doc and
many
pts. Can the DIScd tble have a PK of autonumber and pT ID and DOctor ID
be
in this DISCD table.

"Allen Browne" wrote:

Yes: you will have a PatientDoctor table with fields like this:
- PtID relates to the p.k. of your patient table
- DoctorID relates to the primary key of your doctor table

So if a patient has 3 doctors, their PtID will appear in 3 records in
this
table.

You could use the combination of both field (PtID + DoctorID) as the
primary
key of this table if you wish.

To interface this, your patient form will have a subform bound to the
PatientDoctor table. Show the subfom in Continuous Form view. Use a
combo
box for the DoctorID. Now you can add as many doctors as you need in the
subform for the patient in the main form (one per row.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"TotallyConfused" wrote in
message
...
I am not sure if I need to use a junction table or not. I have the
following
tables:

Doctor ID (PK)
DocLNM
DocFNM
etc.

PtID(PK)
PTFNM
PTLNM
etc.

DISCD(PK)
DISNM
StartDT
etc

Doctor can have many patients and patients can have many doctors as
well
as
many diseases. Do I have to have a junction table? Thank you.

Trust you to make things difficult, Skippy g

--
Cheers,
Graham

"Jeff Boyce" wrote in message
...
Oooh! oooh! What about a doctor that specializes in a disease that s/he
has?!

Jeff B.

"Graham Mandeno" wrote in message
...
Diseases are not related to doctors (unless doctors specialise in certain
diseases, or unless the doctors HAVE the diseases, in which case they
would be patients!)

A simple setup for you would be to have a fourth table for Cases:
CaseID (PK)
CasePatient (foreign key in a 1-M relationship with Patients)
CaseDisease (FK in a 1-M relationship with Diseases)
CaseDoctor (FK in a 1-M relationship with Doctors)
CaseOpenDate
CaseCloseDate
... etc

This design would not allow for such complexities as a patient presenting
with more that one disease, or more than one doctor being involved with a
case, but I think that trying to handle those complexities at this stage
would make you even more than TotallyConfused!

BTW, if you did want to relate doctors with their specialty diseases,
then that would be another many-many relationship and would therefore
require a junction table:
SpecID
SpecDoctor
SpecDisease

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

"TotallyConfused" wrote in
message ...
Can my DisCd table be the junction table? DisCD can ref many doc and
many
pts. Can the DIScd tble have a PK of autonumber and pT ID and DOctor ID
be
in this DISCD table.

"Allen Browne" wrote:

Yes: you will have a PatientDoctor table with fields like this:
- PtID relates to the p.k. of your patient table
- DoctorID relates to the primary key of your doctor table

So if a patient has 3 doctors, their PtID will appear in 3 records in
this
table.

You could use the combination of both field (PtID + DoctorID) as the
primary
key of this table if you wish.

To interface this, your patient form will have a subform bound to the
PatientDoctor table. Show the subfom in Continuous Form view. Use a
combo
box for the DoctorID. Now you can add as many doctors as you need in
the
subform for the patient in the main form (one per row.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"TotallyConfused" wrote in
message
...
I am not sure if I need to use a junction table or not. I have the
following
tables:

Doctor ID (PK)
DocLNM
DocFNM
etc.

PtID(PK)
PTFNM
PTLNM
etc.

DISCD(PK)
DISNM
StartDT
etc

Doctor can have many patients and patients can have many doctors as
well
as
many diseases. Do I have to have a junction table? Thank you.

We each have our talents...

S.
"Graham Mandeno" wrote in message
...
Trust you to make things difficult, Skippy g

--
Cheers,
Graham

"Jeff Boyce" wrote in message
...
Oooh! oooh! What about a doctor that specializes in a disease that s/he
has?!

Jeff B.

"Graham Mandeno" wrote in message
...
Diseases are not related to doctors (unless doctors specialise in
certain diseases, or unless the doctors HAVE the diseases, in which case
they would be patients!)

A simple setup for you would be to have a fourth table for Cases:
CaseID (PK)
CasePatient (foreign key in a 1-M relationship with Patients)
CaseDisease (FK in a 1-M relationship with Diseases)
CaseDoctor (FK in a 1-M relationship with Doctors)
CaseOpenDate
CaseCloseDate
... etc

This design would not allow for such complexities as a patient
presenting with more that one disease, or more than one doctor being
involved with a case, but I think that trying to handle those
complexities at this stage would make you even more than
TotallyConfused!

BTW, if you did want to relate doctors with their specialty diseases,
then that would be another many-many relationship and would therefore
require a junction table:
SpecID
SpecDoctor
SpecDisease

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

"TotallyConfused" wrote in
message ...
Can my DisCd table be the junction table? DisCD can ref many doc and
many
pts. Can the DIScd tble have a PK of autonumber and pT ID and DOctor
ID be
in this DISCD table.

"Allen Browne" wrote:

Yes: you will have a PatientDoctor table with fields like this:
- PtID relates to the p.k. of your patient table
- DoctorID relates to the primary key of your doctor table

So if a patient has 3 doctors, their PtID will appear in 3 records in
this
table.

You could use the combination of both field (PtID + DoctorID) as the
primary
key of this table if you wish.

To interface this, your patient form will have a subform bound to the
PatientDoctor table. Show the subfom in Continuous Form view. Use a
combo
box for the DoctorID. Now you can add as many doctors as you need in
the
subform for the patient in the main form (one per row.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"TotallyConfused" wrote in
message
...
I am not sure if I need to use a junction table or not. I have the
following
tables:

Doctor ID (PK)
DocLNM
DocFNM
etc.

PtID(PK)
PTFNM
PTLNM
etc.

DISCD(PK)
DISNM
StartDT
etc

Doctor can have many patients and patients can have many doctors as
well
as
many diseases. Do I have to have a junction table? Thank you.