MS Access SQL Transform Aggregate Manipluation of

2019-08-06 11:27发布

问题:

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 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.

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]"

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.

Thank you for any responses.

回答1:

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

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

try

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