Ms-Access中的变换和枢轴 [英] Transform and Pivot in Ms-Access
问题描述
我有一张桌子，看起来像:
I have a table looks like:
Field1 Field2 Blank Fail Pass Date
a 1 0 20 40 170101
a 2 1 19 99 170101
b 1 0 54 24 170101
c 3 1 24 30 170101
a 1 0 11 19 170102
b 2 0 21 266 170102
a 1 2 10 40 170103
....
然后用户选择他们要显示的日期范围，我希望我的结果看起来像这样:
The user then chooses the date range they want to display and I want my result to look like:
Field1 Field2 0101_B 0101_F 0101_P 0102_B 0102_F 0102_P 0103_B 0103_F 0103_P
a 1 0 20 40 0 11 19 2 10 40
a 2 1 19 99
b 1 0 54 24
c 3 1 24 30
b 2 0 21 266
任何帮助都非常感激！
推荐答案
选项:
-
一个UNION查询作为CROSSTAB的源-参数确实应该引用表单上的控件.
PARAMETERS StartDate Long, EndDate Long; TRANSFORM Sum(Q.Data) AS SumOfData SELECT Q.Field1, Q.Field2, Q.Yr FROM (SELECT Field1, Field2, Blank AS Data, "B" AS Source, [Date], Mid([Date],3) & "_B" AS MonDayCat, Int(Left([Date],2)) AS Yr FROM Table1 UNION SELECT Field1, Field2, Pass, "P", [Date], Mid([Date],3) & "_P", Int(Left([Date],2)) AS Yr FROM Table1 UNION SELECT Field1, Field2, Fail, "F", [Date], Mid([Date],3) & "_F", Int(Left([Date],2)) AS Yr FROM Table1) AS Q WHERE (((Q.Date) Between [StartDate] And [EndDate])) GROUP BY Q.Field1, Q.Field2, Q.Yr PIVOT Q.MonDayCat;
a UNION query as the source for a CROSSTAB - Parameters really should reference controls on a form.
PARAMETERS StartDate Long, EndDate Long; TRANSFORM Sum(Q.Data) AS SumOfData SELECT Q.Field1, Q.Field2, Q.Yr FROM (SELECT Field1, Field2, Blank AS Data, "B" AS Source, [Date], Mid([Date],3) & "_B" AS MonDayCat, Int(Left([Date],2)) AS Yr FROM Table1 UNION SELECT Field1, Field2, Pass, "P", [Date], Mid([Date],3) & "_P", Int(Left([Date],2)) AS Yr FROM Table1 UNION SELECT Field1, Field2, Fail, "F", [Date], Mid([Date],3) & "_F", Int(Left([Date],2)) AS Yr FROM Table1) AS Q WHERE (((Q.Date) Between [StartDate] And [EndDate])) GROUP BY Q.Field1, Q.Field2, Q.Yr PIVOT Q.MonDayCat;
三个CROSSTAB查询，然后加入CROSSTABS
three CROSSTAB queries then join the CROSSTABS
评论 http://allenbrowne.com/ser-67.html#MultipleValues
VBA过程将记录写入临时"表
VBA procedure writing records to a 'temp' table
如果每天都有数据(最多2.5个月)，则这些选项均不能提供一整年的数据.建立一个基于CROSSTAB永久运行的稳定报告并不容易.查看 http://allenbrowne.com/ser-67.html#ColHead .
None of these options will allow a full year of data if there is data for every day - 2.5 months at most. Building a stable report to run perpetually based on CROSSTAB is not easy. Review http://allenbrowne.com/ser-67.html#ColHead.
这篇关于Ms-Access中的变换和枢轴的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
