Query returns wrong records

[Meredith Bliss]

Using Access 2007, I've recently encountered a problem where a query returns
the expected number of results, but the wrong records. If I edit the SQL
query to say "SELECT DISTINCT" instead of "SELECT", I get the expected
results (or if I do an aggregate query).

Based on my limited testing, it appears that with the SELECT query, the
records are all populated with the first record from the table with a
matching value in the table's index field. These records may (but mostly do
not) agree with the selection criteria in the WHERE clause. (Of course if
they did match the criteria, it wouldn't be a problem ... duh.)

Presumably, this wouldn't be a problem selecting records from a table where
the index field had unique values. But should a unique values index be
necessary for the WHERE clause to select the desired records???

Thanks for any thoughts on this,

 

[Dale Fye]

Meredith,

Post the SQL of your query. It will be a lot easier to identify potential
problems if we can see the query that is causing the problems.

 

[Meredith Bliss]

Here's the most recent example (edited for readability by removing all of the
excess table names that MS adds):

SELECT UNIQ, STREETID, ADD_FULL, UNIT_NUM, ADD_NUM, STDIR, STNAME, STTYPE
FROM dbo_COBADDSx
WHERE ADD_FULL Like "*4925*" AND STNAME="Jamieson";


COBADDS is an ODBC-linked table of addresses where "UNIQ" is the primary
key, and according to properties is indexed "Yes (No Duplicates)" but for
many records the field is null. When I look at the table, the first record
has no UNIQ value, and is for 15760 SW Davis Rd, and that is the record
repeated for the 59 records retrieved by my query. (There are 59 apartments
at the Jamieson Rd address.)

 

[Dale Fye]

Meredith,

What is in the ADD_FULL column for the 59 records that get returned?

The PK field is generally the field that results are sorted by if there is
no explicit sort order. I'm concerned that you have a PK field with NULL
values. How did that happen?

Dale

 

[Meredith Bliss]

ADD_FULL is the full address, made up from the street number (ADD_NUM),
street name (STNAME) and so on. The query returns 59 copies of the first
record with a null UNIQ field, which is "15760 SW DAVIS RD". As to why the PK
has null values, well, I'm not allowed to touch the data and I doubt anyone
would be willing to take responsibility for that ;-)

I do get the same behavior from another table that has a PK with duplicated
values, the query just returns as many copies of the first record as there
are differences in other fields. SELECT DISTINCT gives the expected results
with that one, as well.

Just for grins, I added an ORDER BY ADD_FULL but that had no effect on the
results.