Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Pivoting data in MS Access

I have a query that I've created to pull student IDs and meal items they have taken over a month long period. I would like to count the numbers of each item (Breakfast, Lunch, Snack) taken by a student over the course of the month.

It appears there's too much data for access to handle in a Pivot Table report, so I was hoping there was a SQL query I could run instead.

Here's the current query I've created:

SELECT April2013.SID, MenuItems.MealType AS Apr2013Meal  
FROM April2013 LEFT JOIN MenuItems ON MenuItems.Item=April2013.Item;  

Current output:

+-----+-----------+  
| SID |   Meal    |  
+-----+-----------+  
| 001 | Lunch     |  
| 002 | Lunch     |  
| 003 | Breakfast |  
| 004 | Snack     |  
| 005 | Lunch     |
| 006 | Lunch     |  
| 001 | Breakfast |  
| 003 | Snack     |  
| 004 | Breakfast |  
+-----+-----------+

Here's how I'd like it to look:

+-----+-----------+-------+---------+  
| SID | Breakfast | Lunch | Snack   |  
+-----+-----------+-------+---------+  
| 001 |         3 |    10 |     1   |  
| 002 |         4 |     8 |    10   |  
| 003 |        18 |     2 |     7   |  
| 004 |         6 |     7 |     2   |  
+-----+-----------+-------+---------+  
like image 254
user2382144 Avatar asked May 14 '13 14:05

user2382144


People also ask

What is pivoting the data?

Data pivoting enables you to rearrange the columns and rows in a report so you can view data from different perspectives. For example, in the image below, the Inventory Received from Suppliers by Quarter report shows a set of data spread across the screen in a large grid display.

What is pivot chart in MS Access?

What is MS Access PivotTable? In MS Access, the Pivot table is a programming tool that provides you the option to recognize and summarize selected columns and row of data in a spreadsheet or database table to get the desired report. The Pivot table in Access doesn't actually change the spreadsheet or database itself.

Why do we use pivoting?

The objective of pivoting is to make an element above or below a leading one into a zero. The "pivot" or "pivot element" is an element on the left hand side of a matrix that you want the elements above and below to be zero. Normally, this element is a one.


1 Answers

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; 
like image 150
Taryn Avatar answered Sep 29 '22 04:09

Taryn