MS Access SQL转换汇总数据透视表的值 [英] MS Access SQL Transform Aggregate Manipluation of Values for Pivot

问题描述

我一直在阅读和深入研究SQL，现在我对此感到困惑.我有一个示例表:

I have been reading and diving into depths of SQL and I think I am successfully confused over this now. An example table I have is:

Date       Quantity       Type       Date_Entered
01/02/15   23             Orange     10/01/15
01/02/15   10             Red        10/01/15
01/02/15   18             Yellow     10/01/15
02/02/15   15             Yellow     10/01/15
02/02/15   19             Red        10/01/15
.          .              .          .
.          .              .          .

Date是预计销售的日历日期，Quantity是销售数量，Type是产品类型(可能会随类型的数量而变化)，Date_Entered是将数据放入表中并给出日期的日期其他列的预测.对于每个Date_Entered，都有日期范围(例如2015年全年).对于每个日期，都有每个可用的类型.

Date is the calendar date of projected sales, Quantity is the number of sales, Type is the type of product (this may vary with the number of types), Date_Entered is the date that the data was put into the table and gives the forecast for the other columns. For each Date_Entered, there is the range of dates (say the whole of 2015). For each Date, there is every Type available.

现在，我一直在尝试通过MS Access中的SQL查询来旋转表格(因为我正在Excel中使用它与宏一起使用)以获取如下所示的表格:

Now, I have been trying to pivot the table with an SQL query in MS Access (because I'm using this with a macro in Excel) to get a table like the following:

Date        Orange    Red    Yellow
01/02/15    3         2      5
02/02/15    0         -2     -1
03/02/15    8         -1     2
.           .         .      .
.           .         .      .

其中的值是特定日期的销售数量从一个输入的日期到另一个输入的日期之间的差(例如，一周，一个月，一个季度的预计销售额变化).

Where the values are the difference between the Quantity sold for a particular Date from one Date_Entered to another (e.g. change in projected sales over a week, month, quarter).

到目前为止，我已经改进了以下内容:

So far, I have evolved the following:

TRANSFORM IIF(FIRST([Date_Entered]) > LAST([Date_Entered]), 
              FIRST([Quantity]) - LAST([Quantity]),
              LAST([Quantity]) - FIRST([Quantity]))
SELECT [Date] FROM Sales WHERE [Date_Entered] = #2015-01-10# OR #2015-01-20# 
GROUP BY [Date] ORDER BY [Date] PIVOT [Type]"

但是，FIRST()似乎只是带出零，因此上述内容并未提供正确的数字.我认为，我的问题是计算出表的值所需的汇总.希望有人能指出我正确的方向.

However, FIRST() seems to just bring out zeros and so the above does not give the correct figures. My problem, I think, is working out the aggregate needed for the computation of the values for the table. Hopefully, someone can point me in the right direction.

谢谢您的答复.

推荐答案

我认为问题出在您的WHERE子句中.代替

I think the problem is with your WHERE clause. Instead of

WHERE [Date_Entered] = #2015-01-10# OR #2015-01-20# 

尝试

WHERE [Date_Entered] IN( #2015-01-10#, #2015-01-20# )

这篇关于MS Access SQL转换汇总数据透视表的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！