Only Select Records with 2 or More of the Same Value 2

[johnfrani]

This query ...

SELECT tbl_Person.Lname, tbl_Case.CaseNo, tbl_Case.Date, tbl_Charges.Section
FROM (tbl_Case INNER JOIN tbl_Person ON tbl_Case.CaseID = tbl_Person.CaseID) INNER JOIN tbl_Charges ON (tbl_Person.PersonID = tbl_Charges.PersonID) AND (tbl_Person.PersonID = tbl_Charges.PersonID)
ORDER BY tbl_Person.Lname;


Produces this output ...

Last CaseNo Date Section
Brown 3 12/3/2005 653.22
Edmonds 2 12/2/2005 653.22
Green 2 12/2/2005 647(b)
Johnson 1 12/1/2005 647(b)
Jones 1 12/1/2005 653.22
Jones 4 4/4/2006 653.22
Jones 6 5/5/2005 647(f)
Jones 1 12/1/2005 647(b)
Jones 6 5/5/2005 653.22


How can I construct a query that will return records for a person - only - when they have two or more unique CaseNo records.


The new query should only return these records ...

Last CaseNo Date Section
Jones 1 12/1/2005 653.22
Jones 4 4/4/2006 653.22
Jones 6 5/5/2005 647(f)
Jones 1 12/1/2005 647(b)
Jones 6 5/5/2005 653.22

if possible with a total unique CaseNo field holding the number of unique Case Numbers for each person.

These records are selected because Jones has three unique values (1, 4, 6), among the five records in the result set of the CaseNo field.

Any help is appreciated.
Thanks
John

 

[r937]

you know, with the parentheses around your first join, and the fact that you've got the exact same join condition ANDed twice, i'd say you were using MS Access...

so if you are, the following won't work, because Access doesn't support COUNT DISTINCT

SELECT tbl_Person.Lname
     , tbl_Case.CaseNo
     , tbl_Case.Date
     , tbl_Charges.Section 
  FROM tbl_Case 
INNER 
  JOIN tbl_Person 
    ON tbl_Case.CaseID 
     = tbl_Person.CaseID
INNER 
  JOIN tbl_Charges 
    ON tbl_Person.PersonID 
     = tbl_Charges.PersonID
 where tbl_Person.Lname
    in (
       select Lname     
         from tbl_Person
       group
           by Lname
       having count(distinct CaseNo)
           >= 2
       )    
ORDER 
    BY tbl_Person.Lname

r937.com | rudy.ca

 

[r937]

oh, i am a right idiot

of course you're using Access, this is the Access forum, not the SQL Server forum, my apologies

i'll show you how to use a query of a query to do the same thing as COUNT DISTINCT, hold on a sec...

r937.com | rudy.ca

 

[r937]

create a query called DistinctPersonCases --

select distinct
       PersonID
     , CaseNo     
  from tbl_Person

now create another query called PersonCaseCounts2OrMore --

select PersonID
     , count(*) as Cases
  from DistinctPersonCases
group
    by PersonID
having count(*) >= 2

finally, use this in your main query --

SELECT tbl_Person.Lname
     , tbl_Case.CaseNo
     , tbl_Case.Date
     , tbl_Charges.Section 
  FROM (
       (
       tbl_Case 
INNER 
  JOIN tbl_Person 
    ON tbl_Case.CaseID 
     = tbl_Person.CaseID
       )
INNER 
  JOIN PersonCaseCounts2OrMore 
    ON PersonCaseCounts2OrMore.PersonID 
     = tbl_Person.PersonID
       )       
INNER 
  JOIN tbl_Charges 
    ON tbl_Person.PersonID 
     = tbl_Charges.PersonID
ORDER 
    BY tbl_Person.Lname

r937.com | rudy.ca

 

[MichaelRed]

or. the minor variation

SELECT Count(tblCase.Last) AS NumLast, tblCase.Last
FROM tblCase
GROUP BY tblCase.Last
HAVING (((Count(tblCase.Last))>1));

SELECT tblCase.Last, tblCase.CaseNo, tblCase.dtDate, tblCase.Section
FROM qryCountLast INNER JOIN tblCase ON qryCountLast.Last = tblCase.Last;

where (hopefully) the first code block represents "qryCountLast" and both refer tot he same table (abbreviated here as "tblCase".




MichaelRed

 

[johnfrani]

Thanks for your help.
Using your technique I got the query working perfectly.
John