Crosstab query Invalid precision for decimal data

[darkhat01]

I am trying to create a crosstab query that is the average score for each Line of Business. I thought that this would be simple. But I am running into problems.

Here is what I would like it to look like. The number must be between 0 and 2 with two decimal places.

LOB1 LOB2 LOB3 LOB4
Review 1.12 1.20 1.29 1.8

Data:
LOB1 1.12
LOB1 1.12
LOB2 .08
LOB2 2
LOB2 1.52
LOB3 1.1
LOB3 1.34
LOB3 1.44
LOB4 2
LOB4 1.6



TRANSFORM Avg(DataPlanScorecardReviewAll.InitialScore) AS AvgOfInitialScore
SELECT DataPlanScorecardReviewAll.ACTIVITY_TYPE, Avg(DataPlanScorecardReviewAll.InitialScore) AS [Total Of InitialScore]
FROM DataPlanScorecardReviewAll
GROUP BY DataPlanScorecardReviewAll.ACTIVITY_TYPE
PIVOT DataPlanScorecardReviewAll.Name;

I get an error that “Invalid precision for decimal data” any ideas?

Thanks,

Darkhat01

 

[dhookom]

What is the data type of InitialScore? You may need to change it in the query to double or single.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[darkhat01]

Currently the data type is a number and the Field Size is set to Decimal. I do see Single and Double in the Field size. I changed it to single and it seems to work, why is this? What is the difference between Single and Decimal. I know that Double uses twice the memory as single.

Also now I changed the query a little bit and I need to create the Average and round to the hundredths place for the output, how do I do this in a query?

Here is the new SQL I am using:

TRANSFORM Avg(DataPlanScorecardReviewAll.InitialScore) AS AvgOfInitialScore

SELECT DataPlanScorecardReviewAll.ACTIVITY_TYPE, Avg(DataPlanScorecardReviewAll.InitialScore) AS [Total Of InitialScore]

FROM DataPlanScorecardReviewAll

WHERE (((DataPlanScorecardReviewAll.InitialScore)<>0 And (DataPlanScorecardReviewAll.InitialScore) Is Not Null))

GROUP BY DataPlanScorecardReviewAll.ACTIVITY_TYPE

PIVOT DataPlanScorecardReviewAll.Name;

Thanks,

Darkhat01

 

[dhookom]

You can use the Round() function to limit the result of a calculation to a specific number of decimal places.

I believe when a query performs a calculation on a decimal with a specific scale, it attempts to return the result in the same scale. If you are performing an average, the result will most likely require more decimals.

I would have first attempted to convert the field dynamically with CDbl() rather than changing the data type of the field.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]