Selasa, 31 Maret 2015

Re: [MS_AccessPros] OK one more for today

 

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 
(Paris, France)




On Mar 31, 2015, at 8:44 PM, pattykf@cox.net [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

__._,_.___

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 (2)

.

__,_._,___

Tidak ada komentar:

Posting Komentar