access转sql iif,MS Access 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 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.

解决方案

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# )