Error 3700

[Guest]

I keep getting an error message saying "Invaid precision for decimal data
type" when I try to run the query I've included below. How can I get this to
work?

(I'm using the design grid, but I thought it might be easier to understand
what I'm trying to do if I post the query in SQL.)

TRANSFORM Avg([Gyn Util by Report Group].PaidAmt) AS AvgOfPaidAmt
SELECT [Gyn Util by Report Group].Dx1Code AS [Diagnosis Code], [Gyn Util by
Report Group].Dx1Desc AS [Diagnosis Description]
FROM [DxCodes for Analysis] INNER JOIN [Gyn Util by Report Group] ON
[DxCodes for Analysis].DxCode = [Gyn Util by Report Group].Dx1Code
GROUP BY [Gyn Util by Report Group].Dx1Code, [Gyn Util by Report
Group].Dx1Desc
PIVOT [Gyn Util by Report Group].ReportGroup;

 

[Gary Walter]

I keep getting an error message saying "Invaid precision for decimal data
type" when I try to run the query I've included below. How can I get this
to
work?

(I'm using the design grid, but I thought it might be easier to understand
what I'm trying to do if I post the query in SQL.)

TRANSFORM Avg([Gyn Util by Report Group].PaidAmt) AS AvgOfPaidAmt
SELECT [Gyn Util by Report Group].Dx1Code AS [Diagnosis Code], [Gyn Util
by
Report Group].Dx1Desc AS [Diagnosis Description]
FROM [DxCodes for Analysis] INNER JOIN [Gyn Util by Report Group] ON
[DxCodes for Analysis].DxCode = [Gyn Util by Report Group].Dx1Code
GROUP BY [Gyn Util by Report Group].Dx1Code, [Gyn Util by Report
Group].Dx1Desc
PIVOT [Gyn Util by Report Group].ReportGroup;

I don't know...what follows is speculation....

You brought some data over from an ODBC source
and some number field ended up as type Decimal
whose precision is so many digits allowed on the
left of the decimal point...best guess "PaidAmt"

This *guess* is from making assumption that your
data exists without precision error, so something in
above query is raising the error -- best candidate
would be TRANSFORM clause where you are
adding all the PaidAmt's in the SELECT group
over the PIVOT group, then dividing by the count
essentially. It is *possible* that that summing grows
outside the precision of the PaidAmt....

If so, several possible solutions *might* be

1) Avg([Gyn Util by Report Group].PaidAmt*1)

2) Avg(CCur([Gyn Util by Report Group].PaidAmt))

3) Avg(CCur(Nz([Gyn Util by Report Group].PaidAmt),0))
{this probably will fail -- seems I remember aggregates
don't like NZ inside them...I don't remember for sure}

4) increase precision of PaidAmt
or **change type to Currency**
(if in a table and not in a query)

 

[Guest]

You were right about posting the SQL. It really helps.

The first thing to try when SQL is failing is to simplify. What happens when
you run this?

SELECT [Gyn Util by Report Group].Dx1Code AS [Diagnosis Code],
[Gyn Util by Report Group].Dx1Desc AS [Diagnosis Description]
FROM [DxCodes for Analysis] INNER JOIN [Gyn Util by Report Group]
ON [DxCodes for Analysis].DxCode = [Gyn Util by Report Group].Dx1Code
GROUP BY [Gyn Util by Report Group].Dx1Code,
[Gyn Util by Report Group].Dx1 Desc ;

If it has a problem, you need to simplify further such as:

SELECT [Gyn Util by Report Group].Dx1Code AS [Diagnosis Code],
[Gyn Util by Report Group].Dx1Desc AS [Diagnosis Description]
FROM [DxCodes for Analysis] INNER JOIN [Gyn Util by Report Group]
ON [DxCodes for Analysis].DxCode = [Gyn Util by Report Group].Dx1Code ;

Other than that, I'd wonder about the PaidAmt data type as you are doing a
average of it below:

TRANSFORM Avg([Gyn Util by Report Group].PaidAmt) AS AvgOfPaidAmt

Also is there more than one DX1Code for a Dx1 or the other way around? You
are grouping on both.

GROUP BY [Gyn Util by Report Group].Dx1Code,
[Gyn Util by Report Group].Dx1

 

[Guest]

Thanks so much for your help Jerry. It must have been the data type of
PaidAmt, because this works:

TRANSFORM Avg(Val([Gyn Util by Report Group]!PaidAmt)) AS PaidAmtAvg
SELECT [Gyn Util by Report Group].Dx1Code AS [Diagnosis Code], [Gyn Util by
Report Group].Dx1Desc AS [Diagnosis Description]
FROM [DxCodes for Analysis] INNER JOIN [Gyn Util by Report Group] ON
[DxCodes for Analysis].DxCode = [Gyn Util by Report Group].Dx1Code
GROUP BY [Gyn Util by Report Group].Dx1Code, [Gyn Util by Report
Group].Dx1Desc
PIVOT [Gyn Util by Report Group].ReportGroup;

You were right about posting the SQL. It really helps.

The first thing to try when SQL is failing is to simplify. What happens when
you run this?

SELECT [Gyn Util by Report Group].Dx1Code AS [Diagnosis Code],
[Gyn Util by Report Group].Dx1Desc AS [Diagnosis Description]
FROM [DxCodes for Analysis] INNER JOIN [Gyn Util by Report Group]
ON [DxCodes for Analysis].DxCode = [Gyn Util by Report Group].Dx1Code
GROUP BY [Gyn Util by Report Group].Dx1Code,
[Gyn Util by Report Group].Dx1 Desc ;

If it has a problem, you need to simplify further such as:

SELECT [Gyn Util by Report Group].Dx1Code AS [Diagnosis Code],
[Gyn Util by Report Group].Dx1Desc AS [Diagnosis Description]
FROM [DxCodes for Analysis] INNER JOIN [Gyn Util by Report Group]
ON [DxCodes for Analysis].DxCode = [Gyn Util by Report Group].Dx1Code ;

Other than that, I'd wonder about the PaidAmt data type as you are doing a
average of it below:

TRANSFORM Avg([Gyn Util by Report Group].PaidAmt) AS AvgOfPaidAmt

Also is there more than one DX1Code for a Dx1 or the other way around? You
are grouping on both.

GROUP BY [Gyn Util by Report Group].Dx1Code,
[Gyn Util by Report Group].Dx1

--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I keep getting an error message saying "Invaid precision for decimal data
type" when I try to run the query I've included below. How can I get this to
work?

(I'm using the design grid, but I thought it might be easier to understand
what I'm trying to do if I post the query in SQL.)

TRANSFORM Avg([Gyn Util by Report Group].PaidAmt) AS AvgOfPaidAmt
SELECT [Gyn Util by Report Group].Dx1Code AS [Diagnosis Code], [Gyn Util by
Report Group].Dx1Desc AS [Diagnosis Description]
FROM [DxCodes for Analysis] INNER JOIN [Gyn Util by Report Group] ON
[DxCodes for Analysis].DxCode = [Gyn Util by Report Group].Dx1Code
GROUP BY [Gyn Util by Report Group].Dx1Code, [Gyn Util by Report
Group].Dx1Desc
PIVOT [Gyn Util by Report Group].ReportGroup;