Integer-Based Data Types Introduction to Numeric Fields A number is a digit (0, 1, 2, 3, 4, 5, 6, 7, 8, or 9), a combination of digits, or a combination of one or more digits, a separator, and one or more digits. Microsoft Access supports three categories of numbers and there are various ways you can apply one to a field. You can work in either the Datasheet View or the Design View of a Table. To create a field that supports numbers:
Introduction to Natural Number-Based Fields A natural number is one that contains either only one digit or a combination of digits and no other character. Examples of natural numbers are 122, 8, and 2864347. Microsoft Access supports different types of natural numbers.
A byte is a small positive natural number that is between 0 and 255. To create a field that will need this range of values, display the table in the Design View. Specify the Data Type of a field as Number. Then, in the lower section of the table, set its Field Size to Byte. If you have a value that you want to convert to a Byte, call a function named CByte. Its syntax is: CByte(Expression) As Byte This function takes one argument as the value that needs to be converted. It then converts it to a Byte.
Integers An integer is a natural number larger than the Byte. It can hold a value between -32768 and 32767. Because an integer can hold as much value as the Byte, you can apply the integer type wherever you would use a byte type. Microsoft Access supports the integer type through a data type called Integer. Like the byte, you can specify an Integer data type only in the Design View of a table. To apply the Integer data type to a field, in the Design View of the table, after specifying the field's Data Type as Number, in the bottom section of the table, set its Field Size to Integer. To convert a value to an integer, call a function named CInt. Its syntax is: CInt(Expression) As Integer The function takes a value or expression as argument. It then converts that value or expression to an integer.
Small Integers in SQL Like Microsoft Access, the SQL supports various types of integers. To support the Microsoft Access integer type, the SQL provides a data type named SMALLINT. It is made for fields that need numbers between -32,768 and 32767. Here is an example of creating small integer-based field: CREATE TABLE Elements
(
AtomicNumber SmallInt,
ElementName char(25)
);
To add a value for an integer-based field in SQL, simply specify its name in the appropriate placeholder. Here is an example: INSERT INTO Elements(AtomicNumber, ElementName) VALUES(1, 'Hydrogen'); Long Integers A Long Integer in Microsoft Access A long integer is a natural number whose value is between –2,147,483,648 and 2,147,483,642. Examples are the population of a city, the distance between two places of far locations, the number of words of a book. Microsoft Access supports the long integer type through a data type named Long Integer (in reality the data type is named Long). To apply the long integer type to a field:
Normally, when you set the data type of a field to Number, it is automatically set to Long Integer. If that's the data type you want, there is nothing more to do. If you have a value or expression to convert to a long integer, call a function named CLng. Its syntax is: CLng(Expression) As Long This function takes one argument that is a value or an expression. It converts it to a long integer.
Long Integers and SQL To support long integers, the SQL provides the INT, the INTEGER, and the LONG data types. They are equivalent to the Long Integer in Microsoft Access. An Automatic Long Integer Introduction One of the rules that the primary key must follow is that it must be able to uniquely identify each record in the table. One way to solve this problem is to manually add a unique integer. Of course, adding the numbers manually can lead to errors. As an alternative, Microsoft Access can generate a unique number for each record. Automatic Numbers in Microsoft Access To support this concept, the application provides the AutoNumber type. To automatically have a unique identifier associated with each new record in the database, create a field whose data type is AutoNumber. When a field receives this data type and when the first record is created, it receives the number 1. Every time a new record is created, the number is increased and assigned to the new record. The number never repeats. If a record is deleted, the numbers are not reset: the deleted record is gone with its assigned unique number. This ensures that each record keeps a unique number. The AutoNumber in Microsoft Access is not an actual data type, just like the other options of the Data Type combo box of the Design View of the table (their names are only made friendly to help select a type). AutoNumber is actually a long integer. There are two main ways you can apply the AutoNumber type to a column:
Automatic Numbers in the SQL To support automatic numbers, the SQL provides a data type named COUNTER. Here is an example of using it: CREATE TABLE TimeSheets
(
TimeSheetID COUNTER,
FullName CHAR(50)
);
Remember that, when performing data entry for a table that has an automatic number-based field, you don't provide a value for that field. Here is an example: INSERT INTO TimeSheets(FullName) VALUES('Joan Sons');
Also remember that every time you create a new record, the value of the automatic number-based field is incremented by 1. Here is another example: INSERT INTO TimeSheets(FullName) VALUES('Peter Mukoko');
As seen for the AutoNumber type in Microsoft Access, when a record is created in a table with the COUNTER type, the first value given to the field is 1, the second value is 2, and so on. Unlike the AutoNumber type that starts the records at 1, the COUNTER type allows you to specify by what value to start counting. To provide this information, add the parentheses to the COUNTER type. In the parentheses, enter the value by which to start. Here is an example: CREATE TABLE Payrolls
(
PayrollID counter(1001),
EmployeeNumber text(10)
);
In this case, the first record will have a value of 1001 for the field. Here is an example: INSERT INTO Payrolls(EmployeeNumber) VALUES('29-486');
The second record will be 1002. Here is an example: INSERT INTO Payrolls(EmployeeNumber) VALUES('74-085');
And so on. Here is an example: INSERT INTO Payrolls(EmployeeNumber) VALUES('38-475');
Here is an example of accessing the records: SELECT * FROM Payrolls; Instead of incrementing the records by 1, the COUNTER type allows you to specify by what range to increment. To provide this information, in the parentheses of COUNTER(), after the starting value, add a comma and the desired value. Here is an example: CREATE TABLE Stations
(
StationNumber counter(10070, 25),
StationName varchar(50)
);
This time, the first record will have a value of 10070 for the field. Here is an example: INSERT INTO Stations(StationName) VALUES('Ankoka');
The value of the second record will be incremented by 25. Here is an example: INSERT INTO Stations(StationName) VALUES('Bulham');
And so on. Here is an example: INSERT INTO Stations(StationName) VALUES(&#Fast Pursue'); Here is an example of accessing the records: SELECT * FROM Stations; The Auto-Number and Data Relations A column or field created with the AutoNumber type is the good candidate for a primary key. If you start a new table in the Datasheet View and the automatic column is created, that column is also automatically made a primary key. Of course, you can create an AutoNumber field in the Design View and make it a primary key. If the primary key is of type AutoNumber, the foreign key should use the Long Integer as its data type after selecting the Number in the Data Type combo box of the Design View of the table. Introduction A real number is a number that displays a decimal part. This means that the number can be made of two sections separated by a symbol referred to as the Decimal Separator or Decimal Symbol. In US English, this symbol is the period:
On both sides of the Decimal Symbol, digits are used to specify the value of the number. The number of digits on the right side of the symbol determines how much precision the number offers. Microsoft Access supports various types of decimal numbers. Decimal Numbers with Single Precision When using a decimal number, you may or may not be interested in a high level of precision for that value. If precision is of lesser importance, Microsoft Access provides the Single data type. A single is a decimal number whose value can range from -3.402823e38 and -1.401298e–45 if the number is negative, or 1.401298e–45 and 3.402823e38 if the number is positive. To apply the Single data type to a field, you must open the table in Design View. After specifying the field's type as Number, in the bottom section of the table, set the Field Size to Single.
If you have a value that you want to convert to a Single type, call a function named CSng. Its syntax is: CSng(Expression) As Single This function takes one argument as the value or the expression that needs to be converted. It then converts it to a Single value.
To support decimal numbers with a single precision, the SQL provides two data types named SINGLE and REAL. Here are examples of using them: CREATE TABLE Roads
(
RoadName varchar(32),
LengthInMiles SINGLE,
LengthInKilometers REAL
);
Each of them is equivalent to the Microsoft Access Single data type. At the time of this writing, there is a bug in Microsoft Access 2016: the Field Size in the Design View of a table doesn't display the options for a Number data type:
One solution is to write SQL code to create the table and its Single or Real data type as seen above. Another solution is, if the field was created already with a different type, to modify the column. Here is an example: ALTER TABLE Roads ALTER COLUMN LengthInMiles Single; Remember that, after writing code, you must execute it, which is
done by clicking the Run button
Decimal Numbers with Double Precision If you want to use a number larger than the Single type can carry, use a data type named Double. This type is used for numbers that range from 1.79769313486231e308 to –4.94065645841247e–324 if the number is negative or from 1.79769313486231E308 to 4.94065645841247E–324 if the number is positive. Besides supporting large values and this high level of precision, the Double data type provides various other options. To apply these options, you can use either the Datasheet View or the Design View of the table. To apply the Double data type to a field, open its table in Design View and set the field's Data Type to Number. Then, in the bottom section of the table, set its Field Size to Double. To convert an expression or a value to a double type, call a function named CDbl. Its syntax is: CDbl(Expression) As Double This function takes one argument as a value or an expression to be converted. It then converts it to a Double type of value.
Numeric Double-Precision and SQL To support decimal numbers with double-precision, the SQL provides three data types named NUMBER, FLOAT and DOUBLE. These types are equivalent to the Microsoft Access Double data type. Here are examples of using them: CREATE TABLE Elements
(
AtomicNumber smallint,
ElementName text(40),
AtomicWeight float,
MeltingPoint number,
BoilingPoint double
);
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||
Practical Learning:
Introducing Numbers
in the Design tab
of the Ribbon.
and click
the Detail section of the Ribbon
