multiple Not "this ID"

[Linda RQ]

Hi Everyone,

I am using Access 2003. I have a query that I am trying to get to show only
patients on the adult areas. I only have 4 areas that are pediatric. Here
is my sql, but I used the query grid to make my query.

SELECT tblPatients.PtID, tblAreaList.AreaName, tblPtLocation.PtLocStDtTm,
tblPtLocation.PtLocEnDtTm
FROM tblPatients INNER JOIN (tblAreaList INNER JOIN tblPtLocation ON
tblAreaList.AreaID = tblPtLocation.AreaID_fk) ON tblPatients.PtID =
tblPtLocation.PtID_fk
GROUP BY tblPatients.PtID, tblPtLocation.AreaID_fk, tblAreaList.AreaName,
tblPtLocation.PtLocStDtTm, tblPtLocation.PtLocEnDtTm
HAVING ((Not (tblPtLocation.AreaID_fk)=17)) OR ((Not
(tblPtLocation.AreaID_fk)=18)) OR ((Not (tblPtLocation.AreaID_fk)=19)) OR
((Not (tblPtLocation.AreaID_fk)=20));

tblPatients is related to tblPtLocation via PtID. tblPtLocation is related
to tblAreaList via Area ID

When I run the query, only the area 17 is filtered out.

Thanks,

Linda

 

[Marshall Barton]

I am using Access 2003. I have a query that I am trying to get to show only
patients on the adult areas. I only have 4 areas that are pediatric. Here
is my sql, but I used the query grid to make my query.

SELECT tblPatients.PtID, tblAreaList.AreaName, tblPtLocation.PtLocStDtTm,
tblPtLocation.PtLocEnDtTm
FROM tblPatients INNER JOIN (tblAreaList INNER JOIN tblPtLocation ON
tblAreaList.AreaID = tblPtLocation.AreaID_fk) ON tblPatients.PtID =
tblPtLocation.PtID_fk
GROUP BY tblPatients.PtID, tblPtLocation.AreaID_fk, tblAreaList.AreaName,
tblPtLocation.PtLocStDtTm, tblPtLocation.PtLocEnDtTm
HAVING ((Not (tblPtLocation.AreaID_fk)=17)) OR ((Not
(tblPtLocation.AreaID_fk)=18)) OR ((Not (tblPtLocation.AreaID_fk)=19)) OR
((Not (tblPtLocation.AreaID_fk)=20));

tblPatients is related to tblPtLocation via PtID. tblPtLocation is related
to tblAreaList via Area ID

Why did you make it a Totals type query? I can not see a
reason for that.

Your mixture of Not and OR is inappropriate.

Try this:

SELECT tblPatients.PtID, tblAreaList.AreaName,
tblPtLocation.PtLocStDtTm, tblPtLocation.PtLocEnDtTm
FROM tblPatients
INNER JOIN (tblAreaList
INNER JOIN tblPtLocation
ON tblAreaList.AreaID = tblPtLocation.AreaID_fk)
ON tblPatients.PtID = tblPtLocation.PtID_fk
WHERE Not tblPtLocation.AreaID_fk IN(17,18,19,20)

 

[Michael Gramelspacher]

Hi Everyone,

I am using Access 2003. I have a query that I am trying to get to show only
patients on the adult areas. I only have 4 areas that are pediatric. Here
is my sql, but I used the query grid to make my query.

SELECT tblPatients.PtID, tblAreaList.AreaName, tblPtLocation.PtLocStDtTm,
tblPtLocation.PtLocEnDtTm
FROM tblPatients INNER JOIN (tblAreaList INNER JOIN tblPtLocation ON
tblAreaList.AreaID = tblPtLocation.AreaID_fk) ON tblPatients.PtID =
tblPtLocation.PtID_fk
GROUP BY tblPatients.PtID, tblPtLocation.AreaID_fk, tblAreaList.AreaName,
tblPtLocation.PtLocStDtTm, tblPtLocation.PtLocEnDtTm
HAVING ((Not (tblPtLocation.AreaID_fk)=17)) OR ((Not
(tblPtLocation.AreaID_fk)=18)) OR ((Not (tblPtLocation.AreaID_fk)=19)) OR
((Not (tblPtLocation.AreaID_fk)=20));

tblPatients is related to tblPtLocation via PtID. tblPtLocation is related
to tblAreaList via Area ID

When I run the query, only the area 17 is filtered out.

Thanks,

Linda

maybe:

SELECT tblPatients.PtID,
tblAreaList.AreaName,
tblPtLocation.PtLocStDtTm,
tblPtLocation.PtLocEnDtTm
FROM tblPatients
INNER JOIN (tblAreaList
INNER JOIN tblPtLocation
ON tblAreaList.AreaID = tblPtLocation.AreaID_fk)
ON tblPatients.PtID = tblPtLocation.PtID_fk
WHERE tblPtLocation.AreaID_fk NOT IN (17,18,19,20);

 

[Armen Stein]

((Not (tblPtLocation.AreaID_fk)=17)) OR ((Not
(tblPtLocation.AreaID_fk)=18)) OR ((Not (tblPtLocation.AreaID_fk)=19)) OR
((Not (tblPtLocation.AreaID_fk)=20));

I'm surprised that your query is even filtering out the 17's. After
all, a 17 would meet the criteria of OR NOT 18, so it would be
included.

It seems that you would need ANDs instead of ORs.

Or try something simpler like:

HAVING tblPtLocation.AreaID_fk NOT IN (17, 18, 19, 20)


Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[Linda RQ]

Why did you make it a Totals type query? I can not see a
reason for that.

Your mixture of Not and OR is inappropriate.

Try this:

SELECT tblPatients.PtID, tblAreaList.AreaName,
tblPtLocation.PtLocStDtTm, tblPtLocation.PtLocEnDtTm
FROM tblPatients
INNER JOIN (tblAreaList
INNER JOIN tblPtLocation
ON tblAreaList.AreaID = tblPtLocation.AreaID_fk)
ON tblPatients.PtID = tblPtLocation.PtID_fk
WHERE Not tblPtLocation.AreaID_fk IN(17,18,19,20)

That works. Thanks so much!

I didn't mean to make it a totals query. I think you ask me that alot <g> I
guess I just left the sum button clicked so I could see that totals row. I
get the same records either way. Is it a bad habit? I can see that the sql
I have with the totols on is not as nice looking as yours, does that have an
effect on things? I took the sql you put on here and pasted it in a new
query, then looked at it in the query grid and saw how the Not In function
showed up in the criteria row of my Area_ID fk column. I then just added
that to my query and I got the same result as your query made with the sql.
I pasted the sql from my new query below and once again yours looks cleaner
but works the same. What's the story on that? There is no way, I could
type up sql, I have to use the query grid.

My new sql taking off the sum button which gets rid of my totals row, and
adding Not In (17, 18, 19, 20)

SELECT tblPatients.PtID, tblAreaList.AreaName, tblPtLocation.PtLocStDtTm,
tblPtLocation.PtLocEnDtTm
FROM tblPatients INNER JOIN (tblAreaList INNER JOIN tblPtLocation ON
tblAreaList.AreaID = tblPtLocation.AreaID_fk) ON tblPatients.PtID =
tblPtLocation.PtID_fk
WHERE (((tblPtLocation.AreaID_fk) Not In (17,18,19,20)));

Linda

 

[Linda RQ]

"I'm surprised that your query is even filtering out the 17's. After all, a
17 would meet the criteria of OR NOT 18, so it would be included"

I guess I am must be an Access genious then <bg>

Yep, the Not In Function worked!

Thanks,
Linda

 

[Linda RQ]

maybe:

SELECT tblPatients.PtID,
tblAreaList.AreaName,
tblPtLocation.PtLocStDtTm,
tblPtLocation.PtLocEnDtTm
FROM tblPatients
INNER JOIN (tblAreaList
INNER JOIN tblPtLocation
ON tblAreaList.AreaID = tblPtLocation.AreaID_fk)
ON tblPatients.PtID = tblPtLocation.PtID_fk
WHERE tblPtLocation.AreaID_fk NOT IN (17,18,19,20);

Thanks Michael. This looks just like Marshals in my query grid and returns
the same records. Yours looks like something from the VB window.

I pasted all the variations that work in a notpad file and I'll look at them
once I get my reports done....thanks again.

 

[Marshall Barton]

"Marshall Barton" wrote

That works. Thanks so much!

I didn't mean to make it a totals query. I think you ask me that alot <g> I
guess I just left the sum button clicked so I could see that totals row. I
get the same records either way. Is it a bad habit? I can see that the sql
I have with the totols on is not as nice looking as yours, does that have an
effect on things? I took the sql you put on here and pasted it in a new
query, then looked at it in the query grid and saw how the Not In function
showed up in the criteria row of my Area_ID fk column. I then just added
that to my query and I got the same result as your query made with the sql.
I pasted the sql from my new query below and once again yours looks cleaner
but works the same. What's the story on that? There is no way, I could
type up sql, I have to use the query grid.

My new sql taking off the sum button which gets rid of my totals row, and
adding Not In (17, 18, 19, 20)

SELECT tblPatients.PtID, tblAreaList.AreaName, tblPtLocation.PtLocStDtTm,
tblPtLocation.PtLocEnDtTm
FROM tblPatients INNER JOIN (tblAreaList INNER JOIN tblPtLocation ON
tblAreaList.AreaID = tblPtLocation.AreaID_fk) ON tblPatients.PtID =
tblPtLocation.PtID_fk
WHERE (((tblPtLocation.AreaID_fk) Not In (17,18,19,20)));

Yes, that is a bad habit. The Totals button requires all
the fields in the SELECT clause to be used in an Aggregate
function, be in the GROUP BY clause or have the show box
unchecked. This is not appropriate unless you intend to use
an aggregate function.

In your query, you did not use an aggregate function (Count,
Sum,etc) so the only effect of the GROUP BY clause is to
omit any records that are the same across all the fields in
the SELECT clause. If that was the intended effect, then
you should use the DISTINCT keyword (or the Unique Values
property) instead of making it a Totals query.

Another point is that the query designer is not all that
intelligent. Among other things, for Totlas queies, it
always uses a HAVING clause when a WHERE clause is much more
efficient. The WHERE conditions filter out table records
before any calculations are done, but HAVING does the
calculations on all the records and then discards the
results that do not meet the HAVING conditions. In general,
the only things that should be in a HAVING clause are
aggregate expressions. Any conditions that only check a
field should be in the WHERE clause. Note that in a real
Totals type query the distinction is very important and, if
used incorrectly, could produce incorrect results.
EVERY TIME YOU USE A TOTALS QUERY, you must switch to SQL
view and inspect the HAVING clause. Then move any field
only conditions to the WHERE clause. Not only that, but if
you switch back to design view, the query designer will mess
up the query.

An innocuous, but irritating thing that the query designer
also does is add table names to prefix every field, even
when it is not needed. You have probably already noticed
all the extra ( ) and [ ] that it inserts willy-nilly into
the SQL statement whether they are needed or not.

Don't be misled because your query just happens to be simple
enough that the HAVING clause produced the same results as
the WHERE clause.