MS Access Crosstab query with multiple columns?

1
votes

What's the best way to get multiple columns in a crosstab query? I have the following table:

ID  Name    Topic   Date
123 John    Define  9/30/2015
123 John    Measure 10/30/2015
123 John    Analyze 11/30/2015
321 Mary    Measure 8/28/2015
321 Mary    Define  7/15/2015
321 Mary    Define  6/15/2015

This is the result I'm looking for:

ID  Name    Define  Define Date Measure Measure Date    Analyze Analyze Date
123 John       1      9/30/2015   1     10/30/2015        1     11/30/2015
321 Mary       2      7/15/2015   1     8/28/2015

I created a crosstab query to pivot the topics and count in columns but I have not figured out how to add another column for the date next to the count.

I could have multiple topics with different dates but I will use a Max to get the latest date. Thanks!

1
ms-accessms-access-2010

1 Answers

2
votes

Simply run conditional aggregates in a GROUP BY query to transpose data. Crosstab here will not be helpful since you do not want to transpose row values to columns. Instead you want to define columns according to row values.

SELECT [ID], [Name], 
       Count(IIF(Topic='Define', Topic, Null)) As [Define], 
       Max(IIF(Topic='Define', [Date], Null)) As [Define Date], 
       Count(IIF(Topic='Measure', Topic, Null)) As [Measure],
       Max(IIF(Topic='Measure', [Date], Null)) As [Measure Date], 
       Count(IIF(Topic='Analyze', Topic, Null)) As [Analyze],
       Max(IIF(Topic='Analyze', [Date], Null)) As [Analyze Date]
FROM TableName
GROUP BY [ID], [Name];