Benefit Sokhor I.L. Data entry and editing technology Table development technology

When creating spreadsheets using table processor the user performs a number of actions characteristic of this type of work. These steps constitute the technology for creating a spreadsheet:

1. Design and development of forms of output documents (on paper), as well as algorithms for obtaining calculated data.

2. Development of a spreadsheet, i.e. creating a title, header, entering formulas into calculation columns.

3. Data entry and obtaining calculated values.

4. Saving the table on external media.

5. Printing the table.

Calculations in tables

Formulas. All calculations in spreadsheets are produced using formulas. Any formula in MS Excel must begin with equal, plus or minus signs. Without these signs, the formula is interpreted as text.

Each formula consists of operators And operands.

Operand – This numeric value, text, link to a cell or group of cells. Built-in Excel functions can also act as operands.

Operator – this is the sign of the operation to be performed on the operands of the formula. Excel statements can be classified as follows: arithmetic, text, comparison and reference operators.

Arithmetic operators serve to fulfill arithmetic operations over the numbers. Table 1 shows the arithmetic operators that can be used in Excel.

Table 1 - Arithmetic operators

Comparison Operators are used to compare two values. The result of the comparison is the logical value TRUE or FALSE. The following comparison operators are used: = (equal sign), > (greater than sign),< (знак меньше), >= (greater than or equal sign),<= (знак меньше или равно), <>(the sign is not equal).

Text concatenation operator(ampersand) is used to combine multiple text strings into one line and is denoted by &.

To refer to a range, you must specify the address of the first and last cells of the range separated by a colon, For example, A1:A5, to link to a block, set the address of the upper left and lower right cells of the block separated by a colon, For example, A1:E5.

The following link operators can be used:

; (semicolon)– union operator, combines several links into one link, For example, =SUM(A2:A10,C2:C10);

(space)– set intersection operator, used to refer to common cells of two ranges, For example, (B2:D10 C4:C6).

When organizing calculations, formulas can be copied from cell to cell, and the cell addresses specified in the formulas can change in accordance with the type of link used in the formula.

Relative cell reference written as a sequence of column and row headings, For example, A3, D4. Its peculiarity is that when the formula is moved to a certain number of positions, this cell reference is replaced by a reference to another cell, shifted relative to the original one by the same number of positions and in the same direction as the formula.

Mixed cell reference also uses the $ sign, For example,$A3, B$2, and is a combination of relative and absolute references. When copying a formula, each part of the link behaves according to the specified address: the absolute part of the address remains unchanged, the relative part of the address changes.

Built-in functions

Function in Excel is the combination of several computational operations on values ​​acting as arguments to solve a specific problem.

Arguments– input data values ​​for a function used to perform operations or calculations. Function arguments must be specified in the order specified for the function. Each of them must have the type required by the function. Function arguments can be numeric values, cell references, ranges, names, text strings, expressions, and calls to other functions.

Excel has a wide range of built-in functions, divided into categories, to make calculations easier. Each of these categories includes functions that provide specific calculations. A brief description of the Excel function categories is given in Table 5.1. The functions are accessed using Function Wizards, the call button of which is on the panel Standard pictographic menu.

Table 1 - Brief Description of Excel Function Categories

It should be noted that the user can also develop their own functions using the Visual Basic For Applications language. In this case, the “User Defined” category is formed.

TECHNOLOGY OF WORKING IN AN ELECTRONIC TABLE

Creating a Chart

In any spreadsheet processor, you can easily find a menu containing many options for creating charts. Let us mention the most important of them.

Graph type– Allows you to select the type of graph or chart (for example, stack or pie).

Define series– helps you find spreadsheet cells containing data to build a graph. Each series is a set of sequential values ​​of one of the variables (for example, quarterly values ​​of the profit received in store 1).

x axis– sets the parameters necessary for marking the x-axis. In our example, we would specify the spreadsheet cells that contain the x-axis layout: sq. 1, apt. 2, apt. 3 and sq. 4.

Attributes– specifies additional parameters of the created graph or chart (names of the axes and the graph itself, legend, colors used, etc.).

Generalized technology of work

Spreadsheet Design

Merging Spreadsheets

Macros as a means of automating work

Decision Support Spreadsheet

Although working in each spreadsheet has its own characteristics, we can talk about some generalized (average) technology for working with it. A diagram of this technology is shown in Fig. 14.8.

At stage 1, the table structure is formed. The structure includes: defining the table title, row and column names, as well as entering source data, formulas and functions into the table cells.

Rice. 14.8. Generalized technology for working with spreadsheets

At stage 2, work is done with the data, which consists of examining the generated table. Such research may involve the use of certain mathematical models (simulation), methods of simultaneous work with several tables, and methods of working with databases.

Mathematical models help the user, based on an existing table, to obtain new information by solving such typical computer modeling problems: “What happens if?”, sensitivity analysis, etc. Solving problems like: “What happens if?” – allows the user to find out how the output parameters will change when one or more input values ​​(conditions) change. An extension of such problems are sensitivity analysis problems, which make it possible to determine how the model solution will change when one or more input quantities change with a given step in a certain range of values. The inverse of the problem: “What happens if?” – is the task: “How to do it?”. It occurs when your goal is to achieve a certain model value and you are looking for the values ​​of the input parameters that ensure the achievement of this goal. Various types of analysis of the data contained in the source table can be performed using built-in functions and procedures. Thus, the statistical functions included in the spreadsheet can be used in statistical analysis or for forecasting the data contained in the table. The use of financial functions allows you to analyze the effectiveness of planned capital investments, calculate the cost of securities or the amount of depreciation charges. To solve optimization problems, special mathematical programming models are used.

Often a company has a central office and several branches. In such conditions, the task of combining various documents and reports coming from these branches arises. Solving such a problem requires the use of special multi-table relationships and software methods for manipulating files and generating reporting forms. Simultaneous work with several tables is one of the possibilities of working with data in spreadsheets.

Sometimes when working with large spreadsheets you need to find a particular row (column) or sort the rows (columns) in the desired order. To do this, the spreadsheet provides limited DBMS software tools that allow you to manipulate rows and columns as database components.

Stage 3 of the technology allows you to graphically present the results obtained in the first and second stages and interpret them most clearly.

Stage 4 ensures that the resulting data is printed. In this case, the results can be printed in tabular form or in the form of graphic charts.

DESIGNING A SPREAD SHEET

Let's look at designing spreadsheets. For this purpose, we will create a forecast of the financial activity of a certain company for 5 years (Table 14.2). Income in any year is determined as the product of sales volume in physical terms and the selling price. The amount of profit is determined as the difference between income and the amount of expenses for a given year.

The user sets the initial data for the first year. Data for all subsequent years are calculated by a spreadsheet based on assumptions about the nature of their changes in the future. This data is located in the lower left corner of the table. 14.2. Costs and sales prices are determined taking into account a given price increase, and sales volumes are determined taking into account the increase in sales volumes. When these assumptions change, the spreadsheet should immediately recalculate the values ​​of all projected financial characteristics. For this purpose, the spreadsheet is designed in such a way that the predicted parameters are determined using formulas that depend on the initial forecast assumptions.

Designing a spreadsheet, the calculations for which we see in the table. 14.2 is based on the use of the copy and move commands we have already discussed.

The development of any spreadsheet begins with setting a goal. A spreadsheet designed for financial forecasting purposes must recalculate the company's financial performance whenever its forecast assumptions change.

Creating a spreadsheet begins by entering column and row names. The columns in our table are the years that make up the forecast period, and the rows are the projected financial indicators. In addition, the table includes forward-looking assumptions.

Next, data for the first year is entered (in our example, 1995), which is partly specified as numerical data (sales volume, selling price), and partly as formulas. So, for example, the formula for calculating income +B3*B4 is written in cell B5, and the formula for calculating profit +B5-B6 is written in cell B7. At the same time, numerical data expressing forecast assumptions is entered.

Table 14.2. Financial forecast of the company's activities

The most difficult part of designing our table is entering formulas into the second year column (1996). These formulas take into account first-year results and also reflect forward-looking assumptions. So, for example, 1996 sales are defined as 1995 sales times the percentage of growth indicated in the forecast assumptions (Figure 14.9).

Using relative and absolute addresses in the specified formula will allow you to copy it to the remaining columns. An absolute address for a cell containing sales growth percentage requires that it be used for all calculations within the spreadsheet. The relative address of the cell containing the sales volume of the previous year makes it possible to adjust it when copying the formula, since the logic for calculating sales volume for subsequent years is preserved.

Rice. 14.9. Using absolute and relative addresses

The rest of the parameters from column B to column C are recalculated in the same way.

Thus, the remaining columns (D, E, F) are filled by simply copying the formulas contained in column C. The copy command will automatically adjust the relative cell addresses contained in them. Finally, you can protect the created spreadsheet from changes (except for cells containing forecast assumption values).

The constructed spreadsheet makes it possible to create all kinds of financial forecasts by changing forecast assumptions. You can, for example, change one or more forecast assumptions to determine what will happen to earnings in 1999.

The results obtained can also be presented graphically.

Table 14.3. Financial Forecasting Spreadsheet in Formula View Mode

MERGING SPREADSHEETS

When working with spreadsheets, there is often a need to combine them. Among the tools joining spreadsheets note:

organization of inter-table connections;

consolidation of spreadsheets or parts thereof;

merging files.

Technology for developing single-table forms for the first stage of loading a database

At the first stage of loading the database, tables that are not subordinate to any other tables are filled. To fill out such tables, you can use single-table forms.

The technology for developing single-table forms includes determining the requirements for the forms being created and the design process itself. Before working on a computer, data subcircuits and screen form layouts are determined in accordance with the structure of the input documents and the composition of the details to be placed. Next, screen forms are designed using a specific DBMS.

Let's consider the technology for developing a single-table form for entering and correcting data using the PRINT VIEW table as an example. As a result of loading data by print type, records of only the PRINT TYPE table (load object) are formed. The PRINT VIEW table is not subordinate to other tables. Therefore, during the process of loading records, no links are established with records of other tables. Thus, only one PRINT VIEW table is included in the subschema for the form.

Let's define the structure of the screen form, which we will call PRINT VIEW. The form for entering data into the PRINTING TYPE table must contain, along with the usual details (descriptive NP - name of the type of printing), a large OP field (description of the type of printing) for placing text. OP field type is MEMO. The key CP attribute (print type code) does not fit into the form, since it is of the “Counter” type and is increased automatically for each new record.

Thus, the structure of the PRINT VIEW form is determined by:

form type - single-table (simple),

source of form records - PRINT VIEW table,

the presence, in addition to the usual field, of a large field for description text.

The data area will contain all the details (except for the unique key) of the PRINT VIEW table. The designed input/output screen form for working with data from the PRINT VIEW table is presented in Fig. 3. The remaining screen forms corresponding to the first stage of loading are similarly designed: FORM OF OWNERSHIP, SPECIALTY, TYPE OF PRINTING BASE, TYPE OF PRODUCT, SERVICE, TYPE OF ORGANIZATION, RAW MATERIALS AND EQUIPMENT.

Figure 3 -- I/O form for working with table data PRINT VIEW

Technology for developing forms for filling out two related tables (second stage of loading)

At the second stage of loading, the NAME OF ORGANIZATION-DETAILS OF ORGANIZATION and GROUP-STUDENT tables are filled in simultaneously. Composite multi-table forms are used to fill out tables in the second stage of loading.

When defining requirements for compound forms, the data subschema for the appropriate loading step is defined; defining the general structure of screen forms, i.e. their layouts in accordance with the structure of the input documents and the data subschema; determining the composition of the placed details for each part of the composite form.

After completing the above points, screen forms are designed using a specific DBMS.

Let's consider the development technology using the example of a composite form for simultaneous loading and working with data from two tables - NAME OF ORGANIZATION and DETAILS OF ORGANIZATION (load object), which are connected in the data schema by one-to-multivalued relationships.

The source documents of this form are the “List of Printing Organizations” and the “List of Supplier Organizations”. Two tables are loaded simultaneously from these documents: NAME OF THE ORGANIZATION and DETAILS OF THE ORGANIZATION.

When creating spreadsheets using a spreadsheet processor, the user performs a number of actions characteristic of this type of work. These steps constitute the technology for creating a spreadsheet:

1. Design and development of forms of output documents (on paper), as well as algorithms for obtaining calculated data.

2. Development of a spreadsheet, i.e. creating a title, header, entering formulas into calculation columns.

3. Data entry and obtaining calculated values.

4. Saving the table on external media.

5. Printing the table.

Let's look at the technology for developing a spreadsheet using the example of creating tables 5.1 and 5.2.

Table 5.1.

Table 5.2.

5.3.1. Data entry and editing

After you start Excel, the cell pointer is usually in the cell A1 worksheet with name Sheet1. This cell is the active cell into which the user can enter data. You can enter two types of data into worksheet cells: constants And formulas . Constants are divided into three main categories: numeric meanings, text meanings and meanings dates And time. Numeric values ​​can only contain numbers from 0 to 9 and special characters: + - E () . , % /. The text value can contain almost any characters.

Entering a constant

Constants are entered into the active cell from the keyboard. As you type characters, they appear in the formula bar and in the active cell. The blinking vertical bar that appears in the formula bar and in the active cell is called the insertion point.

You can record data entry into a cell in the following ways:

1) by pressing the key, after which the cursor will move to the next cell. The direction of moving to another cell after entering can be set on the tab Edit dialog box Options, opening it on command Service Parameters;

2) using the cursor keys [¬], [], [®], [¯] of the keyboard;

3) left-click on another cell of the worksheet.

 If the length of the entered text exceeds the width of the cell, it will extend beyond the right edge of the cell, as shown in Fig. 5.2, and be located on top of the adjacent cell while the latter is free. But the text is stored in one cell (in this example, in cell A1).

 To display long text in one cell you can increase the width of the column in which this cell is located. To do this, you can use one of the following methods: select the cell and run the command Format Column Auto-fit width(Fig. 5.3). Double-clicking on the right border of the column header will do the same; or manually , moving the mouse to the right border of the column header when the cursor changes to a double-headed arrow.

Note. To change the row height, you can use the command Format String Automatic height selection or increase the row height manually, similar to changing the column width.

 place text on multiple lines . To do this, select the cell and run the command Cell Format tab Alignment checkbox Wrap according to words, then click [OK]. In Fig. 5.4. shows what the cell will look like after such a transformation. Also on the tab Alignment window Cell Format You can select the alignment method for cell contents from the drop-down lists horizontally And vertically And orientation text in a cell.

Controlling the display of text values ​​in cells

 Values ​​that are stored in cells and appear in the formula bar are called stored values. If you enter a long numeric value into a cell, Excel uses scientific notation for the number. In this case, the precision of the value is chosen such that the number can be displayed in the cell. For example, if you enter the number in a standard-width cell 1238567890123 , then it will be displayed as 1.24E+12.

 Values ​​that appear in a cell according to formulas are called inferred or displayed values. The number of digits displayed depends on the width of the column. If a column is not wide enough, Excel displays a string of characters # . In this case, you just need to increase the column width.

Data editing

 For removal contents of one or more cells, you need to select the cell (or block of cells) and execute the command Edit Clear Content or press on the keyboard.

 For bug fixes , you can select a cell, press F2, and adjust individual characters by moving the insertion point.

Let's look at the procedure for creating and saving a table. 5.1 (we will not enter the initial data for now).

1. Download Excel with the command Start Programs Microsoft Excel.

2. Rename Sheet1. To do this, run the command Format Sheet Rename and change the sheet name Sheet1 on Goods .

3. Enter a table title Accounting for the movement of materials in warehouses in the cell with the address A1. To position the header in the center of the table (Fig. 5.5), select a range of cells A1:F1(since the table will contain 6 columns from A before F) and run the command Cell Format tab Alignment. In field horizontally select from list – in the center of the selection,in field vertical - centered Click [OK].

4. Enter table column names. For this:

 Merge cells A2 And A3, why select them and run the command Cell Format tab Alignment check the boxes merging cells And translate according to words. On the lists vertically and by horizontal set the alignment for the first table column header – in the center. Click [OK]. Then enter a title in the merged cells Warehouse no. .

 Perform similar actions to enter the name of the second column of the table - Material code by merging cells AT 2 And AT 3.

 Enter into cell C2 title Quantity, pcs. and position it in the center of the four columns. To do this, select the range C2:F2 and run the command Cell Format tab Alignment, set in the field horizontally – in the center of the selection, in field vertically – in the center.

Note. Team Cell Format tab Alignment, field installation Alignmenthorizontally option in the center of the selection can be replaced by using the [Merge and Center] button

 In cells C3, D3, E3, F3 Enter appropriate column names. For cells C3 And F3 run the command Cell Format tab Alignment, set horizontally and vertically – in the center , and also a checkbox translate according to words so that the text is distributed within the selected range in several lines.

6. To cell A9 enter - TOTAL .

7. Complete the font design of the cells for the title and header of the table. Select cells A1:F3 and execute Cell Format tab Font. Select in the field Font – Arial, Typeface – italics, Font size – 12 Fri. For cells A4:F9 select Font Arial, Typeface – ordinary, Font size – 12 Fri.

8. For the task table frames select a range of cells A2:F9 and run the command Cell Format tab Border. Select the line type and color from the lists, to specify the location of the boundaries, click on the [External] and [Internal] buttons and click [OK].

9. Activate Sheet2, rename it to Prices and create table 5.2. The design of the table is shown in Fig. 5.6. Please note that the table header is placed on one line.

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 must 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 data type differs from text data in that it is not the data itself, but links to the 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 a user’s work on the Internet. 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 precision of this type of data number does not exceed four decimal places, and the integer 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 that you 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 field to the required properties. 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 Cascading 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 one-to-many relationship established

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 you use hyperlink data?

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 is to provide 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

In the Access system, the user is offered two ways to create queries:

design 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. With 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...