Cross Tab Reporting

[Mick_Hart]

Okay guys, imnew to access, to bare with me.

I have a database which shows the our predicted calls throughout the month, the fields are as follows -

| Date | Time | Client | Station | Est Calls |

I have a crosstab query, (based on a date range) set up display the information colums.

| Date | Station | Time | Sum of Est Calls | Client1 | Client 2 | Client3 |

With Est Calls being shown benethe each of the clients.
This all works fine

Ive build a report to display this information, but have come across a problem.

If i have say 3 clients, 1, 2 ,3 - and the date range only finds say clients 1 and 2, i get an error when i run the report -
The Microsoft Jet database engine does not recognize 'client 3' as a valid field name or expression.

Does anyone know how i can get around this problem?

 

[Corticus]

I imagine in your report, you have a subreport that gets it's data from your crosstab query.

Try this:
Open the main report, right click on the subreport and change it's source object from the subreport to the crosstab query itself (you should be able to pick it from a list).

What's happening is, when you make the subreport, you reference a field name generated in the crosstab query that's not always going to be there, since crosstab queries have dyanamic field names. By having a subquery instead of a subreport, you don't need to explicitly reference any fields, the subquery will represent exactly what's in the crosstab query.