Re: [MS_AccessPros] Re: help with query counting encounters

 

there is another error that I cannot figure out, it is showing this error: and it goes to the first IN



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
FROM [Enc exclude vaccine PPD]
WHERE ([Enc exclude vaccine PPD].person_nbr) IN
SELECT ([Enc exclude vaccine PPD].person_nbr FROM  visits WHERE [Enc exclude vaccine PPD].visit_type="Preventive Medicine")
OR  ([Enc exclude vaccine PPD].person_nbr) IN
(SELECT  [Enc exclude vaccine PPD].person_nbr FROM visits WHERE [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 NOT I>
GROUP BY  [Enc exclude vaccine PPD].person_nbr
HAVING COUNT(*)>=2)

Tried a couple of things but can't resolve it, not sure what is wrong. Sorry really tried to do it myself
Patty

---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

Patty-

You're missing a space before the first IN. 

Instead of:

WHERE ([Enc exclude vaccine PPD].person_nbr)IN

.. it should read:

WHERE ([Enc exclude vaccine PPD].person_nbr) IN

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications 

SQL Queries for Mere Mortals 

http://www.viescas.com/ 

(Paris, France)

On Feb 11, 2015, at 2:20 AM, pattykf@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Thank you John
can you please check this for me: I am getting an error after the first IN I do not fully understand the formating of this one so I can not figure out how to fix it.

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
FROM [Enc exclude vaccine PPD]
WHERE ([Enc exclude vaccine PPD].person_nbr)IN
SELECT ([Enc exclude vaccine PPD].person_nbr FROM  visits WHERE [Enc exclude vaccine PPD].visit_type="Preventive Medicine")
OR  [Enc exclude vaccine PPD].person_nbr IN
(SELECT  [Enc exclude vaccine PPD].person_nbr FROM visits WHERE [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 NOT I>
GROUP BY  [Enc exclude vaccine PPD].person_nbr
HAVING COUNT(*)>=2)

thank you very much
the IN thing is something I am not understanding as of yet.
thankx
Patty

---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

Patty-

Using a Totals query is all wrong.  Because you have included the timestamp field in the GROUP BY, all you will get is individual records.

What you need is something like:

SELECT <some fields>

FROM patients

WHERE patientID IN

(SELECT patient ID FROM visits WHERE VisitType = "Preventive Medicine")

OR patientID IN

(SELECT patientID FROM visits WHERE <exclude all the ones not of interest using NOT IN>

GROUP BY patientID

HAVING Count(*) >=2)

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications 

SQL Queries for Mere Mortals 

http://www.viescas.com/ 

(Paris, France)

On Feb 10, 2015, at 8:19 PM, pattykf@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

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

__._,_.___

---

Posted by: pattykf@cox.net

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (7)

Visit Your Group

• New Members 2

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___