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
(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 :
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
---In MS_Access_Professionals@yahoogroups.com, <pattykf@...> wrote :
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
Microsoft Office Access MVP
My nothing-to-do-with-Access blog
---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
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
__._,_.___
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 (4) |
.
__,_._,___
Tidak ada komentar:
Posting Komentar