Show Duplicates Across 2 Queries

[QandAdam]

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'

<tbody>
</tbody>

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!

 

[welshgasman]

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

HTH

 

[QandAdam]

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

<tbody>
</tbody>

I want it to look like this:

RegNo	Status
123	Active
123	Closed

<tbody>
</tbody>

RegNo '456' is ignored because although it is a duplicate RegNo, the Status on both records is 'Closed'

 

[xenou]

Distinct, then count ...

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):

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')