What's happening? I'm confused!

[Guest]

I have two tables - 'Patient' and 'Treatments'.
'Patient' contains the fields Patient_ID, LastName, FirstName, BirthDate,
Gender, and Suburb.
'Treatment' contains the fields Patient_ID, LastName, FirstName, BirthDate,
Gender, Suburb and a number of fields that require the user to enter data.
The tables are linked in a one-to-many relationship.

Data from 'Patient' is contained in the main form and a linked subform
provides the combo boxes etc for user data entry. The forms are currently
linked in this manner -
Link child Field: Patient_ID; LastName; FirstName; BirthDate; Gender; Suburb
Link Master Field: Patient_ID; LastName; FirstName; BirthDate; Gender; Suburb

Subform properties are set so that Allow Edits is 'yes' and Data Entry is
'no'.

At present, all previous entries for each client are able to be viewed in
the subform except where a client has changed the suburb in which they live.
If I remove 'Suburb' from the Link fields then I can see the entries for all
suburbs in which they have lived. However, when I save the record there is a
null entry in the Suburb field in the 'Treatments' table that means a blank
field in a report that is generated using the data in 'Treatments' table.

Why is this happening and what do I need to do so that I can see all entries
for each Patient_ID?

Your help is, as always, greatly appreciated.

Regards,

Joe

 

[Al Campagna]

Joe,
In a One to Many table relationship as you have described... only only field is
required to establish that realtionship.
If PatientID is a key unique field, such as an auto number, that's the only link
needed between a Patient and the Patient's Treatments.
You're using Patient fields that may change as links, so when one does... like Suburb,
the relationship is lost between the Patient and thier Treatments.
Remove all the links except PatientID, and remove LastName, FirstName, BirthDate,
Gender, and Suburb from your Treatment table.
--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

 

[John W. Vinson]

I have two tables - 'Patient' and 'Treatments'.
'Patient' contains the fields Patient_ID, LastName, FirstName, BirthDate,
Gender, and Suburb.
'Treatment' contains the fields Patient_ID, LastName, FirstName, BirthDate,
Gender, Suburb and a number of fields that require the user to enter data.
The tables are linked in a one-to-many relationship.

Then your table design IS WRONG.

Relational databases use the "Grandmother's Pantry Principle": "A
place - ONE place! - for everything, everything in its place." A
patient has a lastname, a birthdate, and a suburb - a treatment DOES
NOT.

The Treatment table should contain the Patient_ID (hopefully that's
the primary key of the Patient table) and fields pertaining to the
treatment; you should NOT store patient data redundantly in the
treatment table.

If you're entering the patient data repeatedly in the Patient table,
as your message implies, you're making another mistake. You should
have *ONE* record with patient data for each patient, linked
one-to-many to the multiple treatment records for that patient.

John W. Vinson [MVP]

 

[Guest]

Thanks John & Al for your replies.

I understand the principle of database design you outline. My problem is
complicated by the 'Patient' table being a systemic download of active
patients only so that I lose links with data like DOB, gender etc if the
patient is deceased or discharged from care. Also tracking the client's
Suburb is important to data analysis.

I would appreciate any thoughts you might have that can help me retain
meaningful access to past data for analysis today and in the future.

Many thanks,

Joe

 

[John W. Vinson]

I understand the principle of database design you outline. My problem is
complicated by the 'Patient' table being a systemic download of active
patients only so that I lose links with data like DOB, gender etc if the
patient is deceased or discharged from care. Also tracking the client's
Suburb is important to data analysis.

I would appreciate any thoughts you might have that can help me retain
meaningful access to past data for analysis today and in the future.

I guess I don't understand. If you download data, you can choose to
keep it, or to erase it or overwrite it with the next download. If you
want to keep it... keep it!

Do you need a historical record of the patient's suburb,
chronologically? If not, all you need to do is to UPDATE the Suburb
field. If so, you will need a history table with PatientID, Suburb
(and other time-dependent information), and a date.

John W. Vinson [MVP]

 

[Guest]

John,

Thanks for your input. I hadn't thought of using a 'history' table and I
think this would solve my problem very well. Please could you guide me on how
I would review historical data using a query based on the 'history',
'treatment' and 'patient' tables?

 

[Al Campagna]

Joe,
I think John and I need a better understanding of what data you're downloading. It
appears from your description that it has an "updated" master patient listing... but no
associated Treatment data? And then you have to try to reassociate Patients with your
Treatment data you've built up in another database? And, there is no consistent key field
like Patient ID to properly do that? (so... you use "multiple field" linking in order to
try to get an accurate "hit" at linking the new data to the old?
*We need a good description of what data your downloading (just the primary fields)
vs. what data you have in "your" database, and why you seem to be losing the realtionship
between the two.

--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

 

[Guest]

Al, John -

The 'Patient' table contains all the fields relating to patient details - ie
LastName, FirstName, BirthDate, Address, Suburb, and gender. This table is
updated on a regular basis from a manual download (From the Parent
Organisation) that contains the new table with all changes of address or
Surname and removal of details for deceased patients. There is no treatment
data associated with the 'Patient' table download.

The 'Treatment' table contains fields relating to consult_date, History,
Exam_findings, Intervention and Outcome. I had added duplicates of the
fields contained in 'Patient' table to try to retain a historical database
however am now aware this has led to a poor database design.

You are right in understanding that I am attempting to link the details of
specific treatment episodes with the patient details contained in the
'patient' table - not only for current but also historical events. The one
consistent link is the unique ID number for each patient that I use as the
primary key.

However, even with the primary key set as the ID number, when patients have
a change in address or surname the historical records in the dataset are not
retrieved into the subform when it is opened to add a new record of
treatment. It works fine for all patients whose details remain constant.

I'm not sure I have clarified this sufficiently for you guys to follow where
I am at - unfortunately I am very part-time with database design/management.

Regards,

Joe

 

[Al Campagna]

Joe,
OK, you have a key PatientID field, and that PatientID field stays constant for a
patient... regardless of whether the ancillary information for that patient should change.
Your Treatment data should be linked to your Patients via that same PatientID (One
Patient...Many Treatments). Remove all other "Patient" type fields from Treatments, and
from any linking. Just PatientID along with the actual treatment values...

I think you're also saying that a Patient (and it's PatientID) in "your" DB may get
dropped from the new "imported" DB. I'll assume so...

Let's say you bring the new data into Access as temporary "working" table called ...
tblTempPatients.
1. First... Backup your current DB!!!
2. Run an Update query from tblTempPatients to tblPatients, related by the PatientID,
one to one. That will update all your existing Patient records with any new ancillary
data.
3. Next, run an Append query from tblTempPatients to tblPatients to add any new
Patient records that have been added since the last download and update.
4. Your Treatment table should still connect to your Patients via the PatientID when
your normal recordset form is opened.

This is my best suggestion, not having seen the actual data and tables themselves...
--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

 

[Guest]

Thanks Al,

I'll let you know how I go.

Joe.

Joe,
OK, you have a key PatientID field, and that PatientID field stays constant for a
patient... regardless of whether the ancillary information for that patient should change.
Your Treatment data should be linked to your Patients via that same PatientID (One
Patient...Many Treatments). Remove all other "Patient" type fields from Treatments, and
from any linking. Just PatientID along with the actual treatment values...

I think you're also saying that a Patient (and it's PatientID) in "your" DB may get
dropped from the new "imported" DB. I'll assume so...

Let's say you bring the new data into Access as temporary "working" table called ...
tblTempPatients.
1. First... Backup your current DB!!!
2. Run an Update query from tblTempPatients to tblPatients, related by the PatientID,
one to one. That will update all your existing Patient records with any new ancillary
data.
3. Next, run an Append query from tblTempPatients to tblPatients to add any new
Patient records that have been added since the last download and update.
4. Your Treatment table should still connect to your Patients via the PatientID when
your normal recordset form is opened.

This is my best suggestion, not having seen the actual data and tables themselves...
--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."