Using Criteria in Query

B

bridgemonkey

Should be simple, table with 5 columns that can contain #'s 0-9 or "N" (this
is the issue). Trying to create query with Critieria, actually using the
"Or" option to return the primary key for any record containing a # <=5.
This should be quick but it reads "N" as <=5 ... suggestions?

Thanks in advance
 
B

bridgemonkey

Thanks, that's what I thought ... for some reason that doesn't want to work,
it pulls all records. This should be so simple. this is the set up.
Table
Key Value 1 Value 2 Value 3 Value 4
1 N N N 4
2 6 6 7 N
3 N N N 6
4 5 8 4 N

Putting <>"N" And <="5" in the "or" criteria line for all "Values" a select
query should pull records 1 & 4???
 
B

bridgemonkey

by the way, "Values" are stored as text since "N" is the default value, not
sure if that makes a difference.
thanks again for any input.
 
J

John Spencer

You could use
<="5"
OR
Like "[0-5]"

Those should both work. Alphabetically, "N" is greater than "5". And the like
criteria returns any record where the value is 0,1,2,3,4, or 5. Your original
query should also work UNLESS there is a leading space (or other character) in
the field.

"Space 6" is less than "5" and "Space N" is not "N"

You might try
LIKE "*[0-5]*"
That should return records that have 0,1,2,3,4, or 5 anywhere in the value and
ignore any leading or trailing spaces or other invisible characters.

If you are linking to non-Access (JET) tables they can contain leading and
trailing characters that are not visible.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 
K

KARL DEWEY

You should not be using a spreadsheet in a database but this table structure --
Key Value Record
1 N 1
1 N 2
1 N 3
1 4 4
2 6 1
2 6 2
2 7 3
2 N 4

Use this query --
SELECT Key
FROM YourTable
WHERE Val([Value 1]) <=5 OR [Value 1] ="N" OR Val([Value 2]) <=5 OR [Value
2] ="N" OR Val([Value 3]) <=5 OR [Value 3] ="N" OR Val([Value 4]) <=5 OR
[Value 4] ="N";
 
B

bridgemonkey

Thanks John, but I must have something fundamentally wrong here. I'm eating
some serious humble pie over here. Pulling the identifying column value out
of a large table when any of 4 values for that record are 5 or less shouldn't
be that difficult. They all have to be in the "or" line of the select query?


I put the table and query in a blank db on ftp if interested in seeing the
details ... otherwise thanks again for the effort.
ftp://s1107102645:[email protected]

John Spencer said:
You could use
<="5"
OR
Like "[0-5]"

Those should both work. Alphabetically, "N" is greater than "5". And the like
criteria returns any record where the value is 0,1,2,3,4, or 5. Your original
query should also work UNLESS there is a leading space (or other character) in
the field.

"Space 6" is less than "5" and "Space N" is not "N"

You might try
LIKE "*[0-5]*"
That should return records that have 0,1,2,3,4, or 5 anywhere in the value and
ignore any leading or trailing spaces or other invisible characters.

If you are linking to non-Access (JET) tables they can contain leading and
trailing characters that are not visible.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
Thanks, that's what I thought ... for some reason that doesn't want to work,
it pulls all records. This should be so simple. this is the set up.
Table
Key Value 1 Value 2 Value 3 Value 4
1 N N N 4
2 6 6 7 N
3 N N N 6
4 5 8 4 N

Putting <>"N" And <="5" in the "or" criteria line for all "Values" a select
query should pull records 1 & 4???
Click to expand...
Click to expand...
 
J

John Spencer

HERE is the SQL that you should be using. Enter this in the SQL view window
and switch back to the Design window.

SELECT Inspection_Data.SFN, Inspection_Data.Date
, Inspection_Data.[NBI 58 (Deck)]
, Inspection_Data.[NBI 59 (Superstructure)]
, Inspection_Data.[NBI 60 (Substructure)]
, Inspection_Data.[NBI 62 (Culvert)]
FROM Inspection_Data
WHERE (((Inspection_Data.Date) Between #1/1/2008# And #12/31/2008#) AND
((Inspection_Data.[NBI 58 (Deck)])<="5"))
OR (((Inspection_Data.Date) Between #1/1/2008# And #12/31/2008#) AND
((Inspection_Data.[NBI 59 (Superstructure)])<="5"))
OR (((Inspection_Data.Date) Between #1/1/2008# And #12/31/2008#) AND
((Inspection_Data.[NBI 60 (Substructure)])<="5"))
OR (((Inspection_Data.Date) Between #1/1/2008# And #12/31/2008#) AND
((Inspection_Data.[NBI 62 (Culvert)])<="5"))
ORDER BY Inspection_Data.SFN;


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 
B

bridgemonkey

Thanks for taking a look at that for me. Never used the "or" statement like
that but now I know.

Thanks again!

John Spencer said:
HERE is the SQL that you should be using. Enter this in the SQL view window
and switch back to the Design window.

SELECT Inspection_Data.SFN, Inspection_Data.Date
, Inspection_Data.[NBI 58 (Deck)]
, Inspection_Data.[NBI 59 (Superstructure)]
, Inspection_Data.[NBI 60 (Substructure)]
, Inspection_Data.[NBI 62 (Culvert)]
FROM Inspection_Data
WHERE (((Inspection_Data.Date) Between #1/1/2008# And #12/31/2008#) AND
((Inspection_Data.[NBI 58 (Deck)])<="5"))
OR (((Inspection_Data.Date) Between #1/1/2008# And #12/31/2008#) AND
((Inspection_Data.[NBI 59 (Superstructure)])<="5"))
OR (((Inspection_Data.Date) Between #1/1/2008# And #12/31/2008#) AND
((Inspection_Data.[NBI 60 (Substructure)])<="5"))
OR (((Inspection_Data.Date) Between #1/1/2008# And #12/31/2008#) AND
((Inspection_Data.[NBI 62 (Culvert)])<="5"))
ORDER BY Inspection_Data.SFN;


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
Thanks John, but I must have something fundamentally wrong here. I'm eating
some serious humble pie over here. Pulling the identifying column value out
of a large table when any of 4 values for that record are 5 or less shouldn't
be that difficult. They all have to be in the "or" line of the select query?


I put the table and query in a blank db on ftp if interested in seeing the
details ... otherwise thanks again for the effort.
ftp://s1107102645:[email protected]
Click to expand...
Click to expand...
 
Top