Heisenberg Heisenberg - 4 years ago 89
SQL Question

How to pivot after function in MS access

Table1

contractdate age
2013/4/1 4
2014/4/3 10
2015/5/4 11
2014/4/2 15
2015/8/7 17


First I would like to Get Current age by

Datediff("yyyy",contractdate,"2017/1/1")+age AS currentage


YEAR(contractdate) AS contractyear


contractdate age currentage contractyear
2013/4/1 4 7 2013
2014/4/3 10 12 2014
2015/5/4 11 12 2015
2014/4/2 15 17 2014
2015/8/7 17 18 2015


Second,I would like to pivot,by binning with
currentage


2013 2014 2015
[0,4] 0 0 0
[5,9] 1 0 0
[10,14] 0 1 1
[15,19] 0 1 1


I know below format, but couldnt figure out how to transform data before pivoting.


TRANSFORM
SELECT
GROUP BY
PIVOT


How can this be done?
I am happy someone tell me.

Answer Source

Use the Crosstab Wizard in Access - it does all the basic plumbing. Then study the SQL is creates and adjust as needed.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download