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
148 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
[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
  • 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 

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 36

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 36

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

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
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.

756 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