Rabu, 11 Februari 2015

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

 

Patty-


Oh, didn't spot that one.  The subquery needs to be in parens.  Your second subquery is also hosed.  I tried to clean it up using the example from one of your earlier attempts.  Is there a query named "visits"?  I don't think so.  I assume that [Enc exclude vaccine PPD] also has all the "visit" data from your previous attempts.  Your outer query should really be using the main "persons" table.

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 person_nbr FROM  [Enc exclude vaccine PPD] As Visits WHERE Visits.visit_type="Preventive Medicine") 
OR  ([Enc exclude vaccine PPD].person_nbr) IN
(SELECT  person_nbr FROM [Enc exclude vaccine PPD] As Visits2 WHERE 
((Visits2.assessment1) Not Like "Need for*") And ((Visits2.assessment1) <> "influenza vaccine") And ((Visits2.assessment1) <> "Needs flu shot") And ((Visits2.assessment1) NOT LIKE "*VACCINATION*") And ((Visits2.assessment1) <> "Influenza vaccine needed") And ((Visits2.assessment1) <> "Encounter for PPD skin test reading") And ((Visits2.assessment1) <> "Encounter for PPD test") And ((Visits2.assessment1) <> "Screening for tuberculosis") And ((Visits2.assessment1) <> "Screening-pulmonary TB") And ((Visits2.assessment1) <> "Vaccine for viral hepatitis") And ((Visits2.assessment1) <> "Visit for TB skin test")
GROUP BY  Visits2.person_nbr
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 
(Paris, France)




On Feb 11, 2015, at 7:00 PM, pattykf@cox.net [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

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: John Viescas <johnv@msn.com>
Reply via web post • Reply to sender • Reply to group • Start a New Topic • Messages in this topic (8)

.

__,_._,___
Diposting oleh di

Tidak ada komentar:

Posting Komentar

Langganan: Posting Komentar (Atom)