Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Analyzing multiple records 1

Status
Not open for further replies.

dheim70

Programmer
Sep 23, 2011
14
US
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
 
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
 
I am trying to get the Case Numbers, not the Transaction Types, thanks fo giving it a look tho!
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top