Pivoting Data in Ms Access

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; 

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;

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;

Substitute the removed pivot table feature in ms access

A Crosstab query (a.k.a. SQL TRANSFORM/PIVOT query) provides some pivot table features. Whereas a pivot table provides dynamic features (allowing real-time drill down, column selection and criteria specification), the Crosstab provides a single view of the data. The "drill-down" capability can be handled by other Crosstab queries with more specific WHERE selection and finer pivot intervals. The Crosstab does not support multiple pivoting columns, but one can combine results of multiple Crosstabs into a combined query using UNION statements and other techniques.

As expressed in other forums, you can export the data to Excel and use pivot tables there.

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)

---

Related Topics