Convert MS Access query to SQL Server

>My command of T-SQL is moderate at best.
Eyeballeth my article Migrating your Access Queries to SQL Server Transact-SQL which should help you a ton.

Well SQL doesn't have straight forward replacement for cross tab queries of Access...the only way is to use the PIVOT and make the necessary changes in order to accomplish the desired outcome...If you have unknown number of columns then you should utilize Dynamic SQL.
For something for concrete if you fail to find a good similar case on the Internet a small sample of your data would be most helpful

This is what I have so far:

SELECT * FROM
(
SELECT AR_Records3.CoCd,
       T_ComResFlag.ComRes,
       AR_Records3.Customer,
       AR_Records3.CustName,
       AR_Records3.CGrp,
       Sum(AR_Records3.AmtS) AS [Total AR],
         CASE WHEN Ar_Records3.DPD>365 THEN '6-366 and Over' ELSE
            CASE WHEN Ar_Records3.DPD<1 THEN '0-Current' ELSE T_AgingBuckets.AgeBuck
            END
         END      AS SumOfAmtS
FROM AR_Records3 LEFT JOIN T_AgingBuckets ON AR_Records3.DPD = T_AgingBuckets.DaysPastDue
        INNER JOIN T_ComResFlag ON AR_Records3.SDst = T_ComResFlag.SDst
WHERE T_ComResFlag.ComRes='Com' Or T_ComResFlag.ComRes='Res'
GROUP BY AR_Records3.CoCd,
         T_ComResFlag.ComRes,
         AR_Records3.Customer,
         AR_Records3.CustName,
         AR_Records3.CGrp,
             Ar_Records3.DPD,
             T_AgingBuckets.AgeBuck
) T
PIVOT
(
AVG(SumOfAmtS)
      FOR T_AgingBuckets.AgeBuck IN (['1-1 to 30'],['2-31 to 60'],['3-61 to 90'],['4-91 to 180'])
      ) P;

But I'm getting the following error:
The column prefix 'T_AgingBuckets' does not match with a table name or alias name used in the query.

Where am I making my mistake?

I guess ...without the data....

SELECT *
FROM (
	SELECT AR_Records3.CoCd
		,T_ComResFlag.ComRes
		,AR_Records3.Customer
		,AR_Records3.CustName
		,AR_Records3.CGrp
		,Sum(AR_Records3.AmtS) AS [Total AR]
		,CASE 
			WHEN Ar_Records3.DPD > 365
				THEN '6-366 and Over'
			ELSE CASE 
					WHEN Ar_Records3.DPD < 1
						THEN '0-Current'
					ELSE T_AgingBuckets.AgeBuck
					END
			END AS SumOfAmtS
	FROM AR_Records3
	LEFT JOIN T_AgingBuckets ON AR_Records3.DPD = T_AgingBuckets.DaysPastDue
	INNER JOIN T_ComResFlag ON AR_Records3.SDst = T_ComResFlag.SDst
	WHERE T_ComResFlag.ComRes = 'Com'
		OR T_ComResFlag.ComRes = 'Res'
	GROUP BY AR_Records3.CoCd
		,T_ComResFlag.ComRes
		,AR_Records3.Customer
		,AR_Records3.CustName
		,AR_Records3.CGrp
		,Ar_Records3.DPD
		,T_AgingBuckets.AgeBuck
	) T
PIVOT(AVG(SumOfAmtS) FOR T.AgeBuck IN (
			['1-1 to 30']
			,['2-31 to 60']
			,['3-61 to 90']
			,['4-91 to 180']
			)) P;

Select allOpen in new window

I'm close but still not getting it.  The below data if obtained by using the WHERE clause to restrict the data to customer H00022.  I also found I made a mistake by adding ' marks to the pivot.  It should look like the following:

PIVOT
(
      SUM(src.SumOfAmtS) FOR src.AgeBuck IN ([1-1 to 30],[2-31 to 60],[3-61 to 90],[4-91 to 180],[5-181 to 365])
) AS piv;

Sample data:

SumOfAmtS      CoCd      ComRes      Customer      CGrp      CustName      Total_AR      AgeBuck
876.00      LXCA      Com      H00022      MI      Saisons-Air Inc      876.00      NULL
1069.35      LXCA      Com      H00022      MI      Saisons-Air Inc      1069.35      NULL
7841.30      LXCA      Com      H00022      MI      Saisons-Air Inc      7841.30      NULL
-3181.36      LXCA      Com      H00022      MI      Saisons-Air Inc      -3181.36      1-1 to 30
-430.01      LXCA      Com      H00022      MI      Saisons-Air Inc      -430.01      1-1 to 30
107.35      LXCA      Com      H00022      MI      Saisons-Air Inc      107.35      1-1 to 30
874.92      LXCA      Com      H00022      MI      Saisons-Air Inc      874.92      1-1 to 30
55.98      LXCA      Com      H00022      MI      Saisons-Air Inc      55.98      2-31 to 60
86470.40      LXCA      Com      H00022      MI      Saisons-Air Inc      86470.40      2-31 to 60
1021.81      LXCA      Com      H00022      MI      Saisons-Air Inc      1021.81      3-61 to 90