Combining records in a single table 1

[rjm13]

Hi All,

I have an issue that I don't know how to resolve (and don't know why it was done this way originally). I have a table with an identifier field (contract number), Service Category, a value field, and then a field that has a value of either "clients" or "Encounters".

What I need to do via a query is get the following:

Contract Number, Service Category, Client Value, Encounter Value all on the same line. and to let you know contract number, and service category combinations only occur twice

Is this possible

thanks,

R

 

[PHV]

A starting point:
SELECT C.[contract number], C.[Service Category], C.[value field] AS [Client Value], E.[value field] AS [Encounter Value]
FROM yourTable AS C INNER JOIN yourTable AS E ON C.[contract number]=E.[contract number] AND C.[Service Category]=E.[Service Category]
WHERE C.[some field]='clients' AND E.[some field]='Encounters'

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[rjm13]

Thanks PHV, with a little tweaking, that worked out just fine.

Thanks again

R