Crosstab Query

[D Willett]

Hi Guys

In a crosstab query:

TRANSFORM Avg(qryRatesManufacturer.[LabourRate]) AS AvgOfLabourRate
SELECT qryRatesManufacturer.[Insurer], Avg(qryRatesManufacturer.[LabourRate]) AS [Total Of LabourRate]
FROM qryRatesManufacturer
GROUP BY qryRatesManufacturer.[Insurer]
PIVOT qryRatesManufacturer.[VehicleMake];

I am getting #Name? in the Row Headings.
A couple of the [Insurer] names have brackets in the name, could that be the problem?

I also get:Scaling of decimal value resulted in data truncation.

Cheers

[HansV]

What happens if you place a condition on Insurer that selects only names without brackets?

What exactly do you mean by "scaling of decimal value"?

[D Willett]

Hi Hans

As a test I changed Insurer to a different field where I know there are no invalid characters, the result was the same.
But the Scaling issue is:

[HansV]

Are the data stored in SQL Server?

[D Willett]

The data is imported ( Make Table Query ) from an external source ( linked Table ). I've been looking at this error.
It seems it is the design of the table ( Precision and Scale ) which is 19/4. I changed them to 10/2 in the imported freshly made table, saved the table then checked the design properties again.
It seems the Precsion and Scale are inherited.

I'm not sure how to fix this..

[HansV]

Can you change the Data type of the field to Currency or to Number > Double?

[D Willett]

Which table Hans, the link table or the made table?

[HansV]

In the result of the Make-Table query. You cannot change field types in a linked table.

[D Willett]

Yep, I made it work by changing it to Currency manually. I need to make it permanently now on every import.
I have a query in between the result table and the crosstab, perhaps better to make the change there so as not to disturb other tables in the database which rely on the result table.

Rate: CCur([LabourRate])

Do you agree Hans?

[HansV]

It's certainly worth a try.

[D Willett]

Yes it works Hans.
Thanks for the help :-)

[D Willett]

Just a thought Hans.
Creating a report from the crosstab, is the crosstab a dynamic query?
Meaning if the data in a field changes then the report returns an unrecognised field error error?

[HansV]

The easiest option is to fix the column headings:
- Open the crosstab query in design view.
- Click in an empty part of the upper part of the query window.
- Click in the Column Headings property.
- Enter the column headings in the order you want them, e.g.

ALFA ROMEO,ASTON MARTIN,BMW,FERRARI,MERCEDES,ROLLS ROYCE

Otherwise, you'd have to use VBA to populate the report. It's possible but a lot of work. Let me know if you need that - there are some examples in the Access forum.

[D Willett]

Thanks Hans
I understand why it does this now so with that knowledge I can se a date range that would definately include the data for the header source's.
Although, would an IIF statement work ? Not tried that on a crosstab before?

Thanks Again for support

[HansV]

What exactly do you mean?

[D Willett]

Adding an iif function so if field doesn't exist then just ignore it or blank it out:

[HansV]

No, that won't help. The names of the vehicle makes that occur in the query might change depending on the criteria, so the crosstab query might have 10 columns now, 12 next month and 9 the month after that. A report cannot handle that - it expects a fixed set of columns.
Specifying the Column Headers property of the crosstab query gets around this.

[D Willett]

Ok Hans, I get that. The Column Header properties should be just fine.

Thank You Again.
Have a great weekend :-)

[HansV]

Have a great weekend too!