Selasa, 31 Maret 2015

Re: [MS_AccessPros] OK one more for today

 

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

__._,_.___

Posted by: pattykf@cox.net
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (4)

.

__,_._,___

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

__._,_.___

Posted by: pattykf@cox.net
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (3)

.

__,_._,___

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)

.

__,_._,___

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

 

Bill-


Yeah, I avoid NOT IN (correlated subquery) like the plague in Access.  If the obvious solution is NOT IN (correlated subquery), then see if you can do it with an OUTER JOIN using the WHERE clause in the subquery.  I think SQL Server performs this optimization automatically, but Access never has.

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 10:17 PM, wrmosca@comcast.net [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

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


__._,_.___

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

.

__,_._,___

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

__._,_.___

Posted by: wrmosca@comcast.net
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (6)

.

__,_._,___

[MS_AccessPros] OK one more for today

 

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: pattykf@cox.net
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (1)

.

__,_._,___

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

 

John,
excellent, it worked perfectly,
thank you again for coming to my rescue. 
Patty



---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> 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 
(Paris, France)




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

__._,_.___

Posted by: pattykf@cox.net
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (5)

.

__,_._,___

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

 

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 :

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
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
 

__._,_.___

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)

.

__,_._,___