Complex database, hopefully an easy answer.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm using a database to track patients enrolled in research protocols at our
medical center. Any one patient may enroll in several protocols. I have a
patient table (with PtID as the primary key), a protocol table (with
ProtocolID as a primary key), and a table that tracks a patient's activities
relative to a specific protocol; this 3rd table includes PtID & ProtocolID as
foreign keys. I have combo boxes in this 3rd table so I can recognize which
patient & which protocol it refers to at a quick glance.

Within my patient table, I include appointment data. I've built a query & a
report to display the patient appointment list and a form to input data to my
patient table. I can enter a new patient (including appointment time) into my
patient form & it appears in my patient table, but the appointment doesn't
show up when I run my query. I have to go back & manually enter the patient
into my 3rd table which includes the 2 foreign keys. I can use the dropdown
feature in this third table to find the new patient within the combo box, by
the way.

Any help you could give me would be greatly appreciated. Because the
database is huge (870 patients & 155 protocols), I shudder to think about
starting from scratch. If you need additional info, please tell me exactly
what you need to know.

MUCH thanks.
 
I may not understand your situation quite well enough yet, so take these
notions with a grain or two...

Patients
PtID
...(more patient-only data - e.g., FName, LName, DOB)

Protocol
ProtocolID
Title
Description
...(more protocol-only data -- ? date begun, ...)

Enrollment
EnrollmentID
PtID
ProtocolID
DateEnrolled (date this patient enrolled in this protocol)

EnrollmentDetail
EnrollmentDetailID
EnrollmentID (which "Enrollment" does this detail refer to)
AppointmentDate (for which appointment date for this enrollment)
...(whatever details you need to keep that are specific to the
appointment)

I don't quite get why you would keep (?)multiple appointment information
stuffed into the Patient table -- wouldn't you need to just keep adding
columns for each appointment? That would be a very spreadsheetly thing to
do, and would require constant re-modeling of your tables, forms, queries,
reports, etc.


--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/
 
I'm using a database to track patients enrolled in research protocols at our
medical center. Any one patient may enroll in several protocols. I have a
patient table (with PtID as the primary key), a protocol table (with
ProtocolID as a primary key), and a table that tracks a patient's activities
relative to a specific protocol; this 3rd table includes PtID & ProtocolID as
foreign keys. I have combo boxes in this 3rd table so I can recognize which
patient & which protocol it refers to at a quick glance.
Click to expand...

ok... just be sure you're aware of the (rather stringent!) HIPAA
requirements concerning data privacy and data integrity. These can be
a pain in the neck (and elsewhere!) for developers but the penalties
for violation are *not* trivial.
Within my patient table, I include appointment data.
Click to expand...

Ummmmm.... Unless each patient gets one and only one appointment,
that's the wrong place. You really should have a separate table of
appointments, with a PatientID and other fields about the appointment.
I've built a query & a
report to display the patient appointment list and a form to input data to my
patient table. I can enter a new patient (including appointment time) into my
patient form & it appears in my patient table, but the appointment doesn't
show up when I run my query.
Click to expand...

Then there's something wrong with the Query or with the Form. Could
you describe your table structure?
I have to go back & manually enter the patient
into my 3rd table which includes the 2 foreign keys. I can use the dropdown
feature in this third table to find the new patient within the combo box, by
the way.
Click to expand...

You can and should use a Subform. Table datasheets are NOT designed
for data entry or display and should not be used for this purpose. You
could use a Form based on the Patients table, with a Subform based on
the Appointments table; this subform would use the PatientID as the
master/child link field, and have a combo box for the protocol.
Any help you could give me would be greatly appreciated. Because the
database is huge (870 patients & 155 protocols), I shudder to think about
starting from scratch. If you need additional info, please tell me exactly
what you need to know.
Click to expand...

870,000 records in a table is getting pretty big. 8,700,000 is
getting huge. You're still *tiny* in database terms.

John W. Vinson[MVP]
 
I'm up to speed on HIPAA & you're right - it can be a pain. Thanks for the
reminder.

Each patient DOES have only one appointment I care about (at least at one
time), so I included this info in the patient table. It is unique to the
patient and while (s)he will have another appointment once he sees the doc
this time around, I figured I could change after (s)he comes in.

My query & report seem to work OK on patients already included within the
patient table, but it's the new guy that gets added in that doesn't show up.
So I suspect it's the form that's the problem.

As to table structure, I'm not sure what all info you need. I have the
following fields:
PtID (autonumber, primary key)
LastName
FirstName
MedicalRecordNumber
MDID (foreign key from MD table, works fine)
DateOfBirth
DateOfDeath
CauseOfDeaht
ApptDate
ApptTime
Room#OfInpatients
AppointmentNeeded? (yes/no - for pts who don't have a current appt & who
aren't inpatients but who NEED an appointment set)
Comments (memo to self - things to do for this patient)

Those are the biggies.

I have a seperate Protocol table:
ProtocolID (autonumber, primary key)
Protocol name
A few other tedious details that aren't pertinent.

The 3rd table may be my problem. It contains info specific to any one
patient's activities related to any one protocol. Since a protocol may have
many patients enrolled and any one patient can enroll in several protocols, I
have the structure as follows:

PtID (Number... foreign key from tblPt)
Prot_ID (Number... foreigh key from tblPt)
ScreeningDate
Cohort
Study#
PtStatus
Day1Treatment
LastStudyTreatment

As to relationships, I joined both the PtID & ProtocolID primary keys from
the first 2 tables to the PtID & ProtocolID from the 3rd table, enforcing
referential integrity and cascading updates.

Please let me know what else you may need to know to help me unmuddle
myself. I can't thank you enough!
 
Hi Jeff - I think you & John Vinson are both addressing my issue in a very
helpful fashion. I responded to John's reply to my post - can you please read
over that reply & see if you have other suggestions as well?
 
I'm up to speed on HIPAA & you're right - it can be a pain. Thanks for the
reminder.
Click to expand...

Thought you might - but if you didn't it would obviously be important
to let you know!
Each patient DOES have only one appointment I care about (at least at one
time), so I included this info in the patient table. It is unique to the
patient and while (s)he will have another appointment once he sees the doc
this time around, I figured I could change after (s)he comes in.
Click to expand...

Well... the real-life reality is one patient:multiple appointments.
You don't care about a history of who was seen, for what, when, by
whom? It's EASY to add now; much harder later!
My query & report seem to work OK on patients already included within the
patient table, but it's the new guy that gets added in that doesn't show up.
So I suspect it's the form that's the problem.
Click to expand...

I'm not sure when you're adding and when or where you're trying to
display; I can hazard a guess that you might just need to Requery one
form after adding a patient (using another form...?)
As to table structure, I'm not sure what all info you need. I have the
following fields:
PtID (autonumber, primary key)
LastName
FirstName
MedicalRecordNumber
MDID (foreign key from MD table, works fine)
DateOfBirth
DateOfDeath
CauseOfDeaht
ApptDate
ApptTime
Room#OfInpatients
AppointmentNeeded? (yes/no - for pts who don't have a current appt & who
aren't inpatients but who NEED an appointment set)
Comments (memo to self - things to do for this patient)

Those are the biggies.

I have a seperate Protocol table:
ProtocolID (autonumber, primary key)
Protocol name
A few other tedious details that aren't pertinent.
Click to expand...

looks fine, other than the appointment stuff perhaps (but you know
your business better than I do of course!)
The 3rd table may be my problem. It contains info specific to any one
patient's activities related to any one protocol. Since a protocol may have
many patients enrolled and any one patient can enroll in several protocols, I
have the structure as follows:

PtID (Number... foreign key from tblPt)
Prot_ID (Number... foreigh key from tblPt)
ScreeningDate
Cohort
Study#
PtStatus
Day1Treatment
LastStudyTreatment
Click to expand...

Again... only ONE screening, ever, per patient?
What's the Study# - is it truly an attribute of a patient's enrollment
into a protocol, or of the protocol itself, or what?
As to relationships, I joined both the PtID & ProtocolID primary keys from
the first 2 tables to the PtID & ProtocolID from the 3rd table, enforcing
referential integrity and cascading updates.
Click to expand...

Sounds good.
Please let me know what else you may need to know to help me unmuddle
myself. I can't thank you enough!
Click to expand...

You asked above:

Within my patient table, I include appointment data. I've built a
query & a report to display the patient appointment list and a form to
input data to my patient table. I can enter a new patient (including
appointment time) into my patient form & it appears in my patient
table, but the appointment doesn't show up when I run my query.

How are you "running" the query? Why run it at all, for that matter?
QUery datasheets are of *very* limited utility other than for
debugging. Do you have a Report based on the query? Just launch the
report after adding the patient or appointment data. Do you just want
to see the information? Use a Form (either as a Subform of the patient
form, or requery it in the AfterUpdate event of the patient form).

John W. Vinson[MVP]
 
Actually, I don’t need to track the history of patient appointments in my
database – there is an electronic record in the medical center that I can
call up easily if I need to. As far as the patient’s appointment schedule
goes, I’m only using the database to prepare for future appointments. I agree
it would be easiest to add now, but I simply can’t think of a reason to track
this for my purposes.

My problem isn’t adding appointments for existing patients – it happens when
I add a new patient. I’ve had to manually enter him into both tables (the
patient table and the third table that includes the activity specific to a
single patient for a single protocol) in order for his data to show up in my
queries.

I don’t understand what you mean by “requery one form after adding a patient
(using another form…?)†Can you explain further – this may be my problem, but
I haven’t queried forms before.

Is there only ONE screening, ever, per patient? Well, basically, as regards
a single protocol, that’s pretty much true. A patient may be screened for
multiple protocols, but in my original database I added the same patient to
the patient table each time he was screened for or enrolled to a protocol.
This meant I had a good deal of duplicated data (demographic info, for
example) in multiple rows for the same patient. I’m trying to normalize the
tables now before any more cows get out of the barn. Oh – and the study
number is unique to a protocol. Each sponsor uses a different numbering
scheme for a patient, so a patient enrolled in “x†protocol may have number
502-113 on that protocol and the same patient enrolled in “y†protocol may
have number 39-10-17-4 on the second protocol.

Yes, I have an appointment report based on the appointment query, but when I
enter a brand new patient and enter his appointment data, this data doesn’t
appear in the query OR the report.

You also mentioned requerying in the AfterUpdate event of the patient form.
NOW you’re talking a strange and wondrous language. What the heck is that?

Thank you, John. If you weren’t an MVP already, I’d be your campaign chairman.

Merry Christmas!
 
I'll jump back in briefly, as it looks like John's leads are working for
you...

If each protocol X Patient needs to uniquely identify the patient (by some
code/number), that is a fact about the protocol X Patient record, not the
patient alone. This number belongs in the "resolver" table you use to show
valid patient/protocol combinations.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/
 
Keep jumping in, please - this puppy ain't fixed yet.

I suppose by "resolver table" you are referring to the table I've got which
has PtID & Protocol_ID as foreign keys from tblPatient & tbl Protoocols. This
is the only location where I include info specific to a given patient's
activity relative to a given protocol. In the protocol table I include info
that relates only to the protocol - not to the patient - & I don't have a
foreign key "PtID" in the protocol table. In the patient table I include info
relating only to the patient - not to the protocol - and I don't have a
foreign key "Protocol_ID" in the patient table. I do have upcoming patient
appointment data in tblPatient, but i haven't figured out how to enter data
relative to a new patient and have it populate in existing queries. At this
point I'm entering data into tblPt, going to tblPatientProtocolActivity (the
"resolver table" you referred to"), and seeing no new patient there... so I
then head to a new row, use the combo box derived from PtID (the foreign key
from tblPt) to find the new patient, and enter it that way. THEN - and only
then - does the new patient appear in the queries I've built.

How awkward.

Again, thanks so much for your help... both of you!
 
Back
Top