porting Access query to SQL Server 2005

[MSBrady]

Oy!
Access 2003
SQL Server 2005

I need some help porting the following query from Access to SQL Server. I am not familiar enough with the PIVOT and TRANSFORM statements yet to understand fully what's going on.

Access query

TRANSFORM Sum([mtbl Combined Totals].SumOfBalance) AS SumOfSumOfBalance
SELECT [mtbl Combined Totals].Client, [mtbl Combined Totals].Product, [mtbl Combined Totals].Job, Sum([mtbl Combined Totals].SumOfBalance) AS [Total Of SumOfBalance]
FROM [mtbl Combined Totals]
GROUP BY [mtbl Combined Totals].Client, [mtbl Combined Totals].Product, [mtbl Combined Totals].Job
PIVOT [mtbl Combined Totals].Source;

These are my first 2 attempts but neither of them fully do what the query does in Access; which is return the result set pivoted and displays all of the columns involved:

SQL Attempt 1

SELECT 'Client' = Client
, 'Product' = Product
, 'Job' = Job
, 'Source' = Source
, 'SumOfSumOfBalance' = SUM(SumOfBalance)
FROM [mtbl Combined Totals]
--PIVOT (
--	Sum(SumOfBalance) 
--	FOR Source IN ([GL], [PJTRAN])
--) as pvt
GROUP BY Client, Product, Job, Source

That one returns all of the columns I need, but it isn't pivoted and therefore contains twice as many records as the Access query.

SQL Attempt 2

SELECT 'Client' = Client
, 'Product' = Product
, 'Job' = Job
--, 'Source' = Source
--, 'SumOfSumOfBalance' = SUM(SumOfBalance)
FROM [mtbl Combined Totals]
PIVOT (
	Sum(SumOfBalance) 
	FOR Source IN ([GL], [PJTRAN])
) as pvt

That one pivots the data and returns the same amount of rows that the Access query returns but it won't allow me to SELECT Source and SumOfBalance from the TABLE (presumably b/c they are in the PIVOT statement yet they aren’t returned).

thanks,
Michael