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?

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)?
Angelia1Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

 
Patrick MatthewsCommented:
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

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
 
Angelia1Author Commented:
and use surgery types as a look up?
0
 
Ernest GroggCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Angelia1Author Commented:
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
 
Jeffrey CoachmanMIS LiasonCommented:
<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
 
Angelia1Author Commented:
Thank you
0
 
Jeffrey CoachmanMIS LiasonCommented:
...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
 
PatHartmanCommented:
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
 
Angelia1Author Commented:
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
 
PatHartmanCommented:
Combo on the form is correct.  Combo on the table is bad juju.
0
 
Jeffrey CoachmanMIS LiasonCommented:
...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
 
Helen FeddemaCommented:
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
 
Angelia1Author Commented:
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
 
Jeffrey CoachmanMIS LiasonCommented:
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
 
Jeffrey CoachmanMIS LiasonCommented:
...or ...
You cannot add a Surgery for procedure that does not exist in the SurgicalProcedure table.
0
 
Angelia1Author Commented:
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
 
Angelia1Author Commented:
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
 
Jeffrey CoachmanMIS LiasonCommented:
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
 
Angelia1Author Commented:
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
 
Jeffrey CoachmanMIS LiasonCommented:
OK,
So is the issue in your original post title been resolved?
0
 
Helen FeddemaCommented:
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
 
Patrick MatthewsCommented:
Which is exactly what I had recommended in the first place.

:)
0
 
Helen FeddemaCommented:
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
 
Helen FeddemaCommented:
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
 
Angelia1Author Commented:
"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
 
Jeffrey CoachmanMIS LiasonCommented:
OK,
Then I'll bow out now (to avoid confusion) and let the other Experts here help you through this...
;-)

Jeff
0
 
Patrick MatthewsCommented:
Angelia1,

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

Patrick
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.