Results 1 to 4 of 4
  1. #1
    badmem is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Mar 2016
    Posts
    24

    How to ignore duplicates in a query

    I have a query that links three tables - A dosimeter imported table, A dosimeter allocation table and a personal info table. The dosimeter imported table is linked to the allocation table by dosimeter serial number. The allocation table is linked to the personal info by personal ID number. Dosimeters are issued to users for a period. They are then re-issued randomly to other users.



    All of the readings for all of the monitoring periods are imported into the same table, so I can't check for duplicates at this stage. When the query runs I filter the readings for the period I'm looking for - 1st Quarter etc. The first time I run the query it works. If I import more data into the import table, without deleting the original data I get duplicates in the query.

    Is there a way for the query to ignore EXACT duplicates when it runs?

    Thank you.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    set the query property UNIQUE VALUES = YES
    to remove duplicates

  3. #3
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    If I import more data into the import table, without deleting the original data I get duplicates in the query.
    When you "import more data" into the table, are you re-importing what was already there, plus some more? In other words, if there are 10 records already in your table, and you have 5 new ones, does your import process re-import the original 10 as well as the 5 new ones?

    If so, can you not just delete the existing data and re-import all of it (old and new)?

  4. #4
    badmem is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Mar 2016
    Posts
    24
    Thanks for your very quick responses. I've just realised that the problem is with the tables not the query. I import the data from a .csv file into a temporary table which has about 20 columns. I then run a query that pulls out 6 columns and appends them to the dosimeter imported table. Of course every time I import new data into the temporary table and run the append query, the new and old data is appended to the dosimeter imported table. Hence the duplicates. Stupid mistake So yes if I delete the old data from the temporary table before I import the new data - this should avoid the duplicates.

    I think I have another option - the .csv file I import has a Reading ID column which is a running count. If it is a unique number I can use it as a key field in the Dosimeter Import table. Set it to no duplicates and the append query will skip any readings that are already in the table.

    Thanks again.

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

Similar Threads

  1. Ignore duplicates in one field only
    By inhops in forum Queries
    Replies: 2
    Last Post: 06-10-2015, 02:30 AM
  2. query that will ignore a certain word
    By chr1stoper1 in forum Queries
    Replies: 2
    Last Post: 03-16-2015, 10:51 AM
  3. Ignore blank rows in a UNION ALL query
    By Access_Novice in forum Queries
    Replies: 1
    Last Post: 12-20-2014, 08:36 PM
  4. Ignore blank on query expression.
    By zero3ree in forum Access
    Replies: 3
    Last Post: 06-11-2012, 11:22 AM
  5. Replies: 2
    Last Post: 08-17-2010, 02:58 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