Setting up a Many to Many relationship

i will use a third table, tblPatientsMD
tblPatientsMD
PMID - AutoNumber (PK)
MDID (FK)
PID  (FK)

like a junction box?

yes
relationship.jpg

<<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.

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.

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?

I'm going to work on that table and I will be back.

Angie

Does this look right?  I've called the table Junction Box
Angie-s-Relationships-2013-B.docx

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.

So all the table's (PK) will run thru that linking table as (FK) just like PatientID does?

... 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?

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.

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.

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.

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.

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.

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.

2013-Relationships-w-Linking-tbl.docx

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.

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

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)

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.

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.

But your right that's exactly what Im trying to do cause that's how it was before

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.

Angelia,

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

 We can discuss from there.

Jim.

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

Here's the backend will just the tables
Angie-s-2013-Backend-DB.accdb

Angie-s-2013-Backend-DB.zip

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.

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.

Jim,
Was my explanation ok
Angie

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

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  >>


Jim.

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

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.

So check this out now:


Jim.

If your good with that, I'll add the rest in and upload a DB, unless you want to work on it yourself.

Jim.

Can I show you the db I work in everyday that I'm trying to model?

That may help explain my confusion.

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?

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?

I've been able to make all of the relationships connect and enforce referential integrity :)

maybe u better send your db this one isn't wking

<<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.

ok

Jim,

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.

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

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.

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.

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

Yes, I will stay on top of this for you so we can get it done.

Jim.

how do you print the relationships screen?  It seems like there was a way but I can't remember how.

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.

I'll take this home and will work on it this weekend too.

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.

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

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.

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.

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.

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?

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.

Jim,
I figured out the form view for the sub form.  I had to change the default view.

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.

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.

That does not appear to be the best join.  It's not allowing me to update in the form.

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

<<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.

Can I put more than one sub form on a tab?

<<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.

I want to be able to see the patients insurance information and the insurance companies information at the same.

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.

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?

" 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.

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.

The question has not been abandoned.  Inclement weather has prohibited being at work.

Angie

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.

LOL we are in Tennessee, if it flurries we are in a frenzy.

Excellent advise and guidance.  Thank you for your time and patience.