[MS_AccessPros] help with query counting encounters

 

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

__._,_.___

---

Posted by: pattykf@cox.net

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (1)

Visit Your Group

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___