Supress Record in QUERY

[Warren Phillips]

Hi:

I am looking for a way to do the following. I have a QUERY with data that
looks like the following:

Employee Salary Reimbursement Code
1229 500.00
1229 50.00 NT

For this employee (#1229), I want to ONLY keep the record that contains
(CODE = NT). The other record (Salary of 500.00) can drop out of the QUERY.

The key here is that I have a list of Employee numbers that will fall into
this logic....ie

If Employee Number = X
AND
Code = NT

Display the record in the QUERY.

If Employee Number = X
AND
Code <> NT
Then....."Supress" the record from showing in the QUERY

Any ideas

Thanks

Warren Phillips





If any record has NT in the adjustment code field...I want to keep it in the
QUERY.....If

 

[kingston via AccessMonster.com]

Is there a reason you cannot add the criteria "NT" to the field Code in the
query? I must not be understanding your problem correctly.

 

[Warren Phillips]

Yes....it is becuase there are all kinds of other records that need to be
displayed.....Of the records that have "NT" in the CODE field (and only for a
certain subset of Employee numbers)....I need the logic describerd below to
kick in....Thanks

 

[John Spencer]

IF the list is short you could use

WHERE (Employee In (1229,1258,3999) and Code = "NT") OR Employee Not In
(1229,1258,3999)

If the list is long and you have it in a table then you could use logic like

WHERE (Employee in (SELECT ID From TableHideThese) AND Code = "NT") OR
Employee In (SELECT T.ID
FROM MainTable LEFT JOIN TableHideThese
as T
ON MainTable.Employee = T.Employee
WHERE T.Employee is Null)

 

[Warren Phillips]

John:

Thank you

It is a short list.....where would I put this CODE within the QUERY to get
it to work????

WHERE (Employee In (1229,1258,3999) and Code = "NT") OR Employee Not In
(1229,1258,3999)

 

[John Spencer]

Assuming you are using the query grid

Field: Employee
Criteria (Line 1): In (1229,1258,3999)
Criteria (Line 2): Not In (1229,1258,3999)

Field: Code
Criteria (Line 1): "NT"
Criteria (Line 2): <<Leave this blank>>

John:

Thank you

It is a short list.....where would I put this CODE within the QUERY to get
it to work????

WHERE (Employee In (1229,1258,3999) and Code = "NT") OR Employee Not In
(1229,1258,3999)