Number of records discrepancy - only change is sorting

I have an Access 2003 database with a query that is a left outer join of a table to another query. If I didn't sort that final query, I got 42 records. If I sorted the final query by the 2 joined fields, I got 43 records. No other changes were made to the query.

To verify this, I took the query, copied it, applied the sort with no other changes, and the record count went up by one. Perplexed, I copied the results into Excel, sorted, and 开发者_高级运维compared row by row. I discovered one record was duplicated (all fields were exactly the same), where there were actually no duplicate records in the source table and query.

I would think this is a bug, and I know there are a few in Access, but has anyone heard of this behavior before?

It is possible that you have a corrupt index. It may be worth taking a back up and then compacting and repairing the database, which should rebuild the indexes.

I had a similar problem on Access 2003 where I had duplicate records where 1 field was an autonumber which should up twice. My query was :

SELECT qry_Tasks_with_Names.*, Location.Location, WorkRequests.Element, WorkRequests.Site_ID
FROM ((WorkRequests LEFT JOIN Location ON WorkRequests.Site_ID=Location.LocationID) INNER JOIN qry_Tasks_with_Names_by_Individual ON WorkRequests.Work_Request_ID=qry_Tasks_with_Names_by_Individual.Work_Request_ID) INNER JOIN qry_Tasks_with_Names ON WorkRequests.Work_Request_ID=qry_Tasks_with_Names.Work_Request_ID
WHERE (WorkRequests.Site_ID=Forms!TaskList!comboFilter_by_Site Or Forms!TaskList!comboFilter_by_Site Is Null) And (qry_Tasks_with_Names.Assigned_to_User_ID=forms!taskList!comboFilter_by_Person Or forms!taskList!comboFilter_by_Person Is Null) And (qry_Tasks_with_Names.Assigned_to_Team_ID=forms!taskList!comboFilter_by_Team Or forms!taskList!comboFilter_by_Team Is Null) And qry_Tasks_with_Names.Assigned_to_User_ID>0
ORDER BY qry_Tasks_with_Names.SLA_Due;

The above was initially created by the query designer, but it gets itself confused at this level and it also seems that I do.

Once I removed the inner join on qry_Tasks_with_Names_by_Individual all was OK. No idea why, but hopefully this may save someone else some tears if they have the same problem.