Can't display data in a report based on a crosstab query (1 Viewer)

[Shep]

Hi folks...I recently discovered this forum and I love it. I hope someone out there can make sense of my description and give me some guidance.
I am attempting to create a report based on a crosstab query. The report is a daily schedule of service calls for computer technicians. I would like to display times along the left side of the report per half hour of the working day, from 8:00AM to 6:00PM.
The technician's names appear across the top of the form. The grid is filled with customer names, Work Order numbers, phone numbers, etc.
My problem is that the underlying table ([Service Calls]) contains only dates and times that are actually scheduled.
My attempt to work through this problem included creating a table with one field containing all the available scheduling times from 8AM to 6PM, broken down into half-hour increments, and to include this field in the query as a Row Heading.
The query does display all the available times but of course any service calls scheduled for those times also appear, making it impossible for me to isolate those 'times' and force the report to show them as well.
I hope my explanation will serve here, and thanks in advance to anyone who can shed some light on this.

 

[barrygraham]

Hi there,

I think what you need to do is this. Create a table with all of the times you require. I think you said every 1/2 hour from 8am to 6pm. You will then need to make up a query for your crosstab using a right join from your "times" table to the table which contains your data. This means you want to show ALL of the records in the times table, and ONLY those records from your data table where the joined fields are equal. Save this query and then go on to create your crosstab. You can now use the "times" that you have as column headings or row headings depending on how you want your report to look.

NOTE*** You will need to "Nz" the data in your crosstab, because if a field has no data for any reason, say 8.00am in the morning, you will have problems opening your report. It will tell you that it doesnt recognise your field. The Nz function goes in the "data" field for your query and goes like this:

Nz(<field name>,0)

This will insert a zero into any NULL fields, thus stopping the error mentioned above!

Sorry if I have been teaching you to "suck eggs", but I hope this works for you!

Barry.

 

[Shep]

Barry, thanks so much for replying.
I was beginning to think I was the only human who had ever had to do this. *heh*
Anyway, I have already done exactly as you suggested. Actually, I had gone a bit further-I had used my table of 'times' in the crosstab query itself (with a right join to the [Service Calls].[Time Scheduled] field) and have used the same table in a separate query (with the same join), and used it in the crosstab query instead. Honestly, the result is identical.
Since there are service calls scheduled on other days at various times, those time slots do not appear on the report because they are related to other dates (rather than the date chosen to display on the report).
Perhaps the secret lies in the Nz function? I am just barely past the beginner stage and I don't think I have the concept of Nulls vs. zero length fields quite down yet. I am sure it will hit me in the forehead soon! At any rate, I am not having any errors as such-just can't seem to display any more time slots than are scheduled on any given day.
I will explore the Nz function though, and I thank you once again.

 

[barrygraham]

Hello, I am having a major pain in the a**e with a crosstab report as well, so my sympathy goes out to you, but I cant see why the right join isnt displaying all of the "times" and showing data for only those times that are filled!
Hey Ho, the joys of crosstabs eh? Great when they do what you want and then not so great when they dont!!!!!!!!!

I will keep your snag in mind.

Barry.

 

[Shep]

Hello Barry,
After some thought, I think I see why you don't understand how it is that the 'times' do not all display in the report. At first glance, yes...it seems reasonable that they would.
The problem is that the table containing the service call data also includes a [Date Scheduled] field, and the date is the criteria for the report. If I don't include [Date Scheduled] in the crosstab query, all is well, yet the report must know which date I wish to print a schedule for...so I have to include [Date Scheduled] in the query. When I do, and where there are service calls scheduled for those days, the 'times' for those service calls then have a relationship and can't be displayed.
I hope that makes sense.
I saw your question also. I will take another look and decide whether I can offer any intelligent suggestion there.
And agreed, crosstabs are great - usually someone else's!

Shep