Re: Vaccine query additonal information
- From: "Tom Ellison" <tellison@xxxxxxxxxxx>
- Date: Fri, 28 Apr 2006 17:57:35 -0500
Dear Mark:
Because of the complexity and the volume of data for an applicaton like
this, I feel it is a very good candidate for computer automation. You have
added some new twists, but they are very systematic and just make the
overall project that much more appropriate for automation. The computer
could do in a fraction of a second what would take people several minutes.
That's what makes my job worthwhile.
I had mistaken the time intervals for years rather than months. Sorry for
my ignorance. That's very easy to fix up.
Properly designed, built, and tested, the computer could save time and
result in more accurate administration, and provide you with timely and
accurate information on which to make judgements. It could forecast the
need for inventories, for example, and then help you make sure patients are
notified so those inventories are consumed close to schedule.
I did get a bit carried away, as I was hoping I might help you get such a
system off the ground. It seems rewarding because it is worthwhile doing.
Shall I take it this is not worth your time at the moment, or what? Didn't
mean to scare you away. Perhaps we should talk.
Tom Ellison
"Mark M S" <mms88@xxxxxxxxxxx> wrote in message
news:OI0vyOxaGHA.4972@xxxxxxxxxxxxxxxxxxxxxxx
Dear Tom, I didn't realize you might need to spend so much effort and time
on this. Wow!
You are right. It is complex logic and there are accepted ranges when a
second, third, and fourth dose must be spaced. I wanted to count the
numbers because if I am on top of the vaccine administration. I will be
directing the spacing at each visit. If they fall out of the established 2
month 4 month 6 months 12 month or 15 months (that is common) it still is
the total number of doses they receive under 24 months that is being
screened by the insurance companies.
Unfortunately, the names aren't as simple as DPT or HIB because there are
two combinations I must work with. There are the HebB/HIB at 2 months and
six months combination and DPT/HIB combination (at 15-18 months)
VACCINE1 PATNO VACNAME VACDATE
288 FLU - 6-35m 10/5/2005
288 Var 1/25/2005
288 HepB 12/1/2003
288 HepB/HIB 1/13/2004
288 HepB/HIB 4/2/2004
288 HepB/HIB 1/25/2005
288 DTaP 1/13/2004
288 DTaP 4/2/2004
288 DTaP 6/14/2004
288 IPV 1/13/2004
288 IPV 4/2/2004
288 IPV 6/14/2004
288 PCV 1/13/2004
288 PCV 4/2/2004
288 PCV 9/2/2004
288 PCV 1/25/2005
288 MMR 1/25/2005
plus DTaP/HIB will cover the all the variations I use.
I can't expect you to spend much time on this and it is my responsibility
to work with my EMR vendor to generate the ongoing reports. I was
fascinated by the Access query and hoped the solution wouldn't require a
long or substantial expense to resolve. You have been great to discuss
this.
*************************************************
Mark M Simonian MD FAAP
Medical Director, ChildNet Medical Assoc.
681 Medical Center Drive West #106
Clovis, CA 93611
(559) 325-6850
www.markmsimonian.medem.com
****************************************
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" <tellison@xxxxxxxxxxx> wrote in message
news:usUoo$vaGHA.4424@xxxxxxxxxxxxxxxxxxxxxxx
Dear Dr. Mark:
There's a question. Consider DPT, due at ages 2, 4, 6, and 15. Now,
consider a patient who is now 15. If they received DPT at age 14, are
they past due? What if they received it at age 12? Or 10? What is the
threshold, the definition of "past due"? Sorry to get technical, but
this is, well, technical. I have to program by very stringent
definitions.
Tom Ellison
"Mark M S" <mms88@xxxxxxxxxxx> wrote in message
news:e4GnKWvaGHA.4196@xxxxxxxxxxxxxxxxxxxxxxx
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" <tellison@xxxxxxxxxxx> wrote in message
news:%23Oa%23e5uaGHA.608@xxxxxxxxxxxxxxxxxxxxxxx
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
"Mark M S" <mms88@xxxxxxxxxxx> wrote in message
news:uK7nsPuaGHA.996@xxxxxxxxxxxxxxxxxxxxxxx
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.
.
- References:
- Vaccine query
- From: Mark M S
- Re: Vaccine query
- From: Tom Ellison
- Re: Vaccine query clarification
- From: Mark M S
- Re: Vaccine query clarification
- From: Tom Ellison
- Vaccine query additonal information
- From: Mark M S
- Vaccine query
- Prev by Date: Re: Vaccine query clarification
- Next by Date: Re: Multiple table select query remove duplicates
- Previous by thread: Vaccine query additonal information
- Next by thread: Re: Vaccine query clarification
- Index(es):
Relevant Pages
|
|
