Crosstab values

[JEA]

Don't worry, this isn't about parameters. I've searched the forum and can't find the answer to this (but loads on crosstab perameters!).

I have a table:

DateWorked | StaffPIN | StartTime | FinishTime | ShiftType
------------|-------- -|-------- --|-----------|----------
.01/11/07....|..1234.....|...07:00....|....19:00....|.....Day
.01/11/07....|..5678.....|...19:00....|....07:00....|.....Night
.02/11/07....|..1234.....|...07:00....|....19:00....|.....Day
.02/11/07....|..5678.....|...07:00....|....07:00....|.....Sick

I want to turn this in to a report with the format:

DateWorked | 1234 | 5678 |
-------------|------|------|
..01/11/07....|..12...|...12...|
..02/11/07....|..12...|....S...|

Where the values (12, S) are the number of hours worked for that staff member on that day or a summary of what they did.

I have created a Function:

WorkedHours(DateWorked, StaffPIN)

This returns the number of hours worked by a saff member on a perticular day as an int.

I know I need to use a crosstab. My 1st attempt had [DateWorked] as the row heading, [StaffPIN] as column heading and the expression "expr: WorkedHours([DateWorked],[StaffPIN])" as the value with 'expression' as the crosstab property. It kept throwing the error "Data type mismatch in criteria expression". There are no Null fields in the table it's performing the query on.

For my 2nd attempt I created a simple query that had the fields I needed for the crosstab plus the extra field "Hours: WorkedHours([DateWorked],[StaffPIN])". I could then use a crosstab query on this simple query to create something aproaching the table I need. This new crosstab had [DateWorked] as the row heading, [StaffPIN] as the column heading, [Hours] as the value with 'Last' as the crosstab property.


1. Do I have to use a seperate query to calculate the WorkedHours() or can I do it in the crosstab?

2. I don't want the crosstab to average, sum or count etc. the data. I just want it to display the hours worked but I have to choose something in the crosstab properties. What do I select? (I selected 'Last' and it seems to work but I'm not sure why)

3. How do I get it to display something other than the number of hours worked for certain shift types? (I have all the shift types in a table with a flag for the ones that need to be calculated, the absence of this flag would mean it needs to show a summary eg 'S' for a sick shift, 'H' for a holiday shift)

I know I've asked for a lot of help recently, I really appreciate everyone's help.

 

[Rich]

Base the Crosstab on a totals query having used the GroupBy option

 

[JEA]

totals query?

Forget question 3, I've updated WorkedHours() to calculate it.

 

[JEA]

Managed to get it working. Thanks.

The problem I've now got is the number of report columns doesn't change if the number of crosstab columns change.

The info I've seen on how to create a report with a variable number of columns is very complicated. The examples I've found don't have a similar structure to my crosstab.

Can anyone help, or point me in the direction of a 'dummies guide to..'?

Could I work around this by exporting the Crosstab results to Excel insted of creating a report?

 

[JEA]

I've found an interesting work around:
http://www.dbforums.com/showthread.php?t=1605962&page=4

Crosstab Example

--------------------------------------------------------------------------------

The attachment is a very basic crosstab example. The key thing in designing a crosstab type report is making it so that when new records are added to the table, you don't have to manually edit the report and add in a new column to show the new column of information. This can easily be done by making the subreport based on the crosstab query (not a subreport) as the report example illustrates. You'll also notice that you can adjust the column widths in the crosstab query which also reflect in the report.

Test out the example by adding/deleting records to the table and notice that you don't have to do anything to the report design.
Attached Files CrossTabExample.zip (10.0 KB, 24 views)

__________________
Paul Kohn
Owner (EDP)/Developer/DBA, SQL Server (6.5 on up), MSAccess (1.0 on up), Visual Basic (5.0, 6.0)

--------------------------------------------------------------------------------
Last edited by pkstormy : 11-01-07 at 11:49.

It uses the crosstab query as a subreport. It's not ideal as I cant do the formatting I wanted to, but it will display new columns.

 

[JEA]

No, the above work around doesn't work.
I get an error message saying i can't use a query with an unfixed number of columns as a sub report.

Is it possible to export the table to a pre-formatted spread sheet in excel?
I'll workout for my self how to do it, I just want to know what route to go down. Do I use excel or bite the bullet and try to write a report with a variable number of columns?

The problem with writing a report with a variable number of columns is I don't have a maximum number of columns. In every example I've found you need to know the maximum number of columns there can ever be. Short of setting this number absurdly large and hoping it will suffice I don't know how to go about it.

 

[JEA]

If anyone's still interested I found a work around by using a PivotTable as a sub form.

It's quite messy, and doesn't give you any format abilities but it works.

 

[Simon_MT]

I know that this may not be what you want for a myriad of reasons but could you reverse the crosstab to show DayofMonth as a Column and StaffPin as the Row? I gave up on PivotTables, trying to join them together as CrossTab queries was such a mare! So I'm a bit rusty and opted instead for manually creating Expressions in a Query:

Day1: iif(Day([DayWorked]) = 1, calculation, ...)
Day2: iif(Day([DayWorked]) = 2, calculation, ...)

Simon

 

[JEA]

I know that this may not be what you want for a myriad of reasons but could you reverse the crosstab to show DayofMonth as a Column and StaffPin as the Row? I gave up on PivotTables, trying to join them together as CrossTab queries was such a mare! So I'm a bit rusty and opted instead for manually creating Expressions in a Query:

Day1: iif(Day([DayWorked]) = 1, calculation, ...)
Day2: iif(Day([DayWorked]) = 2, calculation, ...)

Simon

Thanks for the post, but I may have 50+ dates and about 12 staff. Having the dates as columns and the staff as rows would cause even more headaches. I can't believe Access doesn't support crosstab reports with dynamic columns. I'd have thought it was such a common requirement.

 

[pkstormy]

Crosstab example

JEA,

The crosstab example should work ok. You can edit the crosstab query to change the way the query is displayed in the subreport. If it gives you an error in the subreport, I have to ask what version of MSAccess you are using. Perhaps older versions of MSAccess do not support the feature of a crosstab in a subreport (it works ok with MSAccess 2002 which I used for the example.)

Other than writing code, this would be the easiest way to do a crosstab in a subreport and having the column's automatically be added.