Results 1 to 4 of 4

Thread: Show Duplicates Across 2 Queries

  1. #1
    New Member
    Join Date
    Jan 2019
    Posts
    30
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Show Duplicates Across 2 Queries

    I have 2 queries:

    Both Queries have the same fields and are based on the same table (tblBusinesses) but have different criteria

    Query Name Criteria
    OpenBusinesses 'Status' field equals 'Open'
    ClosedBusinesses 'Status' field is not equal to 'Open'

    Is there a way to find records which appear in both queries? The Duplicate field will be the 'RegNo' within the table.

    Basically, I'm trying to find any Duplicate Businesses Records which have different Statuses. If there is a better way of doing this then I'm all ears!

    Thanks in advance!

  2. #2
    Board Regular
    Join Date
    May 2013
    Posts
    656
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Show Duplicates Across 2 Queries

    Remove status field, group on RegNo and add a count with criteria > 1 ?

    HTH
    Office 2007
    Access novice. Sometimes trying to give something back

  3. #3
    New Member
    Join Date
    Jan 2019
    Posts
    30
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Show Duplicates Across 2 Queries

    Quote Originally Posted by welshgasman View Post
    Remove status field, group on RegNo and add a count with criteria > 1 ?

    HTH
    How would this filter to only show the duplicate RegNos with different statuses?

    E.g.
    Instead of this:
    RegNo Status
    123 Active
    123 Closed
    456 Closed
    456 Closed

    I want it to look like this:
    RegNo Status
    123 Active
    123 Closed

    RegNo '456' is ignored because although it is a duplicate RegNo, the Status on both records is 'Closed'
    Last edited by QandAdam; Jul 5th, 2019 at 01:46 PM.

  4. #4
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    16,475
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Show Duplicates Across 2 Queries

    Distinct, then count ...
    Code:
    select count(RegNo) as Total
    from
    (
    select distinct RegNo, Status
    from [Businesses]
    ) T
    having count(T.RegNo) > 1

    Another way (not necessarily distinct if there are even three or more records, some open others closed):
    Code:
    select t1.RegNo from [Businesses] t1 
    where 
        t1.Status = 'Open' 
    and exists (select * from [Businesses] t2 where t2.RegNo = t1.RegNo and t2.Status = 'Closed')

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •