Insert First and Last Date from a related table

[Kevin]

Hello:

I have the following two tables:
(1) tblPharmacy has individual prescriptions from a pharmacy listed by
medical record number, date of prescription, and drug class (one of 2 defined
drug classes)
(2) tblPatients has individual patients listed by medical record number

These two tables are related by medical record number.

I want to add fields into tblPatients for the following two values:
(1) Date of first prescription
(2) Date of last prescription

Furthermore, for these two values (first and last date), I want this to be
separated by drug class so tblPatietns would look something like:

MRN, DateFirstDrug1, DateLastDrug1, DateFirstDrug2, DateLastDrug2

*MY QUESTION*

Could I get any help on how I can do this? I am thinking this would take
some sort of VBA script to automatically go through all the records in both
tables and enter the data into these field? Any directions of something to
look up to figure out how to do this would be great!

Thanks!

 

[John W. Vinson]

Hello:

I have the following two tables:
(1) tblPharmacy has individual prescriptions from a pharmacy listed by
medical record number, date of prescription, and drug class (one of 2 defined
drug classes)
(2) tblPatients has individual patients listed by medical record number

These two tables are related by medical record number.

I want to add fields into tblPatients for the following two values:
(1) Date of first prescription
(2) Date of last prescription

Ummmm...

No. You almost certainly DON'T. This is derived data which can and should be
found by running a dynamic query (see below). The stored date of last
prescription, at least, would be WRONG the moment a new prescription is
issued.

Furthermore, for these two values (first and last date), I want this to be
separated by drug class so tblPatietns would look something like:

MRN, DateFirstDrug1, DateLastDrug1, DateFirstDrug2, DateLastDrug2

*MY QUESTION*

Could I get any help on how I can do this? I am thinking this would take
some sort of VBA script to automatically go through all the records in both
tables and enter the data into these field? Any directions of something to
look up to figure out how to do this would be great!

Thanks!

Assuming fieldnames MRN (Medical Record Number), ScripDate (date of
prescription), and DrugClass, try

SELECT MRN,
(SELECT Min([ScripDate]) FROM yourtable AS M1 WHERE M1.MRN = yourtable.MRN AND
M1.DrugClass = 1) AS DateFirstDrug1,
(SELECT Min([ScripDate]) FROM yourtable AS M2 WHERE M2.MRN = yourtable.MRN AND
M2.DrugClass = 2) AS DateFirstDrug2,
(SELECT Max([ScripDate]) FROM yourtable AS X1 WHERE X1.MRN = yourtable.MRN AND
X1.DrugClass = 1) AS DateLastDrug1,
(SELECT Max([ScripDate]) FROM yourtable AS X2 WHERE X2.MRN = yourtable.MRN AND
X2.DrugClass = 2) AS DateLastDrug2
FROM yourtable ORDER BY MRN;

Air code, untested; copy and paste it into the SQL window of a new query, and
change all instances of yourtable to the actual name of your table, and all
fieldnames as appropriate.

John W. Vinson [MVP]

 

[Kevin]

Yes, that works (with assigning appropriate variables), Thanks!

FYI, this dataset is historical, so actually the data will not change,
however, I very much appreciate your tip as this seems much more helpful for
future use.

However, all the records are listed (so each refill is listed for each row
by MRN with all the same data) - I am searching around to find a way to
remove the duplicates, however, it looks like this is a sophisticated thing
to figure out.

Might you have any tips?

Thanks so much!

Hello:

I have the following two tables:
(1) tblPharmacy has individual prescriptions from a pharmacy listed by
medical record number, date of prescription, and drug class (one of 2 defined
drug classes)
(2) tblPatients has individual patients listed by medical record number

These two tables are related by medical record number.

I want to add fields into tblPatients for the following two values:
(1) Date of first prescription
(2) Date of last prescription

Ummmm...

No. You almost certainly DON'T. This is derived data which can and should be
found by running a dynamic query (see below). The stored date of last
prescription, at least, would be WRONG the moment a new prescription is
issued.

Furthermore, for these two values (first and last date), I want this to be
separated by drug class so tblPatietns would look something like:

MRN, DateFirstDrug1, DateLastDrug1, DateFirstDrug2, DateLastDrug2

*MY QUESTION*

Could I get any help on how I can do this? I am thinking this would take
some sort of VBA script to automatically go through all the records in both
tables and enter the data into these field? Any directions of something to
look up to figure out how to do this would be great!

Thanks!

Assuming fieldnames MRN (Medical Record Number), ScripDate (date of
prescription), and DrugClass, try

SELECT MRN,
(SELECT Min([ScripDate]) FROM yourtable AS M1 WHERE M1.MRN = yourtable.MRN AND
M1.DrugClass = 1) AS DateFirstDrug1,
(SELECT Min([ScripDate]) FROM yourtable AS M2 WHERE M2.MRN = yourtable.MRN AND
M2.DrugClass = 2) AS DateFirstDrug2,
(SELECT Max([ScripDate]) FROM yourtable AS X1 WHERE X1.MRN = yourtable.MRN AND
X1.DrugClass = 1) AS DateLastDrug1,
(SELECT Max([ScripDate]) FROM yourtable AS X2 WHERE X2.MRN = yourtable.MRN AND
X2.DrugClass = 2) AS DateLastDrug2
FROM yourtable ORDER BY MRN;

Air code, untested; copy and paste it into the SQL window of a new query, and
change all instances of yourtable to the actual name of your table, and all
fieldnames as appropriate.

John W. Vinson [MVP]

 

[Kevin]

FYI, here is the actual code:

SELECT MRN,
(SELECT Min([ScripDate]) FROM tblPharmacyData AS M1 WHERE M1.MRN =
tblPharmacyData.MRN AND M1.DrugClass = "PI") AS PIFirstRxDate,
(SELECT Min([ScripDate]) FROM tblPharmacyData AS M2 WHERE M2.MRN =
tblPharmacyData.MRN AND M2.DrugClass = "NNRTI") AS NNRTIFirstRxDate,
(SELECT Max([ScripDate]) FROM tblPharmacyData AS X1 WHERE X1.MRN =
tblPharmacyData.MRN AND X1.DrugClass = "PI") AS PILastRxDate,
(SELECT Max([ScripDate]) FROM tblPharmacyData AS X2 WHERE X2.MRN =
tblPharmacyData.MRN AND X2.DrugClass = "NNRTI") AS NNRTILastRxDate
FROM tblPharmacyData ORDER BY MRN;

Thanks!

 

[John W. Vinson]

However, all the records are listed (so each refill is listed for each row
by MRN with all the same data) - I am searching around to find a way to
remove the duplicates, however, it looks like this is a sophisticated thing
to figure out.

Use a Totals query grouping by MRN, or just a DISTINCT:

SELECT DISTINCT MRN,
(SELECT Min([ScripDate]) FROM tblPharmacyData AS M1 WHERE M1.MRN =
tblPharmacyData.MRN AND M1.DrugClass = "PI") AS PIFirstRxDate,
(SELECT Min([ScripDate]) FROM tblPharmacyData AS M2 WHERE M2.MRN =
tblPharmacyData.MRN AND M2.DrugClass = "NNRTI") AS NNRTIFirstRxDate,
(SELECT Max([ScripDate]) FROM tblPharmacyData AS X1 WHERE X1.MRN =
tblPharmacyData.MRN AND X1.DrugClass = "PI") AS PILastRxDate,
(SELECT Max([ScripDate]) FROM tblPharmacyData AS X2 WHERE X2.MRN =
tblPharmacyData.MRN AND X2.DrugClass = "NNRTI") AS NNRTILastRxDate
FROM tblPharmacyData ORDER BY MRN;

John W. Vinson [MVP]