Strange table join result

[Henry]

Hi,

I'm using Access2000 and experience very funny join result.
I have two tables: IOQ and Company, join with a field call
CRN. CRN is the primary key in the Company table.

I write a query:
----------------------------------------------------
SELECT Company.CRN, IOQ.CRN, IOQ.PID, Abs([IOQ].[CRN]-
[Company].[CRN]) AS CRNdiff
FROM Company INNER JOIN IOQ ON Company.CRN = IOQ.CRN
WHERE (((Company.CRN)=8221));
----------------------------------------------------

and get the following result:
----------------------------------------------------
Company.CRN IOQ.CRN PID CRNdiff

8221 8221 6077 0
8221 8221 11144 0
8221 8221 11145 0
8221 8221 5188 0
8221 8221 5093 0
8221 8221 5896 0
8221 5740 10639 2481 <<<<<<
8221 8221 5282 0
8221 8221 11162 0
8221 8221 6265 0
8221 8221 5832 0
8221 8221 6155 0
8221 8221 5495 0
----------------------------------------------------

Can someone explains why 8221 in table Company will join
with 5740 from table IOQ? Thanks in advance for any help
or explaination about this.

cheers
Henry

 

[Allen Browne]

HI Henry

This looks like a corrupted index. You can probably repair it with:
Tools | Database Utilities | Compact/Repair

If that does not work, follow the 8 steps of symptom #2 in this article:
Recovering from Corruption
at:
http://members.iinet.net.au/~allenbrowne/ser-47.html