Results 1 to 4 of 4
  1. #1
    murfeezlaw is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2012
    Posts
    26

    Finding Duplicates within a group


    I'm trying to find records that have a different location number but the rest of the address is the same. For example I want to run a query that will spit these records out because within 1 quote there shouldn't be any records that have a different location number but the same address. Can anyone point me in the right direction? Or if I've done a horrible job explaining this let me know.

    Quote # Location # Address City State Zip_Code
    0767732 1 123 Main Street New York NY 11111
    0767732 1 123 Main Street New York NY 11111
    0767732 1 123 Main Street New York NY 11111
    0767732 2 123 Main Street New York NY 11111
    0767732 2 123 Main Street New York NY 11111
    0767732 2 123 Main Street New York NY 11111

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Try:

    Query1
    SELECT DISINCT QuoteNum, LocationNum, Address, City, State, Zip_Code FROM tablename;

    Query2
    SELECT QuoteNum FROM Query1 GROUP BY QuoteNum, Address, City, State, Zip_Code HAVING Count(LocationNum)>1;

    Advise no spaces or special characters/punctuation (underscore is exception) in naming convention. Better would be QuoteNum or Quote_Num.
    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
    murfeezlaw is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2012
    Posts
    26
    Thanks for the reply. I agree about the spaces. I just typed in the column headers bc I was having trouble copying and pasting. i never use spaces in field names.

    Is there a way to do it using one query? Perhaps using a sub query?

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Often, yes. I use query builder to help structure query syntax. Then I copy/paste the sql of first query into the sql of second query - the result would be like:

    SELECT QuoteNum FROM (SELECT DISINCT QuoteNum, LocationNum, Address, City, State, Zip_Code FROM tablename) AS Query1 GROUP BY QuoteNum, Address, City, State, Zip_Code HAVING Count(LocationNum)>1;
    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. Prevent Group By eliminating duplicates
    By bruegel in forum Queries
    Replies: 3
    Last Post: 03-27-2014, 07:55 AM
  2. Finding duplicates in two fields
    By skipnick in forum Access
    Replies: 6
    Last Post: 12-10-2013, 01:29 PM
  3. Replies: 2
    Last Post: 06-13-2013, 09:10 AM
  4. Replies: 1
    Last Post: 05-02-2012, 01:33 PM
  5. Replies: 5
    Last Post: 07-29-2011, 11:54 AM

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