Results 1 to 4 of 4
  1. #1
    gavlap is offline Novice
    Windows XP Access 2007
    Join Date
    Oct 2011
    Posts
    13

    Any ideas to prevent duplicating records when importing data

    We are a travel company and I am just setting up a new database with two tables - [Client_Table] & [Enquiry_Table].



    Most exisiting clients call in when they want to make a new enquiry so the 'user' can go it to a form which creates a 'new enquiry' for that client.

    We also download 'new enquiries' from our website. This data includes info that goes in to the [Client_Table] and [Enquiry_Table]. They download in to a XL spreadsheet. Currently I copy and paste the data (not sure if there's a better way) from the XL speadsheet in to a query. This query creates a new client record & a new enquiry record which are both linked by a primary key [Client_ID].

    This all works however the problem I have is that sometimes existing clients enquire through the website. They do fill in a field to say they're an existing client but if I paste them in to the query as explained above it creates a duplicate client record.

    Can anyone think of a way of getting around this?



    The only way around this I can think of is it to take out any exisitng clients from the XL spreadsheet first, search for their Client_ID and then paste these enquiries seperately with their Client_Id's in to different query which only adds a new record to the [Enquiry_Table] and links them to their exisitng record in the [Client_Table]. Obviously this will be quite time consuming and is subject to human error so does anyone have any better ways around this problem???


    Any ideas greatfully received

  2. #2
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    I would import the spreadsheet data into a new table in Access, then you can use the unmatched query wizard to compare the new data to your existing client data. You will then create an append query to add the unmatched records (i.e. new clients) to the client table. Of course, if there are spelling differences in the client name (and whatever other fields you use to match), the unmatched query will see those as new clients, so you still might have to do some manual checking of records before appending. You can then also append the applicable records to the enquiry table (you will have to have join between the client table and the imported data table in order to grab the client's primary key value).

  3. #3
    gavlap is offline Novice
    Windows XP Access 2007
    Join Date
    Oct 2011
    Posts
    13
    thanks jzwp11 - I'm not that agreat at using Access so I'm going to have a read up and see if I can make that work.

  4. #4
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    OK. Please let us know if you have any additional questions after you have had time to research it.

    As a caution, I would do all of the testing on a copy of your database, not on your actual working database.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Report duplicating data
    By tarhim47 in forum Reports
    Replies: 3
    Last Post: 05-03-2011, 08:31 AM
  2. Duplicating data in query
    By thart21 in forum Queries
    Replies: 3
    Last Post: 04-07-2011, 10:50 AM
  3. De-duplicating new data
    By BassettProvidentia in forum Queries
    Replies: 7
    Last Post: 03-03-2011, 02:40 PM
  4. duplicating records
    By kstyles in forum Queries
    Replies: 7
    Last Post: 12-31-2010, 02:31 PM
  5. Prevent Users from Deleting Records
    By DocOrganizer in forum Access
    Replies: 15
    Last Post: 11-30-2010, 02:57 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums