Access databases, like the rest of the databases, store a lot of information in their tables. Some of them are interconnected, some are informative, some are entered by the user, and others are calculated automatically by the system. The variety of data in the database makes it necessary to classify them by type, depending on what information is stored in a particular field.
Data types
Separating fields according to the information that is stored in them brings us to the concept of a data type in an Access database or other database management system. This concept characterizes what kind of information is stored in the column, what properties it has and what actions can be performed with it. The following types exist in an Access 2013 database:
- Counter.
- Numerical.
- Monetary.
- Short text.
- Long text.
- Formatted text.
- Date and time.
- Logical.
- Substitution and attitude.
- Computed fields of various types.
- The attachment.
- Hyperlink.
You can set a default value for each of the fields. Then, when making a new record, the necessary information will automatically appear in the column. This data can be changed if necessary. Each field can also be made mandatory and require its uniqueness.
We consider each of these types separately, conditionally dividing into several categories.
Numeric data
As the name implies, these columns contain information in the form of numbers. To the indicated type of field we will assign the actual number format, counter and money format. The latter, in fact, is a combination of number and text.
Access numeric data types are selected if digital information is stored in this field. By default, integers are stored in this column. The developer selects the data format:
- The main one is for storing integers up to 8 bytes in size.
- Fixed - for fixed-point numbers. In this case, the required accuracy of the data is indicated: the number of characters after the decimal point.
- With separators, the format used in accounting. Separates with a space groups of digits: thousands, millions, billions, etc. The format is used for integers and with a fixed comma, for which indicate the desired number of characters after the decimal point.
- Percentage data types in Access are used to display percentages of numbers.
- Exponential - floating point number format.
The data type “Money” in the table has a sign of the currency indicated on the computer in the regional Windows settings. If you select the “euro” format, the euro icon will appear instead of the ruble icon - €.
When creating a table, Access automatically creates one field - "Code". Its data type is Counter. By default, it has the property of uniqueness, is auto-incrementing and indexing. The number in this column helps to accurately identify the row.
Text fields
To display strings, text data types are used in MS Access. This is “Short Text” - with its help text is entered up to 255 characters, in versions of Access until 2013 it was called “Text”. For such a column, the size is set. If, for example, special codes are stored here - international designations of airports or cities - just set the field size to 3 characters. To store a name or surname - up to 15-20, etc.
Long text - this type of field data in Access until 2013 was called “Field MEMO”. As the name implies, it contains long texts. These columns are used to describe products or services.
Long text can be entered into the database immediately formatted. Then it is placed in the "Formatted Text" type field. You can apply the desired font size and type to it, highlight it with color, insert a bulleted or numbered list, and other formatting.
date and time
There is a specialized type for displaying date and time. Moreover, a variety of formats makes it possible to display information here in the form of only date, only time, or both. For example, the full format can be used for logging - fixing the moment the user performs one or another action.
The date is displayed in long, medium or short form. The direct form of these database formats is taken from the Windows system settings. For example, formats may look like this.
The appearance of time information is also set in a long, medium or short format.
Logical data
Another important data type in Access is logical. It is used for those options that take only two values: "Yes" and "No", "Yes" and "No", "Plus" and "Minus", etc. For convenience, choose the display and storage format Yes / No , True / False and On / Off.
Computed Fields and Substitutions
It is not uncommon for columns to be added to the table whose values are automatically calculated based on the definitions in other columns. In such cases, calculated fields of various types are used.
As an example, we add to the test database a table of accounting employees of departments by gender. We have the number of women and men, and to determine the total number of employees in each department, we add the calculated field “Employees”, the value of which is equal to the sum of the values of the “Women” and “Men” fields:
In the table, the row data looks like this.
By analogy, calculated fields of types “Text”, “Date and time”, “Logical” and “Money” are used. For example, to determine the difference between two dates, to carry out operations on financial information, to combine lines or to extract only necessary from them.
There is another type of data in the Access DBMS with predefined information - this is “Substitution and Relation”. We can add a column, the values in which will be selected from a pre-compiled list. The list of data is obtained from another table or entered into the database in advance. In the example, create a list of cabinet numbers where the departments of the company are located. To do this, in the wizard for creating a lookup, select “A fixed set of values will be entered” and add the room numbers. In the table we only need to select a value from the list.
Object fields
Another category is Access data types containing objects. These are files of documents, tables, presentations, images, etc. - “Attachments”. Used if you need to store, for example, photographs of a hotel or a copy of a contract with a partner.
We also include hyperlinks containing the address to which the necessary data is contained. For example, in the table of hotels there may be a link to a page on the Internet with a detailed description and photos of hotels.
The data types described above in Access allow you to store any type of information in the database and create complex relationships between tables and their fields. Knowing this will enable the developer to easily add tables to the database and use the data correctly.