14

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   |  
+-----+-----------+-------+---------+
MikO
  • 18,243
  • 12
  • 77
  • 109
user2382144
  • 207
  • 2
  • 4
  • 9

1 Answers1

22

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;
Taryn
  • 242,637
  • 56
  • 362
  • 405
  • Here is a slightly better example and simpler answer: http://stackoverflow.com/questions/16691853/transform-and-pivot-in-access-2013-sql – CobaltBlue Nov 05 '13 at 05:54