Crosstab Query Issue

[yaaara]

Hi Guys,

I'm back with another question this time.. Stuck again :-(

Here's what I'm trying to do..

I have a table with three fields namely Emp_ID (Text), RDate (Date/Time), FShift (Text)

For every employee, I have 14 Records defining their roster for any given week; 2 Records for any given date defining their start & end time.

I need to output the following format in Excel:

EMPID;RDATE1;RDATE1;RDATE2;RDATE2;RDATE3;RDATE3;RDATE4;RDATE4;RDATE5;RDATE5;RDATE6;RDATE6;RDATE7;RDATE7;

RDATE is the column header which will be replaced with the respective date (Rdate Column Entries)

All goes fine till here...

I wrote a crosstab query to achieve this as under:

TRANSFORM First(tmpTMS.FShift) AS FirstOfFShift
SELECT tmpTMS.EMP_ID
FROM tmpTMS
GROUP BY tmpTMS.EMP_ID
PIVOT tmpTMS.rdate;

But this doesn't give me the duplicate column for the same date as required and also omits the data to be present in the second column.

Any suggestions please?

[Stewart Ross]

Hi Yaara. Although I'm not entirely clear on the format you want from your crosstab, such a query cannot repeat column headers in the way you describe.

The column header acts like a Group By clause to bring all duplicates together into a single unique value under which the pivoted aggregate values will be placed. Crosstabs simply cannot give you repeated columns, any more than you can have a repeated row in a SELECT DISTINCT query.

If you think about it a bit more, how could Access distinguish one occurrence of the date 01/01/2008, say, from any other? If it could, how would it know to give you just two values?

Although Access can display pivot tables in the same way as Excel does, it does not have a pivot table query that allows multiple-column pivoting of data. It is a weakness of the type of crosstab query provided that only one column can be pivoted.

It is possible to use self-joins on the table or query concerned to provide at least some of what you want, but to come up with suitable SQL for this we'd need a bit more detail on the content of the table or query concerned and how the dates follow each other - so if you could post some example data along with how you envisage the result data to look this would be very helpful.

If it turns out that SQL is unsuited to the task it is always possible to use bespoke processing of recordsets to do so - but it's then getting quite complicated if this is the only way forward that would meet your needs.

-Stewart

ps when you mention 14 records for a roster, does this mean some form of repeated record group? If it is, such a repeated group breaks first normal form table design. This may just have been shorthand for something else, but if you are using a non-relational table for rostering purposes it makes it difficult (and in some cases impossible) to use relational queries to extract meaningful data.