How Can I Pivot Data in MS Access Using a TRANSFORM Query?

Pivoting Data in MS Access via TRANSFORM Query

In MS Access, pivoting data involves transforming rows into columns. This technique allows users to summarize and present data in a more concise and readable manner.

Suppose you have a query that extracts student IDs and meal items consumed over a month. To count the frequency of each meal type (Breakfast, Lunch, Snack) for each student, a pivot table may be insufficient due to excessive data. Instead, a SQL query using the TRANSFORM function can be employed.

The following query builds on the provided data:

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;

This query transforms the data into the desired pivoted structure, where:

• TRANSFORM aggregates the count of meal types using the COUNT function.
• GROUP BY groups the data by student ID.
• PIVOT specifies the fields that will become column headings. In this case, MenuItems.MealType is used to create the columns.

The resulting output will resemble the desired format:

SID	Breakfast	Lunch	Snack
001	3	10	1
002	4	8	10
003	18	2	7
004	6	7	2

By leveraging the TRANSFORM query, you can effectively pivot data in MS Access, allowing for easy analysis and summarization of complex data sets.

The above is the detailed content of How Can I Pivot Data in MS Access Using a TRANSFORM Query?. For more information, please follow other related articles on the PHP Chinese website!