Problems with a "Not" query

E

ellezeebub

Hi--I'm new to the group and pretty new to Access. I'm trying to get
a grip though, but this problem is making me crazy.

My problem, in a nutshell, is that I return a query in which all the
occurrences of a certain field containing RB or FB come back to me
(for rinse blank and field blank). But, I cannot write a query that
*excludes* those records.

Here is a SQL view of the query that works:

SELECT Station_location.STATIONNAME, Station_location.DATESAMPLED,
Station_location.Time, Station_samples.[Total metals],
TblTCUavg.AvgOfResult
FROM (Station_location INNER JOIN Station_samples ON
Station_location.STATIONCODE = Station_samples.STATIONCODE) INNER JOIN
TblTCUavg ON Station_samples.[Total metals] = TblTCUavg.SampleID
WHERE (((Station_location.STATIONNAME) Like "* rb*") AND
((TblTCUavg.ResultQualCode) Is Null)) OR
(((Station_location.STATIONNAME) Like "* fb*") AND
((TblTCUavg.ResultQualCode) Is Null))
ORDER BY Station_location.DATESAMPLED, Station_location.Time;

So, it would seem reasonable to me that all I would have to do to
exclude those values would be to insert a "not like" instead of a
"like" and all my problems would be solved.

SQL view of query that does not exclude rinse blanks and field blanks:

SELECT Station_location.STATIONNAME, Station_location.DATESAMPLED,
Station_location.Time, Station_samples.[Total metals],
TblTCUavg.AvgOfResult
FROM (Station_location INNER JOIN Station_samples ON
Station_location.STATIONCODE = Station_samples.STATIONCODE) INNER JOIN
TblTCUavg ON Station_samples.[Total metals] = TblTCUavg.SampleID
WHERE (((Station_location.STATIONNAME) Not Like "* rb*") AND
((TblTCUavg.ResultQualCode) Is Null)) OR
(((Station_location.STATIONNAME) Not Like "* fb*") AND
((TblTCUavg.ResultQualCode) Is Null))
ORDER BY Station_location.DATESAMPLED, Station_location.Time;



But, when I change the query I still get the rinse blanks and field
blanks in my results. Can anyone tell me how I'm messing this up?
Arrg.

Thanks.
 
J

John Spencer

Try
SELECT Station_location.STATIONNAME, Station_location.DATESAMPLED,
Station_location.Time, Station_samples.[Total metals],
TblTCUavg.AvgOfResult
FROM (Station_location INNER JOIN Station_samples ON
Station_location.STATIONCODE = Station_samples.STATIONCODE) INNER JOIN
TblTCUavg ON Station_samples.[Total metals] = TblTCUavg.SampleID

WHERE NOT (((Station_location.STATIONNAME) Like "* rb*") AND
((TblTCUavg.ResultQualCode) Is Null)) OR
(((Station_location.STATIONNAME) Like "* fb*") AND
((TblTCUavg.ResultQualCode) Is Null)))

ORDER BY Station_location.DATESAMPLED, Station_location.Time;

Or try

SELECT Station_location.STATIONNAME, Station_location.DATESAMPLED,
Station_location.Time, Station_samples.[Total metals],
TblTCUavg.AvgOfResult
FROM (Station_location INNER JOIN Station_samples ON
Station_location.STATIONCODE = Station_samples.STATIONCODE) INNER JOIN
TblTCUavg ON Station_samples.[Total metals] = TblTCUavg.SampleID

WHERE (((Station_location.STATIONNAME) Not Like "* rb*") AND
((TblTCUavg.ResultQualCode) Is Null))

AND

(((Station_location.STATIONNAME) Not Like "* fb*") AND
((TblTCUavg.ResultQualCode) Is Null))

ORDER BY Station_location.DATESAMPLED, Station_location.Time;
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hi--I'm new to the group and pretty new to Access. I'm trying to get
a grip though, but this problem is making me crazy.

My problem, in a nutshell, is that I return a query in which all the
occurrences of a certain field containing RB or FB come back to me
(for rinse blank and field blank). But, I cannot write a query that
*excludes* those records.

Here is a SQL view of the query that works:

SELECT Station_location.STATIONNAME, Station_location.DATESAMPLED,
Station_location.Time, Station_samples.[Total metals],
TblTCUavg.AvgOfResult
FROM (Station_location INNER JOIN Station_samples ON
Station_location.STATIONCODE = Station_samples.STATIONCODE) INNER JOIN
TblTCUavg ON Station_samples.[Total metals] = TblTCUavg.SampleID
WHERE (((Station_location.STATIONNAME) Like "* rb*") AND
((TblTCUavg.ResultQualCode) Is Null)) OR
(((Station_location.STATIONNAME) Like "* fb*") AND
((TblTCUavg.ResultQualCode) Is Null))
ORDER BY Station_location.DATESAMPLED, Station_location.Time;

So, it would seem reasonable to me that all I would have to do to
exclude those values would be to insert a "not like" instead of a
"like" and all my problems would be solved.

SQL view of query that does not exclude rinse blanks and field blanks:

SELECT Station_location.STATIONNAME, Station_location.DATESAMPLED,
Station_location.Time, Station_samples.[Total metals],
TblTCUavg.AvgOfResult
FROM (Station_location INNER JOIN Station_samples ON
Station_location.STATIONCODE = Station_samples.STATIONCODE) INNER JOIN
TblTCUavg ON Station_samples.[Total metals] = TblTCUavg.SampleID
WHERE (((Station_location.STATIONNAME) Not Like "* rb*") AND
((TblTCUavg.ResultQualCode) Is Null)) OR
(((Station_location.STATIONNAME) Not Like "* fb*") AND
((TblTCUavg.ResultQualCode) Is Null))
ORDER BY Station_location.DATESAMPLED, Station_location.Time;



But, when I change the query I still get the rinse blanks and field
blanks in my results. Can anyone tell me how I'm messing this up?
Arrg.

Thanks.
Click to expand...
 
E

ellezeebub

Thank you so much!! The first alternative did remove the RBs, but not
the FBs. The second, however, worked beautifully. Thanks for taking
the time to help me get it working. I'm hoping as I use Access more
I'll understand the ins and outs better. Lol. I love puns!!

Try
SELECT Station_location.STATIONNAME, Station_location.DATESAMPLED,
Station_location.Time, Station_samples.[Total metals],
TblTCUavg.AvgOfResult
FROM (Station_location INNER JOIN Station_samples ON
Station_location.STATIONCODE = Station_samples.STATIONCODE) INNER JOIN
TblTCUavg ON Station_samples.[Total metals] = TblTCUavg.SampleID

WHERE NOT (((Station_location.STATIONNAME) Like "* rb*") AND
((TblTCUavg.ResultQualCode) Is Null)) OR
(((Station_location.STATIONNAME) Like "* fb*") AND
((TblTCUavg.ResultQualCode) Is Null)))

ORDER BY Station_location.DATESAMPLED, Station_location.Time;

Or try

SELECT Station_location.STATIONNAME, Station_location.DATESAMPLED,
Station_location.Time, Station_samples.[Total metals],
TblTCUavg.AvgOfResult
FROM (Station_location INNER JOIN Station_samples ON
Station_location.STATIONCODE = Station_samples.STATIONCODE) INNER JOIN
TblTCUavg ON Station_samples.[Total metals] = TblTCUavg.SampleID

WHERE (((Station_location.STATIONNAME) Not Like "* rb*") AND
((TblTCUavg.ResultQualCode) Is Null))

AND

(((Station_location.STATIONNAME) Not Like "* fb*") AND
((TblTCUavg.ResultQualCode) Is Null))

ORDER BY Station_location.DATESAMPLED, Station_location.Time;
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.


Hi--I'm new to the group and pretty new to Access. I'm trying to get
a grip though, but this problem is making me crazy.
Click to expand...
My problem, in a nutshell, is that I return a query in which all the
occurrences of a certain field containing RB or FB come back to me
(for rinse blank and field blank). But, I cannot write a query that
*excludes* those records.
Click to expand...
Here is a SQL view of the query that works:
Click to expand...
SELECT Station_location.STATIONNAME, Station_location.DATESAMPLED,
Station_location.Time, Station_samples.[Total metals],
TblTCUavg.AvgOfResult
FROM (Station_location INNER JOIN Station_samples ON
Station_location.STATIONCODE = Station_samples.STATIONCODE) INNER JOIN
TblTCUavg ON Station_samples.[Total metals] = TblTCUavg.SampleID
WHERE (((Station_location.STATIONNAME) Like "* rb*") AND
((TblTCUavg.ResultQualCode) Is Null)) OR
(((Station_location.STATIONNAME) Like "* fb*") AND
((TblTCUavg.ResultQualCode) Is Null))
ORDER BY Station_location.DATESAMPLED, Station_location.Time;
Click to expand...
So, it would seem reasonable to me that all I would have to do to
exclude those values would be to insert a "not like" instead of a
"like" and all my problems would be solved.
Click to expand...
SQL view of query that does not exclude rinse blanks and field blanks:
Click to expand...
SELECT Station_location.STATIONNAME, Station_location.DATESAMPLED,
Station_location.Time, Station_samples.[Total metals],
TblTCUavg.AvgOfResult
FROM (Station_location INNER JOIN Station_samples ON
Station_location.STATIONCODE = Station_samples.STATIONCODE) INNER JOIN
TblTCUavg ON Station_samples.[Total metals] = TblTCUavg.SampleID
WHERE (((Station_location.STATIONNAME) Not Like "* rb*") AND
((TblTCUavg.ResultQualCode) Is Null)) OR
(((Station_location.STATIONNAME) Not Like "* fb*") AND
((TblTCUavg.ResultQualCode) Is Null))
ORDER BY Station_location.DATESAMPLED, Station_location.Time;
Click to expand...
But, when I change the query I still get the rinse blanks and field
blanks in my results. Can anyone tell me how I'm messing this up?
Arrg.
Click to expand...
Thanks.
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

problems with a summary query 2
Problem with joins - not all records shown 2
Query Calculation using a [Sum of ....] field 3
Hide dupes in a query 3
Sending a query or report with sub-totals in Access 97 2
Union query and two Totals queries 1
Form not working with query 6
Help with crosstab query and combo boxes 1

Top