Results 1 to 4 of 4
  1. #1
    UT227 is offline Expert
    Windows 7 32bit Access 2013 32bit
    Join Date
    Feb 2016
    Posts
    581

    Separating information in a duplicate query

    I have a query that I have duplicates. The PublicContactID is duplicated. I'm trying to count only those that have the same PublicContactID and have the ContactDispositionID of 3 and 4. How can I do this? In this query, #324 has 3&4, #459 also has 3&4, #512 also has 3&4. So, the total I need is 3. Ideas?
    Click image for larger version. 

Name:	query.jpg 
Views:	9 
Size:	22.8 KB 
ID:	40723

  2. #2
    UT227 is offline Expert
    Windows 7 32bit Access 2013 32bit
    Join Date
    Feb 2016
    Posts
    581
    The SQL is:
    SELECT qryStatResultOfStop1.PublicContactID, qryStatResultOfStop1.ContactDispositionID
    FROM qryStatResultOfStop1
    WHERE (((qryStatResultOfStop1.PublicContactID) In (SELECT [PublicContactID] FROM [qryStatResultOfStop1] As Tmp GROUP BY [PublicContactID] HAVING Count(*)>1 )))
    ORDER BY qryStatResultOfStop1.PublicContactID;

  3. #3
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    not tested but perhaps try

    Code:
    SELECT PublicContactID 
    FROM (SELECT PublicContactID
               FROM qryStatResultOfStop1
               WHERE ContactDispositionID in (3,4)) as T
    GROUP BY PublicContactID
    HAVING count(PublicContactID)>1
    

  4. #4
    UT227 is offline Expert
    Windows 7 32bit Access 2013 32bit
    Join Date
    Feb 2016
    Posts
    581
    That worked perfectly. Thanks.

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

Similar Threads

  1. Duplicate information in my tables and queries
    By TheSpecialist in forum SQL Server
    Replies: 2
    Last Post: 06-14-2016, 08:35 PM
  2. Table Design: Fields with duplicate information
    By snowboarder234 in forum Access
    Replies: 3
    Last Post: 10-17-2012, 10:43 AM
  3. Getting duplicate information in my query
    By Toollady in forum Access
    Replies: 3
    Last Post: 06-14-2012, 07:03 PM
  4. Separating information in a table
    By Lupson2011 in forum Queries
    Replies: 27
    Last Post: 05-15-2012, 06:36 AM
  5. Autofill Duplicate Information
    By JohnnyK15 in forum Forms
    Replies: 2
    Last Post: 06-15-2010, 10:53 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