Query sums nonzero data to zero.

[Phil Smith]

WTF?

This Query:SELECT [Distribution Step2].old_sku, Sum([Distribution
Step2].Popularity) AS SumOfPopularity, Sum([Distribution
Step2].[OrigPopularityif notover]) AS [SumOfOrigPopularityif notover]
FROM [Distribution Step2]
GROUP BY [Distribution Step2].old_sku
HAVING ((([Distribution Step2].old_sku)="4414060"))
ORDER BY [Distribution Step2].old_sku;

Produces This result.

old_sku SumOfPopularity SumOfOrigPopularityif notover
4414060 0 0

It SHOULD be returning:
old_sku SumOfPopularity SumOfOrigPopularityif notover
4414060 1 0.25528757

This Query which is the exact same query except Detail not SUM:
SELECT [Distribution Step2].old_sku, [Distribution Step2].Popularity,
[Distribution Step2].[OrigPopularityif notover]
FROM [Distribution Step2]
WHERE ((([Distribution Step2].old_sku)="4414060"))
ORDER BY [Distribution Step2].old_sku;

Produces these results

old_sku Popularity OrigPopularityif notover
4414060 0.02288188002473716759431 0
4414060 0.05182436611008039579468 0
4414060 0.06864564007421150278293 0
4414060 0.04922696351267779839208 0
4414060 0.01236858379715522572665 0.01236858379715522572665
4414060 0.05009276437847866419295 0
4414060 0.09288806431663574520717 0
4414060 0.13370439084724799010513 0
4414060 0.02943722943722943722944 0
4414060 0.04032158317872603586889 0
4414060 0.00977118119975262832406 0.00977118119975262832406
4414060 0.03141620284477427334570 0
4414060 0.04662956091527520098949 0.04662956091527520098949
4414060 0.04415584415584415584416 0.04415584415584415584416
4414060 0.01793444650587507730365 0.01793444650587507730365
4414060 0.05936920222634508348794 0.05936920222634508348794
4414060 0.03920841063698206555349 0.03920841063698206555349
4414060 0.02585034013605442176871 0.02585034013605442176871
4414060 0.06122448979591836734694 0
4414060 0.11304885590599876314162 0


Add up the two coloums and you get 1, and 0.25528757

Any ideas?

Phil

 

[Allen Browne]

What is the data type of the Popularity and [OrigPopularityif notover]
fields? If JET believes they are Text, it won't be able to sum them
correctly for you.

If [Distribution Step2] is a table, open it in design view, and examine the
Data Type of the fields.

If it is a query, you will need to dig deeper to discover the data types.
The visual clue is that if the query results are displayed left-aligned, JET
is treating them as text.

You may be able to typecast the fields, as explained here:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

 

[Phil Smith]

It seemed like while the datatypes of the underlying tables were Double,
it was treating them like Integers. I forced them all to double, and
all is fine. (I actually figured it out before I saw your response.)

Thanx