Query to return only records with multiple IDs 2

[jpkeller55]

I am sure this is a simple question. I have the following query written and I would like to return only records where the ID number is repeated more than once

SELECT [21_Status_Project].ID, [21_Status_Project].LAST, [21_Status_Project].FIRST, [21_Status_Project].THERAPY_TYPE
FROM 21_Status_Project
WHERE ((([21_Status_Project].THERAPY_TYPE) Like "P*"));

So, the current code returns something like this:

ID LAST FIRST THERAPY
123456 JONES BOB PRNV
456789 SMITH DON PRNV
456789 SMITH DON PRNH
789123 HAYES DAN PRNV
789123 HAYES DAN PRNC

I would like the query only to return records with multiples of the ID number like this:
ID LAST FIRST THERAPY
456789 SMITH DON PRNV
456789 SMITH DON PRNH
789123 HAYES DAN PRNV
789123 HAYES DAN PRNC

Thanks Jim

 

[lespaul]

SELECT [21_Status_Project].ID, [21_Status_Project].LAST, [21_Status_Project].FIRST, [21_Status_Project].THERAPY_TYPE
FROM 21_Status_Project
WHERE [21_Status_Project].THERAPY_TYPE) Like "P*"
HAVING Count(ID) > 1;

Check out Thread701-1262760 for an explanation of the HAVING clause.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[jpkeller55]

I added the HAVING clause and I get the message:

"You tried to execute a query that does not include the specific expression 'ID' as part of an aggregate function"

What am I missing? Thanks...Jim

 

[lespaul]

how about:

SELECT ID, LAST, FIRST, THERAPY_TYPE
FROM 21_Status_Project
WHERE THERAPY_TYPE Like "P*"
GROUP BY ID, LAST, FIRST, THERAPY_TYPE
HAVING Count(ID) > 1;

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[jpkeller55]

OK, I tried that but go no records returned.
It is interesting, if I change the HAVING Count(ID)>0, I get all the records but when I make it >1, I get nothing returned. ??

 

[jpkeller55]

I am not sure, but I think what is happening is that the Count(ID) clause is counting the ID in each record which is 1. So, when we put the Having Count(ID)>1 clause in, it returns zero records because each ID count = 1. How do I fix this?

 

[lespaul]

what happens if you do:

SELECT ID, COUNT(*) FROM 21_Status_Project WHERE Therapy_Type LIKE "P*" GROUP BY ID ORDER BY 2 DESC

do you see any IDs that have more than 1 in the second field?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[jpkeller55]

OK, I think that is getting closer. That returned a list of all the IDs and the 2nd column listed the count of each ID number. The range was from 3 to 1

 

[ByteMyzer]

Try:

SELECT T1.ID, T1.LAST, T1.FIRST, T1.THERAPY_TYPE
FROM [21_Status_Project] T1
WHERE T1.THERAPY_TYPE Like 'P*'
AND T1.ID IN
 (SELECT T2.ID
  FROM [21_Status_Project] T2
  WHERE T2.THERAPY_TYPE Like 'P*'
  GROUP BY T2.ID
  HAVING COUNT(T2.ID)>1)
;

 

[jpkeller55]

ByteMyzer, that worked! Thank you very much.

Thank you too lespaul for your help. I appreciate your patience and information on the HAVING clause!

Jim