Query producing different results each time it is run

[Tim Green]

I have a database with a table linked to a FoxPro database. The table has
over 600,000 records.

I run a query that returns about 1500 of those records. But the result
changes every time I run it!

It is an accounts database, and I am querying a closed period, so the FoxPro
table has not changed (or at least the records selected by the query have
not, though more recent data will have been added). I have run the query
multiple times and get a different result each time. Out of 500 or so
accounts, 2 or 3 are wrong each time I run the query, but it's never the same
accounts. Since each account is almost always correct, I think it's safe to
assume the query is correct (though not necessarily effiecient, of course).

My set-up is that I have written a query on this table, and an append query
based on that. When I query the table that was appended to, I find that my
account totals are wrong, and have to run the corresponding delete query.

Is this set-up inherently unstable? If the FoxPro table is just too large
for Access to cope, can anyone suggest another way of doing this?

 

[Tim Green]

Actually, scrap that part about the intermediate query, I was thinking of
something else. This is an append query that directly queries the external
table.

 

[Jerry Whittle]

What is 'wrong'? Could you provide examples?

Please post the SQL statement for the query.