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
(Paris, France)
On Feb 10, 2015, at 8:19 PM, pattykf@cox.net [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 :
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: John Viescas <johnv@msn.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (3) |
.
__,_._,___
Tidak ada komentar:
Posting Komentar