If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
Need help with relationship????
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. |
#3
|
|||
|
|||
Need help with relationship????
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. |
#4
|
|||
|
|||
Need help with relationship????
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. |
#5
|
|||
|
|||
Need help with relationship????
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. |
#6
|
|||
|
|||
Need help with relationship????
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. |
#7
|
|||
|
|||
Need help with relationship????
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. |
#8
|
|||
|
|||
Need help with relationship????
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. |
Thread Tools | |
Display Modes | |
|
|