mcss
asked on
Many to Many issue
I have a situation where I am writing a program in VB6.0 and I nned to create all the tables and relationships first in Access97 first and I am unsure how to handle this situation.
What I am doing is creating a dB for a hospital that will list doctors and the procedures they are qualified to perform. What I am wanting to do is have a table for the doctors(that will list there profile information) and a list of procedures(the procedure table will also have fields that contain info concerning that procedure). Now what I am having problems with is that each doctor(hundreds of them) might be qualified to perform 50 or more procedures. So how can I build these relationships so when I run a query on Dr. Smith that I get a list of all the prodcedures that he is qualified to perform(each doctor will average about 30+ procedures)?
What I am doing is creating a dB for a hospital that will list doctors and the procedures they are qualified to perform. What I am wanting to do is have a table for the doctors(that will list there profile information) and a list of procedures(the procedure table will also have fields that contain info concerning that procedure). Now what I am having problems with is that each doctor(hundreds of them) might be qualified to perform 50 or more procedures. So how can I build these relationships so when I run a query on Dr. Smith that I get a list of all the prodcedures that he is qualified to perform(each doctor will average about 30+ procedures)?
Hi, mcss.
You need 3 tables:
- tblDoctors (DoctorID, FirstName, LastName,...) - DoctorID is key
- tblProcedures(ProcID, ProcDescription, ...) - ProcID is key
- tblDoc_Proc(DoctorID, ProcID) - DoctorID and ProcID are foreign keys.
tblDocProc provides "many-to-many" relationship.
Do you need further assist and detal instructions how you can do all you need in Access?
You need 3 tables:
- tblDoctors (DoctorID, FirstName, LastName,...) - DoctorID is key
- tblProcedures(ProcID, ProcDescription, ...) - ProcID is key
- tblDoc_Proc(DoctorID, ProcID) - DoctorID and ProcID are foreign keys.
tblDocProc provides "many-to-many" relationship.
Do you need further assist and detal instructions how you can do all you need in Access?
Sorry, Mirkwood, don't see your answer...
mcss,
Mirkwood was faster than I and win points.
By the way, your query should be like this:
SELECT tblProcedures.ProcDescript
FROM tblProcedures INNER JOIN (tblDoctor INNER JOIN tblDoc_Proc ON tblDoctor.DoctorID = tblDoc_Proc.DoctorID) ON tblProcedures.ProcID = tblDoc_Proc.ProcID
WHERE (((tblDoctor.FirstName)="J
Regards,
Dedushka
Mirkwood was faster than I and win points.
By the way, your query should be like this:
SELECT tblProcedures.ProcDescript
FROM tblProcedures INNER JOIN (tblDoctor INNER JOIN tblDoc_Proc ON tblDoctor.DoctorID = tblDoc_Proc.DoctorID) ON tblProcedures.ProcID = tblDoc_Proc.ProcID
WHERE (((tblDoctor.FirstName)="J
Regards,
Dedushka
Access does have size limit on the database, set at 2mb. I know that sounds like alot but when dealing with hospital records that memory gets eaten up very quickly. I would suggest going with Oracle or SQL.
boodabelly: Can you elaborate a bit more on this size limit issue?
Cheers,
Raymond.
Cheers,
Raymond.
2Mb in nothing for Access database. I would say 2Gb. Once I worked with 40Mb MDB file and it was OK.
ASKER
The only reason I am rejecting this is because I am wanting more details for this solution. I will actually give someone an extra 50 points if they would actually give me a sample .MDB and I would give an EXCELLENT rating(which will result in bonus points as well). I have not tried dedushka's answer however he/she gave the only accptable answer from the bunch. FYI Access has a 2gig limit per dB. I can deal with size limits later, I just need the basic architecture for now.
My intentions are not to irritate anyone I just want a lot of details so I fully understand the process.
Thanks you all for your help.
My intentions are not to irritate anyone I just want a lot of details so I fully understand the process.
Thanks you all for your help.
Hi mcss.
Are you ready to go step by step or need a sample .mdb file?
Are you ready to go step by step or need a sample .mdb file?
Mirkwood's answer was correct
When you have a many to many relationship, you must go to a 3rd table with two one-to-many relationships.
Look in the northwind database or the biblio, you'll find some examples there too.
When you have a many to many relationship, you must go to a 3rd table with two one-to-many relationships.
Look in the northwind database or the biblio, you'll find some examples there too.
Tip to reduce db size
Per doctor:
3rd table (DoctorCanDoProcedure) will contain
30 records (each record 1 long and 1 integer field)
That is 30 longs and 30 integers.
This can be reduced to 2 longs (or 4 integers), appended to Doctors table.
You'll have 50 CheckBoxes on your form.
Name all your checkboxes: "chkFlag" (0 to 49)
Add "FlagA" field to your table (Long) - this will be used for first 30 checkboxes.
Add "FlagB" field to your table (Long) - this will be used for other 20 checkboxes.
To populate your form with data from your recordset:
Dim tmpFlag As Long
If Not IsNull(rs!FlagA) Then
tmpFlag = rs!FlagA
chkFlag(0).Value = Abs((tmpFlag And 1) = 1)
chkFlag(1).Value = Abs((tmpFlag And 2) = 2)
chkFlag(2).Value = Abs((tmpFlag And 4) = 4)
' ...
chkFlag(29).Value = Abs((tmpFlag And 2^29) = 2^29)
Else
chkFlag(0).Value = 0 ' or any default value
chkFlag(1).Value = 0
' ...
chkFlag(29).Value = 0
End If
To Save your data:
Dim tmpFlag As Long
tmpFlag = 0
tmpFlag = tmpFlag + (1 * chkFlag(0).Value)
tmpFlag = tmpFlag + (2 * chkFlag(1).Value)
tmpFlag = tmpFlag + (4 * chkFlag(2).Value)
' ...
tmpFlag = tmpFlag + (2^29 * chkFlag(29).Value)
rs!FlagA = tmpFlag ' update field
Do the same with other 20 checkboxes and your rs!FlagB field
Drawback:
It will be problem to create your SQL statements, to select doctors that can do certain procedure, e.g:
Select * from Doctors where ((FlagA And 4) = 4) ...
won't work. :(
Hope you don't need this. If you do, just forget this tip.
Per doctor:
3rd table (DoctorCanDoProcedure) will contain
30 records (each record 1 long and 1 integer field)
That is 30 longs and 30 integers.
This can be reduced to 2 longs (or 4 integers), appended to Doctors table.
You'll have 50 CheckBoxes on your form.
Name all your checkboxes: "chkFlag" (0 to 49)
Add "FlagA" field to your table (Long) - this will be used for first 30 checkboxes.
Add "FlagB" field to your table (Long) - this will be used for other 20 checkboxes.
To populate your form with data from your recordset:
Dim tmpFlag As Long
If Not IsNull(rs!FlagA) Then
tmpFlag = rs!FlagA
chkFlag(0).Value = Abs((tmpFlag And 1) = 1)
chkFlag(1).Value = Abs((tmpFlag And 2) = 2)
chkFlag(2).Value = Abs((tmpFlag And 4) = 4)
' ...
chkFlag(29).Value = Abs((tmpFlag And 2^29) = 2^29)
Else
chkFlag(0).Value = 0 ' or any default value
chkFlag(1).Value = 0
' ...
chkFlag(29).Value = 0
End If
To Save your data:
Dim tmpFlag As Long
tmpFlag = 0
tmpFlag = tmpFlag + (1 * chkFlag(0).Value)
tmpFlag = tmpFlag + (2 * chkFlag(1).Value)
tmpFlag = tmpFlag + (4 * chkFlag(2).Value)
' ...
tmpFlag = tmpFlag + (2^29 * chkFlag(29).Value)
rs!FlagA = tmpFlag ' update field
Do the same with other 20 checkboxes and your rs!FlagB field
Drawback:
It will be problem to create your SQL statements, to select doctors that can do certain procedure, e.g:
Select * from Doctors where ((FlagA And 4) = 4) ...
won't work. :(
Hope you don't need this. If you do, just forget this tip.
Are the procedures basically consistent, or will there always be new procedures to add or subtract?
:) dapperry
:) dapperry
ASKER
Yes I would like a sample .MDB and yes I need ability to add new doctors and their profile information and add and remove procedures. So when I query the data I will need to pullin the reflected changes.
As for mirkwoods answer I relize it was technically right however I feel based on the points I was offering and the potential to aquire more by me posting an Excellent rating I feel that he/she could have elaborated a bit more.
Dedushka the more infomration the better. Thanks
Ameba I also considered the check box theory however 50+ check boxes is not the route I want to go and these check boxes would have to be dynamically created based on the doctors and the procedures involved. I think it would be too messy. Thanks for the infpr though.
As for mirkwoods answer I relize it was technically right however I feel based on the points I was offering and the potential to aquire more by me posting an Excellent rating I feel that he/she could have elaborated a bit more.
Dedushka the more infomration the better. Thanks
Ameba I also considered the check box theory however 50+ check boxes is not the route I want to go and these check boxes would have to be dynamically created based on the doctors and the procedures involved. I think it would be too messy. Thanks for the infpr though.
OK, mcss, I'll prepare for you a sample DB and then we'll consider here all questions. When a sample will be ready (later in 3-4 hours), I'll post a comment.
Your sample mcss1.mdb (135Kb) is ready.
If you need it, let me know by email: khamed@istnet.ru
If you need it, let me know by email: khamed@istnet.ru
Sorry to contredict about size limitations but the Jet engine 3.5 (for Access 97) can hold up to 1.2 gigabytes of data.
See Database Engine Questions topic of Microsoft technet that contains also differents comparizons about Fox, SQL, Access, etc. and gives advice about what is the best choice in differents business situations.
I pasted this note into Q.10142036. It'll cost 2 points.
DS
See Database Engine Questions topic of Microsoft technet that contains also differents comparizons about Fox, SQL, Access, etc. and gives advice about what is the best choice in differents business situations.
I pasted this note into Q.10142036. It'll cost 2 points.
DS
ASKER
Dedushka,
That was dead on. Thank you very much for your time and effort. After looking at it it looks so easy, yet I have never had to use this kind of relationship so I had no clue where to start. So go ahead and give me an answer an I will assign you the points.
That was dead on. Thank you very much for your time and effort. After looking at it it looks so easy, yet I have never had to use this kind of relationship so I had no clue where to start. So go ahead and give me an answer an I will assign you the points.
ASKER
Dedushka,
That was dead on. Thank you very much for your time and effort. After looking at it it looks so easy, yet I have never had to use this kind of relationship so I had no clue where to start. So go ahead and give me an answer an I will assign you the points.
That was dead on. Thank you very much for your time and effort. After looking at it it looks so easy, yet I have never had to use this kind of relationship so I had no clue where to start. So go ahead and give me an answer an I will assign you the points.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
mcss,
if you have a questions, post its here in E-E or mail me, any way you welcome.
Best regards,
Dedushka
if you have a questions, post its here in E-E or mail me, any way you welcome.
Best regards,
Dedushka
A suggestion for the GUI.
Have a form for adding doctors and another form for adding procedures. You can use one form with a tab control if you like.
On the third form, or tab, have a combo box for your doctors, and two list boxes for your procedures: lstAvailProcs, lstProcsForDoc
On an new doctor, that you just entered and saved in the doctor dialog, populate the lstAvailProcs with all procedures. Use an "Add" button and a "Remove" button. Select the one or multiple "Available Procedures" and then click you "Add" button to move these items from the lstAvailProcs to the lstProcsForDoc.
Then you will need a "Save" button, and you can loop through the lstProcsForDoc and add them to the tblDoc_Proc.
Note: By having the listbox for your "Available Procedures" and "Procs for Doc", your proc selection will be dynamic and not static as in having a form with 50 check boxes. I will be available until 5pm MST Friday, to return on Monday.
Good Luck,
Preece
Have a form for adding doctors and another form for adding procedures. You can use one form with a tab control if you like.
On the third form, or tab, have a combo box for your doctors, and two list boxes for your procedures: lstAvailProcs, lstProcsForDoc
On an new doctor, that you just entered and saved in the doctor dialog, populate the lstAvailProcs with all procedures. Use an "Add" button and a "Remove" button. Select the one or multiple "Available Procedures" and then click you "Add" button to move these items from the lstAvailProcs to the lstProcsForDoc.
Then you will need a "Save" button, and you can loop through the lstProcsForDoc and add them to the tblDoc_Proc.
Note: By having the listbox for your "Available Procedures" and "Procs for Doc", your proc selection will be dynamic and not static as in having a form with 50 check boxes. I will be available until 5pm MST Friday, to return on Monday.
Good Luck,
Preece
I used checkboxes because it was easy to explain the idea.
My idea was to present how to reduce db space.
Perhaps this is not needed. OK.
Thank you for pointing out how messy and static is what I suggested.
My idea was to present how to reduce db space.
Perhaps this is not needed. OK.
Thank you for pointing out how messy and static is what I suggested.
ASKER
I want to thank EVERYONE for their suggestions, I appreciate them all. They were ALL great ideas and they will help me in completing this project.
Dedushka, I might take you up on the help. I will need to work on getting these tables created and set up and if I run into any trouble I'll give you a ring(Thank you very much).
Tim
Dedushka, I might take you up on the help. I will need to work on getting these tables created and set up and if I run into any trouble I'll give you a ring(Thank you very much).
Tim
ASKER
I want to thank EVERYONE for their suggestions, I appreciate them all. They were ALL great ideas and they will help me in completing this project.
Dedushka, I might take you up on the help. I will need to work on getting these tables created and set up and if I run into any trouble I'll give you a ring(Thank you very much).
Tim
Dedushka, I might take you up on the help. I will need to work on getting these tables created and set up and if I run into any trouble I'll give you a ring(Thank you very much).
Tim
Procedure table: Procedure ID, Procedure Name
DoctorCanDoProcedure table: Doctor ID, Procedure ID