Crosstab question

[R Fourt]

I am trying to create a crosstab or pivot table. I have one table involved
that is used to track dates that employees call in sick. The fields I'm
needing are [ID] (primary key for the table-each "call in" has a unique
value), [EmpID] and [Date]. I want to see the EmpID (rows) and the dates
they've called in as the column data. Ideally, the column headings should be
either "Day1, Day2, etc" or something similar.

I can do a cross tab that shows all the dates but they are each in a
separate column which makes it very hard to view.

Any help would be greatly appreciated.

 

[John Spencer]

Perhaps using a query that looks like the following as the source for the
crosstab query

SELECT C.EmpID, C.TheDate, 1 + Count(C2.TheDate) as DayRank
FROM TableSick as C INNER JOIN TableSick as C2
ON C.EmpID = C2.EmpID and
C.TheDate > C2.TheDate
GROUP BY C.EmpID, C.TheDate

TRANSFORM First(TheDate) as ShowDate
SELECT EmpID
FROM TheQuery
GROUP BY EMPID
PIVOT DayRank

If I got that all correct you should end up with rows of data that look like
EmpID : 1 : 2 : 3
1 : 12/01/2007 : 12/23/2007
6 : 09/11/2007 : 10/11/2007 : 10/14/2007
7 : 01/03/2007

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..