Analyzing multiple records 1

[dheim70]

I'm pretty much a sql novice, so bare with me : ) I have a table that has Case number as a primary key and then Transaction type as another field and then there are several more fields. a record with the same case number can appear many times and will have different transaction types.

What I need to do is pull Case Numbers where a particular Case number has a Transaction Type 2 and then that same Case number also has a record that has a transaction type of 7. example of the case number and Transaction Type fields below:

12345 7
2345X 9
12345 2
2345X 2

In the example above I would want the case number 12345 (because it has a record with a Trans Type 2 and a record with a trans type 7) but would not want the case number 2345X (because it has a 2 record but not a 7)

I can't think of a way to do this? Any help is greatly appreciated!

Thanks,

D

 

[PHV]

And this ?
SELECT TransType
FROM MyTable
WHERE TransType IN (2, 7)
GROUP BY TransType
HAVING Count(*)=2

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dheim70]

I am trying to get the Case Numbers, not the Transaction Types, thanks fo giving it a look tho!

 

[PHV]

OOps, sorry, I meant this:
SELECT [Case Number]
FROM MyTable
WHERE TransType IN (2, 7)
GROUP BY [Case Number]
HAVING Count(*)=2

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dheim70]

Cool! I think that will work! Thanks