Table creation technology
The MS ACCESS DBMS provides several tools for creating tables: manual (creating a table in design mode or by entering data) and automated (development using a wizard). The wizard's work is based on the use of a large number of table templates, and the user can select the required fields from the templates. To properly master the technology of creating tables, it is recommended to use manual tools.
To create a new database table in design mode, you must perform the following steps in the Database window:
· on the objects panel, click on the button - Tables;
· on the object control panel, select - Create a table in design mode;
· in the designer window, form the table structure (fill in the list of fields indicating their types);
List of fields - line by line contains the names of the fields available in the table, and allows you to enter names for new fields line by line. By default, the field name becomes the table column name.
List of field types - allows you to select a field type from the drop-down list.
Field type selection button - a control element for selecting a field type. The button is displayed only after the entered field name.
Panel for editing field properties - contains a list of properties of the selected field: field size, field format, etc. Field properties can be left unchanged (assigned by default) or edited; close the designer window, saving the generated structure and specifying the table name.
Data entry and editing technology
The created table is opened in the Database window double click left mouse button on its icon. The new table has no records - only column names that characterize the structure of the table. Filling the table with data is done in the usual manner. The next entry is entered at the end of the table. Entering specific cell table (highlighted by the cursor) is carried out by typing information on the keyboard and then pressing a key
You can edit data in a table cell with full or partial replacement. To completely replace data, you need to move the cursor to the cell being edited, and then type (enter) new information. When partially replacing data, you can use the mouse cursor or a function key
To delete a record, you need to select it (click on the record marker area) and press the key , or execute the menu command EDIT-DELETE. Confirm deletion when prompted on the screen.
Data search and selection technology
MS ACCESS provides a fairly wide range of possibilities for searching and selecting information in databases. Such tools include the use of search commands, filtering, sorting, creating and using queries.
Queries are tools for retrieving data from tables and presenting them to the user for processing in a convenient form. The peculiarity of queries is that they select data from base tables and create a temporary result table based on them.
Using data queries, the following operations can be performed: selecting, sorting, filtering data, transforming according to a given algorithm, creating a new table, automatically filling tables imported from other sources, performing simple calculations in tables. MS ACCESS has several types of queries.
A select query is the most commonly used query type. This type of query returns data from one or more tables and displays it as a table in which records can be updated (with some restrictions).
A query with parameters is a query during which a dialogue is established with the user in order to determine the parameters for selecting data.
Cross queries - used for calculations and presentation of data in a structure convenient for their analysis.
To delete a record - deletes a group of records from one or more tables. Using a delete request, you can only delete the entire record, not individual fields within it.
To update a record - makes general changes to a group of records of one or more tables.
To add records - adds a group of records from one or more tables to the end of one or more tables.
To create a table - creates a new table based on all or part of the data from one or more tables.
An SQL query is a query created on SQL language(Structured Query Language) is used when creating queries to the database server.
The most common type of query is a select query. To create it using the designer, you must perform the following steps in the Database window:
· on the objects panel, click on the button - Queries;
· on the object control panel, select - Create a query in design mode;
· in the Add Table window, select the object (using the Tables, Queries, Tables and Queries tabs) for which the query is being generated. To complete the work you need to press the buttons<Добавить>And<Закрыть>;
· in the Request for selection window (the structure of which is shown in the figure), create a request structure by performing the following steps:
· in the table shown in the Table Structure area, double-click the left mouse button to select the names of those fields that should be included in the query (the names of the selected fields and tables automatically appear in the Field and Table Name lines of the Query Structure area;
If necessary, set parameters for sorting, displaying and selecting data using the corresponding line pointers:
Sorting - allows you to select a sorting method for each field: ascending or descending. In the request, the data will be sorted by the field for which the sort order is specified. Multi-level sorting is possible - by several fields at once from left to right;
Output to screen - allows you to set the display of field contents on the screen;
Selection condition - allows you to set an individual condition for selecting data for each field. Expressions are used to specify data selection conditions and to create calculated fields in queries; close the Request for selection window, saving the generated structure and specifying the name of the request.
Reference material for LR-1
Topic: “TECHNOLOGY FOR DATABASE DEVELOPMENT USING MICROSOFT ACCESS”
Lesson: Developing tables and queries
Database table development technology
The process of creating database tables can be divided into the following stages:
Development of a physical data model;
Creating a table using Table designer;
Establishing relationships between tables;
Filling tables with data.
Development of a physical data model
Before turning on the computer and launching ACCESS, we suggest, with a pencil in hand, to draw up the required characteristics of database objects, i.e. physical model data:
Establish a nomenclature of object description features (composition and number of fields);
Set the characteristics of each table field;
Present the results in tabular form (Table 1.1).
Once the composition of the object description characteristics and the corresponding field characteristics have been thought out, you can begin to create a table in the ACCESS environment. In the available versions of this system, the sequence of actions is almost the same. The differences consist only in some differences in the design of the dialog boxes. All our further examples will relate to Microsoft versions Access.
Table 1.1 – Table for describing the characteristics of database fields
Creating a table using Table designer
To create a table using Table designer you need to do the following:
Turn on your computer and boot software Windows and Access;
After Access downloads In the dialog box that appears, double-click the menu button File and select a team Create;
In the dialog box that appears Creation activate the switch Database, and then click the OK button (Figure 1.1);
Figure 1.1 – Dialog box Create a database
In the next dialog box that appears File new base data, assign a name to the file, indicating the name of the directory (folder) where the database will be stored; click on the button Create(Figure 1.2).
Figure 1.2 – Dialog box for specifying the name and location of the database file
In the next dialog box that appears Database activate bookmark Table and select a team Create in design mode.
In the dialog box that appears Table builder create a table structure in accordance with the established composition and characteristics of the fields.
The table designer (Figure 1.3) contains four information blocks:
Field name;
Data type;
Description;
Field properties.
Figure 1.3 – Window Table designer
In the block Field properties There are two windows (tabs): Are common And Substitution.
Field properties Are common must be filled out. In the window Substitution You can specify a list of values that will be displayed when you enter data directly into the table. In this case, the user will have to click on the desired value. These fields are called combo boxes.
When assigning names to table fields, you must follow the following recommendations:
The field name must not begin with a space;
The field name should not contain punctuation marks, parentheses, or exclamation marks;
Repeating names in the table is not allowed;
Field names can be up to 255 characters long. Name should be given as name minimum number characters (this is necessary to minimize the amount of memory and time to search for information). It is desirable that the field name be an abbreviation of the name of the object attribute that will be entered into the cells of the field.
Technology of filling data into rows of information blocks Table designer similar to the technology for working with tables in text editor Word.
Filling out information blocks should be done sequentially for each field. We recommend the following procedure for filling out information blocks:
Enter the field name;
Select data type;
Enter in block line Description a comment explaining the nature of the values entered into the cell of this field(later, when filling out the table, this comment is displayed in the hint line at the bottom of the screen);
Set field properties;
Repeat these steps for all other fields of the table.
After the field name has been entered in accordance with the recommendations outlined above, we select a data type for it. IN Table designer Microsoft Access data type selection can be done by selecting from the list. The list offers the following data types.
Text. Field of this type can contain up to 255 characters. These can be any symbols, including numbers. Only numbers can be entered into the text field if they are not intended to be used for calculations.
MEMO. The MEMO field is called the text notes field. This type of field is intended to be entered into text information longer than 255 characters (in Access 2000 - up to 65,535 characters). This data type differs from text data in that the table does not store the data itself, but links to data blocks that are stored separately. This significantly speeds up table processing. MEMO fields cannot be key or index fields.
Numerical. This data type is for characteristics of database objects that can participate in mathematical calculations.
Date Time. This type of data is intended to indicate a date or time that characterizes a specific table record (for example, the date of receipt of a product at a warehouse or the start and end time of a user’s work on the Internet). You can enter dates from 100 to 9999 in this field.
Monetary. This data type is similar to numeric. It differs from it only in the characteristics of the entered numbers. The precision of the number does not exceed four decimal places. The integer part can contain up to 15 decimal places. Currency symbols (r. or $) can be placed at the end of the number.
Counter. The field contains a unique (non-repeating) database table record number. The values of this field are not updated.
Logical. A type of field whose parameters can take only two values, interpreted as YES or NO (Yes/No), True/False, Enabled/Disabled. Logical type fields cannot be key, but can be index.
OLE (OLE object). In the cells of this type of field, links to applications developed for Windows are entered. These can be text, graphic and multimedia files. The amount of data stored in the cells of this field is limited only by the disk space of the computer.
Hyperlink. This data type allows you to insert a hyperlink into a field, which can be used to refer to any file or fragment of a file located on the same computer on which the database table is located, or on any computer in local network or the Internet. A hyperlink consists of three parts: an address indicating the path to the file; an additional address indicating the position of the fragment within a file or page of text; display text. Each part of a hyperlink can contain up to 2048 characters.
Substitution Wizard. When you select this type, it is possible to create a fixed list of values that can accept data entered into the cells of the field.
After establishing the name and data type, place the cursor in the appropriate line of the Description block and enter a comment to allow the user to correctly enter information when filling out the table.
After entering a comment, you need to go to the block Field properties, section Are common and set the required properties to the field. IN Table designer Each role, depending on the data type, is automatically (by default) given a specific set of properties. When designing a table, you can change these properties to suit your specific data requirements.
Table 1.2 lists the characteristics of the field properties specified in the information block Field properties, Are common.
Table 1.2 – Characteristics of properties of database table fields
| Field property | Characteristic |
| Field size | Sets the maximum size of data entered into the cells of this field. The data size of text (character) fields cannot exceed 255 characters. For numeric fields, the size of the entered data is set automatically depending on the type of number: byte – integers from 0 to 255 – 1 byte; integer – integers from -32 768 to +32 767 – 2 bytes; long – integers from -2 147483 648 to +2 147483 648; floating point integer with precision up to six digits – numbers from -3.4 x 1038 to +3.4 x 1038 – 4 bytes; floating point integer with precision to eight digits - numbers from -1.797 x 10,308 to +1.797 x 10,308 -8 bytes |
| Field Format | For fields of the Text and MEMO type, you can specify the data entry format in accordance with which the data will be displayed on the display screen. For fields of the Numeric and Currency types, the following formats can be selected: standard – the default format (there are no thousands separators, currency signs, the number of decimal places corresponds to the precision of the number); monetary – two decimal places are set and the currency symbol is displayed; fixed – at least one decimal place and two decimal places; with thousands separators – two decimal places and a thousands separator; percentage – a percentage sign is displayed at the end of the number; exponential – numbers are displayed in exponential form (for example, 1.10 ´ 103). The following formats exist for Date/Time fields: full format dates – set by default and have, for example, the following form: 04/15/97.05:30:10 RM; long date format, for example: Friday, April 13, 1997; medium date format, for example: 13-Apr-97; short format dates, for example: 04/13/97; long time format, for example: 14:33:10; medium time format, for example: 14:33 PM; short time format, for example: 14:33. For Boolean type fields, the following formats can be used: Yes/No; True/False; On off |
| Number of decimal places (field precision) | Set for fields of the Numeric and Currency types. Number of characters – from 0 to 15 |
| Input mask | The mask sets a template for entering data into fields of the Text, Numeric, Currency, and Date/Time types. The input mask for fields of the Date/Time type corresponds to the selected format |
| Field signature | Intended for a more descriptive field name that will be entered in the headings (“headers”) of tables and other elements of forms and reports. If a field signature is not entered, then the field names will be entered in the corresponding elements of tables, forms and reports |
| Condition on value | Sets restrictions on the values of the input data. For example, setting the condition “<100» для числового поля означает, что в это поле нельзя вводить данные более 100. Условие вида «Москва» OR «Вологда» OR «Новосибирск» означает, что вводимые названия городов должны быть только Москва, или Вологда, или.Новосибирск. Условия на значение вводимых данных задаются выражениями, состоящими из операторов сравнения, и значениями, которые используются для сравнения. При задании условий применяются известные операторы: < (меньше); <= (меньше или равно); >(more); >= (greater than or equal) When setting conditions, well-known operators are used: = (equal);< >(not equal). Expressions can use logical operators: OR (or), AND (and), as well as comparison operators: BETWEEN, IN, LIKE: BETWEEN – checks that the entered field value is within the specified range. The upper and lower bounds of the range are separated by the logical AND operator. For example, the expression BETWEEN 20 AND 45 means that the entered value must be between 20 and 45. This expression can also be written as: >50 AND< 100; IN – проверяет равенство введенного значения поля любому значению из given list. For example, IN (“Moscow”, “Vologda”, “Novosibirsk”) means that this expression also matches the expression “Moscow” OR “Vologda” OR “Novosibirsk”; LIKE – checks whether the Text or Memo fields match the specified character pattern. For example, the LIKE expression "Tech*" means that the character string you enter must begin with the characters "Tech" |
| Error message | Text that will be displayed on the screen if the input data values do not meet the specified conditions |
| Obligatory field | If a field is selected as mandatory, this means that when filling out the table, data must be entered into the cells of this field. |
| Blank lines | Allow blank lines for Text and Memo fields |
| Indexed field | It is recommended to set this value for fields whose values are used to search for data in tables. Setting an index significantly speeds up data retrieval |
Figure 1.4 shows an example of filling in table field properties.
Figure 1.4 - Example of filling in table field properties
After describing the characteristics (properties) of all fields of the table, the constructor is closed; at the same time they open dialog boxes, which prompt you to specify the table name and set key fields if they have not been specified.
When specifying a table name, consider the following guidelines:
The field name must reflect the content of the data in the table (object class);
The table name should not contain punctuation marks, parentheses, or exclamation marks;
The table name must not begin with a space;
There should not be tables with the same names in the same database file.
Key fields are set in cases where data from a database table must be associated with data from other tables. The key field must uniquely identify each record in the table. Key field data values are not repeated (should not be repeated). Any table field can be a key field if the data values of this field can uniquely identify the entire record. If a record cannot be unambiguously determined by the data value of one field, then several key fields are set. You can select a field like Counter, which uniquely identifies each table entry. A key field is created when describing field properties in Table designer. To do this, select the required field and click on the corresponding button on the toolbar.
Let's make a few comments on table development technology. Technology of work in Table designer is completely similar to working with tables in the Word text editor.
When creating several tables containing the same characteristics of objects, you should use data copying technology. To do this you need:
1) open the previously created table in the mode Constructor;
2) select a field that is repeated in another table;
3) copy the selected field (with all its properties) to the clipboard;
4) when constructing another table, paste the field characteristics from the clipboard into the corresponding line Table designer.
After the database table structures are created, it is necessary to establish logical connections between the tables.
Establishing relationships between tables
Establishing relationships between tables is necessary to ensure data integrity. Data integrity ensures that information is protected from accidental changes in related tables. In linked tables, one table is the master table and the other table is the child table. The main table must contain a key field. The subtable must contain a similar field that is not a key field.
To establish relationships between tables, you must perform the following steps.
On the toolbar of the database window, activate the command (icon) Data Schema;
In the data schema builder window that opens, enter the main and subordinate tables (Figure 1.5);
Link tables using the same field (Figure 1.6).
When creating a link, the option is enabled Ensuring data integrity. This option does not allow arbitrary deletion or modification of records in the main table. If you set (enable) connection parameters between tables Cascade update of related fields And Cascade deletion of related records, then with any changes in the data in the main table there will be automatic change related data in a subtable.
Figure 1.5 – Data schema builder window
Figure 1.6 – Example of establishing a one-to-many relationship
Figure 1.7 shows an example of a communication diagram for the “Educational Process” database.
Figure 1.7 – Communication diagram of the “Educational process” database
After the composition of the database tables has been established, the structure of each table has been developed, connections between tables have been defined and established, and we begin to fill the tables with data.
The technology for entering data into tables is done in two ways:
Direct data entry into table cells;
Organization of data entry through forms.
When choosing the first data entry method, you must be guided by:
Reducing the likelihood of operator errors;
Convenience of organizing the data entry process itself.
If the database table has a small number of fields that are located on the monitor screen and is not connected to other tables, and also if you are creating a non-commercial system, then you do not need to create a corresponding form for data entry.
What is data import, export and attachment?
What is the difference between data import and data append?
In what cases is it advisable to use import and in what cases is it advisable to join data?
What is data mining?
In what areas of activity is AD used?
What are the main stages of the IAD process?
What is the purpose of information analysis?
What is data transformation technology used for?
What operations does the problem of finding patterns consist of?
What causes the appearance of IAD?
Glava 6TECHNOLOGY FOR DATABASE TABLE DEVELOPMENT
6.1. Development of a physical data model
Before turning on the computer and launching Access, it is necessary to install the required characteristics of database objects - the physical data model, i.e. the nomenclature of the characteristics of the object description (composition and number of fields) and the characteristics of each field of the table, and present the results in tabular form (Fig. 6.1).
You can then begin creating the table in Access. In all available versions of this system, the sequence of actions is almost the same. There is only some difference in the design of the dialog boxes. Let's look at examples of creating tables using Microsoft Access 2000.
6.2. Create a table using the table designer
To create a table, you must perform the following steps:
=> turn on the computer and download the software - Windows and Access;
=> in the dialog box that appears, double-click on the menu File and select a team Create;
=> in the dialog box Creation(Fig. 6.2) activate the Database switch and click on the [OK] button;
=> in the dialog box New Database File(Fig. 6.3) assign a name to the file, indicating the name of the directory (folder) where the database will be stored, and click on the button Create;
=> in the dialog box Database activate bookmark Tab-faces and select a team Create in design mode;
=$ in the table designer dialog box that appears (Fig. 6.4), create a table structure in accordance with the established composition and characteristics of the fields.
The table designer contains four information blocks: Field name; Data type; Description; Field properties.
In the block Field properties There are two windows (bookmarks) - Are common And Substitution. General properties fields must be filled in. In the window Substitution you can specify a list of values, which, when entering data, will be displayed directly in the table. In this case, the user will only have to click on the desired value. These fields are called combo boxes.
When specifying a name for a table field, you should follow the following recommendations:
the field name must not begin with a space;
Although the field name can contain up to 64 (Access 2000) characters, it should be specified with a minimum number of characters in order to minimize the amount of memory and information search time;
It is desirable that the field name be an abbreviation of the name of the object attribute that will be entered into the cells of the field;
The field name must not contain punctuation marks, parentheses,
exclamation marks;
Repeating names in a table is not allowed.
The technology of entering data into the lines of information blocks
The technology of the table designer is similar to the technology for working with tables.
tsami in the Word text editor. Moreover, filling out the information
tion blocks should be produced sequentially for each
th fields in the following order:
=> enter field name;
=> select data type;
=> enter into block line Description a comment explaining the nature of the values entered into the cell of this field (later, when filling out the table, this comment is displayed in the tooltip line at the bottom of the screen);
=> set field properties;
=> repeat these steps for all other fields of the table.
In the Microsoft Access table designer, you can select a data type through a list. Here are the types of data included in this list.
Text. A data field of this type can contain up to 255 any characters, including numbers. Only numbers can also be entered into such a data field if calculations are not intended to be made with them.
Memo. This type of data field, called a text notes field, is intended to contain text information longer than 255 characters; in Access 2000 - up to 65,535 characters. This type of data differs from text data in that it is not the data itself, but links to corresponding data blocks stored separately, which significantly speeds up the processing of tables. In this case, data fields of the type Memo cannot be key or index.
Numerical. Data of this type are intended to characterize database objects that can participate in mathematical calculations.
Date Time. Data of this type is intended to indicate a date or time characterizing a specific table record, for example, the date of receipt of goods at the warehouse or the start and end time of the user’s work in Internet networks. You can enter dates from 100 to 9999 in this type of data field.
Monetary. Data of this type are similar to data of the numeric type and differ from them only in the characteristics of the entered numbers. The accuracy of the number of data of this type does not exceed four decimal places, and whole part can contain up to 15 decimal places. At the end of the number, a currency designation can be placed.
Counter. The field contains a unique (non-repeating) entry number of the database table. The values of this field are not updated. An Access 2000 table can contain 2 billion records.
Logical. Data field parameters of this type can take only two values, interpreted as Yes/No, True/False, On/Off. Boolean data fields cannot be key fields, but can be index fields.
OLE(OLE object). The cells of this type of data field contain links to applications developed for Windows. These can be text, graphic and multimedia files. The volume of data of this type stored in field cells is limited only by the disk space of the computer.
Hyperlink(Hyperlink). Allows you to insert an address into the field, with which you can refer to any file or fragment of any file located on the same computer where the database table is located, or on any computer on the local network or on the Internet.
A hyperlink consists of three parts: an address indicating the path to the file, an additional address indicating the location of the fragment within the file or page of text, and the displayed text. Each part of a hyperlink can contain up to 2048 characters.
Master of substitutions. Allows you to create fixed lists of values that can accept data entered into field cells.
After establishing the name and data type, the cursor is placed on the appropriate line of the block Description and create a comment that allows the user to correctly enter information when filling out the table.
It is recommended to always enter a comment, especially in cases where the field name or signature does not contain enough information to correctly enter the data. For example, when entering the characteristics of technological equipment in the Z) max field (see Fig. 4.3), the user should know that the maximum diameter of the workpiece that can be processed on a machine of a given model is indicated in millimeters, i.e. in this case in the block line Description You can make the following comment: maximum diameter of the workpiece in mm.
After entering a comment, you need to go to the block Mine-properties of the field In chapter Are common and set the required properties to the field. In the table designer, each field, depending on the data type, is automatically (by default) given a specific set of properties. When constructing a table, these properties are modified to suit specific data requirements.
In Fig. Figure 6.5 shows a fragment of the table structure created in design mode, with a description of the properties of one of the fields.
After describing the characteristics (properties) of all fields of the table, the designer is closed, and dialog boxes open in which it is proposed to specify the name of the table and set key fields if they have not been specified previously.
When specifying a table name, consider the following guidelines:
the name must reflect the content of the data in the table (object class);
the name should not contain punctuation marks or parentheses;
the name must not begin with a space;
there should not be tables with the same names in the same database file.
Key fields are set in cases where the data of one database table must be linked to the data of other tables. In this case, the key field must uniquely identify each record in the table, and its values must not be repeated.
A key field can be any table field whose data values uniquely define the entire record. If a record cannot be unambiguously determined by the data value of one field, several key fields are set. You can select a data field as a key field Counter, which uniquely identifies each table entry.
A key field is created when describing the properties of fields in the table designer, for which you should select the required field and click on the corresponding button on the toolbar.
When creating several tables containing the same characteristics of objects, you should use data copying technology. To do this, you must perform the following steps:
=> open the previously created table in Constructor;
=> highlight a field that is repeated in another table;
=> copy the selected field (with all its properties) to the clipboard;
=> paste the field characteristics from the clipboard into the corresponding row of the table designer.
When database table structures are created, to ensure data integrity it is necessary to establish connections between related tables.* Data integrity guarantees the protection of information from accidental changes in related tables.
In linked tables, one table is the master table and the other table is the slave table. The main table must necessarily contain a key field, and the subordinate table must contain a similar field, which is not a key one.
To establish relationships between tables, you must perform the following steps:
=> on the toolbar of the database window, activate the command with the corresponding icon Data Schema(Fig. 6.6);
=> In the data schema builder window that opens (Fig. 6.7), enter the main and subordinate tables;
=> link tables using the same field.
In Fig. Figure 6.8 shows the data schema builder window in which a one-to-many relationship is established between two CAD TP “LASER 2000” tables. During the connection creation process, the parameter is enabled Ensuring data integrity, in which arbitrary deletion or modification of records in the main table is not allowed.
If you set (enable) connection parameters between tables Cascade update of related fields And Cascading deletionrelated records, then any changes to the data in the main table will automatically change the related data in the subordinate table.
So, the composition of the database tables has been established, the structure of each table has been developed, and connections between the tables have been defined and established. You can start populating tables with data.
* An information system may have tables that are not related to other database tables.
Rice. 6.8. Data diagram window with established connection one to many
Data entry into tables is done in two ways: directly into table cells and through forms. When choosing a method for entering data into tables, you must be guided by the following considerations:
reducing the likelihood of operator errors;
Convenience of organizing the data entry process itself.
Control questions
What information blocks does the table designer consist of and in what sequence should they be filled out?
How many characters can a field name consist of?
Can a field name start with spaces?
What characters cannot be used when denoting a field name?
What is the difference between text type data and Memo type data?
What is the difference between numeric and monetary data types?
When should you use OLE data type?
When should hyperlink data be used?
In what cases is a field assigned the key property?
Can a key field have duplicate data values in a DB table?
In what cases is a field assigned the required property?
What tables are called master and slave?
What is data integrity assurance?
Gla va 7
QUERY DEVELOPMENT TECHNOLOGYIN DBMSMICROSOFT ACCESS
7.1. Types of requests when working with data
The main purpose of any information system consists of providing the user with the necessary and reliable information, and databases are more suitable for this purpose.
Processing of information contained in database tables is carried out using queries, which are a certain set of commands designed to search and process information in tables according to user-specified conditions (field values). In the Access system, depending on the actions performed, you can create the following types of requests: to perform actions (selection); update; addition; deletion; creating a table.
Types of requests may differ in the technology of creation and the form of presentation of information. Depending on the creation technology, queries are divided into constant and parametric. In persistent queries, the conditions for selecting information do not change for a long time. In parametric queries, the information selection parameters change.
As a result of executing queries, dynamic tables are obtained, which can be of two types in their form: with a structure corresponding to the original database table(s), and with a structure different from the original database table(s), which are called cross-tables .
Unlike the original database table, the column headings in the cross-tab are not the names, but the values of the selected fields. Cross tables are formed using special types of queries - cross, i.e. queries that are used to select information while simultaneously grouping data by the values of individual fields.
7.2. Ways to create queries
The Access system offers the user two ways to create queries:
design in mode Menu;
programming in mode SQL.
Creating a request in mode Menu performed using a wizard system.
In this case, the user, having specified the query parameters in the design window, uses the capabilities provided by the wizard.
In this case, the Access system automatically generates the program code in the form of a special sequence of commands in a structured query generation language - SQL.
When programming in mode SQL the user must describe all actions performed upon request using the appropriate commands in SQL.
Possibility of constructing queries in the mode Menu sufficient for solving almost any problems of processing information in database tables.
Let's look at the technology for constructing queries using the example of the Access 2000 DBMS, in which query design can be performed in two ways: independently and with the help of wizards. For any design method, you need to open a window Requests database objects (Fig. 7.1) by activating the [Query] button*.
Rice. 7.2. Initial dialog window when creating a request
The user can choose the following options for constructing queries:
click on the [Create] button in the menu bar of the database elements window;
click on the [Create a query in design mode] icon;
Click on the [Create a request using the wizard] icon.
When choosing the first method, a request creation window opens (Fig. 7.2), in which the user is offered Independentcreating a new request And Creating a query using the constructor in modes: Idle request; Cross; Repetitive for-pussy; Records without subordinates. performance and optimization...
A table is an object defined to store data. Each table includes information about a real-world object, such as a firm's customers. A table consists of a header and a body. The header includes the object attribute names (columns) and their properties, such as the customer's last name, phone number, and address. The body contains tuples (rows), each row representing a set of column values that store data about a particular object instance.
Creating a table. Let's start creating the first table. It should contain information about tenants.
In the database window, click on the button Tables press the button Create, to start designing a new table. Access opens a dialog box that asks you to use one of the following methods to create a table.
Table mode─creating a table in a tabular view (the table is designed while filling it out by analyzing the input data).
Constructor─creating a table using the table designer.
Table Wizard─create a table using the table wizard based on a collection of tables and fields.
Select an option Constructor to create a table using the table designer and click the button OK.
At the top of the window that appears (Fig. 7.3), specify the fields that the table should contain. In the lower part Field properties give a detailed definition of the format of the table fields, specifying the necessary characteristics for each field.
Rice. 7.3. Tenant table structure
Entering data into a table. Entering data into table cells is done in the usual way ─ you move the cursor to the desired cell, i.e. make it active and enter data into it (enter data into cells for a field with a data type Counter is not required, since the values in this field appear automatically when you enter data in any other cell of this record). The contents of the Tenant table are shown in Fig. 7.4.
Editing and printing records. The user can add any number of records to the table, delete or correct existing data. The data to be deleted must first be marked using the selector column and pressed or selected from the menu Edit team Delete or Delete entry. To avoid accidental errors, Access will ask you whether the marked entry should really be deleted, i.e. the user will have the opportunity to refuse deletion in time.
Rice. 7.4. Contents of the Tenant table
If the table needs to be transferred to paper, from the menu File you should select a team Seal. Only some entries can be printed. They should first be marked in table filling mode and then the command should be activated Seal. In Group Print activate the button Selected entries and click on the button OK to start the printing process (Fig. 7.5).
Rice. 7.5. Print window view
Since our database consists of two tables, we create the Rental table in the same way. In Fig. 7.6 shows the contents of the Rent table.
Rice. 7.6. Contents of the Rent table
Making connections. Access allows you to build relational databases, the individual tables of which can be interconnected by relationships. To do this from the menu Service you should select a team Data schema. A window will appear on the screen Data schema. The first time you open it, it will be empty.
To add tables or queries to a window Data schema, you must select an option Add table on the menu Connections orclick Add table button located in the icon line. Using your mouse, drag the field that you want to use to establish a link from the list in one table to the corresponding field in another table.
A dialog box will appear on the screen Changing connections , which will suggest relationships between tables. After pressing the button Create in the window Data Schema the created connection between the Tenant and Rent tables will be graphically shown (Fig. 7.7).
Rice. 7.7. Data Schema
Data integrity is a system of rules used in the Access DBMS to maintain relationships between records in related tables, as well as to provide protection against accidental deletion or modification of related data. To establish data integrity, you must check the “Ensure data integrity” property in the table selection window for the data schema. The properties “Cascade update of related fields” and “Cascade deletion of related records” are set in the same way.
This work examines the two most important objects in Access: databases and tables. The database window is like the starting point from which almost all operations on database objects begin. Access displays toolbars that match the type of active database object.
By default, all tables open in view mode. If the view mode has been changed, to switch back to Datasheet View, expand the list next to the Datasheet View button and highlight Datasheet View.
The entry marker, which resembles a pencil, indicates that changes are being made to the entry. To add a new entry to a table, use the entry marked with an asterisk. If the table contains OLE objects, then the words "BMP Bitmap" indicate that the field contains a graphic. You cannot change the text contained in the fields of OLE objects! To view the drawing contained in an OLE object field, double-click the data cell that contains the string "BMP Bitmap". The graphical paint editor and the drawing stored in the OLE object field will be opened in it. To return to Access, close the Paint window.
To select several records one after another, press the left mouse button when the mouse pointer is over the selection button for the first record in the group and, without releasing the left mouse button, drag the mouse pointer over the selection buttons for the remaining records in the group.
At the top of the table are the field headings. When the mouse pointer moves over the field header, it turns into a small downward-pointing arrow. The column header allows you to resize the field and select the field to copy or move it (left mouse button).
In the table designer mode, you can view and change the values of properties of fields and the table as a whole, set the format for displaying field values, etc. This mode also allows you to get information about table indexing.
Click the "New Database" button on the toolbar or select the "File, New Database" command. For the New Database button and the File, New command to be available, the database window must be active or the main Access window must be empty.
To create an empty database, expand the General tab of the New dialog box, highlight New Database, and click OK. The New Database File dialog box appears.
Access table properties define the table's properties as a whole. Open the table in Design view and click the Properties button on the toolbar. Each table has 5 properties:
- Description. Defines text that contains a description of the table in the database window.
- Defines the requirements for data entered into the fields of a record. Used to ensure data integrity and consistency. This property, unlike the similar field property, applies to several (all) table fields.
- Filter. Defines the subset of records that are returned after applying a filter to the table.
- Order By. Determines the sort order of records in the table.
In the table form you can set the following table field properties:
- Field Name. Each table field must have a unique name. It is considered good practice not to include spaces in the field name and to replace spaces with underscores "_".
- Data Type:"Text", "MEMO Field", "Numeric", "Date/Time", "Currency", "Counter", "Boolean", "OLE Object Field".
- Description. This text appears in the status bar when you add or change data in a field.
- Key field (Primary Key). To make a field a key, select it and click the "Key Field" button on the toolbar.
The General tab contains the basic properties of the field. In the Lookup tab, you can set the behavior of the field in the form. The following list shows the table field properties that can be set in the "General" tab:
- Field Size. Defines the data size for fields with the type "Text" (an integer in the range from 0 to 255, the default size is 50) or "Numeric" (you need to select the field size in the drop-down list). The Field Size property is not set for fields with data types "Date/Time", "Boolean", "Currency", "MEMO", and OLE object fields.
- Field format (Format). Allows you to specify formats for displaying text, numbers and dates on the screen and for printing. You can leave the format selected automatically, select a format from the drop-down list, or enter a custom format. The Field Format property is not set for OLE object fields.
- Number of decimal places (Decimal Places). Determines the number of decimal places used when displaying numbers.
- Input Mask. Specifies an input mask to make it easier to enter data into the field. For example, it is convenient to create the following input mask for the "Phone" field, allowing you to enter only numbers and automatically adding intermediate characters: "(___) ___-____". To launch the Input Mask Creator, select a field with a data type of "Text" or "Date/Time" and click the builder button ("...") on the right side of the "General" tab.
- Caption. Specifies the text that appears for a table or query in the column header in Datasheet view. Any punctuation marks can be used.
- Default Value. Allows you to specify a value that automatically appears in the field when a new record is created. For example, in a field with a Date/Time data type, you can enter the current date by default. Not set for fields with the Counter data type and OLE object fields.
- Validation Rule. Defines requirements for input data. The value of this property is an expression. Not set for Counter, MEMO, and OLE object fields.
- Error message (Validation Text). Allows you to specify the text of the message displayed on the screen if the entered data violates the condition defined in the "Condition on value" property.
- Required field. Indicates whether the field requires a value to be entered. Null values are not allowed in this field. The key field must be set to Yes.
- Empty lines (Allow Zero Length). Determines whether blank lines are allowed in this field. Used for fields with the data type "Text" and "MEMO".
- Indexed field. Defines an index created on a single field.
- New Values. Set only for fields with the Counter data type and determines how the Counter field values change when new records are added. Typically the value used is: "Sequential" - the value of the counter field is increased by 1 in each new record.
Expand the "Tables" tab and click the "Create" button. In the New Table window, select the Design element and click OK. Define the above properties for all fields of the table you are creating. Click the "Datasheet View" button to switch to the tableau mode and see the result of your work. The Save As dialog box appears. Enter a name for the table and click OK or
To improve productivity when entering data into a table, you need to set default values for table fields. For example, the default value "=Date()+28" is an expression. It returns the current date and adds 4 weeks to it. Once the default values are assigned, return to Tableau mode by clicking the Tableau Mode button on the toolbar.
Before you start populating a table, you need to determine the relationships of this table with other tables in the database, define a key field, and create indexes. A primary key is one or more table fields that uniquely identifies a record. Defining a table key helps prevent duplicate entries from appearing in a table. Open the table in Design view. Press the key
Key fields are used to establish relationships between tables. When you define a table key, Access automatically indexes the table by the key value. In most cases, you associate a key field in one table with a corresponding field (often with the same name), which is called a foreign key field in a second table. Related fields must have the same data types and field size. The connection between tables can be carried out using fields with the “Counter” and “Long Integer” data types. The table containing the key field is called the master table, and the table containing the foreign key is called the linked table. There are four types of relationships: one to one, one to many, many to one, many to many. One-to-many and many-to-one relationships differ only in which table is the master table.
Table indexing. Although Access automatically indexes a table on the value of a key, you may need to create additional indexes on the values of other fields. Indexes are internal tables that speed up the construction of a table containing the results of queries and sort the table by the value of a key field. Each Access table can have up to 32 indexes, 5 of which can be compound (a compound index can have a maximum of 10 fields). But you should not create indexes for each table field and all their combinations, since this can significantly slow down the process of filling the table (when changing or adding a record, all indexes are automatically updated!).
To index a table by the value of a certain field, select the field by clicking the field selection button on the left side of the table structure form. Make the "Indexed field" property active and select the value "Yes (matches are allowed)" from the list. The "Indexes" window displays all indexes of the table. To create a composite index, enter its name in the Index Name column and select the required fields from the drop-down lists in the Field Name column. Switch to table view. Confirm saving the table structure.
Relationships between database tables can be defined and viewed in the window "Data Schema" (Relationships).
Select the Tools, Relationships command or click the Relationships button on the toolbar.
Select the Relationships, Show Table command or click the Show Table button on the toolbar. In the list of tables, select the required tables and click the "Add" button. Click the Close button.
Place the mouse pointer over a field in the main table, press the left mouse button and, without releasing it, drag the field icon that appears to the corresponding field in the linked table. The Links dialog box appears.
Click the Join Type button. The Join Properties dialog box appears. Select desired type associations.
In the Relationships dialog box, to enforce referential integrity, select the Enforce Referential Integrity check box.
Click the "Create" button to confirm the creation of the relationship and go to the "Data Schema" window.
Ensuring referential integrity of data is an important feature of Access. If referential integrity conditions are imposed on the relationship between tables, then Access does not allow you to add records to the related table for which there are no corresponding records in the main table, and to change records in the main table in such a way that then in linked table records will appear that do not have master records; and also delete records in the main table that have subordinate records in the related table.
When checked "Cascading update of related fields" changing the value in a key field of the main table leads to automatic update corresponding values in all related records. When checked "Cascade deletion of related records" deleting a record in the main table results in automatic deletion related records in a subtable.
Access allows you to change the length of text fields, format, value condition, and error message in tables that contain data. Open the table in Design view, select the field, and change the property value. Changing a field's data type may result in data loss. Access allows you to change the order of fields by dragging the field with the mouse to a new location.
Changing the relationships between tables containing data is a very drastic step, so Try to correctly determine the types of relationships before adding data to the table! Try to avoid changing table field names after you have created forms and reports that use that table.
A change in a relationship can be caused by a change in the data type of the fields on which the relationship is built. In this case, you need to delete the association, change the data types, and re-establish the association. To do this, close all table windows. Left-click the link you want to change. Press the key
The ease of adding and changing information is one of the main criteria for the effectiveness of a DBMS. To check the database structure, you need to enter test data into it. In this case, it is better to use table mode to add data. Another important function that a DBMS must provide is support for data integrity (“Validation Rule” of fields or tables).
Expressions are used to evaluate various quantities and contain at least one operator (for example, arithmetic operators, comparison operators, etc.). The most commonly used arithmetic operators are +, -, * (multiplication operator), / (division operator). The quantities to which the operator is applied are called operands.
There are comparison operators (for example, > (greater than sign), = (equal sign), etc.). Logical operators(And, Or, is, Not, Between, and Like) return one of the values True, False, or Null if the result cannot be calculated. The operator is used to merge (concatenate) two strings into one. You can create complex expressions from simple ones by combining them.
Value conditions use expressions that return one of the values “True” (data is correct) or “False” (data error). If the record data does not satisfy the value condition, then when you move to another record or try to save changes, a window appears informing you about this.
Conditions on values that check the correctness of the data only in one field, regardless of the values of other fields, are called conditions on the field value.
To check, you can set condition on the value of the record tables. In the value condition, records can be compared values of several fields at the same time.
Place the cursor in the "Condition on value" input field and click the "..." button (to open the Expression Builder) to the right of the input field. The Expression Builder dialog box appears. The left list in this dialog box highlights the table, and the center list displays a list of the table's fields. An example of an expression defining a condition on a value: [DateFact]>=[DatePlan] OR [DateFact] IS Null. In the "Error message" input field, enter the text of the error message that will be displayed when the value condition is violated.
Testing database applications often takes much longer than developing them. It is necessary to develop test cases that will help verify the correct operation of value conditions and referential integrity mode. While filling out the table, you can check whether the Default Value and Field Format property values are correctly defined. You can try entering incorrect data into the table to check the conditions for the meaning of fields and records. To fill it out, open the table in table view.
Condition on the value. Enter an incorrect value in the field and press the key<стрелка вверх>. When you try to save changes to a new record, Access automatically checks the condition for value, and you receive an error message. Uniqueness of table key values. In the added entry, enter the field values that match the previous entry, and then press<стрелка вверх>. An error message appears.
Loading...
