在MS Access中透视数据 [英] Pivoting data in MS Access
本文介绍了在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.
在数据透视表报表中似乎有太多数据无法访问,因此我希望可以代替一个SQL查询来运行.
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;
当前输出:
+-----+-----------+
| SID | Meal |
+-----+-----------+
| 001 | Lunch |
| 002 | Lunch |
| 003 | Breakfast |
| 004 | Snack |
| 005 | Lunch |
| 006 | Lunch |
| 001 | Breakfast |
| 003 | Snack |
| 004 | Breakfast |
+-----+-----------+
这就是我想要的样子:
+-----+-----------+-------+---------+
| SID | Breakfast | Lunch | Snack |
+-----+-----------+-------+---------+
| 001 | 3 | 10 | 1 |
| 002 | 4 | 8 | 10 |
| 003 | 18 | 2 | 7 |
| 004 | 6 | 7 | 2 |
+-----+-----------+-------+---------+
推荐答案
您可以使用TRANSFORM旋转数据:
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;
这篇关于在MS Access中透视数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文