Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Many to Many issue

Posted on 1999-07-05
24
Medium Priority
?
222 Views
Last Modified: 2006-11-17
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)?
0
Comment
Question by:mcss
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 6
  • 2
  • +8
24 Comments
 
LVL 13

Expert Comment

by:Mirkwood
ID: 1998859
Doctor table: doctor ID, Doctor Name
Procedure table: Procedure ID, Procedure Name
DoctorCanDoProcedure table: Doctor ID, Procedure ID

0
 
LVL 7

Expert Comment

by:Dedushka
ID: 1998860
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?

0
 
LVL 7

Expert Comment

by:Dedushka
ID: 1998861
Sorry, Mirkwood, don't see your answer...
0
Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

 
LVL 7

Expert Comment

by:Dedushka
ID: 1998862
mcss,
Mirkwood was faster than I and win points.
By the way, your query should be like this:

SELECT tblProcedures.ProcDescription
FROM tblProcedures INNER JOIN (tblDoctor INNER JOIN tblDoc_Proc ON tblDoctor.DoctorID = tblDoc_Proc.DoctorID) ON tblProcedures.ProcID = tblDoc_Proc.ProcID
WHERE (((tblDoctor.FirstName)="John") AND ((tblDoctor.LastName)="Smith"));

Regards,
Dedushka
0
 
LVL 1

Expert Comment

by:boodabelly
ID: 1998863
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.
0
 
LVL 12

Expert Comment

by:rwilson032697
ID: 1998864
boodabelly: Can you elaborate a bit more on this size limit issue?

Cheers,

Raymond.
0
 

Expert Comment

by:mirrorinternetzahavnet
ID: 1998865
2Mb in nothing for Access database. I would say 2Gb. Once I worked with 40Mb MDB file and it was OK.
0
 

Author Comment

by:mcss
ID: 1998866
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.
0
 
LVL 7

Expert Comment

by:Dedushka
ID: 1998867
Hi mcss.
Are you ready to go step by step or need a sample .mdb file?
0
 
LVL 4

Expert Comment

by:vindevogel
ID: 1998868
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.
0
 
LVL 15

Expert Comment

by:ameba
ID: 1998869
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.
0
 
LVL 3

Expert Comment

by:dapperry
ID: 1998870
Are the procedures basically consistent, or will there always be new procedures to add or subtract?

:) dapperry
0
 

Author Comment

by:mcss
ID: 1998871
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.


0
 
LVL 7

Expert Comment

by:Dedushka
ID: 1998872
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.
0
 
LVL 7

Expert Comment

by:Dedushka
ID: 1998873
Your sample mcss1.mdb (135Kb) is ready.
If you need it, let me know by email: khamed@istnet.ru

0
 
LVL 3

Expert Comment

by:dsegard
ID: 1998874
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
0
 

Author Comment

by:mcss
ID: 1998875
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.

0
 

Author Comment

by:mcss
ID: 1998876
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.

0
 
LVL 7

Accepted Solution

by:
Dedushka earned 880 total points
ID: 1998877
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?


0
 
LVL 7

Expert Comment

by:Dedushka
ID: 1998878
mcss,
if you have a questions, post its here in E-E or mail me, any way you welcome.
Best regards,
Dedushka
0
 
LVL 12

Expert Comment

by:Preece
ID: 1998879
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
0
 
LVL 15

Expert Comment

by:ameba
ID: 1998880
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.
0
 

Author Comment

by:mcss
ID: 1998881
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

0
 

Author Comment

by:mcss
ID: 1998882
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

0

Featured Post

New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question