How Can I Pivot Data in MS Access Using SQL to Count Meal Types Consumed by Students?

Pivoting Data in MS Access Using SQL

To pivot data in MS Access, one can utilize the TRANSFORM function to count occurrences of distinct values across multiple columns. This approach is particularly useful when dealing with large datasets that may exceed the capacity of pivot tables.

Consider the following scenario: You have a query that retrieves student IDs (SIDs) and meal items (MealType) they consumed over a month. To count the number of times each student consumed specific meal types (Breakfast, Lunch, Snack), you can employ the following SQL query:

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 will pivot the data, creating a new table with columns for each MealType (Breakfast, Lunch, Snack) and displaying the count of each meal type for each student. The resulting table will be structured as follows:

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

This pivoted table provides a concise summary of the meal consumption patterns for each student over the specified time period.

The above is the detailed content of How Can I Pivot Data in MS Access Using SQL to Count Meal Types Consumed by Students?. For more information, please follow other related articles on the PHP Chinese website!