Wrong result from INNER JOIN query if not sorting.

[Afzal]

Hi.
I am surprised by different result of a simple INNER JOIN query based on
SORTING the result.
Situation: There is Customers and Inventory table. CustomerID, a text
field, is in both of the tables. I was trying to find records that match
and those don’t match in the Inventory table based on CustomerIDs. Here is
the query find the matched records:
SELECT Inventory.customerID, Inventory.CutomerName
FROM Inventory INNER JOIN CustomerList ON Inventory.customerID =
CustomerList.customerID;

The result returned 2052 records. But, as soon as I sort on any column,
result changes to 1968. I know that 1968 is the correct answer because
another query “Inventory Without Matching CustomerList†returns 96 records
(doesn’t mater how I sort it; out of 2064 total records).

To find out what was going on, I printed some screen shots of two different
results. It seems like Access is printing some of the records twice in the
result pane; as soon as I sort, the duplicate records go away.

Is there a bug in Access 2003 (11.6355.6408) SP1 ?

(I can duplicate this problem easily)

 

[MGFoster]

Just a guess: your indexes are messed up. Try a compact & repair.

 

[Afzal]

I checked and re-did the indexes. I compacted and repair the db. I converted
the db to 2002/2003 format from 2000.
Even if I inculde the ORDER BY command, query still returns wrong result.
Query only returns correct result if I press the sorting button "A-Z" up on
the tool bar. From then on forward, it always is correct.
On a new query, even if I copy and past the same sql code from working
query, I returns wrong result until I press the sort button on the tool bar
"A-Z".

 

[david epsom dot com dot au]

Is there a bug in Access 2003 (11.6355.6408) SP1 ?

This is probably not the number you should be looking
at. What is the version of msjet40.dll?

Are these tables local tables in the MDB?

(david)

 

[Afzal]

The version for msjet40.dll is 4.0.8618.0
All files are local.
There are duplicate records in customers table. But, that should not produce
duplicate records from inventory table with INNER JOIN (I may be wrong on
that).

But,
A gal from Microsoft seems to say that, the reason, I get correct answer
when I manually sort the result, is that Manual Sort Button has an additional
undocumented feature which has same effect has a DISTINCT keyword in the
query.
That is a strange. I thought ORDER BY clause should be same as manual sort...

Thanks,
Afzal

 

[wei]

Hi.
I am surprised by different result of a simple INNER JOIN query based on
SORTING the result.
Situation: There is Customers and Inventory table. CustomerID, a text
field, is in both of the tables. I was trying to find records that match
and those don’t match in the Inventory table based on CustomerIDs. Here
is
the query find the matched records:
SELECT Inventory.customerID, Inventory.CutomerName
FROM Inventory INNER JOIN CustomerList ON Inventory.customerID =
CustomerList.customerID;

The result returned 2052 records. But, as soon as I sort on any column,
result changes to 1968. I know that 1968 is the correct answer because
another query “Inventory Without Matching CustomerList� returns 96
records
(doesn’t mater how I sort it; out of 2064 total records).

To find out what was going on, I printed some screen shots of two
different
results. It seems like Access is printing some of the records twice in
the
result pane; as soon as I sort, the duplicate records go away.

Is there a bug in Access 2003 (11.6355.6408) SP1 ?

(I can duplicate this problem easily)

Re;I can duplicate this problem easily