actually I just thought of something what I really would like is a list of the patients that meet this criteria, 2 or more visits or one preventative exam (Preventive Medicine) (visit type field)
Sorry brain just fully processed the query
thank you so much
Patty
this query is already limited to the type of visits so I think the "NOT LIKE" stuff probably can be removed but I was not sure
:) thankx
---In MS_Access_Professionals@yahoogroups.com, <pattykf@...> wrote :
Query: lists all pts with encounters (pt information included), want to show each patient and the count of encounters in the query, ie sally sue all her information and had 3 encounters
this is what I wrote:
SELECT [Enc exclude vaccine PPD].person_id, [Enc exclude vaccine PPD].person_nbr, [Enc exclude vaccine PPD].last_name, [Enc exclude vaccine PPD].first_name, [Enc exclude vaccine PPD].date_of_birth, [Enc exclude vaccine PPD].sex, [Enc exclude vaccine PPD].enc_id, [Enc exclude vaccine PPD].modify_timestamp, [Enc exclude vaccine PPD].assessment1, [Enc exclude vaccine PPD].assessment2, [Enc exclude vaccine PPD].Encounter_Date, [Enc exclude vaccine PPD].visit_type, [Enc exclude vaccine PPD].person_nbr, Count([Enc exclude vaccine PPD].person_nbr) AS CountOfperson_nbr
FROM [Enc exclude vaccine PPD]
GROUP BY [Enc exclude vaccine PPD].person_id, [Enc exclude vaccine PPD].person_nbr, [Enc exclude vaccine PPD].last_name, [Enc exclude vaccine PPD].first_name, [Enc exclude vaccine PPD].date_of_birth, [Enc exclude vaccine PPD].sex, [Enc exclude vaccine PPD].enc_id, [Enc exclude vaccine PPD].modify_timestamp, [Enc exclude vaccine PPD].assessment1, [Enc exclude vaccine PPD].assessment2, [Enc exclude vaccine PPD].Encounter_Date, [Enc exclude vaccine PPD].visit_type, [Enc exclude vaccine PPD].person_nbr, [Enc exclude vaccine PPD].assessment1
HAVING ((([Enc exclude vaccine PPD].assessment1) Not Like "Need for*" And ([Enc exclude vaccine PPD].assessment1) Not Like "influenza vaccine" And ([Enc exclude vaccine PPD].assessment1) Not Like "Needs flu shot" And ([Enc exclude vaccine PPD].assessment1) Not Like "*VACCINATION*" And ([Enc exclude vaccine PPD].assessment1) Not Like "Influenza vaccine needed" And ([Enc exclude vaccine PPD].assessment1) Not Like "Encounter for PPD skin test reading" And ([Enc exclude vaccine PPD].assessment1) Not Like "Encounter for PPD test" And ([Enc exclude vaccine PPD].assessment1) Not Like "Screening for tuberculosis" And ([Enc exclude vaccine PPD].assessment1) Not Like "Screening-pulmonary TB" And ([Enc exclude vaccine PPD].assessment1) Not Like "Vaccine for viral hepatitis" And ([Enc exclude vaccine PPD].assessment1) Not Like "Visit for TB skin test") AND ((Count(*))>=1));
Needless to say just looking at it I am sure there is a better way but I am not sure how, it also is not counting the encounters, the count I was doing is off the person_nbr
thank you
this is what I wrote:
SELECT [Enc exclude vaccine PPD].person_id, [Enc exclude vaccine PPD].person_nbr, [Enc exclude vaccine PPD].last_name, [Enc exclude vaccine PPD].first_name, [Enc exclude vaccine PPD].date_of_birth, [Enc exclude vaccine PPD].sex, [Enc exclude vaccine PPD].enc_id, [Enc exclude vaccine PPD].modify_timestamp, [Enc exclude vaccine PPD].assessment1, [Enc exclude vaccine PPD].assessment2, [Enc exclude vaccine PPD].Encounter_Date, [Enc exclude vaccine PPD].visit_type, [Enc exclude vaccine PPD].person_nbr, Count([Enc exclude vaccine PPD].person_nbr) AS CountOfperson_nbr
FROM [Enc exclude vaccine PPD]
GROUP BY [Enc exclude vaccine PPD].person_id, [Enc exclude vaccine PPD].person_nbr, [Enc exclude vaccine PPD].last_name, [Enc exclude vaccine PPD].first_name, [Enc exclude vaccine PPD].date_of_birth, [Enc exclude vaccine PPD].sex, [Enc exclude vaccine PPD].enc_id, [Enc exclude vaccine PPD].modify_timestamp, [Enc exclude vaccine PPD].assessment1, [Enc exclude vaccine PPD].assessment2, [Enc exclude vaccine PPD].Encounter_Date, [Enc exclude vaccine PPD].visit_type, [Enc exclude vaccine PPD].person_nbr, [Enc exclude vaccine PPD].assessment1
HAVING ((([Enc exclude vaccine PPD].assessment1) Not Like "Need for*" And ([Enc exclude vaccine PPD].assessment1) Not Like "influenza vaccine" And ([Enc exclude vaccine PPD].assessment1) Not Like "Needs flu shot" And ([Enc exclude vaccine PPD].assessment1) Not Like "*VACCINATION*" And ([Enc exclude vaccine PPD].assessment1) Not Like "Influenza vaccine needed" And ([Enc exclude vaccine PPD].assessment1) Not Like "Encounter for PPD skin test reading" And ([Enc exclude vaccine PPD].assessment1) Not Like "Encounter for PPD test" And ([Enc exclude vaccine PPD].assessment1) Not Like "Screening for tuberculosis" And ([Enc exclude vaccine PPD].assessment1) Not Like "Screening-pulmonary TB" And ([Enc exclude vaccine PPD].assessment1) Not Like "Vaccine for viral hepatitis" And ([Enc exclude vaccine PPD].assessment1) Not Like "Visit for TB skin test") AND ((Count(*))>=1));
Needless to say just looking at it I am sure there is a better way but I am not sure how, it also is not counting the encounters, the count I was doing is off the person_nbr
thank you
__._,_.___
Posted by: pattykf@cox.net
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (2) |
.
__,_._,___
Tidak ada komentar:
Posting Komentar