Results 1 to 5 of 5
  1. #1
    Vanz is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2021
    Posts
    12

    Prevent duplicate record

    Hi all,



    i am currently working on a project that required the use of access. I have already figure out on how to do import, export of excel and realize that they might be duplication records in the database. May i know how can i prevent user to import data that are already exist in the access database?

    I do know that we can set primary key in the table but it does not seem to work. Attached is my sample data. Seek your kind help! thanks.

    The primary key = GL.Sample data.zip

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Too late in the hour for me to download your file and start looking at it. Best approach is to either link to the spreadsheet as you would a table OR import or use transferspreadsheet function to put 'raw' data into Access in a staging table. Then append to the final/destination table in Access, which has all the settings you need (primary key, index(es), data types and so on). If 123 comes across from Excel as text, when you append it to the final table, it will become a number type if that's how you define the field. You prevent the duplication of records by defining a unique index, which could be a single field or if need be, 2 or more fields.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Key the table on the fields you don't want to duplicate.
    or,
    if that's not possible, make a select query , Q1, bring in the source and target tables,
    Make an outer join to show what is in the source table (to import) that is NOT in the target table.
    Then make Q2 to append Q1.

  4. #4
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Vanz,

    A primary key uniquely defines each record in the table. All the GK values are identical in your sample data???

    What makes a duplicate record in your data? More clarification required.
    I think you'll need an autonumber PK assigned by Access to your data on insert to table, AND
    a unique composite index on those fields that uniquely identify each record.

  5. #5
    Vanz is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2021
    Posts
    12
    I can only use GL as my unique key. As the other fields in this report might appear in the other reports but the only field that will change is the GL. Hence, i have a hard time trying to prevent duplicate records

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

Similar Threads

  1. Prevent duplicate value
    By IvanGAa in forum Forms
    Replies: 1
    Last Post: 03-11-2020, 01:18 AM
  2. Replies: 12
    Last Post: 06-26-2017, 02:40 PM
  3. Prevent Duplicate Entries
    By Abhijeet in forum Forms
    Replies: 2
    Last Post: 08-23-2015, 12:15 AM
  4. How to prevent duplicate data
    By ariansman in forum Forms
    Replies: 2
    Last Post: 08-11-2015, 06:25 AM
  5. Prevent Duplicate Entry
    By kilosierra71 in forum Forms
    Replies: 4
    Last Post: 07-28-2011, 03:08 PM

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