Migrating Data from Access to SQL Server - Proper Data Type?
-
I used the Data Transformation Service to move a table from Accesss to the SQL Server...what I found is that a Number data type in Access came over as float..which is fine..but the number in access as an example was .33 and it came over to SQL Server as '0'....and any number like .55 came over as 1...so it is rounding...
I am doing some calculations in an sql so as to mimic the results I had in Access, and for some reason the one field I am having a problem with has to do with the above issue..
Any ideas of what I can do to resolve this...maybe a proper data type???
Thank you.
-
Can you post the calculations?
When you say 'proper', what do you mean?
Phil
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Ok...in the Access query I have the following as an expression:
xServiceLevel: IIf(Sum([handled])=0,0,Sum([servicelevel]*[handled])/Sum([handled]))
In SQL I have the following so as to mimic the above:
(CASE Sum(handled) WHEN 0 THEN 0 ELSE 100*Sum(servicelevel*handled)/Sum(handled) END) AS [xServiceLevel]
The result in access as an example is .6663369
The result in SQL is .7069717
When I look at the servicelevel field in Access they are like .33, .75, .25....etc.
When I look at the field in SQL Server it is 1 or 0...
I hope that I am explaining myself..
Thank you.
-
What I mean by proper is accurate....
I think that I may have confused some...I am sure that someone has come across this problem (if it is a problem)...
Basically, the numbers appearing on the SQL Server are different than those that appear in a similar table within Access...I have narrowed it down to that...
Any and all suggestions will be very helpful!
Thank you.
-
Can you tell us what the datatypes are in both SQL Server and Access?
I wasn't born stupid - I had to study.
-
Yep, if the datatypes of 'Handled' and 'ServiceLevel' are integer, then the result of your calculation will be an integer.
You could try the following, to force the result to be a float:
(CASE Sum(handled) WHEN 0 THEN 0 ELSE 100*Sum(servicelevel*handled)/cast(Sum(handled) as float)
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
The datatypes for the fields in question are as follows:
System - Fields - Datatype
Access - ServiceLevel - Number
Access - Handled - Number
SQL Server - ServiceLevel - int
SQL Server - Handled - int
I used the suggestion by Phil Parkin, and it did not adjust the value...it just changed it to a float...
As I stated, it has to do with the ServiceLevel field...I mean I am no expert, but when this field is viewed in Access you can see the decimal placement such as .33 or .75...but when you view it in SQL Server...whether the datatype is int or float...it still come out as either 0 or 1..
I hope that this can help you to help me!
-
Once you change it to a float, you will need to load the data from Access again.
-
I do not understand what you mean....
Could you explain a little more...I am a newbie at most of this!
Thank you.
-
I think I see now, (said the blind man). A number in Access is not the same as an integer. I do not know what the precision is in Access, but it must handle numbers out to some decimal place.
Hence, once your data came into SQL and was converted to an integer, it lost that precision. That is why you were no longer seeing the 0.33, but rather just 0. (Actually, that is why I asked the question about the datatypes).
Converting an integer to a float does not gain back that precision. Once you brought the data into SQL and cast it as an integer, the precision was lost. Robert Stewart is absolutely correct in stating that you need reload the data from Access again. The data from Access needs to be loaded into a SQL table already defined with a float, (or numeric(5,3) or decimal(25,5)) column.
You should be able to retain your precision that way. I hope that helps...
I wasn't born stupid - I had to study.
-
Farrell,
Correct. You should not use the upsizing wizard to load data. Sometimes you can have issues with the datatypes it translates to. Once the corrections are made, if any, then you can use DTS or Access queries to transfer the data from Access to SQL Server.
When you look at the Access side of it, there is a 2-setp process for defining numbers. The first is numeric, the second is integer, long integer, single, double, decimal, etc. The second step defines the "precision" in Access. Access calls it "Field Size."
-
Thank you all very much...for your assistance!
Everything is working fine.
This is a great forum, and I am learning alot from the active dialog in here...keep it up!
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply