Hi There --
I've got a problem with gathering data from a large transaction table (300,000 records). It's a foxpro database, and I've got an ODBC link to an Access 2003 db.
I've been getting unusual results, and today have tried to get to the lowest common denominator to determine the problem.
I created an append query into an empty table, to insert nothing more than the RECID (primary key of the transaction table) and the DTRAN (transaction date), for all records where the date was <= 12/30/05.
The results give me 275,000 records (approx).
When I view the results of the append query, I find multiple hundreds of records (it varies per run) where I'm getting DUPLICATE RECID numbers, for different dates.
When I do a search on the foxpro table for one of the duplicate RECIDs, there is only one.
HOW COULD THIS BE HAPPENING?????
I've been dealing with side-effects of this problem, and have just now pinpointed exactly what seems to be happening. I was having trouble when it was a SELECT query, and changed it to an APPEND query in order to troubleshoot. I have the temp table residing in a separate db, linked back to the main access db.
Any thoughts to shed some light on this would be MOST helpful... people are getting grumpy over here!!