Results 1 to 3 of 3
  1. #1
    Amras is offline Novice
    Windows 2K Access 2010 64bit
    Join Date
    Jul 2013
    Location
    Sioux Falls, SD
    Posts
    29

    Update one field in an Access record when Excel import comes across duplicate Primary Key record

    Good afternoon, and thank you for taking the time to read my dilemma. Sorry - trying to edit three fields rather than just a single field.



    I've been tasked with creating a database that will keep track of eligible accounts. I'll receive several Excel spreadsheets throughout the week to be imported; some will be new records, so will be updates to existing records. Importing new records into the table isn't a problem, but when one or several of those records are duplicates, I'll need to update three fields in the existing record to indicate that that record/customer has been contacted. My first attempt at writing the necessary SQL resulted in this:

    Code:
    INSERT INTO tblScrappyData ( OBC, Eligible, EDate )SELECT True AS OBC, False AS Eligible, Date() AS EDate
    FROM tblContactedListTemp
    WHERE (((tblContactedListTemp.[Member ID])=[tblScrappyData].[MID]));
    When I run that code I'm greeted with Access' Enter Parameter Value window, requesting that I enter tblScrappyData.MID. Is this not something that can be done with a query? If that isn't possible, how do I accomplish this?

    I hope I've communicated my problem sufficiently. 'preciate any help I can get.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    INSERT adds new records, does not edit existing.

    Should probably have a JOIN clause and use UPDATE action.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Amras is offline Novice
    Windows 2K Access 2010 64bit
    Join Date
    Jul 2013
    Location
    Sioux Falls, SD
    Posts
    29
    ....and that's why I'm ASKING the questions, and you're ANSWERING them, June. Thank you for pointing-out my mistake. It's working perfectly since I made that change.

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

Similar Threads

  1. Replies: 7
    Last Post: 01-04-2018, 06:35 PM
  2. VBA code import and Add record from excel to MS access
    By darkdarkwt in forum Import/Export Data
    Replies: 2
    Last Post: 07-20-2015, 02:23 AM
  3. Duplicate Lines in Excel Import Mess up Primary Key
    By accessdave84 in forum Import/Export Data
    Replies: 2
    Last Post: 03-25-2015, 07:03 AM
  4. Replies: 1
    Last Post: 02-22-2013, 02:09 PM
  5. Replies: 2
    Last Post: 03-25-2011, 12:22 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