title: Pivoting data in MS Access
body: <p>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. </p>
  
  <p>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.  </p>
  
  <p>Here's the current query I've created:  </p>
  
  <pre><code>SELECT April2013.SID, MenuItems.MealType AS Apr2013Meal  
  FROM April2013 LEFT JOIN MenuItems ON MenuItems.Item=April2013.Item;  
  </code></pre>
  
  <p>Current output:</p>
  
  <pre><code>+-----+-----------+  
  | SID |   Meal    |  
  +-----+-----------+  
  | 001 | Lunch     |  
  | 002 | Lunch     |  
  | 003 | Breakfast |  
  | 004 | Snack     |  
  | 005 | Lunch     |
  | 006 | Lunch     |  
  | 001 | Breakfast |  
  | 003 | Snack     |  
  | 004 | Breakfast |  
  +-----+-----------+
  </code></pre>
  
  <p>Here's how I'd like it to look:  </p>
  
  <pre><code>+-----+-----------+-------+---------+  
  | SID | Breakfast | Lunch | Snack   |  
  +-----+-----------+-------+---------+  
  | 001 |         3 |    10 |     1   |  
  | 002 |         4 |     8 |    10   |  
  | 003 |        18 |     2 |     7   |  
  | 004 |         6 |     7 |     2   |  
  +-----+-----------+-------+---------+  
  </code></pre>