Results 1 to 3 of 3
  1. #1
    johnmerlino is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Oct 2010
    Posts
    97

    SELECT DISTINCT not working


    Hey all,
    The statement below is supposed to be pulling NO DUPLICATES in a new table called final_output. However, when I run it, it indeed populates the new table where multiple records could have the same name and address even though below I specify that name and address for every record must be unique:
    Code:
    SELECT DISTINCT fullName, addresses, ID INTO final_output
    FROM (SELECT possibles.fullname, PrepareForDuplicateCheck.addresses, PrepareForDuplicateCheck.id FROM possibles, PrepareForDuplicateCheck WHERE INSTR(PrepareForDuplicateCheck.names_1, possibles.fullname) > 0  Or  INSTR(PrepareForDuplicateCheck.names_2, possibles.fullname) > 0)  AS [%$##@_Alias];
    Any idea what's wrong?

    Thanks for response.

  2. #2
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    have you tried using DISTINCTROW instead?

  3. #3
    johnmerlino is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Oct 2010
    Posts
    97
    Quote Originally Posted by ajetrumpet View Post
    have you tried using DISTINCTROW instead?
    I tried DINSTINCTROW and it was about to add 350 rows to the table, so I cancelled the query. DINSTINCT adds 56 rows, but there are a number of duplicate records where name and address are identical so it should only be adding like 40 rows.

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

Similar Threads

  1. help counting distinct values
    By joo-joo-man in forum Queries
    Replies: 2
    Last Post: 10-17-2010, 05:18 AM
  2. Access DB distinct count
    By anziga in forum Queries
    Replies: 3
    Last Post: 10-12-2010, 02:20 PM
  3. Need help for distinct...
    By gunapriyan in forum Queries
    Replies: 2
    Last Post: 05-28-2010, 12:18 AM
  4. Distinct Values
    By Acramer8 in forum Reports
    Replies: 1
    Last Post: 06-15-2009, 08:37 AM
  5. Counting distinct id's
    By jqljql in forum Access
    Replies: 1
    Last Post: 09-01-2006, 07:28 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