ACCESS FEED: Re: [MS_AccessPros] OK one more for today

Selasa, 31 Maret 2015

Re: [MS_AccessPros] OK one more for today

OH that makes total since, obviously have not gotten to that in the book yet
Thank you again for your help
Patty

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

Patty-

Then you don't want a LEFT JOIN. What you want is all the records from your "main" query where the Person_ID is IN the set of persons who have had two encounters or more and also IN the set of persons who had encounters for PE.

SELECT *

FROM MainQuery

WHERE Person_ID IN (SELECT person_id from TwoEncountersQuery)

AND Person_ID IN (SELECT person_ID from EncountersPEQuery)

Got it?

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 Mar 31, 2015, at 8:44 PM, pattykf@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

I want the last encounter for the patients in this group, I know last is picky but I am getting gibberish in some of the fields when I try to do the last for the person nbr
this is the basic query:
SELECT wo_Master_IM3_filter_count.VisitCount, wo_master_IM3_filter_PE.rendering_provider, wo_Master_IM3_filter.enc_id, wo_Master_IM3_filter.modify_timestamp, wo_Master_IM3_filter.visit_type_expanded, wo_Master_IM3_filter.person_id
FROM (wo_Master_IM3_filter LEFT JOIN wo_Master_IM3_filter_count ON wo_Master_IM3_filter.person_id = wo_Master_IM3_filter_count.person_id) LEFT JOIN wo_master_IM3_filter_PE ON wo_Master_IM3_filter.person_id = wo_master_IM3_filter_PE.person_id
WHERE (((wo_Master_IM3_filter_count.VisitCount) Is Not Null)) OR (((wo_master_IM3_filter_PE.rendering_provider) Is Not Null))
ORDER BY wo_Master_IM3_filter_count.VisitCount, wo_master_IM3_filter_PE.rendering_provider;

I am trying to pull together those patients that had 2 or more visits or a PE in the time frame. So I wrote two queries one to find the 2 or more encounters and one to pull in the encounters for PEs. the Master IM filter are all the encounters for the period that both of the two queries pull from. So I thought I would be able to identify those that were in the count or those in the PE and get the population (it duplicates some of the encounters if it meets both criteria-so needless to say don't have the query down correctly), but then I thought if I asked for the last on the person ID I would be able to get a result of those that met both and it would remove any that fit both criteria and eliminate all the encounters that show up when they have multiple encounters (some people have 9 or more). Any help is greatly appreciated.
Patty