query parameter ignored - please check my syntax

[iliace]

This one has me stumped. My question is, basically: "For the two
departments in question, list all employees who (a) got paid on
11/27/2006, and (b) had one of two particular deductions on that
paycheck." I replaced names of actual tables with more readable ones.
I also broke out each where condition on a separate line.

SELECT chkVwDeduction.chkVwHOMEDEPT, chkVwDeduction.[FILE#],
chkVwDeduction.NAME, chkVwDeduction.chkVwDEDCODE,
chkVwDeduction.chkVwDEDAMT, chkVwDeduction.chkVwPAYDATE
FROM chkVwDeduction
WHERE
(((chkVwDeduction.chkVwHOMEDEPT) In ('600000','700000'))
AND
((chkVwDeduction.[FILE#]) In
(SELECT DISTINCT chkVwDeduction.[FILE#]
FROM chkVwDeduction
WHERE chkVwDeduction.chkVwPAYDATE=#11/27/2006# AND
chkVwDeduction.chkVwDEDCODE IN ('W','X','Y','Z')))
AND
((chkVwDeduction.chkVwDEDCODE) In ('1','2'))
AND
((chkVwDeduction.chkVwPAYDATE)=#11/27/2006#))
ORDER BY chkVwDeduction.chkVwHOMEDEPT;

I am using a linked table from the payroll software, which has a table
called chkVwDeduction. Each record is a deduction taken on the
paycheck. File number uniquely identifies each record.

So, I need these criteria:
* home department is either 600000 or 700000
* employees who had a deduction of either (W X Y or Z) which indicate
direct deposit, meaning hours worked and paid, on the pay date in
question
* deduction code and amount if the employee paid had a deduction code
of either (1 or 2)
* pay date 11/27/2006

For some reason, the inner query performs the required filter by date.
In other words, this by itself does what it's supposed to (it's the IN
SELECT from the WHERE condition above):

SELECT DISTINCT chkVwDeduction.[FILE#]
FROM chkVwDeduction
WHERE chkVwDeduction.chkVwPAYDATE=#11/27/2006# AND
chkVwDeduction.chkVwDEDCODE IN ('W','X','Y','Z'))

However, the larger query ignores this condition and returns a few
random pay dates earlier this year.

Can anyone see where the issue is? I tried just about everything.
Technically, I could just match on check sequence number, but it should
be working as is, correct?

 

[John Spencer]

I would be cautious and alias the table in the subquery, but even that
shouldn't cause a problem with your query. I suspect you have a data
problem. Have you tried running the query with only the subquery condition
to see what data is returned? If that looks good, try adding in on other
condition in the where clause until the returned data starts looking wrong.

I've restructured your query to remove all the unneeded parentheses that
have been added by Access.

SELECT chkVwDeduction.chkVwHOMEDEPT, chkVwDeduction.[FILE#],
chkVwDeduction.NAME, chkVwDeduction.chkVwDEDCODE,
chkVwDeduction.chkVwDEDAMT, chkVwDeduction.chkVwPAYDATE
FROM chkVwDeduction
WHERE
chkVwDeduction.chkVwHOMEDEPT In ('600000','700000')
AND
chkVwDeduction.chkVwDEDCODE In ('1','2')
AND
chkVwDeduction.chkVwPAYDATE=#11/27/2006#
AND
chkVwDeduction.[FILE#] In
(SELECT DISTINCT T.[FILE#]
FROM chkVwDeduction as T
WHERE T.chkVwPAYDATE=#11/27/2006# AND
T.chkVwDEDCODE IN ('W','X','Y','Z'))

ORDER BY chkVwDeduction.chkVwHOMEDEPT;

This one has me stumped. My question is, basically: "For the two
departments in question, list all employees who (a) got paid on
11/27/2006, and (b) had one of two particular deductions on that
paycheck." I replaced names of actual tables with more readable ones.
I also broke out each where condition on a separate line.

SELECT chkVwDeduction.chkVwHOMEDEPT, chkVwDeduction.[FILE#],
chkVwDeduction.NAME, chkVwDeduction.chkVwDEDCODE,
chkVwDeduction.chkVwDEDAMT, chkVwDeduction.chkVwPAYDATE
FROM chkVwDeduction
WHERE
(((chkVwDeduction.chkVwHOMEDEPT) In ('600000','700000'))
AND
((chkVwDeduction.[FILE#]) In
(SELECT DISTINCT chkVwDeduction.[FILE#]
FROM chkVwDeduction
WHERE chkVwDeduction.chkVwPAYDATE=#11/27/2006# AND
chkVwDeduction.chkVwDEDCODE IN ('W','X','Y','Z')))
AND
((chkVwDeduction.chkVwDEDCODE) In ('1','2'))
AND
((chkVwDeduction.chkVwPAYDATE)=#11/27/2006#))
ORDER BY chkVwDeduction.chkVwHOMEDEPT;

I am using a linked table from the payroll software, which has a table
called chkVwDeduction. Each record is a deduction taken on the
paycheck. File number uniquely identifies each record.

So, I need these criteria:
* home department is either 600000 or 700000
* employees who had a deduction of either (W X Y or Z) which indicate
direct deposit, meaning hours worked and paid, on the pay date in
question
* deduction code and amount if the employee paid had a deduction code
of either (1 or 2)
* pay date 11/27/2006

For some reason, the inner query performs the required filter by date.
In other words, this by itself does what it's supposed to (it's the IN
SELECT from the WHERE condition above):

SELECT DISTINCT chkVwDeduction.[FILE#]
FROM chkVwDeduction
WHERE chkVwDeduction.chkVwPAYDATE=#11/27/2006# AND
chkVwDeduction.chkVwDEDCODE IN ('W','X','Y','Z'))

However, the larger query ignores this condition and returns a few
random pay dates earlier this year.

Can anyone see where the issue is? I tried just about everything.
Technically, I could just match on check sequence number, but it should
be working as is, correct?

 

[iliace]

Thank you for your reply. Unfortunately, the result is still the same.
I sincerely hope there isn't a problem with the data - this is the
actual table that gets uploaded to the payroll provider's mainframe to
generate the checks - then again, anything is possible knowing these
guys.

When I run this portion of the query as a separate query:

(SELECT DISTINCT T.[FILE#]
FROM chkVwDeduction as T
WHERE T.chkVwPAYDATE=#11/27/2006# AND
T.chkVwDEDCODE IN ('W','X','Y','Z'))

....I get a list of 403 items. If I include the chkVwPAYDATE as part of
my criteria, only 11/27/2006 is listed. If I remove the DISTINCT
keyword, again I get a list of many more paydates than I want.

However, when I run the full query (with your parenthetical
deobfuscation), I get a list of 17 items, *none* of which have the
11/27/2006 date. The other criteria fields work - the deduction is "in
(1, 2)" and the department code is "in (600000, 700000)". I attempted
to use an alias in the full query (using Tbl for my alias) with the
same results.

I did some further investigating. For example, I ran an aggregate
query to count the number of deductions, the resulting record count of
which returns the number of employees paid on 11/27/2006:

SELECT Tbl.chkVWPAYDATE, Tbl.chkVWHOMEDEPT, Tbl.[FILE#], Tbl.NAME,
Count(Tbl.chkVWDEDCODE) AS NumDeds
FROM chkVwDeduction AS Tbl
GROUP BY Tbl.chkVWPAYDATE, Tbl.chkVWHOMEDEPT, Tbl.[FILE#], Tbl.NAME
HAVING Tbl.chkVWPAYDATE=#11/27/2006#
ORDER BY Tbl.chkVWHOMEDEPT;

The DISTINCT keyword seems to have something to do with it, but exactly
why it affects my criteria parameter, I cannot explain.

I would be cautious and alias the table in the subquery, but even that
shouldn't cause a problem with your query. I suspect you have a data
problem. Have you tried running the query with only the subquery condition
to see what data is returned? If that looks good, try adding in on other
condition in the where clause until the returned data starts looking wrong.

I've restructured your query to remove all the unneeded parentheses that
have been added by Access.

SELECT chkVwDeduction.chkVwHOMEDEPT, chkVwDeduction.[FILE#],
chkVwDeduction.NAME, chkVwDeduction.chkVwDEDCODE,
chkVwDeduction.chkVwDEDAMT, chkVwDeduction.chkVwPAYDATE
FROM chkVwDeduction
WHERE
chkVwDeduction.chkVwHOMEDEPT In ('600000','700000')
AND
chkVwDeduction.chkVwDEDCODE In ('1','2')
AND
chkVwDeduction.chkVwPAYDATE=#11/27/2006#
AND
chkVwDeduction.[FILE#] In
(SELECT DISTINCT T.[FILE#]
FROM chkVwDeduction as T
WHERE T.chkVwPAYDATE=#11/27/2006# AND
T.chkVwDEDCODE IN ('W','X','Y','Z'))

ORDER BY chkVwDeduction.chkVwHOMEDEPT;

This one has me stumped. My question is, basically: "For the two
departments in question, list all employees who (a) got paid on
11/27/2006, and (b) had one of two particular deductions on that
paycheck." I replaced names of actual tables with more readable ones.
I also broke out each where condition on a separate line.

SELECT chkVwDeduction.chkVwHOMEDEPT, chkVwDeduction.[FILE#],
chkVwDeduction.NAME, chkVwDeduction.chkVwDEDCODE,
chkVwDeduction.chkVwDEDAMT, chkVwDeduction.chkVwPAYDATE
FROM chkVwDeduction
WHERE
(((chkVwDeduction.chkVwHOMEDEPT) In ('600000','700000'))
AND
((chkVwDeduction.[FILE#]) In
(SELECT DISTINCT chkVwDeduction.[FILE#]
FROM chkVwDeduction
WHERE chkVwDeduction.chkVwPAYDATE=#11/27/2006# AND
chkVwDeduction.chkVwDEDCODE IN ('W','X','Y','Z')))
AND
((chkVwDeduction.chkVwDEDCODE) In ('1','2'))
AND
((chkVwDeduction.chkVwPAYDATE)=#11/27/2006#))
ORDER BY chkVwDeduction.chkVwHOMEDEPT;

I am using a linked table from the payroll software, which has a table
called chkVwDeduction. Each record is a deduction taken on the
paycheck. File number uniquely identifies each record.

So, I need these criteria:
* home department is either 600000 or 700000
* employees who had a deduction of either (W X Y or Z) which indicate
direct deposit, meaning hours worked and paid, on the pay date in
question
* deduction code and amount if the employee paid had a deduction code
of either (1 or 2)
* pay date 11/27/2006

For some reason, the inner query performs the required filter by date.
In other words, this by itself does what it's supposed to (it's the IN
SELECT from the WHERE condition above):

SELECT DISTINCT chkVwDeduction.[FILE#]
FROM chkVwDeduction
WHERE chkVwDeduction.chkVwPAYDATE=#11/27/2006# AND
chkVwDeduction.chkVwDEDCODE IN ('W','X','Y','Z'))

However, the larger query ignores this condition and returns a few
random pay dates earlier this year.

Can anyone see where the issue is? I tried just about everything.
Technically, I could just match on check sequence number, but it should
be working as is, correct?

 

[John Spencer]

Since you are running this against a linked file it could be the ISAM
(driver?) that is causing a problem.
It could be a bad index that is causing the problem (if one exists).

This is a case where I don't see what else I can do to help. Desparation
things that you could try
-- DistinctRow in your sub-query
-- Add a sort to the sub-query
One of those may work to change your results

Thank you for your reply. Unfortunately, the result is still the same.
I sincerely hope there isn't a problem with the data - this is the
actual table that gets uploaded to the payroll provider's mainframe to
generate the checks - then again, anything is possible knowing these
guys.

When I run this portion of the query as a separate query:

(SELECT DISTINCT T.[FILE#]
FROM chkVwDeduction as T
WHERE T.chkVwPAYDATE=#11/27/2006# AND
T.chkVwDEDCODE IN ('W','X','Y','Z'))

...I get a list of 403 items. If I include the chkVwPAYDATE as part of
my criteria, only 11/27/2006 is listed. If I remove the DISTINCT
keyword, again I get a list of many more paydates than I want.

However, when I run the full query (with your parenthetical
deobfuscation), I get a list of 17 items, *none* of which have the
11/27/2006 date. The other criteria fields work - the deduction is "in
(1, 2)" and the department code is "in (600000, 700000)". I attempted
to use an alias in the full query (using Tbl for my alias) with the
same results.

I did some further investigating. For example, I ran an aggregate
query to count the number of deductions, the resulting record count of
which returns the number of employees paid on 11/27/2006:

SELECT Tbl.chkVWPAYDATE, Tbl.chkVWHOMEDEPT, Tbl.[FILE#], Tbl.NAME,
Count(Tbl.chkVWDEDCODE) AS NumDeds
FROM chkVwDeduction AS Tbl
GROUP BY Tbl.chkVWPAYDATE, Tbl.chkVWHOMEDEPT, Tbl.[FILE#], Tbl.NAME
HAVING Tbl.chkVWPAYDATE=#11/27/2006#
ORDER BY Tbl.chkVWHOMEDEPT;

The DISTINCT keyword seems to have something to do with it, but exactly
why it affects my criteria parameter, I cannot explain.

I would be cautious and alias the table in the subquery, but even that
shouldn't cause a problem with your query. I suspect you have a data
problem. Have you tried running the query with only the subquery
condition
to see what data is returned? If that looks good, try adding in on other
condition in the where clause until the returned data starts looking
wrong.

I've restructured your query to remove all the unneeded parentheses that
have been added by Access.

SELECT chkVwDeduction.chkVwHOMEDEPT, chkVwDeduction.[FILE#],
chkVwDeduction.NAME, chkVwDeduction.chkVwDEDCODE,
chkVwDeduction.chkVwDEDAMT, chkVwDeduction.chkVwPAYDATE
FROM chkVwDeduction
WHERE
chkVwDeduction.chkVwHOMEDEPT In ('600000','700000')
AND
chkVwDeduction.chkVwDEDCODE In ('1','2')
AND
chkVwDeduction.chkVwPAYDATE=#11/27/2006#
AND
chkVwDeduction.[FILE#] In
(SELECT DISTINCT T.[FILE#]
FROM chkVwDeduction as T
WHERE T.chkVwPAYDATE=#11/27/2006# AND
T.chkVwDEDCODE IN ('W','X','Y','Z'))

ORDER BY chkVwDeduction.chkVwHOMEDEPT;

This one has me stumped. My question is, basically: "For the two
departments in question, list all employees who (a) got paid on
11/27/2006, and (b) had one of two particular deductions on that
paycheck." I replaced names of actual tables with more readable ones.
I also broke out each where condition on a separate line.

SELECT chkVwDeduction.chkVwHOMEDEPT, chkVwDeduction.[FILE#],
chkVwDeduction.NAME, chkVwDeduction.chkVwDEDCODE,
chkVwDeduction.chkVwDEDAMT, chkVwDeduction.chkVwPAYDATE
FROM chkVwDeduction
WHERE
(((chkVwDeduction.chkVwHOMEDEPT) In ('600000','700000'))
AND
((chkVwDeduction.[FILE#]) In
(SELECT DISTINCT chkVwDeduction.[FILE#]
FROM chkVwDeduction
WHERE chkVwDeduction.chkVwPAYDATE=#11/27/2006# AND
chkVwDeduction.chkVwDEDCODE IN ('W','X','Y','Z')))
AND
((chkVwDeduction.chkVwDEDCODE) In ('1','2'))
AND
((chkVwDeduction.chkVwPAYDATE)=#11/27/2006#))
ORDER BY chkVwDeduction.chkVwHOMEDEPT;

I am using a linked table from the payroll software, which has a table
called chkVwDeduction. Each record is a deduction taken on the
paycheck. File number uniquely identifies each record.

So, I need these criteria:
* home department is either 600000 or 700000
* employees who had a deduction of either (W X Y or Z) which indicate
direct deposit, meaning hours worked and paid, on the pay date in
question
* deduction code and amount if the employee paid had a deduction code
of either (1 or 2)
* pay date 11/27/2006

For some reason, the inner query performs the required filter by date.
In other words, this by itself does what it's supposed to (it's the IN
SELECT from the WHERE condition above):

SELECT DISTINCT chkVwDeduction.[FILE#]
FROM chkVwDeduction
WHERE chkVwDeduction.chkVwPAYDATE=#11/27/2006# AND
chkVwDeduction.chkVwDEDCODE IN ('W','X','Y','Z'))

However, the larger query ignores this condition and returns a few
random pay dates earlier this year.

Can anyone see where the issue is? I tried just about everything.
Technically, I could just match on check sequence number, but it should
be working as is, correct?

 

[Gary Walter]

I wasn't going to butt in but this just bothers me...

is this a Jet table?
or a linked table that needs deleted,
then relinked?
------------------
is [FILE#] part of a *multi-field* primary key?

seems like there were problems using

WHERE f1 IN (SELECT f1...)

when f1 was not a single-field pk...
-----------------
chkVwDEDCODE IN ('W','X','Y','Z')
<snip>
AND
<snip>
chkVwDEDCODE In ('1','2')

I know a lot snipped out, but above
just doesn't make sense (to me)...

Thank you for your reply. Unfortunately, the result is still the same.
I sincerely hope there isn't a problem with the data - this is the
actual table that gets uploaded to the payroll provider's mainframe to
generate the checks - then again, anything is possible knowing these
guys.

When I run this portion of the query as a separate query:

(SELECT DISTINCT T.[FILE#]
FROM chkVwDeduction as T
WHERE T.chkVwPAYDATE=#11/27/2006# AND
T.chkVwDEDCODE IN ('W','X','Y','Z'))

...I get a list of 403 items. If I include the chkVwPAYDATE as part of
my criteria, only 11/27/2006 is listed. If I remove the DISTINCT
keyword, again I get a list of many more paydates than I want.

However, when I run the full query (with your parenthetical
deobfuscation), I get a list of 17 items, *none* of which have the
11/27/2006 date. The other criteria fields work - the deduction is "in
(1, 2)" and the department code is "in (600000, 700000)". I attempted
to use an alias in the full query (using Tbl for my alias) with the
same results.

I did some further investigating. For example, I ran an aggregate
query to count the number of deductions, the resulting record count of
which returns the number of employees paid on 11/27/2006:

SELECT Tbl.chkVWPAYDATE, Tbl.chkVWHOMEDEPT, Tbl.[FILE#], Tbl.NAME,
Count(Tbl.chkVWDEDCODE) AS NumDeds
FROM chkVwDeduction AS Tbl
GROUP BY Tbl.chkVWPAYDATE, Tbl.chkVWHOMEDEPT, Tbl.[FILE#], Tbl.NAME
HAVING Tbl.chkVWPAYDATE=#11/27/2006#
ORDER BY Tbl.chkVWHOMEDEPT;

The DISTINCT keyword seems to have something to do with it, but exactly
why it affects my criteria parameter, I cannot explain.

I would be cautious and alias the table in the subquery, but even that
shouldn't cause a problem with your query. I suspect you have a data
problem. Have you tried running the query with only the subquery
condition
to see what data is returned? If that looks good, try adding in on other
condition in the where clause until the returned data starts looking
wrong.

I've restructured your query to remove all the unneeded parentheses that
have been added by Access.

SELECT chkVwDeduction.chkVwHOMEDEPT, chkVwDeduction.[FILE#],
chkVwDeduction.NAME, chkVwDeduction.chkVwDEDCODE,
chkVwDeduction.chkVwDEDAMT, chkVwDeduction.chkVwPAYDATE
FROM chkVwDeduction
WHERE
chkVwDeduction.chkVwHOMEDEPT In ('600000','700000')
AND
chkVwDeduction.chkVwDEDCODE In ('1','2')
AND
chkVwDeduction.chkVwPAYDATE=#11/27/2006#
AND
chkVwDeduction.[FILE#] In
(SELECT DISTINCT T.[FILE#]
FROM chkVwDeduction as T
WHERE T.chkVwPAYDATE=#11/27/2006# AND
T.chkVwDEDCODE IN ('W','X','Y','Z'))

ORDER BY chkVwDeduction.chkVwHOMEDEPT;

This one has me stumped. My question is, basically: "For the two
departments in question, list all employees who (a) got paid on
11/27/2006, and (b) had one of two particular deductions on that
paycheck." I replaced names of actual tables with more readable ones.
I also broke out each where condition on a separate line.

SELECT chkVwDeduction.chkVwHOMEDEPT, chkVwDeduction.[FILE#],
chkVwDeduction.NAME, chkVwDeduction.chkVwDEDCODE,
chkVwDeduction.chkVwDEDAMT, chkVwDeduction.chkVwPAYDATE
FROM chkVwDeduction
WHERE
(((chkVwDeduction.chkVwHOMEDEPT) In ('600000','700000'))
AND
((chkVwDeduction.[FILE#]) In
(SELECT DISTINCT chkVwDeduction.[FILE#]
FROM chkVwDeduction
WHERE chkVwDeduction.chkVwPAYDATE=#11/27/2006# AND
chkVwDeduction.chkVwDEDCODE IN ('W','X','Y','Z')))
AND
((chkVwDeduction.chkVwDEDCODE) In ('1','2'))
AND
((chkVwDeduction.chkVwPAYDATE)=#11/27/2006#))
ORDER BY chkVwDeduction.chkVwHOMEDEPT;

I am using a linked table from the payroll software, which has a table
called chkVwDeduction. Each record is a deduction taken on the
paycheck. File number uniquely identifies each record.

So, I need these criteria:
* home department is either 600000 or 700000
* employees who had a deduction of either (W X Y or Z) which indicate
direct deposit, meaning hours worked and paid, on the pay date in
question
* deduction code and amount if the employee paid had a deduction code
of either (1 or 2)
* pay date 11/27/2006

For some reason, the inner query performs the required filter by date.
In other words, this by itself does what it's supposed to (it's the IN
SELECT from the WHERE condition above):

SELECT DISTINCT chkVwDeduction.[FILE#]
FROM chkVwDeduction
WHERE chkVwDeduction.chkVwPAYDATE=#11/27/2006# AND
chkVwDeduction.chkVwDEDCODE IN ('W','X','Y','Z'))

However, the larger query ignores this condition and returns a few
random pay dates earlier this year.

Can anyone see where the issue is? I tried just about everything.
Technically, I could just match on check sequence number, but it should
be working as is, correct?

 

[iliace]

Sorry I misspoke. FILE# uniquely identifies an EE, in the master EE
table. I'm not using that one here.

In the chkVwDeduction table, the primary key consists of [FILE#],
[CHECK#], and the deduction code field.

It is not a Jet table, it is linked from an ODBC database, but I don't
understand why it would need to be relinked? I might not know enough
about these things. To me it seems to have something to do with the
DISTINCT designation, that's the only thing I've pinpointed as to
making a difference. That works, notwithstanding.

I wasn't going to butt in but this just bothers me...

is this a Jet table?
or a linked table that needs deleted,
then relinked?
------------------
is [FILE#] part of a *multi-field* primary key?

seems like there were problems using

WHERE f1 IN (SELECT f1...)

when f1 was not a single-field pk...
-----------------
chkVwDEDCODE IN ('W','X','Y','Z')
<snip>
AND
<snip>
chkVwDEDCODE In ('1','2')

I know a lot snipped out, but above
just doesn't make sense (to me)...

Thank you for your reply. Unfortunately, the result is still the same.
I sincerely hope there isn't a problem with the data - this is the
actual table that gets uploaded to the payroll provider's mainframe to
generate the checks - then again, anything is possible knowing these
guys.

When I run this portion of the query as a separate query:

(SELECT DISTINCT T.[FILE#]
FROM chkVwDeduction as T
WHERE T.chkVwPAYDATE=#11/27/2006# AND
T.chkVwDEDCODE IN ('W','X','Y','Z'))

...I get a list of 403 items. If I include the chkVwPAYDATE as part of
my criteria, only 11/27/2006 is listed. If I remove the DISTINCT
keyword, again I get a list of many more paydates than I want.

However, when I run the full query (with your parenthetical
deobfuscation), I get a list of 17 items, *none* of which have the
11/27/2006 date. The other criteria fields work - the deduction is "in
(1, 2)" and the department code is "in (600000, 700000)". I attempted
to use an alias in the full query (using Tbl for my alias) with the
same results.

I did some further investigating. For example, I ran an aggregate
query to count the number of deductions, the resulting record count of
which returns the number of employees paid on 11/27/2006:

SELECT Tbl.chkVWPAYDATE, Tbl.chkVWHOMEDEPT, Tbl.[FILE#], Tbl.NAME,
Count(Tbl.chkVWDEDCODE) AS NumDeds
FROM chkVwDeduction AS Tbl
GROUP BY Tbl.chkVWPAYDATE, Tbl.chkVWHOMEDEPT, Tbl.[FILE#], Tbl.NAME
HAVING Tbl.chkVWPAYDATE=#11/27/2006#
ORDER BY Tbl.chkVWHOMEDEPT;

The DISTINCT keyword seems to have something to do with it, but exactly
why it affects my criteria parameter, I cannot explain.

I would be cautious and alias the table in the subquery, but even that
shouldn't cause a problem with your query. I suspect you have a data
problem. Have you tried running the query with only the subquery
condition
to see what data is returned? If that looks good, try adding in on other
condition in the where clause until the returned data starts looking
wrong.

I've restructured your query to remove all the unneeded parentheses that
have been added by Access.

SELECT chkVwDeduction.chkVwHOMEDEPT, chkVwDeduction.[FILE#],
chkVwDeduction.NAME, chkVwDeduction.chkVwDEDCODE,
chkVwDeduction.chkVwDEDAMT, chkVwDeduction.chkVwPAYDATE
FROM chkVwDeduction
WHERE
chkVwDeduction.chkVwHOMEDEPT In ('600000','700000')
AND
chkVwDeduction.chkVwDEDCODE In ('1','2')
AND
chkVwDeduction.chkVwPAYDATE=#11/27/2006#
AND
chkVwDeduction.[FILE#] In
(SELECT DISTINCT T.[FILE#]
FROM chkVwDeduction as T
WHERE T.chkVwPAYDATE=#11/27/2006# AND
T.chkVwDEDCODE IN ('W','X','Y','Z'))

ORDER BY chkVwDeduction.chkVwHOMEDEPT;

This one has me stumped. My question is, basically: "For the two
departments in question, list all employees who (a) got paid on
11/27/2006, and (b) had one of two particular deductions on that
paycheck." I replaced names of actual tables with more readable ones.
I also broke out each where condition on a separate line.

SELECT chkVwDeduction.chkVwHOMEDEPT, chkVwDeduction.[FILE#],
chkVwDeduction.NAME, chkVwDeduction.chkVwDEDCODE,
chkVwDeduction.chkVwDEDAMT, chkVwDeduction.chkVwPAYDATE
FROM chkVwDeduction
WHERE
(((chkVwDeduction.chkVwHOMEDEPT) In ('600000','700000'))
AND
((chkVwDeduction.[FILE#]) In
(SELECT DISTINCT chkVwDeduction.[FILE#]
FROM chkVwDeduction
WHERE chkVwDeduction.chkVwPAYDATE=#11/27/2006# AND
chkVwDeduction.chkVwDEDCODE IN ('W','X','Y','Z')))
AND
((chkVwDeduction.chkVwDEDCODE) In ('1','2'))
AND
((chkVwDeduction.chkVwPAYDATE)=#11/27/2006#))
ORDER BY chkVwDeduction.chkVwHOMEDEPT;

I am using a linked table from the payroll software, which has a table
called chkVwDeduction. Each record is a deduction taken on the
paycheck. File number uniquely identifies each record.

So, I need these criteria:
* home department is either 600000 or 700000
* employees who had a deduction of either (W X Y or Z) which indicate
direct deposit, meaning hours worked and paid, on the pay date in
question
* deduction code and amount if the employee paid had a deduction code
of either (1 or 2)
* pay date 11/27/2006

For some reason, the inner query performs the required filter by date.
In other words, this by itself does what it's supposed to (it's the IN
SELECT from the WHERE condition above):

SELECT DISTINCT chkVwDeduction.[FILE#]
FROM chkVwDeduction
WHERE chkVwDeduction.chkVwPAYDATE=#11/27/2006# AND
chkVwDeduction.chkVwDEDCODE IN ('W','X','Y','Z'))

However, the larger query ignores this condition and returns a few
random pay dates earlier this year.

Can anyone see where the issue is? I tried just about everything.
Technically, I could just match on check sequence number, but it should
be working as is, correct?

 

[Gary Walter]

I searched Google Groups and Help with no luck,
but I seem to remember (getting old...) that you
need to use EXISTS, rather than IN, when you have
a multi-column primary key. Why? I just don't remember.
Does the DISTINCT counteract against the problem?
I don't remember....

If this is a mdb, one test would be to append the following
to a Jet table whose primary key is only a simple autonumber
field

SELECT chkVwDeduction.chkVwHOMEDEPT, chkVwDeduction.[FILE#],
chkVwDeduction.NAME, chkVwDeduction.chkVwDEDCODE,
chkVwDeduction.chkVwDEDAMT, chkVwDeduction.chkVwPAYDATE
FROM chkVwDeduction
WHERE
chkVwDeduction.chkVwHOMEDEPT In ('600000','700000')
AND
chkVwDeduction.chkVwPAYDATE=#11/27/2006#
ORDER BY chkVwDeduction.chkVwHOMEDEPT;

then, run your further criteria on this Jet table.

You saying

"anything is possible knowing these guys."

was why I suggested deleting link, then relinking.

I assume this is a link to a View? (SQL SERVER, ORACLE, DB2?)

If so, perhaps someone else could give you better advice
if you post the SQL for the view and the properties of
the tables used in the SQL.....

Sorry I misspoke. FILE# uniquely identifies an EE, in the master EE
table. I'm not using that one here.

In the chkVwDeduction table, the primary key consists of [FILE#],
[CHECK#], and the deduction code field.

It is not a Jet table, it is linked from an ODBC database, but I don't
understand why it would need to be relinked? I might not know enough
about these things. To me it seems to have something to do with the
DISTINCT designation, that's the only thing I've pinpointed as to
making a difference. That works, notwithstanding.

I wasn't going to butt in but this just bothers me...

is this a Jet table?
or a linked table that needs deleted,
then relinked?
------------------
is [FILE#] part of a *multi-field* primary key?

seems like there were problems using

WHERE f1 IN (SELECT f1...)

when f1 was not a single-field pk...
-----------------
chkVwDEDCODE IN ('W','X','Y','Z')
<snip>
AND
<snip>
chkVwDEDCODE In ('1','2')

I know a lot snipped out, but above
just doesn't make sense (to me)...

Thank you for your reply. Unfortunately, the result is still the same.
I sincerely hope there isn't a problem with the data - this is the
actual table that gets uploaded to the payroll provider's mainframe to
generate the checks - then again, anything is possible knowing these
guys.

When I run this portion of the query as a separate query:

(SELECT DISTINCT T.[FILE#]
FROM chkVwDeduction as T
WHERE T.chkVwPAYDATE=#11/27/2006# AND
T.chkVwDEDCODE IN ('W','X','Y','Z'))

...I get a list of 403 items. If I include the chkVwPAYDATE as part of
my criteria, only 11/27/2006 is listed. If I remove the DISTINCT
keyword, again I get a list of many more paydates than I want.

However, when I run the full query (with your parenthetical
deobfuscation), I get a list of 17 items, *none* of which have the
11/27/2006 date. The other criteria fields work - the deduction is "in
(1, 2)" and the department code is "in (600000, 700000)". I attempted
to use an alias in the full query (using Tbl for my alias) with the
same results.

I did some further investigating. For example, I ran an aggregate
query to count the number of deductions, the resulting record count of
which returns the number of employees paid on 11/27/2006:

SELECT Tbl.chkVWPAYDATE, Tbl.chkVWHOMEDEPT, Tbl.[FILE#], Tbl.NAME,
Count(Tbl.chkVWDEDCODE) AS NumDeds
FROM chkVwDeduction AS Tbl
GROUP BY Tbl.chkVWPAYDATE, Tbl.chkVWHOMEDEPT, Tbl.[FILE#], Tbl.NAME
HAVING Tbl.chkVWPAYDATE=#11/27/2006#
ORDER BY Tbl.chkVWHOMEDEPT;

The DISTINCT keyword seems to have something to do with it, but exactly
why it affects my criteria parameter, I cannot explain.



John Spencer wrote:
I would be cautious and alias the table in the subquery, but even that
shouldn't cause a problem with your query. I suspect you have a data
problem. Have you tried running the query with only the subquery
condition
to see what data is returned? If that looks good, try adding in on
other
condition in the where clause until the returned data starts looking
wrong.

I've restructured your query to remove all the unneeded parentheses
that
have been added by Access.

SELECT chkVwDeduction.chkVwHOMEDEPT, chkVwDeduction.[FILE#],
chkVwDeduction.NAME, chkVwDeduction.chkVwDEDCODE,
chkVwDeduction.chkVwDEDAMT, chkVwDeduction.chkVwPAYDATE
FROM chkVwDeduction
WHERE
chkVwDeduction.chkVwHOMEDEPT In ('600000','700000')
AND
chkVwDeduction.chkVwDEDCODE In ('1','2')
AND
chkVwDeduction.chkVwPAYDATE=#11/27/2006#
AND
chkVwDeduction.[FILE#] In
(SELECT DISTINCT T.[FILE#]
FROM chkVwDeduction as T
WHERE T.chkVwPAYDATE=#11/27/2006# AND
T.chkVwDEDCODE IN ('W','X','Y','Z'))

ORDER BY chkVwDeduction.chkVwHOMEDEPT;

This one has me stumped. My question is, basically: "For the two
departments in question, list all employees who (a) got paid on
11/27/2006, and (b) had one of two particular deductions on that
paycheck." I replaced names of actual tables with more readable
ones.
I also broke out each where condition on a separate line.

SELECT chkVwDeduction.chkVwHOMEDEPT, chkVwDeduction.[FILE#],
chkVwDeduction.NAME, chkVwDeduction.chkVwDEDCODE,
chkVwDeduction.chkVwDEDAMT, chkVwDeduction.chkVwPAYDATE
FROM chkVwDeduction
WHERE
(((chkVwDeduction.chkVwHOMEDEPT) In ('600000','700000'))
AND
((chkVwDeduction.[FILE#]) In
(SELECT DISTINCT chkVwDeduction.[FILE#]
FROM chkVwDeduction
WHERE chkVwDeduction.chkVwPAYDATE=#11/27/2006# AND
chkVwDeduction.chkVwDEDCODE IN ('W','X','Y','Z')))
AND
((chkVwDeduction.chkVwDEDCODE) In ('1','2'))
AND
((chkVwDeduction.chkVwPAYDATE)=#11/27/2006#))
ORDER BY chkVwDeduction.chkVwHOMEDEPT;

I am using a linked table from the payroll software, which has a
table
called chkVwDeduction. Each record is a deduction taken on the
paycheck. File number uniquely identifies each record.

So, I need these criteria:
* home department is either 600000 or 700000
* employees who had a deduction of either (W X Y or Z) which
indicate
direct deposit, meaning hours worked and paid, on the pay date in
question
* deduction code and amount if the employee paid had a deduction
code
of either (1 or 2)
* pay date 11/27/2006

For some reason, the inner query performs the required filter by
date.
In other words, this by itself does what it's supposed to (it's the
IN
SELECT from the WHERE condition above):

SELECT DISTINCT chkVwDeduction.[FILE#]
FROM chkVwDeduction
WHERE chkVwDeduction.chkVwPAYDATE=#11/27/2006# AND
chkVwDeduction.chkVwDEDCODE IN ('W','X','Y','Z'))

However, the larger query ignores this condition and returns a few
random pay dates earlier this year.

Can anyone see where the issue is? I tried just about everything.
Technically, I could just match on check sequence number, but it
should
be working as is, correct?