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
151 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

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 37

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 37

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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

739 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