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

Rabu, 01 April 2015

Re: [MS_AccessPros] OK one more for today

Display all patients that are on the wo_Master_IM3_filter_PE and all patients on the
wo_Master_IM3_filter_count but display them only once per person.id

thank you, sorry for the confusion
Patty

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

Patty-

So, you want ALL the patients and then count just certain ones? You also mentioned something about wanting the "last" one. Please restate your problem in words, and I'll try to help. Something like:

"Display all patients and …."

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 Apr 1, 2015, at 2:25 AM, pattykf@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

thank you, got it, But there is a problem it is not pulling in all the patients. I sorted my PE list so person ID low to high and verified against this query and the first person was not in this report,
and I still have patients that have more than one encounter in the time period and I only need to know who they are once. I have tried count and last but not having luck getting it to give me them only once. (yes need to fix this one first then figure out the unique pt listing)

SELECT wo_Master_IM3_filter.person_id, wo_Master_IM3_filter.modify_timestamp, wo_Master_IM3_filter.enc_id, wo_Master_IM3_filter.visit_type_expanded, wo_Master_IM3_filter.rendering_provider
FROM wo_Master_IM3_filter
WHERE (((person_id) IN (SELECT wo_Master_IM3_filter_count.person_id from [wo_Master_IM3_filter_count]) And person_id IN (SELECT wo_Master_IM3_filter_PE.Person_ID from wo_Master_IM3_filter_PE)));

I was able to get that patient to come in using this query:

SELECT wo_Master_IM3_filter.person_id, wo_Master_IM3_filter.enc_id, wo_Master_IM3_filter.modify_timestamp, wo_Master_IM3_filter_count.VisitCount, wo_Master_IM3_filter.rendering_provider, wo_Master_IM3_filter.visit_type_expanded, wo_master_IM3_filter_PE.CountOfperson_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.CountOfperson_id) Is Not Null));

the first query resulted in 20542 records and the second one came back with 29378 records
they both have duplicate patient id numbers in them.

sorry to be a bother but this is the most important query/report I need. this is the population (after I take away the inactive patients) that all my reports should work off of.
thank you for any help you can offer
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