Solved

Setting up a Many to Many relationship

Posted on 2015-01-30
81
105 Views
Last Modified: 2015-03-24
In a previous question, one of the suggestions was perhaps having a Many to Many relationship set up.  For example: a Patient can have many MDs and an MD can have many Patients.  Would I put the (PK) of each table into the other table as an (FK)?
0
Comment
Question by:Angelia1
  • 37
  • 28
  • 9
  • +2
81 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 40580253
i will use a third table, tblPatientsMD
tblPatientsMD
PMID - AutoNumber (PK)
MDID (FK)
PID  (FK)
0
 

Author Comment

by:Angelia1
ID: 40580257
like a junction box?
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 40580272
0
 
LVL 57
ID: 40580281
<<like a junction box? >>

 yes and it's often referred to as a "linking table".

You have a one to many  2 x so end up with a M to M:

  O ----> M M <----- O

Jim.
0
 
LVL 57
ID: 40580291
Note that when you set this up with forms, you'll need two Main/Subform combinations.

For the first, the main form would be doctors, the subform is based on the linking table, and in the subform, a combo would allow you to select a patient (setting the FK for patient in the linking table).

On the second one, the main form would be patients, the subform is still based on the linking table, but now the combo would allow you to select a doctor, thus setting the FK for the doctor in the linking table).

HTH,
Jim.
0
 

Author Comment

by:Angelia1
ID: 40580293
So I can take all of my (PK) and put them in one table (the junction box) and create the many to many relationships and that will allow me to enter info into a qry or form even if all the fields in the other tables aren't occupied?
0
 

Author Comment

by:Angelia1
ID: 40580308
I'm going to work on that table and I will be back.

Angie
0
 

Author Comment

by:Angelia1
ID: 40580509
Does this look right?  I've called the table Junction Box
Angie-s-Relationships-2013-B.docx
0
 
LVL 57
ID: 40580544
Looks pretty good!

I'm not sure I'd call it "Junction box" though; looks to me like it's recording a visit?

Other thing that looks  a bit off is that you have PatientID in tblOutsideFacilities.

 and last, save yourself some headaches; no embedded spaces in table or field names.

Jim.
0
 

Author Comment

by:Angelia1
ID: 40580559
So all the table's (PK) will run thru that linking table as (FK) just like PatientID does?
0
 

Author Comment

by:Angelia1
ID: 40580561
... and do I maintain the relationships between the other tables or just use the linking table.  I guess so or it would just be back to a one to many relationship right?
0
 
LVL 57
ID: 40580624
Not sure I understand your question; it's a matter of what you modeling.

Take insurance for example...a patient may have more than one health insurance, but one health insurance policy is not going to have more than one patient.

So that's a one to many from patient to insurance.

What else do you feel is a many to many?

Also something that may not be clear is that each M to M relationship needs it's own linking table.

Jim.
0
 

Author Comment

by:Angelia1
ID: 40580642
Yes one policy per pt but a Company will have many patients.

In the past (Im redesigning a whole new db in 2013 from an older one in 2003) I just used ins co in a row source for Insurance which made it tedious to add new or delete old companies in the list.  So this time I thought I should have a table for ins.

Maybe the specific ins id number and such needs to go into the Patient Demographic tbl.
0
 
LVL 57
ID: 40580675
Well you might have:

tblPatients    O ----> M   tblInsurancePolicies   M <-----  tblCompanies
PatientID                         InsurancePolicyID                       CompanyID
LName                             PatientID                                        CompanyName
FName                             CompanyID
                                          PolicyDate

 Some might design the tblInsurancePolicies table without InsurancePolicyID as PatientID and CompanyID will most likely be a unique combination.

But if your going to point to that policy instance from another table (such as a visit, surgery, etc) then having InsurancePolicyID  is a good idea for performance.

Jim.
0
 

Author Comment

by:Angelia1
ID: 40580687
In the old db I had main table that was pretty big.  

There were several other tables that were smaller like Physicians or Outside Facilities, City State and Zip Code,  Clinical Data, and Surg/Path table.  

Out of this I created one huge qry then forms w tabs w data fields on them for each area like Demographics tab or Physicians Tab.  

I want to do that again.  There was some code written for me by Microsoft to make things auto fill and such (event code) which I don't currently know how to do but that's another days concern.
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 40580800
So all the table's (PK) will run thru that linking table as (FK) just like PatientID does?
The junction table is not populated automatically if that is what you are asking.  Your user identifies the relationship and then goes to the form/subform to record it.  The junction table may or may not contain a row for every patient.  It may or may not contain a row for every doctor.  So your queries have to use left joins when joining to the junction table unless you really don't want information on the missing data.  For example, if you wanted to produce a list of doctors with a count of their patients, you would join tblDoctors to the tblPatientMD junction table.  If you use an inner join, you will only have doctors with patients in your list.  If you want ALL doctors plus the count even if 0, then you need to use a left join.
0
 

Expert Comment

by:Ange1ia
ID: 40582044
I'm doing something fundamentally wrong and I'm getting frustrated w myself.  I need help to figure out what I'm doing wrong.  I can post this as a new question if necessary.

I made the linking table.  Including an attachment.

The relationships looked right didn't they in the doc I attached?  But when I go to run a qry it doesn't pull everything together or its not updateable.  

Does it look like the structure is sound?  I've changed everything and am running it thru the Linking tbl.
0
 

Expert Comment

by:Ange1ia
ID: 40582059
0
 
LVL 57
ID: 40582291
OK, where your getting confused is what's a many to many vs a one to many.

Also how a linking table is used.  A linking table does not form the joins between all the tables, it forms a M to M relationship between two tables.

Look at tblApppointments.  Right now, your saying that a single appointment can have many surgeries, doctors, patients, facilities, etc associated with each appointment.

 Can a single appointment be at more than one facility?  No.  Be fore more than one patient?  No.

 A patient though can have more than one doctor and a doctor can have many patients.

 I think what your struggling  with and don't have is a "visit" or "event" (encounter?) table and are using the junction table both for that and representing a M to M.

 So get rid of the junction table for the moment.

 Now, what's missing?   What are the questions that you  can't answer with your model?

 When your designing a database, you start with the questions (what answers you need to get out).  Then what you have to put in (in terms of data) to get those answers,

Jim.
0
 

Expert Comment

by:Ange1ia
ID: 40582435
Ok right now I have:

tbl Patient Demographics and tbl Physicians in a qry

it has a 1 to  many relationship w a join property of 2
I can add records in this qry and it updates each table

going to try and add another table to the qry and see if it will also let me update it
0
 

Expert Comment

by:Ange1ia
ID: 40582470
So the questions I need to ask are for example,  This is a lot...
Can a patient have more than one physician or surgeon?  We know that's a yes.

Can a patient have films or path at more than one facility? Yes.  one to many
Can a patient have multiple clinical items? Yes  one to many
More than one Insurance? Yes  one to many
Can a pt have more than on surgery? Yes  one to many

All of these connected to the tblPatientDemographics w the PtID being in each table as the FK?

More than one appointment? Yes but only one appt at a time.  So this would need a linking table?
Would look like this?
Pt/Appt ID (PK)
pt demographics Id (FK)
Appts Id(FK)
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 40582583
Ange1ia,
I can't view your attachment so I'm going to guess at the problem.  From what you are saying, it sounds like you are trying to make one query do everything but that isn't how it works.  Each form/subform, report/subreport needs separate queries that include only the tables relevant to what is being shown on the form/report.

When you create a query, you can only include tables that are in the same hierarchy or are lookup tables.    Just because patients and doctors have a relationship doesn't mean that insurance and doctors have a relationship so you can't include both doctors and insurance in the same query.  That creates a Cartesian product and renders the query not updateable.

I'll use a silly example of a Cartesian product using students, subjects, and pets.  Joining those three tables in a single query results in:

Tommy, Algebra, BoBo
Tommy, English, Bobo
Tommy, Algebra, Spot
Tommy, English, Spot

Although BoBo and Spot are Tommy's pets and they have a relationship, and Tommy studies English and Algebra so he has a relationship with the subjects, BoBo and Spot don't have a relationship with subjects so including them in the same table renders the query not updateable and makes it appear to duplicate data.  A Cartesian product is produced by joining every row in tblA with every row in tblB.  That causes the query engine to loose the ability to identify individual rows - Algebra appears twice and BoBo appears twice and once the query engine can't identify individual rows, it can't update the recordset.
0
 

Expert Comment

by:Ange1ia
ID: 40582781
But can all those separate items go into one data entry form?  

That's what we have now.  I have a form w multiple tabs on it and we go from tab  to tab to enter all the data we need. Like Demographics tab, Physicians tab (shows referring MD and their address and phones), Films tab (shows what Outside Facility films came from.) and so on.
0
 

Expert Comment

by:Ange1ia
ID: 40582805
But your right that's exactly what Im trying to do cause that's how it was before
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 40582822
Each tab should have a separate subform with based on a query of the subject matter table.  So each entity that has a relationship with the patient ends up in a separate subform.  When the relationships go deeper than two levels, you might want to break them up and use a separate form or a popup form to show the final list.  For example, a client has a relationship with a number of doctors but beyond that we have visit detail.  So you need to think about how it makes sense to display the data.  Do you want just a subform with a simple list of doctors where if you double click on a specific doctor, you open a popup form that lists the visits?  Or do you want your subform to show visits with the doctor simply being an attribute of the visit?  Frequently you want both types of display but you can't do it cleanly with a single subform, you would need to use two subforms.

Don't try to overload the forms with too much information.  Try to organize it into the way different users will need to interact with it.  You might end up with several forms/subforms each designed to work best for a different person.
0
 
LVL 57
ID: 40583611
Angelia,

  Post a blank DB with just the tables and I'll adjust it.

 We can discuss from there.

Jim.
0
 

Expert Comment

by:Ange1ia
ID: 40584084
Jim,

I'm attaching a copy of my blank data entry form in Word so you can see the pages as I have it set up now.  Each pg represents a tab in the form.  This is what I'm trying to get back to for this current 2003 db... I can.

I will also post a copy of the new db in 2013 that I've started working on.  We don't have any sub forms or anything like that in our working 2003 db.  It's all data entry fields on our form that feeds into the tbs land qrys.  Let me know if you can't open these or if this isn't what you need.
Blank-2003-Data-Entry-Pages.doc
0
 

Expert Comment

by:Ange1ia
ID: 40584132
Here's the backend will just the tables
Angie-s-2013-Backend-DB.accdb
0
 

Expert Comment

by:Ange1ia
ID: 40584139
0
 
LVL 57
ID: 40584544
OK so let me ask a couple of things now, because I don't understand quite what your working with.   I'm thinking in terms of a general medical situation, but it looks like it may be more specific (like a oncology department).

 So first thing that would be helpful is a basic description of what's going on "Patient is referred to us,  has a primary doctor assigned, who establishes a course of treatment, which may include surgeries and radiation treatments.  The course of treatment as a whole is authorized by the patients insurance carriers.   The patient will have a primary insurance carrier and may have secondary insurances."

 You get the idea.

 The reason I need to know that, is in taking a look at tblPatientDemographics, you have:

OutsideFacilityID
PhycianID
InsuranceID
AppointmentsID
ClinicalID

 and I need to know what those may represent.   For example, is the outside facility assigned to them and they will never go to another?   or is this the "default" facility for them, but they may be treated at another?   or is it just a matter of the patient may go to any outside facility at some point?   If that's true, then this foreign key does not belong here.

 PhyscianID,  InsuranceID and AppointmentsID don't belong here.   While they can belong to a patient, they don't us something about the "patient"; it's not an attribute like last name, first name, ss#, height, eye color, etc.

 When you design a table, *everything* in that table should tell use something about what "thing" that the table represents, which in this case is "a patient".   Hope that makes sense<g>

 So a little more detail please and we'll start getting this done.

Thanks,
Jim.
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 40584897
As Jim said, some of these are not attributes of a client and so don't belong in the client table.
OutsideFacilityID
 PhycianID
 InsuranceID
 AppointmentsID
 ClinicalID
The patient has a 1-m or m-m relationship with the listed entities.  For example Facilities, Physicians, and Clinics all serve multiple patients so the relationship is m-m.  However, these entities are probably all connected to an Appointment rather than directly to the patient. So the Appointment table serves as the junction table to connect a patient to one or more of the other entities.  A patient has an appointment with a physician and that appointment may be at a Clinic or Outside Facility.  We don't really know what the relationships are.  Remember we are implementing real world relationships.  They are not arbitrary.

It is always difficult to get your head around how the foreign keys work.  Think of a 1-m relationship as that of a parent to a child.  A parent can have many children so you wouldn't put the names of each child in the parent record.  How many columns would you have to add?  Think about the confusion of trying to do anything with them.  The "child"  record stores the pointer to the parent.  Think of it as a surname.  A patient can have many appointments so the patient is the parent in this relationship.  The appointment has only one patient (except for perhaps a psychologist which would be modeled as a m-m) and so we store the PatientID in the Appointment record.
0
 

Author Comment

by:Angelia1
ID: 40586098
Jim,
Was my explanation ok
Angie
0
 

Author Comment

by:Angelia1
ID: 40586710
Does this look ok?

tblPatientDemographics  -  PatientID  -  tblPhysicians
                                                 PhyciansID

This gives me a many to many relationship w the tblpatientdemographics and tblphysicians, yes?
There will be a physician referring a patient.  A patient will also have appointments w a physician sometimes more than one physician  

tblPatientsDemographics  -  PatientsID  -  tblFacilities
                                                  FacilitiesID

Many to Many?  Here we will request films and records from different facilities.  Records being CT/PET scans Xrays, Pathology...  A patient can have more than on facility (Maybe this is a one to many one patient can have many facilities making that  tbl patientdemographics -  tblfacilities  )
                                                                                                            PatientID

tblPatientDemographics  -  PatientID  -  tblClinical
                                                 ClinicalID

And here we will list all of the patients clinical attributes, referrals to the Social Worker, Dietician and so on and also RN Notes, pts meds... This too will be a many to many?
Many to Many?
...after some consideration I think Facilities and Clinical are one to many.  What do you think?

Angie
0
 
LVL 57
ID: 40586899
Take a look at the following and see if it makes sense.  Then we'll work on this next:

<<This gives me a many to many relationship w the tblpatientdemographics and tblphysicians, yes?
There will be a physician referring a patient.  A patient will also have appointments w a physician sometimes more than one physician  >>

Basic relationships
Jim.
0
 
LVL 57
ID: 40586911
and just to give you a little more to go on:

tblAppointmentTypes:
Surgery
Office Visit
MRI
X-RAY
CT SCAN

tblRelationships
Wife
Husband
Son
Daughter
Priest
Rabbi
0
 
LVL 57
ID: 40586918
and on this:

<<This gives me a many to many relationship w the tblpatientdemographics and tblphysicians, yes?
There will be a physician referring a patient.  A patient will also have appointments w a physician sometimes more than one physician  >>

Those are two separate things; physician that referred the patient, and physician(s) associated with an appointment; so where do you think the first should go?   and the second?

Jim.
0
 
LVL 57
ID: 40586951
So check this out now:

Adding physician's for referral and appointments
Jim.
0
 
LVL 57
ID: 40588549
If your good with that, I'll add the rest in and upload a DB, unless you want to work on it yourself.

Jim.
0
 

Author Comment

by:Angelia1
ID: 40588866
Can I show you the db I work in everyday that I'm trying to model?
0
 

Author Comment

by:Angelia1
ID: 40588871
That may help explain my confusion.
0
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 

Author Comment

by:Angelia1
ID: 40589011
Jim,

For tblFacilities, this is where I will request records from not a facility that someone will be going to.  It's where they've already been and had some kind of test or treatment and I need the records.  It's not appointment related.  So I should take that out?
0
 

Author Comment

by:Angelia1
ID: 40589076
It won't allow me to set up referential integrity between tblpatientdemographics and tblappointments because i have patients entered into pt demographics.  Just delete them?
0
 

Author Comment

by:Angelia1
ID: 40589137
I've been able to make all of the relationships connect and enforce referential integrity :)
0
 

Author Comment

by:Angelia1
ID: 40589171
maybe u better send your db this one isn't wking
0
 
LVL 57
ID: 40589262
<<For tblFacilities, this is where I will request records from not a facility that someone will be going to.  >>

 OK.  And sorry for the lag in responding, it's been busy in the office past couple of days.

 let me work up the rest and then we can go through it.

Jim.
0
 

Author Comment

by:Angelia1
ID: 40590225
ok
0
 

Author Comment

by:Angelia1
ID: 40593718
Jim,
0
 
LVL 57
ID: 40593726
Sorry, I know this has dragged out...I'm actually sitting here working on this now.   Give me a half hour or so and I'll get it done.

Jim.
0
 

Author Comment

by:Angelia1
ID: 40593789
Thank you I meant to say that I wasn't able to post the old db at the moment but I was going to work on it and try later so you could see what I'm working w now.  Its in 2000 or 2003.

Angie
0
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 total points
ID: 40594334
OK, that was a LONG half hour, but here's what I came up with.

Now I'm sure their will be tweaks that are needed, but there is enough here to show you how you might set something up.

Specifically, look at tblContacts.    It's a general table for a "contact", but that contact can be linked to an insurance company or a facility (through linking tables).  Also look at tblPhysicians; number of ways it can be referred to.

One place I was not sure of how to handle was insurance.  What I have here now is that a patient can have any number of insurances.  Reason for that is what if they changed carriers?   When looking back, you might want to know that a given procedure was tied to an old carrier.

Also based on what you've  said and what I see, I added tblTreatmentCourses.    What it does is group one or more different types of treatments (surgery, chemo, or radiation) as part of a course of treatment.  That may be past or future.  That may or may not be correct.

The other thing you will notice that I broke up chemo and radiation into two separate tables.

The reason for that is when your looking at a table, all the fields should apply for every record.   When you had that as a single table, you could tell that you were talking about two different things in the same table because you prefixed all the fields:

Chemo...
Chemo...
Chemo...
XRT....
XRT....
XRT....

 and had two separate sets of fields.   Look this over, walk through it, then ask questions on anything you don't understand and I'll explain why I did what I did.

 There actually is a lot here...medical situations are one of the most complex to deal with.

Jim.

screen shotAngie-s-2013-Backend-DB.accdb
0
 
LVL 57
ID: 40594344
ugh and I keep thinking of a million things to say.

In regards to "facilities", I don't think you should make a distinction between yours and outside ones; just treat them all the same.

You also might want to add a "tblInfoRequests" table; "I requested MRI's from the following place on xxxx for patient zzzz.   I spoke to wwww and they will be sent via e-mail".    Then have fields for date request made/received, etc.

Jim.
0
 
LVL 57
ID: 40594348
and on the tblTreatmentCourses, a course of treatment may be just chem, XRT, or a surgery, or one or more of any and/or all three.  There's no limits there.

So if a patient was giving their history "I had a surgery on xxxx by ZZZ", then it would be a course of treatment with just a surgery.

Jim.
0
 

Author Comment

by:Angelia1
ID: 40594383
Thank you for all your effort.  Give me a chance to look over every thing (I'll start now) and I will comment back.  Will you be checking the site later today and over the weekend?  I'm certain there will be questions.

Angie
0
 
LVL 57
ID: 40594479
Yes, I will stay on top of this for you so we can get it done.

Jim.
0
 

Author Comment

by:Angelia1
ID: 40594543
how do you print the relationships screen?  It seems like there was a way but I can't remember how.
0
 
LVL 57
ID: 40594558
I have a screen capture utility.  

 But you can do a relationships report.  It's under Database Tools, click on relationships, then run the relationships report.

Jim.
0
 

Author Comment

by:Angelia1
ID: 40594626
I'll take this home and will work on it this weekend too.
0
 
LVL 57
ID: 40594673
I'll be in working at least part of the weekend and will keep an eye on my phone so we can get this wrapped up for you.

Jim.
0
 

Author Comment

by:Angelia1
ID: 40598190
Good Morning, Sorry I didn't get back to this this weekend.  I work a 2nd job and got tied up there.  Best laid plans... but back in the office today.

Angie
0
 
LVL 57
ID: 40598272
Morning,

 No worries, but I did wonder what happened.  Kept expecting an e-mail over the weekend, but did see any.

  As it turns out, I will be out a good chunk of today, but I will try and stay on top of the e-mails (having a CAT scan done today).

 I think with what I've done you'll have a good start.  As I said, may not be 100% on because I still don't have a clear picture of what business processes you have, but it should make clearer what you need to do, especially in regards to the M to M.

  What you want to do is:

1. Come up with a table for each type of "thing"; patient, doctor, appointment, contact, etc.
2. The relationships between the things, which will be:

a. One to One
b. One to Many
c. Many to Many

  One to One you won't see very often.  One to Many (Parent/Child relationship) is quite common.   Many to Many not as common, but I think you'll see clearly now that you need a separate linking table for each of those relationships.

  and by the way, these relationships are not what a relational database is all about; it's about the tables themselves, which the real term for is a relation.    A relation is a set of records, all with the same attributes, each which is uniquely identifiable based on those attributes.

 So work through it, see how I've set things up, and ask any questions.

Talk to you later,
Jim.
0
 

Author Comment

by:Angelia1
ID: 40598952
I am looking over this but I'm confused and it's so much different than what I'm used to working with.  I don't understand why I can update in my database I work w daily and not in this new one.
0
 

Author Comment

by:Angelia1
ID: 40603880
OK, I figured out the column thing.  I'm not using auto form.

So Im trying to create a form from scratch.  I have the main form which contains the patient demographics and I'm going to try to put the other items like Clinical, Physicians, Insurance, Treatments and so on on tabs.
0
 

Author Comment

by:Angelia1
ID: 40603893
Except when I go back to Form View it gives me a table view not a form like view.  Is that because I've created the sub form off of a table?
0
 

Author Comment

by:Angelia1
ID: 40603899
Sorry it gives me a tableivew on the sub form instead of a form view.  In Design view I see what looks like a form on the tab but it's different on the Form view side.
0
 

Author Comment

by:Angelia1
ID: 40606309
Jim,
I figured out the form view for the sub form.  I had to change the default view.
0
 
LVL 57
ID: 40606609
OK.

 Keep in mind that when developing the forms, you will have a main/subform for each of the one to many relationships.

 For the Many to many, you'll still have a main/subform, but the subform will have a combo that allows you to select a record from the other side of the relationship.

 For example, your main form might be appointments (tblAppointments),and  the subform a list of the doctors for that appointment (tblAppointmentPhysicians).  

 On the subform, when entering another physician for the appointment, you would have a combo control that would display the physicians from tblPhysicians, and when you selected one, it would save the PhysicianID in tblAppointmentPhysicians.PhysicianID, thus giving you the link.

Jim.
0
 

Author Comment

by:Angelia1
ID: 40616708
Hi! Back at work today, been iced/snowe in.

I've been working on the form and what I've done so far:

Created a tab for the Clinical w a sub form on a tab using tblClinical.  There are drop downs on that tab from the table and they seem to work.

Created a tab for Insurance from a qry using tblInsurance and tblInsuranceCompanies using a Join Type 1.  I'm not sure it that's the best Join Type or not.  I want to include the tblContact but it doesn't seem to want to let me.

Working on rearranging the fields on the tabs at the moment.
0
 

Author Comment

by:Angelia1
ID: 40616725
That does not appear to be the best join.  It's not allowing me to update in the form.
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 40616785
Angelia,
Join type doesn't determine whether or not a query is updateable.  If the join is not updateable, it is most likely due to one of the 3 following reasons - although there are others.

1. Join is not PK to FK.  Although it isn't necessary to create formal relationships, it is best practice because it allows you to enforce RI.  Never the less, each table should have a primary key and when you want to join one table to another, the PK of the "parent" table is placed in the "child" table where we refer to it as the FK.  So, In tblOrder, we place ClientID so we can identify an order as belonging to a specific client.  You would join tblOrder.ClientID to tblClient.ClientID.  The names don't have to match but you'll save yourself some brain cells if you use good naming techniques.
2. The query contains aggregation.  You can tell this because it contains a Group By or a Distinct predicate.
3. The query includes tables not related to each other.  In this case, you probably have two tables that are related to a "parent" but not to each other.  A student has classes and he has pets.  Although both classes and pets include StudentID, including them in the same query isn't logical because classes and pets have nothing to do with each other.  You'd end up with:
Joe, Algebra, Tweety
Joe, Algebra, Spot
Joe, Algebra, Smokey
Joe, English, Tweety
Joe, English, Spot
Joe, English, Smokey
0
 
LVL 57
ID: 40616920
<<Created a tab for Insurance from a qry using tblInsurance and tblInsuranceCompanies using a Join Type 1.  I'm not sure it that's the best Join Type or not.  I want to include the tblContact but it doesn't seem to want to let me.>>

  Your trying to do too much in one spot.

  Access with a Main/Subform pair will only let you handle a One to Many relationship.

 You can't in a single subform handle both the insurances for a patient and the contacts that belong to an insurance company.

 Right now, your main form is the patient.   tblInsurance should be a subform, and the field InsuranceCompanyID would be set with a combo control.  The combo would display in it's dropdown the list of companies from tblInsuranceCompanies

Jim.
0
 

Author Comment

by:Angelia1
ID: 40617021
Can I put more than one sub form on a tab?
0
 
LVL 57
ID: 40617055
<<Can I put more than one sub form on a tab? >>

 Sure can or as many tabs as you want, with one sub-form on each.

Jim.
0
 

Author Comment

by:Angelia1
ID: 40617062
I want to be able to see the patients insurance information and the insurance companies information at the same.
0
 
LVL 57
ID: 40617114
There are a number of ways to handle that:

1.  Include the other tables information in the forms underlying query - Typically, I don't like doing this and would rather keep the forms recordsource simple.

2. Have a series of unbound text boxes on the form, and in afterupdate event of a control, populate those with information (multiple ways of doing that).

3. Have a button that pops up a form where they can see the complete details.  Click "OK" and their back to what they were working on.  This is pretty easy to do and works the best when you want them to see more than just summary information.

  For example, you might event pop up a main/subform, where the main form displays the company data and the subform displays all the contacts at that company (say they want to look up a phone number for the approval person).

Jim.
0
 

Author Comment

by:Angelia1
ID: 40617336
Sound of heavy sighing,
Ok, what if I put a combo called InsCoName below the Insurance sub form w a drop down of all the ins co's info (address, phone, fax, email and so on)?  How do I get the drop down to show all that info and not just the ins co name?
0
 

Author Comment

by:Angelia1
ID: 40617500
" Right now, your main form is the patient.   tblInsurance should be a subform, and the field InsuranceCompanyID would be set with a combo control.  The combo would display in it's dropdown the list of companies from tblInsuranceCompanies "

I think this last question is what you suggested earlier?  I just need the name, address and phone numbers to be visible when you choose the company I want to be able to see all that info.
0
 
LVL 57
ID: 40618911
Were getting off the database design here and into other areas.

I'll answer the combo question, but it would be best for future readers of the thread if you starting asking additional questions rather than continually tacking onto this question.

When you use a combo or list control, it can display related data from a table or tables, but stores the value of one field in the current record.

The bound field is what determines which field, and the Rowsource property determines the data.

So:
1. create a query that has the tblInsuranceCompanies in it.
2. Pull down into the grid:

InsuranceCompanyID
CompanyName

3. Define a column defined as:

Location:[City] & ", " & [State] & "  " &  [Zip]

4. and then pull down Phone into the grid.

  You now have four fields total in the query.

5. Add a sort on CompanyName.

6. Save the query and name it something meaningful.   I would use 'qry', followed by the name of the form your using it on, followed by the name of the combo control.

7. In the combo control, set:

Columns to 4
BoundColumn to 1
Column Widths to:  0; 1.5";1.5", .65"

This hides the first column in the drop down, which is the ID.  When the user hits the drop down then, they will only see company name, location, and phone number.

The combo's control source would be set to InsuranceCompanyID.  

Also set:

autoincrement to Yes
limit to list as yes.

So now when they hit the combo and drop it down, they will be able to select an entry as they type, have to pick one of the entries, and when they pick a entry, the value for the bound column (which is InsuranceCompanyID from tblInsuranceCompanies) will be stored in the InsuranceCompanyID field in tblInsurance, which is the table your sub form is based on.

Jim.
0
 

Author Comment

by:Angelia1
ID: 40625808
The question has not been abandoned.  Inclement weather has prohibited being at work.

Angie
0
 
LVL 57
ID: 40626490
Angie,

  Not a problem....sorry to hear you've been impacted by the weather.   Hopefully not too much of an ordeal<g>.

  Have a great day,

Jim.
0
 

Author Comment

by:Angelia1
ID: 40626502
LOL we are in Tennessee, if it flurries we are in a frenzy.
0
 

Author Closing Comment

by:Angelia1
ID: 40685015
Excellent advise and guidance.  Thank you for your time and patience.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

757 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now