Re: Criteria & <>

Hi Ken,
Perfect! I finally see what I expect to see, although I do wonder why the
query didn't work when I used <>6 OR <>9 OR... I don't believe I have ever
created a query where I had to put the same basic criteria in both fields. It
wouldn't let me put more than 2 anyway. I just thought it should work with
either one (ID# or descriptive name). Thanks so much for your help.
--
Aria W.


"Ken Snell MVP" wrote:

If you just want to filter out certain TitleID values, this is probably the
easier way to do it:

SELECT tblEmployees.EmpID, tblTitlesEmps.TitleID, tblTitles.TitleDescription
FROM tblTitles INNER JOIN (tblEmployees INNER JOIN tblTitlesEmps ON
tblEmployees.EmpID = tblTitlesEmps.EmpID) ON tblTitles.TitleID =
tblTitlesEmps.TitleID
WHERE NOT tblTitlesEmps.TitleID
IN (6, 24, another value, another value, another value);


Just include all the excluded TitleID values in the parentheses after IN.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/




"Aria" <Aria@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:91785F8D-E13D-47CC-A832-83343EAC1CB8@xxxxxxxxxxxxxxxx
Hello,

I'm trying to create a year-end check out report for my school. All staff
will either need to return their keys to be inventoried if they have been
pre-approved to retain over the summer by an administrator, or else turned
in
if they have not been approved.

I'm having a problem creating the query for this new report. The report
will
pull from too many tables so I am trying to create a sub query (?) that
will
exclude certain records. I'll try to be brief, but I also want to give you
enough info to work with.
Because there are different employee types/classifications, I won't be
able
to just use department name (everyone doesn't have a dept.) so I decided
to
use title since this applies to everyone.

This is the SQL I have so far:

SELECT tblEmployees.EmpID, tblTitlesEmps.TitleID,
tblTitles.TitleDescription
FROM tblTitles INNER JOIN (tblEmployees INNER JOIN tblTitlesEmps ON
tblEmployees.EmpID = tblTitlesEmps.EmpID) ON tblTitles.TitleID =
tblTitlesEmps.TitleID
WHERE (((tblTitlesEmps.TitleID)<>6) AND
((tblTitles.TitleDescription)<>"Food
Service Assistant")) OR (((tblTitlesEmps.TitleID)<>24) AND
((tblTitles.TitleDescription)<>"Custodian"));

Problems encountered:
1. I thought I could just use <> then list each title ID I wanted to
exclude. It behaved as expected for the first title ID exclusion, <>6.
2. I dropped to the "or" criteria line and added another title ID that I
wanted to exclude. I checked the results in data*** view and discovered
all
exclusionary criteria included in the view.
3. I thought about going the opposite direction by including only titles
for
which I want to see results, but the list for title Ids included will be
longer than that for the exclusions.
4. I was not able to exclude the results I would like until I also added
<>
to the title description for the same ID #.
5. There are more Ids I need to exclude but I am unable to add any
additional criteria.

This doesn't seem right to me. I have another query for a different report
that uses a number of "or" criteria. Looking for help from anyone who sees
or
knows something I have forgotten. I'm using A2000.

--
Aria W.



.

Loading