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

Posted on 2006-04-17
Last Modified: 2008-03-06
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!!



Question by:Ronda-S
    LVL 42

    Expert Comment

    Yeah, you probably have some bad joins.  Show us your query and the primary keys of your tables.

    Author Comment

    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.

    LVL 17

    Expert Comment

    I know this sounds like an obvious question, but are you remembering to delete the existing records in tblFASFAT before performing the INSERT ?

    Author Comment

    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
    LVL 17

    Accepted Solution

    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 ?

    Author Comment

    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.


    Author Comment

    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!


    LVL 17

    Expert Comment

    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 ?

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    Join & Write a Comment

    The first two articles in this short series — Using a Criteria Form to Filter Records ( and Building a Custom Filter ( — discuss in some detail how a form can be…
    This isn't a frequent question on EE. I must have seen it three or four times (among several thousand questions). However, I use this trick quite often, most frequently as a delayed Current event. A form does not expose it's calculation dependenc…
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    25 Experts available now in Live!

    Get 1:1 Help Now