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.
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…
First create a Table called “Dual” with two columns: ID, DummyTxt
Populate one row with anything.
Create a Query (qDual) against Dual that will create 9 rows.
Query SELECT 0 AS a
FROM dual
UNION ALL
SELECT 1
FROM dual
UNION ALL
SELECT 2
FROM dual
UNION ALL
SELECT 3
FROM dual
UNION ALL
SELECT 4
FROM dual
UNION ALL
SELECT 5
FROM dual
UNION ALL
SELECT 6
FROM dual
UNION ALL
SELECT 7
FROM dual
UNION ALL
SELECT 8
FROM dual
UNION ALL SELECT 9
FROM dual;
Create a Query (qDates).
SELECT top 90 date() + (a.a + (10 * b.a) + (100 * c.a)) AS MyDate
FROM (SELECT *
FROM qDual) AS a, (SELECT *
FROM qDual) AS b, (SELECT *
FROM qDual) AS c;
This 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.
Create Crosstab Query.
TRANSFORM [Staffing].Name
SELECT [Staffing].Name
FROM [Staffing], [qDates]
WHERE [qDates].MyDate between [Staffing].[Task Start] and [Staffing].[Task End]
GROUP BY [Staffing].Name
PIVOT [qDates].MyDate
You can use another column (eg. Task Name) for the Transform. When you run the query you should have something like this.
The first column is today’s date. And though it’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.
