You are working on a query where you are trying to convert source data to numeric data type and you get an “Arithmetic overflow error”.
Solution:
Let’s understand this with an example:
Here’s the source data: 132.56000000 and you want to store just 132.56 so write a query that looks like:
cast([source language=”column”][/source] as numeric(3,2)) as destination_column_name
and after you run the query its throws an error “Arithmetic Overflow Error” – so what’s wrong?
The issue is that you incorrectly specified the precision and scale – by writing the query that says numeric(3,2) you are saying I want 3 data places with 2 on the right (after decimal point) which leaves just 1 place for left.
what you need to write is numeric(5,2) – and this will have 2 places on the right and leaves 3 places for left.
so after you run this, it shouldn’t complain about the arithmetic overflow error. you just need to make sure that the precision and scale of the numeric data type is correct.
Conclusion:
In this post, you saw an example of how to correctly use the precision and scale in the numeric data type and that should help you solve the arithmetic overflow errors.
DateTime columns can be tricky for analysis purposes. They don’t work well with Pivot Tables because of the time part, each value seems unique to the Pivot Table & it also creates problems while creating relationships with Date Dimensions. And so, It’s a common need to convert them to just Date before analyzing data & also a common need to create a relationship between the Date (and not DateTime) with Date Dimension Table.
So if it’s possible, I try to do the data type conversion in the source system query. If your source system is SQL Server, you could use this piece of code:
[code language=”sql”] select [your-fields],cast([DateTime_Col] as date) as Date_Col from TableName [/code]
Doing the data type conversion upfront in the source system query is a good thing to do. And I hope this is helpful.
A short Blog-Post explaining what each “term” means in Access. If you are interested, introduction” to Access is here (via Wikipedia).
Here are the details:
1. Tables:
The place where data is stored. Access is a “data store” which allows us to store data in Rows & Columns format. Here are couple of things that you should know about Tables:
1a. Tables can be related: If you come from the database world – you know that tables can also have relationships among them. If not, just think of relationships as a way to link similar data items. For example, Product Table having Product-ID column can be related to Product-Category table having Product-ID column. Benefit? The Product-Category column can have details about the particular category and since it is linked to the Product Table you do not have to enter the details about the category in the product table again & again. Saves time (and storage space) by eliminating redundancy.
1b. Columns in the Tables have Data-Types: You can specify the data-type of a column/field. So you can say that Column A will contain text data, Column B will contain Numeric Data.
1c. Each row added in a table is called a record
2. Forms:
Forms are used to “input” data into Tables. Think of forms as “cards” that allow you to enter data into tables one field at a time. Now, if you are new to Access, you know that you can enter data while creating/designing tables without creating forms – then why do you need forms? Let me give you an example: In an organization, Person A designs Access Tables and Person B who is not access-savvy uses it to enter data. Now, it makes sense to abstract/hide the “technology details” from the person who is not access-savvy and in that case, creating forms helps person B in entering data without worrying about the underlying table-structure.
3. Queries:
Queries gets it data from “Tables”. Why do you need queries?
3a. Queries help you “find” data from your tables. You can specify criteria like fetch data for month of January 2011.
3b. Combine data from more than one table
3c. Edit/change data. (adding a criteria is optional)
3d. You can delete data. (adding a criteria is optional)
4. Reports:
Once you have your queries/Tables that need to be “outputted” (or say printed) – you can create reports. Access has a nice Report Wizard that would walk you through steps that are needed to create a report.
That’s about it for this Post on a Database Management System! How do you use Access in your Organization or personally? Speak up in the Comments section!
