Getting Duplicate Records from ODBC source -- HELP!!!

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.


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!!



Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Yeah, you probably have some bad joins.  Show us your query and the primary keys of your tables.
Ronda-SAuthor Commented:
That's the thing... there IS no joins.

I did a simple query on ONE table.

The SQL :

INSERT INTO tblFASFAT ( PKTrans, dtran )
SELECT [fat Transactions].recid, [fat Transactions].dtran
FROM [fat Transactions]
WHERE ((([fat Transactions].dtran)<=#12/30/2005#));

I got 700+ Duplicate records when I did a "find duplicates" query on tblFASFAT where it's looking at the RECID.

???????  I'm stumped... it's just not following logic.

I know this sounds like an obvious question, but are you remembering to delete the existing records in tblFASFAT before performing the INSERT ?
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Ronda-SAuthor Commented:
I know you needed to ask me that question... :-)

Yes, I made sure I had an empty table.

I started out with a select query, and I was getting the duplicates, so I thought that maybe if I appended the data into a table it would make the problem disappear.  No luck. The query started out more complicated, and I kept stripping it down, removing tables, thinking it HAD to be my joins... what else could it be?

Could this be something with the ODBC?  The Visual FoxPro driver version?  

I was up until midnight last night trying to get this to work... I ran a summary query (append) that said to group by RECID and give me the MAX of dtran.  With THAT, I got no duplicates.  I then created about 3 other tables, each with subsets of the data I needed, each with the RECID.  Then,  I could put humpty dumpty back together again and have a dataset I could work with.  Not pretty, but I think it's correct for now.

I just can't see why I would have to go through such a hairy process just to get a recordset.

Any advice would be most helpful!  Thanks a bunch
How is RecID getting populated ?
When you say it is the  primary key, is it just that field in [fat Transactions] which is the primary key, or is it part of a compound ?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ronda-SAuthor Commented:
Sorry for the delay in getting back here...

The RECID is the primary key (according to the documentation from the foxpro database) of the FAT table.  They make no mention of a compound key, and when I have searched for duplicates in that table (thinking that was the issue), I have always found them to be unique.

That's why I'm so confused as to why, when I ask for a large amount of data at one time, it would somehow return with duplicates.

Here's what I'm trying to accomplish:

The FAT table contains transaction of purchases, sales, dividends, etc. of stock transactions.  The way the foxpro database is designed, there is a BALANCE field in each record, which represents the balance AFTER that particular transaction (stored calculation - YUCK).

If I want a client's portfolio balance at say, Dec 31st 2005, what I first need to do is to look at ALL transaction records that have occurred on or before that date, because I have no idea when the last transaction for an asset might be (they could hold a stock that hasn't had activity in 3 years, as an example).  I find the record with the most recent date for each asset.  Then, for that date, I look at the transaction codes (because they process dividends and interest BEFORE buys and sells, regardless of the sequence of the primary key).  Then, I look at the MAX RECID to find the correct transaction.

Having that RECID that represents the last transaction for an asset within my given time period, I then have the number of shares.  I go look at the price table, do the match, and come up with the portfolio balance.

The transaction table (FAT) is VERY large (>300K records), and this process takes 10-15 minutes to complete.  It seems that when I ask for this data for ALL clients, and it really has to chew on that table for awhile, that I get the duplication.

I have been experimenting with workarounds, including the creation of three or four queries which give me subsets of the data that I want.  Then, I use an update query to bring together the three or four queries and therefore build my dataset.

Is there a weakness with the foxpro database that could be causing this?  I have no idea how old the foxpro db is.  I'm really at a loss... I do seem to have a workaround, though, so in the absense of any logical reasoning (on the part of the foxpro database, not you!) , I will go with that.

Any last thoughts?  Thanks for your help.

Ronda-SAuthor Commented:
Thanks for your help...

I may have other issues at work causing this problem, like two versions of Access on a machine and confusion over JET.

I appreciate your time!


Sorry I've never used foxpro so I can't really comment on any possible weaknesses.  Could you migrate the whole thing over to Access, assuming you don't get any duplicates generated during the migration that is ?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.