Different results between access and Sql

Question

Post reply

Different results between access and Sql

Nelson Viggiani-332216

Ten Centuries

Points: 1264
More actions
July 26, 2006 at 8:08 am

#169768

I'm running in parallel 2 queries, on in Access and one in SQL (same query)
I'm getting this result in Access ==> 2.850349609E+11
and
I'm getting this result in SQL ==>> 2850349609.11377
Question is:
Why Access is doing this? and they are the same?
thanks in advance

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

SQLZ SSChampion Points: 12872 More actions · Answer 1

Hmm,

The two numbers are different. 2.850349609E+11 is 285034960900.

The question is, what are you doing to arrive at these figures? Is it a mathematical operation of some sort?

Karl
source control for SQL Server

Nelson Viggiani-332216 Ten Centuries Points: 1264 More actions · Answer 2

yes, it is a mathematical operation:

In Access this way:

Sum(([MVG T003 - Eliminate outliers from T001]![CurMileage]-[CPM T001 - Calc avg cur mileage & proceeds]![AvgOfCurMileage])^2) AS [Reg Equation Slope Denomator]

In SQL this way:

sum(SQUARE(([MVG T003 - Eliminate outliers from T001].[CurMileage]-[CPM T001 - Calc avg cur mileage & proceeds].[AvgOfCurMileage]))) AS [Reg Equation Slope Denomator]

Am I doing this wrong?

thanks

SQLZ SSChampion Points: 12872 More actions · Answer 3

The operation seems to be ok.

Just to confirm: Is [MVG T003 - Eliminate outliers from T001] a table or table alias, and [CurMileage] is a column?

Is it possible that the data is different or are you querying from a single source?

I'd strip down the mathematical operation by performing a sum on [CurMileage] and [AvgOfCurMileage] seperately to see if those figures differ between SQL Server an Access.

Karl
source control for SQL Server

Nelson Viggiani-332216 Ten Centuries Points: 1264 More actions · Answer 4

[MVG T003 - Eliminate outliers from T001] it is a table and [CurMileage] it is a column

and yes it is only one source for both

Stephen Turner Mr or Mrs. 500 Points: 530 More actions · Answer 5

They look the same, but there might be an issue with type casting in there somewhere. And did you have to use an ampersand (&) in the table name as well as hyphens and spaces! Your code would be much more readable if you learnt a thing or two about object naming.

What types are each of the two fields in the database?

Try putting ^2.0 in the access code instead of ^2.

macrocharlie Ten Centuries Points: 1055 More actions · Answer 6

It might be the data type you are using. Decimal is much more accurate than float in SQL Server 2000. Here is a quote from BOL: "Approximate number data types for use with floating point numeric data. Floating point data is approximate; not all values in the data type range can be precisely represented".

Scott Coleman One Orange Chip Points: 27451 More actions · Answer 7

The float data type is good for about 15 digits, I doubt very much that there is any chance that some difference out in digit 16 or 17 would produce a result that is off by a factor of 100 but matches exactly for the first 10 digits. On SQL Server at least, decimal values would be converted to float anyway by the SQUARE function.

It looks to me like your CurMileage values are off by a factor of 10 somehow (10x smaller on the SQL Server data). After you subtract the average and square it, the result would have the same digits but would be 100x smaller.

Stephen Turner Mr or Mrs. 500 Points: 530 More actions · Answer 8

What should the result be?

i.e is it access or sql that is wrong.