ACCESS FEED: Re: [MS_AccessPros] Re: Identify patients that are not those with an identified enc number

Selasa, 31 Maret 2015

Re: [MS_AccessPros] Re: Identify patients that are not those with an identified enc number

Ah, much better, John. Thanks for correcting me. That should be much faster.

-Bill

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

Patty-

Access is notoriously horrible at solving NOT IN predicates. Try this:

SELECT wo_Master_IM3_filter.person_id

, wo_Master_IM3_filter.enc_id

, wo_Master_IM3_filter.modify_timestamp

, wo_Master_IM3_filter.visit_type_expanded

, wo_Master_IM3_filter.rendering_provider

FROM wo_Master_IM3_filter LEFT JOIN [wo_master_IM3_shot only encounters]

ON wo_Master_IM3_filter.enc_ID = [wo_master_IM3_shot only encounters].enc_id

WHERE [wo_master_IM3_shot only encounters].enc_id IS NULL;

That will give you all the records in wo_Master_IM3_filter that are not in the other recordset.

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 7:34 PM, pattykf@cox.net [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Thank you,
I gave it a shot, it ran but it keeps hanging when I try to determine the number of patients it returns
this is how I have it in my system, did I forget to delete a space or something? it should be returning 35475 rows of data. the larger file (wo_master_IM3_filter) works fine and I can see the 36511 patients and the count at the bottom.
thank you so much for your help

SELECT wo_Master_IM3_filter.person_id, wo_Master_IM3_filter.enc_id, wo_Master_IM3_filter.modify_timestamp, wo_Master_IM3_filter.visit_type_expanded, wo_Master_IM3_filter.rendering_provider
FROM wo_Master_IM3_filter
WHERE wo_Master_IM3_filter.enc_id NOT IN (SELECT [wo_master_IM3_shot only encounters].enc_id FROM [wo_master_IM3_shot only encounters]);

Patty

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

Patty

I think this is what you are looking for.

SELECT wo_Master_IM3_filter.person_id
, wo_Master_IM3_filter.enc_id
, wo_Master_IM3_filter.modify_timestamp
, wo_Master_IM3_filter.visit_type_expanded
, wo_Master_IM3_filter.rendering_provider
FROM wo_Master_IM3_filter
WHERE wo_Master_IM3_filter.enc_id NOT IN
(SELECT [wo_master_IM3_shot only encounters].enc_id
FROM [wo_master_IM3_shot only encounters]
)

Regards,
Bill Mosca, Founder - MS_Access_Professionals
http://www.thatlldoit.com
Microsoft Office Access MVP
http://mvp.microsoft.com/en-us/mvp/Bill%20Mosca-35852
My nothing-to-do-with-Access blog
http://wrmosca.wordpress.com

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

I have this query, but what i want is the opposite, I want those without the same enc number
SELECT wo_Master_IM3_filter.person_id, wo_Master_IM3_filter.enc_id, wo_Master_IM3_filter.modify_timestamp, wo_Master_IM3_filter.visit_type_expanded, wo_Master_IM3_filter.rendering_provider
FROM wo_Master_IM3_filter LEFT JOIN [wo_master_IM3_shot only encounters] ON wo_Master_IM3_filter.enc_id = [wo_master_IM3_shot only encounters].enc_id;

I tried to use the <> and != and both resulted in incorrect info or error message. I am sure there is a way to do it. I just have a block right now
thank you
Patty