WHERE to query for multiple criteria

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm trying to do a Group By query and selecting WHERE in the total row to
find Providers that have the Network Codes ("PPO" and "NOWC" is null) or
"WRKCMP" as well as also excluding any providers that have "DO", "DUP" or
"PEND".

Examples of combinations I want to find

Providers that have PPO but don't have NOWC
(and do not have DO, DUP, or PEND)

Providers that have WRKCMP with or without also having NOWC
(and do not have DO, DUP, or PEND)

Any help would be greatly appreciated :)
 
You seems to have omitted some information.
You talk about two fields - Providers and Network Codes
I think that PPO, DO, DUP or PEND are Network Codes.
What are NOWC and WRKCMP?
 
Susanne,

In a "Group By query", or Totals Query, you are normally using some sort
of aggregate such as Sum or Count or Max or whatever, so I am not clear
on what you are trying to achieve here. Can you tell us what the table
is, and a bit more about the query you are trying to work with here?
Since you are looking for records where certain values are *not*
included, you will need to have a table listing all possible Network
Codes, which can be joined into this query. Do you have such a table
already?
 
Replying to Karl & Steve:

All of the codes are Network Codes. Each provider has at least one Network
Code. Some have as many as a dozen. What I figured out to do was to do two
queries. In the first query I used WHERE and put "WRKCMP" in Criteria and
then put Like "PPO" and not like "NOWC" in the Or Criteria. I needed to find
providers that have either ["WRKCMP"] or ["PPO" but don't have "NOWC"]. Then
I created a second query on the first query using WHERE again and putting Not
like "DO" and not like "PEND" and not like "DUP" in Criteria.

I felt that a Group By query was necessary for what I needed. I want to pull
each unique Provider only once as not get duplicates caused by them having
multiple Network Codes that I was looking for. Some could have more than one
"PPO" Network Code while others can have both a "PPO" and a "WRKCMP".

Thank you both for answering. This is truly a wonderful website that helps
me out a lot!
 
Susanne,

If I understand you correctly, there was probably no need to do this via
a query based on a query. You can have multiple criteria in the one
query, so in the query design grid, you can just keep adding one below
the other, so each in interpreted as another "OR".

A "Group By query" (this is called a Totals Query) is not apprlicable in
this instance. If you need to handle duplicated records returned in the
query data set, you can use the DISTINCT keyword in the SQL. To do this
from the design view of the query, right-click anywhere on the
background of the upper panel of the query design window, select
'Properties' from the pop-up menu, and then set the Unique Values
property to Yes.

The way you expressed "Not like 'DO' and not like 'PEND' and not like
'DUP'", makes me thing you entered the criteria using the Like keyword.
This is not really appropriate (though will probably work ok). Like
is used when you want to use a wildcard ("*" or "?") in the criteria.
Probably this criteria should have been written like this...
<>"DO" And <>"PEND" And <>"DUP"
or...
Not In("DO","PEND","DUP")

Best wishes for the rest of your project.
 
I never knew that I could set unique values using Properties! Thanks for the
tip!
I'll be doing this from now on. I'm still having trouble getting my query to
work as I need it to.

Physicians can have one or more Network codes. I need to find those that
have "PPO" but don't have "NOWC" as well as ones that have "WRKCMP"
regardless of whether or not they also have "NOWC"..

Physician A Physician B Physician C Physician D
PPO PPO WKRCMP WKRCMP
NOWC XYZ XYZ NOWC
(don't need) (need) (need) (need)


How do I get there and how exactly do I combine the <> "DO" and <> "PEND"
and <> "DUP" or Not In ("DO", "PEND", "DUP") all into a single query?
Appreciate the help!

"PPO" and <> "NOWC" and Not In ("DO, "PEND", "DUP")
or "WKRCMP" and Not In ("DO", "PEND", "DUP")


Steve Schapel said:
Susanne,

If I understand you correctly, there was probably no need to do this via
a query based on a query. You can have multiple criteria in the one
query, so in the query design grid, you can just keep adding one below
the other, so each in interpreted as another "OR".

A "Group By query" (this is called a Totals Query) is not apprlicable in
this instance. If you need to handle duplicated records returned in the
query data set, you can use the DISTINCT keyword in the SQL. To do this
from the design view of the query, right-click anywhere on the
background of the upper panel of the query design window, select
'Properties' from the pop-up menu, and then set the Unique Values
property to Yes.

The way you expressed "Not like 'DO' and not like 'PEND' and not like
'DUP'", makes me thing you entered the criteria using the Like keyword.
This is not really appropriate (though will probably work ok). Like
is used when you want to use a wildcard ("*" or "?") in the criteria.
Probably this criteria should have been written like this...
<>"DO" And <>"PEND" And <>"DUP"
or...
Not In("DO","PEND","DUP")

Best wishes for the rest of your project.

--
Steve Schapel, Microsoft Access MVP
Replying to Karl & Steve:

All of the codes are Network Codes. Each provider has at least one Network
Code. Some have as many as a dozen. What I figured out to do was to do two
queries. In the first query I used WHERE and put "WRKCMP" in Criteria and
then put Like "PPO" and not like "NOWC" in the Or Criteria. I needed to find
providers that have either ["WRKCMP"] or ["PPO" but don't have "NOWC"]. Then
I created a second query on the first query using WHERE again and putting Not
like "DO" and not like "PEND" and not like "DUP" in Criteria.

I felt that a Group By query was necessary for what I needed. I want to pull
each unique Provider only once as not get duplicates caused by them having
multiple Network Codes that I was looking for. Some could have more than one
"PPO" Network Code while others can have both a "PPO" and a "WRKCMP".

Thank you both for answering. This is truly a wonderful website that helps
me out a lot!
Click to expand...
Click to expand...
 
Susanne,

Thanks for the further explanation. I am beginning to see a bit more
clearly what you are trying to do here, although without knowing more
details of your table stucture it is not really possible to advise
specifically.

But do I understand correctly?... The object here is to return a list
of physicians that meet the criteria? And, for example, if they have a
code of "DO", they should be excluded, no matter what other codes thay
have? Same with "PEND"? etc. Well, it seems to me that you won't
really be able to do this with a Select Query, via applying criteria to
the Network Codes field (or whatever it's called). These criteria are
applied at a record by record level, so don't take account of what is in
other records. You could do it using a subquery, but it would be a bit
complicated I think. I would be inclined, in such a situation, to write
a user-defined function instead. Something like this...

Public Function IncludePhysician(InputID As Long) As Boolean
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("SELECT NetworkCode FROM
YourTable WHERE PhysicianID=" & InputID)
With rst
.FindFirst "NetworkCode='DO' Or NetworkCode='PEND' Or
NetworkCode='DUP'"
If .NoMatch Then
.FindFirst "NetworkCode='WKRCMP'"
If .NoMatch Then
.FindFirst "NetworkCode='PPO'"
If .NoMatch
IncludePhysician = False
Else
.FindFirst "NetworkCode='NOWC'"
If .NoMatch Then
IncludePhysician = True
Else
IncludePhysician = False
End If
End if
Else
IncludePhysician = True
End if
Else
IncludePhysician = False
End If
.Close
End With
Set rst = Nothing
End Function

And then, your query can be something like this...
SELECT Physician FROM Physicians WHERE IncludePhysician(PhysicianID)=True

Well, I'm only guessing, really, as I have no idea what you've got
there, so I hope this concept makes some sense to you.
 
Back
Top