Dear Dr. Mark:
I created a database in which to work on your questions. I have added a
new table "Vaccine":
Vaccine AgeRecommended
DPT 2
DPT 4
DPT 6
DPT 15
IPV 2
IPV 4
IPV 6
MMR 12
VAR 12
HEPB 1
HEPB 2
HEPB 6
I have the following query so far:
SELECT R.PATNO, V.Vaccine, V.AgeRecommended,
DateAdd("yyyy", V.AgeRecommended, R.BIRTHDAT) AS DateRecommended,
(SELECT MAX(V1.VACDATE)
FROM Vaccine1 V1
WHERE V1.PATNO = R.PATNO
AND V1.VACNAME = V.Vaccine) AS VaccDate
FROM REGISTER AS R, VACCINE AS V
WHERE V.AgeRecommended = (SELECT MAX(Va.AgeRecommended)
FROM Vaccine Va
WHERE Va.Vaccine = V.Vaccine
AND Va.AgeRecommended <= DateDiff("yyyy", R.BIRTHDAT, Now()) +
Int(Format(now(), "mmdd") < Format(R.BIRTHDAT, "mmdd")))
ORDER BY R.PATNO, V.Vaccine;
If you add the table I show above, you may be able to run this and see
something of the progress so far.
This shows each patient and each vaccine for that patient. Using the
patient's birth date and the age at which each vaccine is recommended, it
gives the most recent date when that vaccine should have been
administered. This could be extended to look back from some future date,
say 30 or 60 days from today. It also shows the most recent
administration on record of that same vaccine.
What I do not know is how you would want to "flag" those patient/vaccine
combinations that are imminent or past due. Let me give an example.
DPT is recommended at 2 and 4. If a child had DPT at age 3 years 1 month,
when would this patient be flagged for DPT? Perhaps reasonably the answer
is age 5 years 1 month. That is, the interval for DPT seems to be 2 years
for children at this age. Does that make sense? Is it anywhere close to
what good practice would be?
As you can see, I am anticipating that the rules for how to do this would
require some rather complex explanation to be formally formulated. I know
that much of this is in the doctor's discretion, but you want this built
into a computer program, in part so you can make those decisions. Yet, it
seems completely unnecessary to flag someone for DPT who has had that
vaccine less than a year ago, right?
I hope it's not irritating to be so picky and specific, but the product
you end up with will depend on your participation in answering this.
Tom Ellison
Mark M S said:
REGISTER PATNO LNAME FNAME MI BIRTHDAT SEX PORGFNM PORGLNM PORGMI
ADDRESS CITY STATE ZIPCODE HPHONE BPHONE DOACCT TYPACCT CURRBAL LASTPAY
BILLDATE BILLTWO PORG2FNM PORG2LNM PORG2MI DOCTOR TELCALNO SICKVNO
REFERDBY PATSSNO P1SSNO P2SSNO INSURED_ID COPAYBAL ADDRESS1 P1ADD1 P1ADD2
P1CITY P1ST P1ZIP P1LWP P1RELATIONSHIP P1HPHONE P1BPHONE P2ADD1 P2ADD2
P2CITY P2ST P2ZIP P2LWP P2RELATIONSHIP P2HPHONE P2BPHONE P3FNM P3LNM P3MI
P3SSNO P3ADD1 P3ADD2 P3CITY P3ST P3ZIP P3LWP P3RELATIONSHIP P3HPHONE
P3BPHONE P4FNM P4LNM P4MI P4SSNO P4ADD1 P4ADD2 P4CITY P4ST P4ZIP P4LWP
P4RELATIONSHIP P4HPHONE P4BPHONE EMERGENCYCONTACT PATALTERNATEFNM
PATALTERNATELNM ZIP4 LASTUPDATE UPIF_HPCODE INS_CARRIER_CODE BILLTO
INSURED_ID_OTHER INS2_CARRIER_CODE LEGACYID P1_ADD_TYPE P2_ADD_TYPE
P3_ADD_TYPE P4_ADD_TYPE PRIVACY_TAG PRIVACY_NOTE CELL_PHONE PAGER1 FAX1
E_MAIL_ADDRESS DOD VOID_NOTE ADDR_ID P_ADDR_ID S_ADDR_ID PAT_REF_BY
IMM_REGISTRY_OK
99 TESTPATIENT MARY
5/16/1998 F SUSAN TESTPATIENT
123 XYZ STREET CLEVELAND OH 44130 215-555-1212
5/16/2000 2 0 0 9/16/2005 9/29/2005 JOHN TESTPATIENT
LRT 3 5 @@MOM NEEDS TO UPDATE OFFICE W/NEW INS INFO 7/12/05 KR
VACCINES TO BE MAILED TO HOME PLAN COPAY BILLED TO #2 HOME PLAN
SELF 0
Y MOTHER
Y FATHER
N
N
1/27/2006 XX ABI 1 456789 AAK
0
0 389
306
0
--
VACCINE1 PATNO VACNAME VACDATE
99 HepB 9/7/1998
99 HepB 6/15/1998
99 HepB 2/15/1999
99 DTaP 7/15/1998
99 DTaP 9/15/1998
99 DTaP 11/15/1998
99 IPV 7/15/1998
99 IPV 9/15/1998
99 IPV 8/16/1998
99 HIB-PRP-T 7/15/1998
99 HIB-PRP-T 9/15/1998
99 HIB-PRP-T 11/15/1998
99 HIB-PRP-T 8/15/1999
99 MMR 5/17/1999
Minimum table would be in months
DPT 2,4,6,15
IPV 2,4,6
HIB 2,4,6,15
MMR 12
VAR 12
HEPB 1,2, 6
Tom, I wanted to be able to generate a monthly report to see who might be
out of compliance so I could follow up with the patient. It is unusual to
have patients not follow up but I don't want to miss people
*************************************************
Mark M Simonian MD FAAP
****************************************
Alert: This email and any files transmitted with it
are intended solely for the use of the individual or
entity to whom they are addressed and may contain
confidential, patient health or other legally
privileged information. If you have received this
email in error please notify the sender by email,
delete and destroy this message and its attachments.
Any unauthorized review, use, disclosure,
or distribution is prohibited.
Tom Ellison said:
Dear Dr. Mark:
What I propose is to filter a cross-product of all patients with all
vaccines and check the required number of vaccinations against the
actual number given.
To be able to flesh this out, I need to know a few things:
- Is the REGISTER table a registration of patients? One row, one
patient, correct?
- Is the VACCINE1 table a record of vaccinations given? If so, does
each record show one specific innoculation? If there are 3
innoculations given in a single visit to a single patient, is this 3
rows of data?
The solution will require 3 tables:
- a table of patients
- a table of innoculations given to those patients
- a table of all innoculations and the number of times they are to be
given
It is possible that there are innoculations that should be given a
certain number of times to someone of age 2, a larger number of times by
age 5, and so forth. Tracking all this, with rules like "within 6
years" and the like can also be accomodated. Describing these rules for
each innoculations should be modeled as well.
A more complete layout of what is in your tables and some good sample
data would be most helpful. I'm expecting that you, as an MD, may have
some interest in building databases, but that this is not the primary
function you want to be performing next week. Perhaps it would work
well to send me what you have so far, and I'll try to work out what can
be done.
Tom Ellison
I would like to determine which patients did not obtain the right number
of vaccines by the age of 2. My database can tell me if each patient is
not up to date through the electronioc medical record but I must look at
a child individually. There is no general report. I can produce a
summary file in Access but must figure out the query
I sure would appreciate some hints how to find outlyers if you know
that each child needs
4 dpt
4 hib
3 ipv
3 hepb
1 mmr
1 var
My query so far that would include the basic data is
SELECT REGISTER.LNAME, REGISTER.FNAME, REGISTER.MI, REGISTER.BIRTHDAT,
REGISTER.SEX, Year(Now())-Year([BirthDAT]) AS Age, VACCINE1.VACDATE,
VACCINE1.VACNAME
FROM REGISTER INNER JOIN VACCINE1 ON REGISTER.PATNO = VACCINE1.PATNO
WHERE (((REGISTER.FNAME) Not Like "baby*") AND
((Year(Now())-Year([BirthDAT]))<2) AND ((REGISTER.DOCTOR)="mms"))
ORDER BY REGISTER.LNAME;
If I can get a start on how to do this it would be most appreciated.
*************************************************
Mark M Simonian MD FAAP
****************************************
Alert: This email and any files transmitted with it
are intended solely for the use of the individual or
entity to whom they are addressed and may contain
confidential, patient health or other legally
privileged information. If you have received this
email in error please notify the sender by email,
delete and destroy this message and its attachments.
Any unauthorized review, use, disclosure,
or distribution is prohibited.
