Results 1 to 4 of 4
  1. #1
    keith2511 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2017
    Location
    UK
    Posts
    45

    Duplicate records in lookup table

    I have a database containing invoice data imported from suppliers, which is updated each month.



    I populate additional fields in the invoice table using an update query and a relationship to a lookup table. For example, products are categorised by product type. (The relationship is a match of supplier name and product description).

    After the update I check the additional fields for blanks and if any exist, add a new record to the lookup table for that combination of supplier name and product description together with a product type. I then rerun the update query and the blank fields are then populated with values from the lookup table.

    However I have noticed that of the 6,000 records in the lookup table there are 300 instances of duplicate records. When I inspect these duplicates they appear to be identical i.e. field lengths are the same and there are no leading or trailing spaces.

    What might be happening here and what could I do to avoid it happening again?

    Thanks.

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Please provide a few examples of what you consider duplicates.

  3. #3
    Bullschmidt's Avatar
    Bullschmidt is offline Freelance DB Developer
    Windows 10 Office 365
    Join Date
    Mar 2020
    Location
    USA
    Posts
    64
    It sounds like you have the following fields in your lookup table:
    - supplier name
    - product description
    - product type

    So how about in table design mode shift-highlight all three fields and right-click as Primary Key to give you a multiple-fields primary key. This would not allow duplicates and would give you an alert whenever you tried to do something to add a duplicate record.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Not necessary to be set as primary key, could just set as compound index.
    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.

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

Similar Threads

  1. Table Duplicate Records Question
    By shoelesscraig in forum Access
    Replies: 6
    Last Post: 04-03-2019, 03:39 AM
  2. Replies: 4
    Last Post: 04-09-2018, 07:57 AM
  3. Join table - duplicate records
    By Lukael in forum Programming
    Replies: 10
    Last Post: 05-26-2016, 02:11 AM
  4. check duplicate records within same table
    By cthorn112 in forum Queries
    Replies: 0
    Last Post: 06-19-2012, 12:23 PM
  5. Replies: 2
    Last Post: 04-13-2012, 12:53 AM

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