{"id":514,"date":"2017-03-21T10:29:51","date_gmt":"2017-03-21T14:29:51","guid":{"rendered":"http:\/\/markwheeler.com\/wordpress\/?p=514"},"modified":"2017-03-21T10:29:51","modified_gmt":"2017-03-21T14:29:51","slug":"access-2013-crosstab-query-with-dynamic-column-headers","status":"publish","type":"post","link":"https:\/\/www.markwheeler.com\/?p=514","title":{"rendered":"Access 2013 Crosstab Query with Dynamic Column Headers"},"content":{"rendered":"<p>In my scenario, I have a SharePoint 2013 list with Task Name, Assignee, Task Start and Task End. I needed to query the list and parse the data into a pivot table with all dates between the task dates as the column headers. The following steps were pieced together from multiple posts around the Web.<\/p>\n<p>There are easier ways to do this with SQL or ReportBuilder, but I had to use Access in this case. After exporting the list into Access&#8230;<\/p>\n<p>First create a Table called &#8220;Dual&#8221; with two columns: ID, DummyTxt<br \/>\nPopulate one row with anything.<\/p>\n<p>Create a Query (qDual) against Dual that will create 9 rows.<br \/>\n<code>Query SELECT 0 AS a<br \/>\nFROM dual<br \/>\nUNION ALL<br \/>\nSELECT 1<br \/>\nFROM dual<br \/>\nUNION ALL<br \/>\nSELECT 2<br \/>\nFROM dual<br \/>\nUNION ALL<br \/>\nSELECT 3<br \/>\nFROM dual<br \/>\nUNION ALL<br \/>\nSELECT 4<br \/>\nFROM dual<br \/>\nUNION ALL<br \/>\nSELECT 5<br \/>\nFROM dual<br \/>\nUNION ALL<br \/>\nSELECT 6<br \/>\nFROM dual<br \/>\nUNION ALL<br \/>\nSELECT 7<br \/>\nFROM dual<br \/>\nUNION ALL<br \/>\nSELECT 8<br \/>\nFROM dual<br \/>\nUNION ALL SELECT 9<br \/>\nFROM dual;<\/code><\/p>\n<p>Create a Query (qDates).<br \/>\n<code>SELECT top 90 date() + (a.a + (10 * b.a) + (100 * c.a)) AS MyDate<br \/>\nFROM (SELECT *<br \/>\n   FROM qDual)  AS a, (SELECT *<br \/>\n   FROM qDual)  AS b, (SELECT *<br \/>\n   FROM qDual)  AS c;<\/code><br \/>\nThis creates 90 days of dates from Today. I am limiting to 90 days out. If you have too many days, the later crosstab query will complain about having too many crosstab headers.<\/p>\n<p>Create Crosstab Query.<br \/>\n<code>TRANSFORM [Staffing].Name<br \/>\nSELECT [Staffing].Name<br \/>\nFROM [Staffing], [qDates]<br \/>\nWHERE  [qDates].MyDate between [Staffing].[Task Start] and [Staffing].[Task End]<br \/>\nGROUP BY [Staffing].Name<br \/>\nPIVOT [qDates].MyDate<\/code><\/p>\n<p>You can use another column (eg. Task Name) for the Transform. When you run the query you should have something like this.<\/p>\n<p><a href=\"http:\/\/54.159.73.21\/wp-content\/uploads\/2017\/03\/AccessCrosstab.png\"><img decoding=\"async\" src=\"http:\/\/markwheeler.com\/wordpress\/wp-content\/uploads\/2017\/03\/AccessCrosstab-300x22.png\" alt=\"\" width=\"600\" class=\"alignnone size-medium wp-image-515\" srcset=\"https:\/\/www.markwheeler.com\/wp-content\/uploads\/2017\/03\/AccessCrosstab-300x22.png 300w, https:\/\/www.markwheeler.com\/wp-content\/uploads\/2017\/03\/AccessCrosstab-1024x76.png 1024w, https:\/\/www.markwheeler.com\/wp-content\/uploads\/2017\/03\/AccessCrosstab-768x57.png 768w, https:\/\/www.markwheeler.com\/wp-content\/uploads\/2017\/03\/AccessCrosstab-1536x114.png 1536w, https:\/\/www.markwheeler.com\/wp-content\/uploads\/2017\/03\/AccessCrosstab.png 1619w\" sizes=\"(max-width: 300px) 100vw, 300px\" \/><\/a><\/p>\n<p>The first column is today&#8217;s date. And though it&#8217;s not in the screenshot, the last column is the MAX Task End. In this pseudo-GANTT table, you can see that I am tasked from today and everyday forward. Aquaman has a task starting on 3\/22 and forward and Superman has a task starting 4\/1 and forward.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>In my scenario, I have a SharePoint 2013 list with Task Name, Assignee, Task Start and Task End. I needed to query the list and parse the data into a pivot table with all dates between the task dates as the column headers. The following steps were pieced together from [&hellip;]<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"footnotes":""},"categories":[2,10,11],"tags":[21,37,100],"class_list":["post-514","post","type-post","status-publish","format-standard","hentry","category-computer","category-sharepoint","category-software","tag-access-2013","tag-crosstab-query","tag-sharepoint-2013"],"jetpack_featured_media_url":"","_links":{"self":[{"href":"https:\/\/www.markwheeler.com\/index.php?rest_route=\/wp\/v2\/posts\/514","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.markwheeler.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.markwheeler.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.markwheeler.com\/index.php?rest_route=\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/www.markwheeler.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=514"}],"version-history":[{"count":0,"href":"https:\/\/www.markwheeler.com\/index.php?rest_route=\/wp\/v2\/posts\/514\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.markwheeler.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=514"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.markwheeler.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=514"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.markwheeler.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=514"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}