Access SQL with Pivot

How to Pivot Query Results in MS Access?

Simply use the crosstab query, a unique command in Jet/ACE SQL:

TRANSFORM Max(t.rights) AS MaxOfrights
SELECT t.user
FROM accTable t
GROUP BY t.user
PIVOT t.report;

Access SQL With PIVOT

The syntax to pivot in MS Access is different from sql server. You have to use the TRANSFORM function to pivot data.

The syntax will be similar to this:

TRANSFORM SUM([Retention & Graduation].Hc)
SELECT [Retention & Graduation].Year, 
  [Retention & Graduation].Status, 
  [Retention & Graduation].Hc
FROM [Retention & Graduation]
GROUP BY [Retention & Graduation].Year
PIVOT [Retention & Graduation].[STATUS]

Pivoting data in MS Access

You can pivot the data using TRANSFORM:

TRANSFORM COUNT(MenuItems.MealType)
SELECT April2013.SID, MenuItems.MealType
FROM April2013 
LEFT JOIN MenuItems 
  ON MenuItems.Item=April2013.Item
GROUP BY April2013.SID
PIVOT MenuItems.MealType;

Convert the Pivot table in MS Access to Pivot table in SQL Server

After looking at the solution provided by @Jonathan Willcock, I was able to make up a solution for your problem. Have a look at this and perhaps you might need to refine it.

USE LinesmanRPL
DECLARE @PrerequisiteColumn AS NVARCHAR(MAX),
        @SQLforPivotTable  AS NVARCHAR(MAX);
SET @PrerequisiteColumn = STUFF((SELECT distinct ',' + QUOTENAME(Prerequisite) 
        FROM [UETTDRSB39A Criteria]
        WHERE [UETTDRSB39A Criteria].Pathway = 'Common' OR [UETTDRSB39A Criteria].Pathway = 'Electrical'
        FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'')
SET @SQLforPivotTable = 'SELECT RPLEvidenceOptionsDetail, ' + @PrerequisiteColumn + ' FROM 
        (
          SELECT [RPL Link], RPLEvidenceOptionsDetail, Prerequisite 
          FROM [UETTDRSB39A Criteria]
        ) Temp
        PIVOT 
        (
          MIN([RPL Link]) FOR Prerequisite IN (' + @PrerequisiteColumn + ')
        ) PivotTable '
EXECUTE(@SQLforPivotTable)

Pivot Query in MS Access

Consider:

TRANSFORM First(Data.Cat) AS FirstOfCat
SELECT Data.ID, Data.Name
FROM Data
GROUP BY Data.ID, Data.Name
PIVOT "Cat" & DCount("*","Data","ID=" & [ID] & " AND Cat<'" & [Cat] & "'")+1;

Or if there is a unique record identifier field - autonumber should serve:

TRANSFORM First(Data.Cat) AS FirstOfCat
SELECT Data.ID, Data.Name
FROM Data
GROUP BY Data.ID, Data.Name
PIVOT "Cat" & DCount("*","Data","ID=" & [ID] & " AND ID_PK<" & [ID_PK])+1;