excluding in a query

[Guest]

I have a table where employees can be "Management & Supervisory" or
"Inspection" or they can have both values assigned to them. In my query I
want to get all of the employees with just "Inspection" and none of the
employees with both values. In my criteria box I have:

<>"Management & Supervisory" Or "Inspection"

But this is still returning the people with both titles, how can I modify my
query to fix this?

Thanks

 

[Guest]

The answer is very dependent upon the schema of your data. If the data is
normalized, then you'll need to perform some 'set theory' to find those with
one attribute, but not the other.

List your table structure(s) for a more concise answer.

Steve Clark, Access MVP
fmsinc.com/consulting

 

[Rick B]

If you want all the employees with just inspection, why not use...

="Inspection"

?

 

[Guest]

Thanks for responding, first to Rick, if i use
="Inspection"
It returns people with both "Management & Supervisory" and "Inspection", but
what i want to do is exclude people with "Management & Supervisory".

To Steve~ I am using two tables in the query, the first [Employee_Info] and
the second [ManagInspet]. They both contain 'EmployeeNumber' and they are
link by that with a one-to-many relationship. the table [ManagInspt] has a
combo box for data entry where the only two options are "Management &
Supervisory" or "Inspection". In this table there are about 4 employee
numbers that show up twice, once for each choice.

i hope this clears things up,

thanks

 

[John Spencer]

Pardon me, but how about something like the following

SELECT Employee_Info.*
FROM Employee_Info
WHERE Employee_Info.EmployeeNumber NOT IN
(SELECT EmployeeNumber
FROM ManagInspet
WHERE SomeField = "Management & Supervisory")

Thanks for responding, first to Rick, if i use
="Inspection"
It returns people with both "Management & Supervisory" and "Inspection",
but
what i want to do is exclude people with "Management & Supervisory".

To Steve~ I am using two tables in the query, the first [Employee_Info]
and
the second [ManagInspet]. They both contain 'EmployeeNumber' and they are
link by that with a one-to-many relationship. the table [ManagInspt] has a
combo box for data entry where the only two options are "Management &
Supervisory" or "Inspection". In this table there are about 4 employee
numbers that show up twice, once for each choice.

i hope this clears things up,

thanks

The answer is very dependent upon the schema of your data. If the data
is
normalized, then you'll need to perform some 'set theory' to find those
with
one attribute, but not the other.

List your table structure(s) for a more concise answer.

Steve Clark, Access MVP
fmsinc.com/consulting

 

[Guest]

THere probably exists a much more elegant solution than this, but this is the
first solution that came to mind.

Create a query with [Employee_Info] innerjoin [ManagInspet]
Qry1:
Select 'EmployeeNumber', Logic:
iif([MngmntSupInsp]="Management....",1,iif([MngmntSupInsp]="Inspection",2,0))

Qry2:
Select qry1, groupby 'EmployeeNumber' and sum "Logic"

Qry3Inspec: (Inspection only)
Select qry2 'EmployeeNumber' where logic = 2

Qry3Mng: (Manag....only)
Select qry2 'EmployeeNumber' where logic = 1

Qry3both:
Select qry2 'EmployeeNumber' where logic = 3

HTH
Hafeez Esmail

Thanks for responding, first to Rick, if i use
="Inspection"
It returns people with both "Management & Supervisory" and "Inspection", but
what i want to do is exclude people with "Management & Supervisory".

To Steve~ I am using two tables in the query, the first [Employee_Info] and
the second [ManagInspet]. They both contain 'EmployeeNumber' and they are
link by that with a one-to-many relationship. the table [ManagInspt] has a
combo box for data entry where the only two options are "Management &
Supervisory" or "Inspection". In this table there are about 4 employee
numbers that show up twice, once for each choice.

i hope this clears things up,

thanks

The answer is very dependent upon the schema of your data. If the data is
normalized, then you'll need to perform some 'set theory' to find those with
one attribute, but not the other.

List your table structure(s) for a more concise answer.

Steve Clark, Access MVP
fmsinc.com/consulting

 

[Guest]

Thanks for the ideas! i will try them out

Pardon me, but how about something like the following

SELECT Employee_Info.*
FROM Employee_Info
WHERE Employee_Info.EmployeeNumber NOT IN
(SELECT EmployeeNumber
FROM ManagInspet
WHERE SomeField = "Management & Supervisory")

Thanks for responding, first to Rick, if i use
="Inspection"
It returns people with both "Management & Supervisory" and "Inspection",
but
what i want to do is exclude people with "Management & Supervisory".

To Steve~ I am using two tables in the query, the first [Employee_Info]
and
the second [ManagInspet]. They both contain 'EmployeeNumber' and they are
link by that with a one-to-many relationship. the table [ManagInspt] has a
combo box for data entry where the only two options are "Management &
Supervisory" or "Inspection". In this table there are about 4 employee
numbers that show up twice, once for each choice.

i hope this clears things up,

thanks

The answer is very dependent upon the schema of your data. If the data
is
normalized, then you'll need to perform some 'set theory' to find those
with
one attribute, but not the other.

List your table structure(s) for a more concise answer.

Steve Clark, Access MVP
fmsinc.com/consulting

:

I have a table where employees can be "Management & Supervisory" or
"Inspection" or they can have both values assigned to them. In my query
I
want to get all of the employees with just "Inspection" and none of the
employees with both values. In my criteria box I have:

<>"Management & Supervisory" Or "Inspection"

But this is still returning the people with both titles, how can I
modify my
query to fix this?

Thanks