Help with query criteria

  • Thread starter Thread starter Wendy L
  • Start date Start date
W

Wendy L

Windows XP Office XP

I have 5 fields of data called "Prdtyp", "Prdty1", "Prdty2", "Prdty3",
"Prdty4" (no parentheses). In each field, data is entered in two-character
text strings. The data which might be contained within these fields are
"OP", "OW", "OO" or "OT" or no data at all.

Here is my question. If I want my query results to return all data entries
in which the data is "OW" or "OO" exists in ANY of the 5 fields of data.
For example, the "Prdtyp" field may have the data "OP" but the "Prdty1"
field may contain "OO", in this case i want this to be returned as a result.
The way i was setting it up with the criteria in each field set as "OO" or
"OW". How do i set up the criteria so that it doesn't rule out data?
 
Wendy

Here is a relatively simple solution that might be easy for you to follow
along.


Create a new query that concatentates the other fields, call it Query1
Select Prdtyp & Prdty1 & Prdty2 & Prdty3 &Prdty4 as [AllFields]
From <table>

Create another query (for the sake of it, Query2) that calls the first
Query.
Select AllFields
From Query1
Where AllFields Like "*OW*"

HTH


--
Rob

FMS Professional Solutions Group
http://www.fmsinc.com/consulting

Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
 
I'm sorry to be a pest Rob, but how does one concatenate the other fields?

RobFMS said:
Wendy

Here is a relatively simple solution that might be easy for you to follow
along.


Create a new query that concatentates the other fields, call it Query1
Select Prdtyp & Prdty1 & Prdty2 & Prdty3 &Prdty4 as [AllFields]
From <table>

Create another query (for the sake of it, Query2) that calls the first
Query.
Select AllFields
From Query1
Where AllFields Like "*OW*"

HTH


--
Rob

FMS Professional Solutions Group
http://www.fmsinc.com/consulting

Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Wendy L said:
Windows XP Office XP

I have 5 fields of data called "Prdtyp", "Prdty1", "Prdty2", "Prdty3",
"Prdty4" (no parentheses). In each field, data is entered in two-character
text strings. The data which might be contained within these fields are
"OP", "OW", "OO" or "OT" or no data at all.

Here is my question. If I want my query results to return all data entries
in which the data is "OW" or "OO" exists in ANY of the 5 fields of data.
For example, the "Prdtyp" field may have the data "OP" but the "Prdty1"
field may contain "OO", in this case i want this to be returned as a result.
The way i was setting it up with the criteria in each field set as "OO" or
"OW". How do i set up the criteria so that it doesn't rule out data?
Click to expand...
Click to expand...
 
Wendy, you can do it this way:

Create your query, which will include all five "Prodty.." fields.
Your criteria for each of these fields will be

IN("OW","OO")

BUT YOU MUST ENTER THE CRITERIA ON FIVE SEPARATE ROWS. In other words,
you'll enter the criteria in the "Criteria" row under the first "Prodty.."
field, in the "Or" row under the second, in the next row down under the
third, and so on.

That should return any record in which either OW or OO is entered in any
field.

DDM
"DDM's Microsoft Office Tips and Tricks"
http://ddmara.tripod.com
 
How do you concatenate a field? I've read the help file, but it doesn't
seem clear to me.


RobFMS said:
Wendy

Here is a relatively simple solution that might be easy for you to follow
along.


Create a new query that concatentates the other fields, call it Query1
Select Prdtyp & Prdty1 & Prdty2 & Prdty3 &Prdty4 as [AllFields]
From <table>

Create another query (for the sake of it, Query2) that calls the first
Query.
Select AllFields
From Query1
Where AllFields Like "*OW*"

HTH


--
Rob

FMS Professional Solutions Group
http://www.fmsinc.com/consulting

Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Wendy L said:
Windows XP Office XP

I have 5 fields of data called "Prdtyp", "Prdty1", "Prdty2", "Prdty3",
"Prdty4" (no parentheses). In each field, data is entered in two-character
text strings. The data which might be contained within these fields are
"OP", "OW", "OO" or "OT" or no data at all.

Here is my question. If I want my query results to return all data entries
in which the data is "OW" or "OO" exists in ANY of the 5 fields of data.
For example, the "Prdtyp" field may have the data "OP" but the "Prdty1"
field may contain "OO", in this case i want this to be returned as a result.
The way i was setting it up with the criteria in each field set as "OO" or
"OW". How do i set up the criteria so that it doesn't rule out data?
Click to expand...
Click to expand...
 
Put the query in the way it is with the exception of changing "<table>" to
the name of the table where the 5 fields below exist. The part after
"Select" is the all the fields put together.


Select Prdtyp & Prdty1 & Prdty2 & Prdty3 &Prdty4 as [AllFields]
From <table>


--
Rob

FMS Professional Solutions Group
http://www.fmsinc.com/consulting

Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Wendy L said:
How do you concatenate a field? I've read the help file, but it doesn't
seem clear to me.


RobFMS said:
Wendy

Here is a relatively simple solution that might be easy for you to follow
along.


Create a new query that concatentates the other fields, call it Query1
Select Prdtyp & Prdty1 & Prdty2 & Prdty3 &Prdty4 as [AllFields]
From <table>

Create another query (for the sake of it, Query2) that calls the first
Query.
Select AllFields
From Query1
Where AllFields Like "*OW*"

HTH


--
Rob

FMS Professional Solutions Group
http://www.fmsinc.com/consulting

Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Wendy L said:
Windows XP Office XP

I have 5 fields of data called "Prdtyp", "Prdty1", "Prdty2", "Prdty3",
"Prdty4" (no parentheses). In each field, data is entered in two-character
text strings. The data which might be contained within these fields are
"OP", "OW", "OO" or "OT" or no data at all.

Here is my question. If I want my query results to return all data entries
in which the data is "OW" or "OO" exists in ANY of the 5 fields of data.
For example, the "Prdtyp" field may have the data "OP" but the "Prdty1"
field may contain "OO", in this case i want this to be returned as a result.
The way i was setting it up with the criteria in each field set as
Click to expand...
Click to expand...
"OO"
or
Click to expand...
Click to expand...
 
Thank you Rob, I'm sorry to be such a newbie, but if i'm in design view,
where would I insert that expression?


RobFMS said:
Put the query in the way it is with the exception of changing "<table>" to
the name of the table where the 5 fields below exist. The part after
"Select" is the all the fields put together.


Select Prdtyp & Prdty1 & Prdty2 & Prdty3 &Prdty4 as [AllFields]
From <table>


--
Rob

FMS Professional Solutions Group
http://www.fmsinc.com/consulting

Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Wendy L said:
How do you concatenate a field? I've read the help file, but it doesn't
seem clear to me.


RobFMS said:
Wendy

Here is a relatively simple solution that might be easy for you to follow
along.


Create a new query that concatentates the other fields, call it Query1
Select Prdtyp & Prdty1 & Prdty2 & Prdty3 &Prdty4 as [AllFields]
From <table>

Create another query (for the sake of it, Query2) that calls the first
Query.
Select AllFields
From Query1
Where AllFields Like "*OW*"

HTH


--
Rob

FMS Professional Solutions Group
http://www.fmsinc.com/consulting

Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Windows XP Office XP

I have 5 fields of data called "Prdtyp", "Prdty1", "Prdty2", "Prdty3",
"Prdty4" (no parentheses). In each field, data is entered in two-character
text strings. The data which might be contained within these fields are
"OP", "OW", "OO" or "OT" or no data at all.

Here is my question. If I want my query results to return all data entries
in which the data is "OW" or "OO" exists in ANY of the 5 fields of data.
For example, the "Prdtyp" field may have the data "OP" but the "Prdty1"
field may contain "OO", in this case i want this to be returned as a
result.
The way i was setting it up with the criteria in each field set as
Click to expand...
"OO"
or
"OW". How do i set up the criteria so that it doesn't rule out data?
Click to expand...
Click to expand...
Click to expand...
 
Nevermind Rob, I muddled my way through it and figured it out. Thank you so
much for taking time to respond.


RobFMS said:
Put the query in the way it is with the exception of changing "<table>" to
the name of the table where the 5 fields below exist. The part after
"Select" is the all the fields put together.


Select Prdtyp & Prdty1 & Prdty2 & Prdty3 &Prdty4 as [AllFields]
From <table>


--
Rob

FMS Professional Solutions Group
http://www.fmsinc.com/consulting

Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Wendy L said:
How do you concatenate a field? I've read the help file, but it doesn't
seem clear to me.


RobFMS said:
Wendy

Here is a relatively simple solution that might be easy for you to follow
along.


Create a new query that concatentates the other fields, call it Query1
Select Prdtyp & Prdty1 & Prdty2 & Prdty3 &Prdty4 as [AllFields]
From <table>

Create another query (for the sake of it, Query2) that calls the first
Query.
Select AllFields
From Query1
Where AllFields Like "*OW*"

HTH


--
Rob

FMS Professional Solutions Group
http://www.fmsinc.com/consulting

Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Windows XP Office XP

I have 5 fields of data called "Prdtyp", "Prdty1", "Prdty2", "Prdty3",
"Prdty4" (no parentheses). In each field, data is entered in two-character
text strings. The data which might be contained within these fields are
"OP", "OW", "OO" or "OT" or no data at all.

Here is my question. If I want my query results to return all data entries
in which the data is "OW" or "OO" exists in ANY of the 5 fields of data.
For example, the "Prdtyp" field may have the data "OP" but the "Prdty1"
field may contain "OO", in this case i want this to be returned as a
result.
The way i was setting it up with the criteria in each field set as
Click to expand...
"OO"
or
"OW". How do i set up the criteria so that it doesn't rule out data?
Click to expand...
Click to expand...
Click to expand...
 
Glad you were able to work through it!


--
Rob

FMS Professional Solutions Group
http://www.fmsinc.com/consulting

Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Wendy L said:
Nevermind Rob, I muddled my way through it and figured it out. Thank you so
much for taking time to respond.


RobFMS said:
Put the query in the way it is with the exception of changing "<table>" to
the name of the table where the 5 fields below exist. The part after
"Select" is the all the fields put together.


Select Prdtyp & Prdty1 & Prdty2 & Prdty3 &Prdty4 as [AllFields]
From <table>


--
Rob

FMS Professional Solutions Group
http://www.fmsinc.com/consulting

Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Wendy L said:
How do you concatenate a field? I've read the help file, but it doesn't
seem clear to me.


Wendy

Here is a relatively simple solution that might be easy for you to follow
along.


Create a new query that concatentates the other fields, call it Query1
Select Prdtyp & Prdty1 & Prdty2 & Prdty3 &Prdty4 as [AllFields]
From <table>

Create another query (for the sake of it, Query2) that calls the first
Query.
Select AllFields
From Query1
Where AllFields Like "*OW*"

HTH


--
Rob

FMS Professional Solutions Group
http://www.fmsinc.com/consulting

Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Windows XP Office XP

I have 5 fields of data called "Prdtyp", "Prdty1", "Prdty2", "Prdty3",
"Prdty4" (no parentheses). In each field, data is entered in
two-character
text strings. The data which might be contained within these
Click to expand...
fields
are
"OP", "OW", "OO" or "OT" or no data at all.

Here is my question. If I want my query results to return all data
entries
in which the data is "OW" or "OO" exists in ANY of the 5 fields of
data.
For example, the "Prdtyp" field may have the data "OP" but the "Prdty1"
field may contain "OO", in this case i want this to be returned as a
result.
The way i was setting it up with the criteria in each field set as "OO"
or
"OW". How do i set up the criteria so that it doesn't rule out data?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top