Solved

I have patients that will be having mulitple surgeries... If I want to capture that data, won't I need a filed in a table for each surgery listed?

Posted on 2015-01-27
27
139 Views
Last Modified: 2015-01-30
I have a table called tbl surgery.  A patient can have several surgeries in their lifetime and we want to capture that data and possibly qry it in the future.  Won't each surgery require a field in a table (Surg 1, Surg 2 and so on)?
0
Comment
Question by:Angelia1
  • 9
  • 8
  • 4
  • +3
27 Comments
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 500 total points
ID: 40573455
Absolutely NOT!  :)

You should have one record per surgery performed, and each record should have a foreign key pointer back to your "patients" table.  For example:

tblPatients
--------------------------------
PatientID (PK)
PatientLName
PatientFNam
<other patient attributes>

tblSurgeryTypes
--------------------------------
SurgeryTypeID (PK)
SurgeryTypeName

tblSurgeries
--------------------------------
SurgeryID PK
PatientID (FK)
SurgeryTypeID (FK)
SurgeryDate

Putting each surgery in a separate column would be a first class ticket to data hell.
0
 

Author Comment

by:Angelia1
ID: 40573514
and use surgery types as a look up?
0
 

Expert Comment

by:Ernest Grogg
ID: 40573638
You can.  It depends on how you want to query your data.  If you want to search just the person, or if you want to search just a type of surgery.  

By separating the tables you can create queries based on the type of data your looking for.  

PK and FK will be related so the query then will pull the associated data along.
0
 

Author Comment

by:Angelia1
ID: 40573682
I will want to qry both.  For example, I will want to know how many Gall Bladder surgeries have been done and i will want to know who has had Gall Bladder Surgery.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40573691
<Absolutely no points wanted here as Patrick Matthews has already answered your question>

You can use this sample as a starting point to learn about main/subforms that are useful in these type of designs.
;-)

JeffCoachman
Database64.mdb
0
 

Author Comment

by:Angelia1
ID: 40573704
Thank you
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40573735
...and this for seeing surgeries and a list of Patients who had them...

Study this, and then you can post new questions about building the queries (and/or reports) you may need.
;-)
Database64.mdb
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 40573955
No points for me either but it's another absolutely not to :
and use surgery types as a look up?
NEVER, EVER use lookups at the table level.  These are a crutch and will cause nothing but trouble.  Use combos on a form to select from the list.
0
 

Author Comment

by:Angelia1
ID: 40575145
Actually that's what I meant. Create a Surgery Types table but create a field for surgery type in the Surgery table and use Surgery Types as a combo box in the form.  Does that sound correct?
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 40575210
Combo on the form is correct.  Combo on the table is bad juju.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40576280
...that is what my sample illustrates as well..
But remember,   I am not seeking any pints,... just having a bit of fun helping out...
;-)
0
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 40576697
You might need a many-to-many relationship between patients and surgeries.  See my Access Archon article and sample database:

http://www.helenfeddema.com/Files/accarch178.zip

and here is a screen shot of one of the forms:

Many-to-Many-Relationship-Form.jpg
0
 

Author Comment

by:Angelia1
ID: 40577327
I'm obviously doing something wrong w my relationships in the whole scheme of the db though I thought it looked correct (this is probably an entirely different question and I can post it as such and probably should) but here is a copy of my relationships.  Its telling me basically I can't put data in a field because another tables data requirement due the relationship hasn't been met.  So maybe I do need Many to Many relationships.  
What do you think?
2013-Relationships.docx
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40577398
There are all questions hat should have been dealt with *before* you started adding data.
AFAICT, a Many to many would be suitable,
Just because you get an error does not mean the design is wrong.

In a nutshell you cannot ad a "Child" record, until the "Parent" record is created.
You did not state what tables were involved with your error, but as an examle:
You cannot add a Surgery For "PatientID 67, ...until you first Create Patient67 in the Patients table.

JeffCoachman.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40577413
...or ...
You cannot add a Surgery for procedure that does not exist in the SurgicalProcedure table.
0
 

Author Comment

by:Angelia1
ID: 40577429
I had entered sample data into the Pt Demographics table.  The I created a qry for Pt Demographics and Physicians table.  I believe the error noted there not being a record for the Facilities table.

So does the layout look correct?

Ok reading "Understanding Table Relationships"  Many to Many from Access 2013 Bible
0
 

Author Comment

by:Angelia1
ID: 40577581
I've read that section and it states that I can create a "join table" by putting the (PKs) of each tbl into the "join table".  But those can be difficult to create it says.  

I have used the Patient ID as the (PK) for Patient Demographics and have used it as the (FK) in my other tables.  If I pull the (PKs) of the other tables in to the Patient Demographics as (FKs) will that work?
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40577582
So does the layout look correct?
You may have a bit more going on here than what can be dealt with in one question...

I created a qry for Pt Demographics and Physicians table.  
Creating a query, in and of itself, wont necessarily generate the error,.
The error will, most likely, occur when you try to add or delete a record in the query.
So, in your case you may have tried to add a Patient Demographic record and use a Facility that was not yet created in the facilities table.

JeffCoachman
0
 

Author Comment

by:Angelia1
ID: 40577603
Yes that's exactly what happened.

I am happy to split up any question I know there's a lot going on.  Just tell me when  I veer to far away from the path cause I'm gonna be asking a lot of questions.  One thing just seems to lead to another.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40577737
OK,
So is the issue in your original post title been resolved?
0
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 40577768
Here is the Relationships diagram from my Many to Many sample database; it shows a typical join table with FK fields that are the PK fields in the two main tables:

Many-to-Many Relationships
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 40577804
Which is exactly what I had recommended in the first place.

:)
0
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 40577889
Right!  It is so much easier when the relationships are set up correctly before data is entered.  But if not,  you can generally fix the data (or most of it) using update and append queries.  My Access Archon article on Fixing Normalization Errors may be helpful here:

http://www.helenfeddema.com/Files/accarch229.zip
0
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 40577894
One other note on the many-to-many relationship between patients and surgeries:  you may need a few more fields in the junction table, such as SurgeryDate or SurgeryLocation, since those values belong to a specific surgery for a specific patient.
0
 

Author Comment

by:Angelia1
ID: 40578177
"So is the issue in your original post title been resolved? " JC  I'm not sure but I think so.

The original question was : I have a table called tbl surgery.  A patient can have several surgeries in their lifetime and we want to capture that data and possibly qry it in the future.  Won't each surgery require a field in a table (Surg 1, Surg 2 and so on)?

Even if I have a look up table w surgical procedures... I will need a field in the surgical table for each procedure to live in when I choose it from the combo box (based on the surgical procedure tbl) on the form.  "one record per surgery performed"
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40578722
OK,
Then I'll bow out now (to avoid confusion) and let the other Experts here help you through this...
;-)

Jeff
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 40578744
Angelia1,

What, exactly, is lacking in the approach I suggested in the very first comment?

Patrick
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

762 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