Querring fields with Zeros

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a field called IsEmployee. If this person is an employee, it displays
a -1, and if they are not an employee, it displays 0. I'm looking for all the
non employee's which I would set that field to say Isemployee=0, but for some
reason, it still displays people in my query that are both employees and non
employess. This should have just been a simple thing, but for some reason
it's not working in this query. Please Help.
 
for the Field Name in your query, just put the name of the field in.
In the Criteria row for the field type in True

True = -1
False = 0
This appears to be a Yes/No (boolean) field. This will limit your return to
only those where the value in the field is -1
 
It is still not working. It still gives me all the personal. Here is my SQL
statement, maybe this will help. What I am really looking for is all Subs
that are not employees. We have some Employees that can be subs, but I only
want Subs that are not employees.

SELECT DISTINCT Substitute.ID, Person.LastName, Person.FirstName,
Person.Phone, Person.Zip, Person.Classified, Person.Certified,
Substitute.Inactive, Substitute.Restricted, Position.Name, Person.IsEmployee,
Format([Phone],"(000)000-0000") AS [Phone Number], Person.IsSubstitute,
SubPosition.PositionID
FROM (SubPosition INNER JOIN [Position] ON SubPosition.PositionID =
Position.ID) INNER JOIN (Person INNER JOIN Substitute ON Person.ID =
Substitute.ID) ON SubPosition.SubstituteID = Substitute.ID
WHERE (((Person.Classified)<>0) AND ((Person.Certified)=0) AND
((Substitute.Inactive)=0) AND ((Substitute.Restricted)=0) AND
((Person.IsEmployee)=False) AND ((Person.IsSubstitute)=True) AND
((SubPosition.PositionID)=527)) OR (((SubPosition.PositionID)=538)) OR
(((SubPosition.PositionID)=537));
 
Two things,
I thought you wanted only those where IsEmployeed = True. If this is
correct, change the False to True.
You have an OR condition in the Where clause, it is possible they could be
included in the Or condition?

KRB said:
It is still not working. It still gives me all the personal. Here is my SQL
statement, maybe this will help. What I am really looking for is all Subs
that are not employees. We have some Employees that can be subs, but I only
want Subs that are not employees.

SELECT DISTINCT Substitute.ID, Person.LastName, Person.FirstName,
Person.Phone, Person.Zip, Person.Classified, Person.Certified,
Substitute.Inactive, Substitute.Restricted, Position.Name, Person.IsEmployee,
Format([Phone],"(000)000-0000") AS [Phone Number], Person.IsSubstitute,
SubPosition.PositionID
FROM (SubPosition INNER JOIN [Position] ON SubPosition.PositionID =
Position.ID) INNER JOIN (Person INNER JOIN Substitute ON Person.ID =
Substitute.ID) ON SubPosition.SubstituteID = Substitute.ID
WHERE (((Person.Classified)<>0) AND ((Person.Certified)=0) AND
((Substitute.Inactive)=0) AND ((Substitute.Restricted)=0) AND
((Person.IsEmployee)=False) AND ((Person.IsSubstitute)=True) AND
((SubPosition.PositionID)=527)) OR (((SubPosition.PositionID)=538)) OR
(((SubPosition.PositionID)=537));


Klatuu said:
for the Field Name in your query, just put the name of the field in.
In the Criteria row for the field type in True

True = -1
False = 0
This appears to be a Yes/No (boolean) field. This will limit your return to
only those where the value in the field is -1
Click to expand...
Click to expand...
 
It is still not working. It still gives me all the personal. Here is my SQL
statement, maybe this will help. What I am really looking for is all Subs
that are not employees. We have some Employees that can be subs, but I only
want Subs that are not employees.

SELECT DISTINCT Substitute.ID, Person.LastName, Person.FirstName,
Person.Phone, Person.Zip, Person.Classified, Person.Certified,
Substitute.Inactive, Substitute.Restricted, Position.Name, Person.IsEmployee,
Format([Phone],"(000)000-0000") AS [Phone Number], Person.IsSubstitute,
SubPosition.PositionID
FROM (SubPosition INNER JOIN [Position] ON SubPosition.PositionID =
Position.ID) INNER JOIN (Person INNER JOIN Substitute ON Person.ID =
Substitute.ID) ON SubPosition.SubstituteID = Substitute.ID
WHERE (((Person.Classified)<>0) AND ((Person.Certified)=0) AND
((Substitute.Inactive)=0) AND ((Substitute.Restricted)=0) AND
((Person.IsEmployee)=False) AND ((Person.IsSubstitute)=True) AND
((SubPosition.PositionID)=527)) OR (((SubPosition.PositionID)=538)) OR
(((SubPosition.PositionID)=537));
Click to expand...

Klatuu is correct: the OR clause is causing the problem. You have
(with all the extra parentheses that Access throws in) two different
conditions: you're searching for employees for whom

(((Person.Classified)<>0) AND ((Person.Certified)=0) AND
((Substitute.Inactive)=0) AND ((Substitute.Restricted)=0) AND
((Person.IsEmployee)=False) AND ((Person.IsSubstitute)=True) AND
((SubPosition.PositionID)=527))

OR employees where

(((SubPosition.PositionID)=538))

OR employees where

(((SubPosition.PositionID)=537))

If any one of these three criteria is TRUE you'll get the record. THat
is, you'll see all of the employees in PositionID 537 and 538,
regardless of the other criteria.

Try instead:

WHERE (((Person.Classified)<>0) AND ((Person.Certified)=0) AND
((Substitute.Inactive)=0) AND ((Substitute.Restricted)=0) AND
((Person.IsEmployee)=False) AND ((Person.IsSubstitute)=True) AND
((SubPosition.PositionID IN (527, 537, 538)));


John W. Vinson[MVP]
 
Thanks, John
I'm old, my eyesight isn't that good, and when I start reading through all
those parenthises, I go into convulsions :)

John Vinson said:
It is still not working. It still gives me all the personal. Here is my SQL
statement, maybe this will help. What I am really looking for is all Subs
that are not employees. We have some Employees that can be subs, but I only
want Subs that are not employees.

SELECT DISTINCT Substitute.ID, Person.LastName, Person.FirstName,
Person.Phone, Person.Zip, Person.Classified, Person.Certified,
Substitute.Inactive, Substitute.Restricted, Position.Name, Person.IsEmployee,
Format([Phone],"(000)000-0000") AS [Phone Number], Person.IsSubstitute,
SubPosition.PositionID
FROM (SubPosition INNER JOIN [Position] ON SubPosition.PositionID =
Position.ID) INNER JOIN (Person INNER JOIN Substitute ON Person.ID =
Substitute.ID) ON SubPosition.SubstituteID = Substitute.ID
WHERE (((Person.Classified)<>0) AND ((Person.Certified)=0) AND
((Substitute.Inactive)=0) AND ((Substitute.Restricted)=0) AND
((Person.IsEmployee)=False) AND ((Person.IsSubstitute)=True) AND
((SubPosition.PositionID)=527)) OR (((SubPosition.PositionID)=538)) OR
(((SubPosition.PositionID)=537));
Click to expand...

Klatuu is correct: the OR clause is causing the problem. You have
(with all the extra parentheses that Access throws in) two different
conditions: you're searching for employees for whom

(((Person.Classified)<>0) AND ((Person.Certified)=0) AND
((Substitute.Inactive)=0) AND ((Substitute.Restricted)=0) AND
((Person.IsEmployee)=False) AND ((Person.IsSubstitute)=True) AND
((SubPosition.PositionID)=527))

OR employees where

(((SubPosition.PositionID)=538))

OR employees where

(((SubPosition.PositionID)=537))

If any one of these three criteria is TRUE you'll get the record. THat
is, you'll see all of the employees in PositionID 537 and 538,
regardless of the other criteria.

Try instead:

WHERE (((Person.Classified)<>0) AND ((Person.Certified)=0) AND
((Substitute.Inactive)=0) AND ((Substitute.Restricted)=0) AND
((Person.IsEmployee)=False) AND ((Person.IsSubstitute)=True) AND
((SubPosition.PositionID IN (527, 537, 538)));


John W. Vinson[MVP]
Click to expand...
 
I'm old, my eyesight isn't that good, and when I start reading through all
those parenthises, I go into convulsions :)
Click to expand...

<g> I'm 60, have 8 diopters correction in one eye and 10 in the other,
presbyopia, and I didn't even TRY to count parentheses... just by
guess and b'gorrah!

Teamwork! That's what it takes... teamwork! <bg>

John W. Vinson[MVP]
 
I'm 63 and only wear my glasses when I need to see.
Spent many a year as a professional musician and decided it was too many
crazy people in a crazy business so I quit and got into computer.
Been thinking about going back and putting together a band. Think I'll call
it The Olphardts
 
I'm 63 and only wear my glasses when I need to see.
Spent many a year as a professional musician and decided it was too many
crazy people in a crazy business so I quit and got into computer.
Been thinking about going back and putting together a band. Think I'll call
it The Olphardts
Click to expand...

LOL!!! I don't know where you are... but I'd come listen!


John W. Vinson[MVP]
 
Iowa - Isn't that a suburb of Amarillo?

When I was a traveling musician, I played the Holiday Inn in Otttumwa a
couple of times. 1976
 
Back
Top