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.