Access 2013 Crosstab Query with Dynamic Column Headers

 Computer, Sharepoint, Software  Comments Off on Access 2013 Crosstab Query with Dynamic Column Headers
Mar 212017
 

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.

Raspberry Pi – PulseAudio daemon SystemWide at Boot

 Uncategorized  Comments Off on Raspberry Pi – PulseAudio daemon SystemWide at Boot
Feb 042017
 

Various findings on how to start PulseAudio at boot.

https://www.freedesktop.org/wiki/Software/PulseAudio/Documentation/User/SystemWide/ has general information on running PulseAudio systemwide at boot. Those instructions, however, did not seem to actually start the daemon at boot.

sudo chmod 555 /var/run/pulse/.config/pulse/cookie
to get around cookie file/authentication errors.

Modifying /etc/pulse/system.pa with…
load-module module-native-protocol-unix auth-anonymous=1
…seems to help. But, I have no audio when booting into the Pixel GUI.

 Posted by at 10:59 am