query returns records that don't match criteria

[wshs-spartan]

I am viewing an SQL db thru Access 2000. Sometimes when I do a query, I get
data for records that don't match the criteria.

For instance, I want to search for all clients = abc in tablea (code,number
returned,amount received,client). I add all fields to the query and in
criteria put abc. The query returns abc but also cde and fgh.

What is happening?

 

[Jerry Whittle]

Show us the SQL. Open the query in design view. Next go to View, SQL View
and copy and past it here.

Also examples of the returned data could help.

 

[wshs-spartan]

Here is the SQL:
SELECT dbo_results.match_code, dbo_results.total_responses,
dbo_results.total_dollars, dbo_results.client_cd, dbo_results.last_update
FROM dbo_results
WHERE (((dbo_results.client_cd)="sch"));

and here is a partial listing of the results:
match code total response total dollars client last update
0507A07 194 $3,061.00 CSM 8/27/2007 15:48
0507A24 14 $1,725.00 CSM 8/27/2007 15:48
A040503 1 $347.00 FTW 8/20/2007 11:54
A040504 1 $25.00 FTW 8/20/2007 11:54
A040508 1 $10.00 FTW 8/20/2007 11:54
A040527 1 $15.00 FTW 8/20/2007 11:54
0401BBA 210 $8,802.81 SCH
0401CIA 98 $4,420.00 SCH
0401CKA 24 $1,491.00 SCH
0401DCA 0 $0.00 SCH
041B01A 2 $220.00 SCH
041B02A 8 $941.00 SCH
041B03A 4 $525.00 SCH
041B04A 33 $3,275.00 SCH

 

[wshs-spartan]

The sql is as follows:
SELECT dbo_results.match_code, dbo_results.total_responses,
dbo_results.total_dollars, dbo_results.client_cd, dbo_results.last_update
FROM dbo_results
WHERE (((dbo_results.client_cd)="sch"));

the results are:
match_code total_responses total_dollars client_cd last_update
0507A06 332 $8,220.50 CSM 8/27/2007 15:48
0507A07 194 $3,061.00 CSM 8/27/2007 15:48
0507A24 14 $1,725.00 CSM 8/27/2007 15:48
A040503 1 $347.00 FTW 8/20/2007 11:54
A040504 1 $25.00 FTW 8/20/2007 11:54
A040508 1 $10.00 FTW 8/20/2007 11:54
A040527 1 $15.00 FTW 8/20/2007 11:54
0401BBA 210 $8,802.81 SCH
0401CIA 98 $4,420.00 SCH
0401CKA 24 $1,491.00 SCH
0401DCA 0 $0.00 SCH
041B01A 2 $220.00 SCH
041B02A 8 $941.00 SCH
041B03A 4 $525.00 SCH
041B04A 33 $3,275.00 SCH
041B05A 42 $1,980.00 SCH
041B06A 125 $2,980.00 SCH


thanks,

 

[Jerry Whittle]

That is strange.

Is dbo_results a table or another query? Not that it should matter. If
dbo_results is another query, please post its SQL.

If dbo_results.client_cd has an index on it, I'd remove the index; do a
compact and repair; then rebuild the index.

Actually a compact and repair might be a good idea anyway.

Would dbo_results.client_cd happen to have a lookup field? Sometimes that
can mess up a querey.

 

[wshs-spartan]

dbo_results is a table with dbo_results.match_code marked index-no
duplicates. However, duplicates have been entered since some clients are
using the same coding for match_code. I will try removing the index and
compacting and repairing the db and rebuilding the index with match_code &
client_cd since there are duplicates of the match_code among the different
clients.

I will post again if this doesn't solve my issue.

thanks for your help.