"Numeric field overflow" from simple SUM query

[SUS2.04ever_ISU4now]

I have 3 different queries with identical SELECT and FROM
statements. Only one of the three works.

The one that works is:

SELECT SUM(

.[column of numbers]) AS [the total]
FROM

WHERE

.[STR_name]=[@name];

The ones that don't work are:

SELECT SUM(

.[column of numbers]) AS [the total]
FROM

;

and

SELECT SUM(

.[column of numbers]) AS [the total]
FROM

WHERE

.[INT_IDnum]=[@IDnum];

 

[Tom Ellison]

Dear SUS:

From your sample queries, it appears you are using MSDE.

I suspect the datatype of the [column of numbers] is of a type too
small to contain the sum. If so, you may need to CONVERT this to a
larger datatype or precision to allow larger sums.

SELECT SUM(CONVERT(NUMERIC(20,4), [column of numbers])) AS [the total]

Something like this may help. The best choice depends on the current
datatype and precision of the values in [column of numbers] and the
number of values to be summed.

I have 3 different queries with identical SELECT and FROM
statements. Only one of the three works.

The one that works is:

SELECT SUM(

.[column of numbers]) AS [the total]
FROM

WHERE

.[STR_name]=[@name];

The ones that don't work are:

SELECT SUM(

.[column of numbers]) AS [the total]
FROM

;

and

SELECT SUM(

.[column of numbers]) AS [the total]
FROM

WHERE

.[INT_IDnum]=[@IDnum];

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[SUS2.04ever_ISU4now]

Ok, I submitted before getting to my point.

The first query returns the total from the column of
values for a specific group. I cannot figure out why this
one works while the query to return the total of ALL
values in the column does not. The same for the other
query which is just totalling based on the values of a
different column from the first.

 

[Tom Ellison]

Dear SUS:

The same considerations about which I wrote before still apply.

Ok, I submitted before getting to my point.

The first query returns the total from the column of
values for a specific group. I cannot figure out why this
one works while the query to return the total of ALL
values in the column does not. The same for the other
query which is just totalling based on the values of a
different column from the first.

-----Original Message-----
I have 3 different queries with identical SELECT and FROM
statements. Only one of the three works.

The one that works is:

SELECT SUM(

.[column of numbers]) AS [the total]
FROM

WHERE

.[STR_name]=[@name];

The ones that don't work are:

SELECT SUM(

.[column of numbers]) AS [the total]
FROM

;

and

SELECT SUM(

.[column of numbers]) AS [the total]
FROM

WHERE

.[INT_IDnum]=[@IDnum];


.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts