records

L

landoJ

Hi, my first post

I have a database containing patients' personal details. I have another
table, query,and form etc from which I print and store receipts, and all
works very well.

However, when a patient changes her surname, or a patient changes address,
this information is updated in the personal details table. Thereafter, all
future receipts contain the updated information which is good, BUT, so do
the previous records (previous receipts already given, for example 6 months
ago)

I need some way of keeping the previous 'old' information on the previous
receipts, (i.e. the patient's previous address and previous unmarried name
need to remain on those older receipts) but still be able to print the new
receipts with the current updated information.

Is this possible? Any help appreciated.

Thanks

L
 
A

Arvin Meyer [MVP]

You need to keep a history so that when a patient name changes, you have a
table containing the PatientID, the patient's old name, the patient's new
name, and the date.

In a query, you can check if there is an entry in the name history table,
then compare dates to display the correct name. The address, however can
change independently of the name, so it gets even more complex.

Perhaps the easiest solution is to break normalization rules and store the
patient surname and address in the detail table in addition. Make sure that
the PatientID is stored as well, so that you can always run a query to get
all the names and addresses for a patient.
 
K

KARL DEWEY

The history table to contain all fields that are subject to change --
NameHistory --
PatientID
FName
LName
Middle
Addr1
Addr2
City
State
Zip
Phone1
Phone2
Gender
StartDate
EndDate

Information for old reciepts query --
SELECT *, NameHistory.*
FROM Reciepts INNER JOIN NameHistory ON Reciepts.PatientID =
NameHistory.PatientID
WHERE Reciepts.TransDate Between StartDate AND IIF([EndDate] Is Null,
#12/31/9999#, [EndDate]);
 
A

Arvin Meyer [MVP]

In your design there are 3 main variables that change and they can do so
together or independently of one another. I think only 2 of them really
matter. Old phone numbers are typically useless, so I wouldn't bother
storing the history of those. And except for a very few folks, gender is
unlikely to change, Neither is the first or middle names likely to change.
What does change is last name and address. Because they can do so
independently, I'd use 2 tables:

tblPatientNameHistory
PatientID
LName
ChangeDate

tblPatientAddressHistory
PatientID
Address
City
State
ZipCode
ChangeDate

You do not need an end date because it is the same as a start date for the
next record for that patient.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


KARL DEWEY said:
The history table to contain all fields that are subject to change --
NameHistory --
PatientID
FName
LName
Middle
Addr1
Addr2
City
State
Zip
Phone1
Phone2
Gender
StartDate
EndDate

Information for old reciepts query --
SELECT *, NameHistory.*
FROM Reciepts INNER JOIN NameHistory ON Reciepts.PatientID =
NameHistory.PatientID
WHERE Reciepts.TransDate Between StartDate AND IIF([EndDate] Is Null,
#12/31/9999#, [EndDate]);

--
Build a little, test a little.


Arvin Meyer said:
You need to keep a history so that when a patient name changes, you have
a
table containing the PatientID, the patient's old name, the patient's new
name, and the date.

In a query, you can check if there is an entry in the name history table,
then compare dates to display the correct name. The address, however can
change independently of the name, so it gets even more complex.

Perhaps the easiest solution is to break normalization rules and store
the
patient surname and address in the detail table in addition. Make sure
that
the PatientID is stored as well, so that you can always run a query to
get
all the names and addresses for a patient.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com
Click to expand...
Click to expand...
 
K

KARL DEWEY

I prefer an EndDate as it makes writing the query criteria easier to show
what was when for the older receipts.

--- WHERE Reciepts.TransDate Between StartDate AND IIF([EndDate] Is
Null,
#12/31/9999#, [EndDate]);

--
Build a little, test a little.


Arvin Meyer said:
In your design there are 3 main variables that change and they can do so
together or independently of one another. I think only 2 of them really
matter. Old phone numbers are typically useless, so I wouldn't bother
storing the history of those. And except for a very few folks, gender is
unlikely to change, Neither is the first or middle names likely to change.
What does change is last name and address. Because they can do so
independently, I'd use 2 tables:

tblPatientNameHistory
PatientID
LName
ChangeDate

tblPatientAddressHistory
PatientID
Address
City
State
ZipCode
ChangeDate

You do not need an end date because it is the same as a start date for the
next record for that patient.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


KARL DEWEY said:
The history table to contain all fields that are subject to change --
NameHistory --
PatientID
FName
LName
Middle
Addr1
Addr2
City
State
Zip
Phone1
Phone2
Gender
StartDate
EndDate

Information for old reciepts query --
SELECT *, NameHistory.*
FROM Reciepts INNER JOIN NameHistory ON Reciepts.PatientID =
NameHistory.PatientID
WHERE Reciepts.TransDate Between StartDate AND IIF([EndDate] Is Null,
#12/31/9999#, [EndDate]);

--
Build a little, test a little.


Arvin Meyer said:
You need to keep a history so that when a patient name changes, you have
a
table containing the PatientID, the patient's old name, the patient's new
name, and the date.

In a query, you can check if there is an entry in the name history table,
then compare dates to display the correct name. The address, however can
change independently of the name, so it gets even more complex.

Perhaps the easiest solution is to break normalization rules and store
the
patient surname and address in the detail table in addition. Make sure
that
the PatientID is stored as well, so that you can always run a query to
get
all the names and addresses for a patient.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


Hi, my first post

I have a database containing patients' personal details. I have
another
table, query,and form etc from which I print and store receipts, and
all
works very well.

However, when a patient changes her surname, or a patient changes
address,
this information is updated in the personal details table. Thereafter,
all
future receipts contain the updated information which is good, BUT, so
do
the previous records (previous receipts already given, for example 6
months ago)

I need some way of keeping the previous 'old' information on the
previous
receipts, (i.e. the patient's previous address and previous unmarried
name
need to remain on those older receipts) but still be able to print the
new
receipts with the current updated information.

Is this possible? Any help appreciated.

Thanks

L
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Curiosity question relating to Dcount in a Text Box and in VBA 3
Passing information from one form to another 5
New Database Design 6
Access assuming duplicate records in a form 4
Adding records to unbound subform 1
Display new record option upon NotInList event in combobox 2
help with auto fill 2
Help with design of form with multiple tables 2

Top