Query producing different results each time it is run

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?

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.

"Tim Green" wrote:

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?

What is 'wrong'? Could you provide examples?

Please post the SQL statement for the query.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Tim Green" wrote:

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.

"Tim Green" wrote:

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?