Crosstab Query and Report layout

[D B Lawson]

I have a dynamic crosstab query that shows the workschedule for the operatives for the coming week. The next week's dates are the headers, the operatives are the row headings and the clients show under the appropriate date and in line with the appropriate operative. The report is unbound and fills using code each time it is opened. My problem is if an operative is going to Client 1 on Monday, Tuesday and Wednesday it is in one row. If the same operative is going to Client 2 on Thursday and Friday it is in a different record in the line below. I understand why it is doing it, but I would like it in the same line as the data for Monday, Tuesday and Wednesday. Is there any way to get that layout, or will it always drop it to the line below. It looks really odd in the report.

Thanks for any suggestions

 

[D B Lawson]

Hi Pat

I don't have the ClientID in the Crosstab. Some more information:

I have a select query that I use to gather the information for the Crosstab. I have the following fields:

Operative (made up of the First and Surname of the operative)
WDate (date that the work will be carried out)
WTime (time the work will be carried out - AM, PM)
Claimant (made up of the First and Surname of the claimant)
Claimant2 (as above)

My Crosstab has

WDate (ColumnHeader)
Operative (Row Heading)
Time (Row Heading)

and I'm using SQL which you were kind enough to give me a while:

The Value: First of Claimant2
Total of Claimant2: First as Row Heading

This is so I can have the person's name rather than a figure.

And I'm getting

Mon Tue Wed Thur Fri
Operative1 C1 C1
Operative1 C2 C2
Operative2 C3 C3
Operative2 C4 C4


I have been struggling with this for months and feel as if I'm nearly there.

Are there any other suggestions you might have?

Thank you.

Dawn


[This message has been edited by D B Lawson (edited 09-04-2001).]

[This message has been edited by D B Lawson (edited 09-04-2001).]

 

[D B Lawson]

Hi Pat

Here is the SQL from the crosstab:

TRANSFORM First(qryWeeklyWorkscheduleCrosstabInfo.Claimant2) AS [The Value]
SELECT qryWeeklyWorkscheduleCrosstabInfperative, qryWeeklyWorkscheduleCrosstabInfo.Claimant, qryWeeklyWorkscheduleCrosstabInfo.WTime, First(qryWeeklyWorkscheduleCrosstabInfo.Claimant2) AS [Total of Claimant2]
FROM tblDates LEFT JOIN qryWeeklyWorkscheduleCrosstabInfo ON tblDates.Heading = qryWeeklyWorkscheduleCrosstabInfo.WorkDate
GROUP BY qryWeeklyWorkscheduleCrosstabInfperative, qryWeeklyWorkscheduleCrosstabInfo.Claimant, qryWeeklyWorkscheduleCrosstabInfo.WTime
PIVOT tblDates.Heading;

Dawn



[This message has been edited by D B Lawson (edited 09-05-2001).]

 

[D B Lawson]

Hi Pat

If you've had a chance to look at the above SQL, is there anything in that I could change to stop the multiple entries?

Thanks

Dawn