Topic: Creating and using queries.

Purpose of work:

ü to consolidate the skills of editing tables;

ü get acquainted with the main types of inquiries;

ü learn to create select queries different ways;

ü learn to create complex queries;

ü learn to create cross-reference queries.

Theoretical part.

Inquiry Is a means by which information that meets certain criteria is retrieved from a database. Query results do not represent all records from the table, but only those that match the query.

Requests consist of a number of conditions, each condition consists of three elements:

1.the field that is used for comparison;

2. operator describing the type of comparison;

3. the value with which the field value should be compared.

Expressions and operators used in selection conditions.

Expressions and Operators	Description of expressions and operators
Numbers	Introduced without restrictions
Text	Must be quoted
Dates	Delimited on both sides by # symbols (for example, # 01.02.02 #)
*; +; -; /; ^	Arithmetic operators linking expressions
<; <=; >; >=; =; <>	Comparison Operators
And (And); Not (No); Or (Or)	Logical operators
Like	Used for replacement logic in expressions
In	To determine if a data item is contained in a list of values
Between ... And ...	To select values \u200b\u200bfrom a specific interval
?	Replaces one character (letter or number)
*	Replaces multiple characters

Queries can be simple, complex cross-reference.

Practical part

Progress:

1) Open your sample database.

2) Create a query to select students who only have good grades in all subjects using Query Wizards.

On the toolbar, select the command<Мастер запросов>.

In the dialog box that appears, select<Простой запрос> and click on the button .

In the next window, select the table on which the query is built (<Ведомость успеваемости>), and those fields that are involved in the request. Move them to the right side of the window using the button, click<Далее>... In the next window, also click<Далее>.

In another window, give the name of the request "Good people" and press<Готово>.

A table will appear<Хорошисты>, which reflects the names of all students and the subjects studied.

Open the table "Good guys" go to mode<Конструктор>... Here in the field<Условия отбора> under each item put a condition >=4 or 4OR5.

Note: Checkmarks in each field mean that you can enable or disable any field for selection.

Switch to table mode by answering<Да> to the question about saving the request. (The surnames of the “good guys” should remain in the table).

3) Using<Конструктора запросов> create a query to select a table<Личные данные>.

Click on the table<Личные данные>, go to the menu<Создание>, select command<Конструктор запросов >.

· Add the required table to the query field. Select it in the list and click on the button<Добавить>... Close the window<Добавление таблицы>.

· Select students whose last names begin with the letter "B" and who live in Anapa. For this:

Add to line<Поле> two fields<Фамилия> and<Город>;

In line<Условия отбора> enter the value in the first column
Like “B *”, and in the second column with the name<Город> - "Anapa";

close the request by saving it as "BBB" (you must have students living in Anapa on the list). Figure 2.

Figure 2.

Independent task

a) Make a request with a title<Запрос 1> table-based<Ведомость успеваемости>, which will indicate students who have marks of at least "4" in the first two subjects. (Make a request or via Query constructor, or through Query Wizard)

b) Make<Запрос 2> table-based<Ведомость успеваемости>, which will indicate students with no more than 30 hours of non-excuse absences. Add a good reason gaps field to this request between 30 hours and 45 hours (use the operator Between ... And… )

c) Make<Запрос> table-based<Личные данные>... Print the list of students who at the moment, i.e. as of today, is 17 years old (use the operator Between ... And ... )

Note: The date is written using the # symbol, for example, # 01.02.02. #

4) Create a query based on three tables<Ведомость успеваемости>, <Личные данные> and<Преподаватель>... Select students who live in Novorossiysk and who have a favorite subject "Management". Title <Запрос 4>. Use<Конструктор запросов>.

· On the menu<Создание> select<Конструктор запросов>.

· Add all three tables to the query field. Close the window<Добавление таблицы>.

To first column to row<Поле> drag from the first table with the mouse<Фамилия>, from the second table to the second column<Город> and from the third table to the third column of the row<Поле> - <Предмет> (Figure 3).

Figure 3

· In field<Условия отбора> in column<Город> enter the city "Novorossiysk" in the column<Предмет> enter "Management".

Save the query under a name <Запрос 4>.

· Open a request and check the result of the work done.

5) Run a query to create a new table, which should have fields<Фамилия>, <Имя>, <Пропуски по неуважительной причине>, <Город> and<Предмет>.

· On the menu<Создание> select<Конструктор запросов>.

Add all three tables from the window list<Добавление таблицы>... Close this window.

First line<Поле> transfer from the first table to the first column of the field<Фамилия>, in the second<Имя> and the third<Пропуски по уважительной причине>, drag the field to the fourth column<Город> from the second table and the last column will have a field<Предмет> from the third table.

Close the request by saving it with a name <Запрос 5>.

6) Create cross query.

· On the menu<Создание> select<Мастер запросов>.

In the dialog box, select<Перекрестный запрос>, click on the button .

· In the window<Создание перекрестных запросов> highlight the table<Ведомость успеваемости> and click<Далее>.

Select the field whose values \u200b\u200bwill be used as the row header - this is<Фамилия> and<Имя>... Click the button<Далее>.

Select a field whose value will be used as column headings, for example<Менеджмент>... Click the button<Далее>.

Select the function by which the values \u200b\u200bof cells at the intersection of columns and rows will be calculated (in this case Count - quantity). Click the button<Далее>.

Provide a name for the request <Итог по менеджменту> and click on the button<Готово>.

Independent task

Make similar queries for grades in three other subjects.

7) Present the teacher with all your database queries on the display screen.

8) Finish working with Access.

Transcript

1 Lab 1 Introduction to Access. Creation of tables Database (DB) is an ordered set of data intended for storage, accumulation and processing by means of a computer. To create and maintain databases (update them, provide access on request and issue data on them to the user), a set of language and software tools called a database management system (DBMS) is used. 1. Database objects access data Access database objects include: 1. Tables are designed for orderly storage of data. 2. Queries are for searching, retrieving data, and performing calculations. 3. Forms are designed for easy viewing, changing and adding data in tables. 4. Reports are used to analyze and print data. 5. Data access pages are designed to view, enter, update and analyze data over the network or from anywhere on your computer. 6. Macros are used to execute a common set of macros that process data. 7. Modules are designed to describe instructions and procedures for vBA language... The main object of the database is a table, which consists of records (rows) and fields (columns). At the intersection of a record and a field, a cell is formed that contains data. Each field in the table is assigned a unique name that cannot exceed 64 characters. Each field contains data of the same type. 1 Koshelev V.E. Access Practical Guide. M., S

2 Data types Type Text Field Memo Numeric Date / Time Currency Counter Boolean OLE Object Field Hyperlink Lookup Wizard Description Used to store character or numeric data that does not require computation. The Field Size property specifies the maximum number of characters that can be entered in this field. By default, the size is set to 50 characters. The maximum number of characters that can be contained in the text field, 255 Intended for entering text information exceeding 255 characters; can contain up to characters Designed to store numeric data used in mathematical calculations. On the General and Lookup tabs, you can set properties for a numeric field, including Field Size, Field Format, Number of Decimal Places Used to represent the date and time. The choice of a specific date or time format is set in the Date format property. It is intended for storing data, the accuracy of which ranges from 1 to 4 decimal places. Integer part can contain up to 15 decimal places Designed to automatically insert unique sequential (increasing by 1) or random numbers as a number new entry... The number assigned to the entry cannot be deleted or changed. Fields with this data type are used as key fields of the table. Designed to store one of two values \u200b\u200binterpreted as "Yes / No", "True / False", "On." / Off " Contains data created by other programs that use the OLE protocol. This can be, for example, word documents, electronic excel tables, pictures, sound and video recordings, and more. OLE objects are linked to or embedded in an Access database. You cannot sort, group, or index fields of OLE objects. Special type for storing hyperlinks. Designed for automatic field detection. It will create a combo box from which you can select data contained in another table or in a set of constant values \u200b\u200b2

3 Creating a database 1. Start Microsoft Access Click the button. 3. Name the new database "Notebook.accdb". 4. On the Create tab of the ribbon, on the Tables toolbar, click the Table Design button. 5. Enter the field names and specify the data types to which they apply. Field name Data type p / p Counter Surname Text Name Text Address Text Index Numeric Phone Text Hobby Text Email e-mail Hyperlink 6. Exit Constructor mode after saving the table under the name "Friends"; do not specify key fields. 7. Open the table "Friends" by double clicking and fill in 10 lines in it. 8. Add the fields "Patronymic" and "Date of birth", for this: 1) place the cursor on the field before which you want to insert a new column; 2) run the command: ribbon tab Table mode toolbar Fields and columns Insert; 3) by double-clicking on Field1, rename it to "Patronymic", and Field2 to "Date of birth". 9. Switch to Design mode with the command: ribbon tab Home Modes Design. 10. For the field "Date of birth" set the data type Date / Time; in the field properties, select Short Date Format. 3

4 11. Format the table as follows: 4) the grid color is dark red; 5) the background color is blue; 6) text color is dark red, size 12 pt, italic style. 12. Rename the Hobbies field to Hobbies. 13. Delete the entry numbered. Resize the cells so that all data is visible. To do this, just double-click the left mouse button on the border of the fields. 15. Arrange the fields in the following order: Last Name, First Name, Patronymic, Phone, Date of Birth, Hobbies, Address, Zip, Photo, Email. 16. Fill in the blank cells in the table. 17. In Design view, add a field "Marital status", which will contain a fixed set of values \u200b\u200bmarried, not married, married, not married. To create a drop-down list, we will use the Substitution Wizard: 7) set the Substitution Wizard data type; 8) in the dialog box that appears, select the line "A fixed set of values \u200b\u200bwill be entered" and click Next; 9) the number of columns is 1; 10) enter the details of the list married, not married, married, not married; 11) click Finish. 18. Use the drop-down list to fill in the new column. Since the table turned out to be wide, some inconveniences arise when filling out this column: the surname of the person for whom the "Marital status" field is filled in is not visible. To make the last name permanently visible when filling out the table, you must use the Freeze Columns command from the context menu of the Last Name field. 19. Show the work to the teacher. 4

5 Test questions 1. What is called a database (DB)? 2. What is a database management system (DBMS)? 3. What is the difference between Microsoft Excel and Microsoft Access? 4. What are the objects of the base microsoft data Do you know Access? 5. What is the main object in the database? 6. What are called fields and records in the database? 7. What types of data do you know? 8. How can I rename a field? 9. How can I create a dropdown box? 10. With what extension is the Access database file saved? five

6 Lab 2 Creating relationships between tables 1. Start Microsoft Access Let's create a database "Company". Employees of this organization work with clients and fulfill their orders. If you put all the information in one table, then it will become very inconvenient to work with. It will start repeating data. Every time an employee Ivanov works with a company, you will have to re-register the data about the employee and the client, as a result of which many mistakes can be made. To reduce errors, you can split the source table into multiple tables and establish relationships between them. It will be more rational than before. Thus, you need to create 3 tables: Employees, Customers and Orders. Employees Field name Data type Employee code Counter Surname Text Name Text Patronymic Text Position Text Phone Text Address Text Date of birth Date / Time Salary Money Photo Object OLE E-mail Hyperlink 6

7 Customers Field Name Customer Code Company Name Address Telephone Number Fax Address email Notes Data type Counter Text Text Text Numeric Hyperlink Field MEMO Orders Field name Order code Customer code Employee code Placement date Execution date Amount Completion mark Data type Counter Numeric Numeric Date / Time Date / Time Money Logical 3. Separate tables containing information on specific topic must be linked into a single database structure. Key fields must be specified to link tables. A key consists of one or more fields whose values \u200b\u200buniquely identify each record in the table. The most suitable as a key field is "Counter", since the values \u200b\u200bin this field are unique (that is, do not duplicate). 4. Open the Employees table in Design view. 5. Right-click on the Employee ID field and select the Key field command in the context menu that appears. If you need to set several key fields in the table, you can select them by holding down the Ctrl key. 6. For the Customers table, set the key field Customer Code, and for the Orders table, the Order Code. 7

8 7. The Orders table contains the fields Employee Code and Customer Code. When filling them out, some difficulties may arise, since it is not always possible to remember all the enterprises with which the company works, and all employees with a code number. For convenience, you can create drop-down lists using the Lookup Wizard. 8. Open the Orders table in Design view. 9. For the Employee ID field, select the Lookup Wizard data type. 10. In the window that appears, select the command "The lookup column object will use values \u200b\u200bfrom the table or query" and click the Next button. 11. In the list of tables, select the Employees table and click Next. 12. From the Available Fields list, select the Employee ID field and click the arrow button to enter the field in the Selected Fields list. Add the Last Name and First Name fields in the same way and click the Next button. 13. Select the sort order for the list by the Last Name field. 14. In the next dialog box, set the desired width of the drop-down list columns. 15. Select the Hide Key Column check box and click Next. 16. In the last step of the Lookup Wizard, change the label for the lookup field if necessary and click Finish. 17. Create a drop-down list for the Customer ID field in the same way. 18. After creating the key fields, you can start creating links. There are several types of relationships between tables: 12) in a one-to-one relationship, each key field record in the first table corresponds to only one record in a related field in another table, and vice versa. This type of relationship is not used very often. In- 8

9 where they can be used to split tables containing many fields to separate parts of the table for security reasons; 13) in the case of a one-to-many relationship, each record in the first table corresponds to several records in the second, but the record in the second table cannot have more than one related record in the first table; 14) in a many-to-many relationship, one record in the first table may correspond to several records in the second table, and one record in the second table may correspond to several records in the first. 19. Close all open tables, as you cannot create or modify links between open tables. 20. Run the command: ribbon tab Work with databases button. 21. If earlier there were no links between the database tables, then when you open the Data Scheme window, the Add Table window opens at the same time, in which select the Employees, Customers and Orders tables. 22. If relations between tables have already been defined, then to add a new table to the data schema, right-click on the data schema and select Add Table from the context menu. 23. Establish a relationship between the Employees and Orders tables. To do this, select the Employee Code field in the Employees table and drag it to the corresponding field in the Orders table. 24. After dragging, the Edit Links dialog box will open (Fig. 1), in which enable the Ensure integrity condition check box. This will prevent you from deleting records from one table and leaving the associated data from other tables unrelated. nine

Fig. 10 1. Creating a relationship between tables 25. The check boxes Cascade update of related fields and Cascade delete of related records ensure the simultaneous update or deletion of data in all subordinate tables when they change in the main table. 26. Communication parameters can be changed by clicking the Union button. 27. After setting all the necessary parameters, click OK. 28. Establish the relationship between the Clients and Orders tables yourself. 29. The result should be the data schema shown in fig. 2. Fig. 2. Data Scheme 10

11 This example uses one-to-many relationships. In the data schema, they are displayed as connecting lines with special icons next to tables. A one-to-many relationship is marked with a "1" near the master table (which has a primary key) and near the subordinate table (which has a foreign key). A one-to-one relationship is marked with two “1s” (both table fields have primary keys). An indefinite relationship has no signs. If a join is set, its direction is indicated by an arrow at the end of the connector (none of the combined fields are key and have no unique index). 30. In the Employees table, enter the data for seven employees. 31. In the table Clients enter data on ten companies with which this firm works. 32. In the Orders table, fill out several orders received by the company. 33. Show the work to the teacher. Test questions 1. With what can you create tables? 2. What is a key field? 3. How to set multiple key fields? 4. How to establish relationships between tables? 5. What are the relationships between tables? 6. What do "1" and mean in the data diagram? 7. Why is the Substitution Wizard needed? eleven

12 Lab 3 Selecting Data Using Queries Queries are the primary means of viewing, filtering, modifying, and analyzing information that is contained in one or more tables in a database. There are various types of queries, but the most common are fetch queries, so let's start our acquaintance with them. 1. Open the Firm database you created earlier. 2. Run the command: Create ribbon tab Query Wizard Simple Query. 3. In the dialog box that appears (Fig. 3), specify the Employees table and select the Surname, Name, Phone fields. Click "Next. Figure: 3. Creation simple request 4. Enter a name for the Phones request and click Finish. You will see a request in which you can view the phone numbers of employees. 5. Try to create the next query using the Designer, for this run the command: ribbon tab New Query designer. 12

13 6. In the Add Tables dialog box, select the Customers table and click the Add button and then the Close button. 7. To transfer the required fields to the request form, double-click on them with the left mouse button (Fig. 4). Figure: 4. Creating a query in Design mode 8. To sort the records in the Company name field in alphabetical order, select ascending from the Sort drop-down list. 9. Save the query with the name Client Addresses. 10. Create a request "Birthdays" yourself, where you can view the birthdays of employees. 11. Suppose we want to know which employee has a birthday in the current month, for example, in April. To do this, open the query in Design mode. thirteen

12. In the line Criteria for the field "Date of birth" enter the value * .04. *. In this record, * means that the date and year of birth can be any, and the month is the 4th (that is, April). After that, the request window should look like it is shown in Fig. 5. Fig. 5. Creating a Query 13. Close the Designer and view the result. If there are no records in the Birthdays query, then there is no person born in April in the Employees table. Add a few people born in April to the Employees table and see how the query changes. Queries are automatically refreshed each time it is opened. 14. If we need to find out which of the employees was born in May, we will have to create a new request or change the condition in the existing request Birthdays. This procedure is inconvenient and time-consuming. If you have to frequently execute a query, but each time with new values \u200b\u200bof conditions, use a query with a parameter. When you run such a request, screen 14

15 displays a dialog box for entering a value as a selection condition. To create a request with a parameter, the user needs to enter the message text in the Condition line of the request form (fig. 6). Figure: 6. Creating a query with the parameter 15. The entry Like [Enter date] means that when you open the query, a dialog box will appear (Fig. 7) with the text “Enter the date” and a field for entering a selection condition. If you enter the condition * .04. *, Then the query will display a list of employees born in April. Run the query again and enter the value * .05. *, See how the query has changed. Figure: 7. Window for entering selection conditions 15

16 16. Modify the "Phones" query so that when you run it, a dialog box with the message "Enter your last name" appears. Since you need to display a specific surname in the query, you do not need to write the word Like in the selection condition. 17. Change the "Phones" query so that when it starts, not only the surname, but also the name of the employee is requested. 18. Create a request "Completed orders" on your own, containing the following information: the name and surname of the employee, the name of the company with which he works, the mark of completion and the order amount. Take query data from several tables. 19. In the selection condition for the Completed flag logical field, enter Yes to display only completed orders in the query. 20. Make sure the Completion Flag column is not displayed. 21. Create a request Order Amount, which will display orders worth more than RUB. 22. Change the request so that the order amount is from to RUB. For these queries, you can use the comparison operators\u003e,<, =, >=, <=, < > and logical operators And, Or, Not, etc. 23. Sometimes in queries it is required to do some calculations, for example, to calculate income tax of 13% for each transaction. To do this, open the Order Amount request in Design view. 24. In an empty column of the query form, right-click on the Field cell and select Build from the context menu that appears. You will see the Expression Builder window (Fig. 8), which consists of three areas: the expression field, operator buttons and expression elements. At the top is the expression field, in which it is created. Items entered in this field are selected in the other two areas of the Builder window. sixteen

17 25. In the left list, open the Requests folder and select the Order Amount request. In the middle list, highlight the Amount field and click the Insert button. The ID of this field appears in the Expression field of the Builder. 26. Click on the * button and enter 0.13 (see Figure 8). Thus, we will calculate the income tax of 13%. Figure: 8. Expression Builder 27. Press the OK button, after which the value "Expression1: [Sum] * 0.13" will appear in the Field property cell. 28. Replace Expression1 with Tax and close the Constructor. 29. Open the request and see what you get. 30. Using the Expression Builder, add a Profit field to the Order Amount query, which will calculate the order revenue (that is, amount minus tax). 31. Create a query Managers, by means of which in the table Employees find all the managers of the company. 32. Show the work to the teacher. 17

18 Security questions 1. What are queries for? 2. What types of queries do you know? 3.What can you use to create queries? 4. What is a parameter query used for? 5. How can you make calculations in queries? 6. What does the entry in the query condition "\u003d 50" mean? 7. Can you create queries based on multiple tables? eighteen

19 Lab 4 Using Forms in a Database Forms are database objects designed to view data from tables and queries, to enter data into a database, correct existing data, and perform specified actions. A form can contain graphics, pictures, and other embedded objects. It is possible to enter data into tables without using any forms. But there are several reasons that make forms an indispensable means of entering data into a database: 15) when working with forms, access to tables (the most valuable in the database) is limited; 16) different people may have different access rights to information stored in the database. They are provided with different forms to enter data, although the data from the forms can enter the same table; 17) it is easier to enter data into a form than into a table, and more convenient, since, as a rule, one table record is displayed in the form window; 18) in most cases, information for databases is taken from paper forms (questionnaires, invoices, invoices, certificates, etc.). Screen forms can be made an exact copy of paper forms, thereby reducing the number of errors in input and reducing staff fatigue. You can create forms based on several tables or queries using the Wizard, using the AutoForm tool, “manually” in Design mode, saving the table or query as a form. The form created in any way can then be modified in Design mode. Let's consider some of the listed methods. 1. Run the command: ribbon tab Create toolbar Forms Other forms Form wizard. 2. In the Create Forms dialog box, select the tables (queries) and fields that will be placed on the form. Click the Next button. 23

20 3. In the following dialog boxes of the wizard, select appearance shape, style, set shape name. Click the Finish button. 4. With the help of the Wizard, create the forms Employees, Customers, Orders, Managers. 5. Open the Employees form in Design view. This mode is intended for creating and editing forms. 6. Place the elements in the order convenient for you, change the size and color of the text. 7. Add the text Company employees to the form heading. 8. Add a Button object to the form note (ribbon tab Constructor toolbar Controls). 9. After you "draw" the button with the pointer, the Create buttons dialog box will appear on the screen (Fig. 9). Figure: 9. Create buttons on a form 10. In the Form Tools category, select the Close Form action, and then click Next. 11. Select a picture or text to place on the button. 12. In the last dialog box of the Button Wizard, give the button a name and click Finish. 24

21 13. The Button Wizard has written a procedure for this button in Microsoft Visual Basic. You can view the event handling procedure using the Event handling command of the button context menu. 14. Create the Exit Application, Search Record, Delete Record buttons on your own. 15. Sometimes on the form it is required to place several pages containing data from various sources, reference or auxiliary information. A set of tabs can be used for this purpose. 16. Create an empty form. 17. To add a set of tabs to the form, click the Tab button on the Controls toolbar. First, only two tabs will be added with the formal names Tab 1 and Tab Add another tab. 19. Rename the tab labels so that they display the names of the data that will be located in them: Employees, Managers, Help. 20. Go to the Employees tab and drag the Employees form onto it from the database. 21. In the same way, place the Managers form on the Managers tab. 22. On the Help tab, put some tips for working with the database. 23. Save this form with the name Company employees. 24. In Microsoft Access, you can create button forms. They contain only buttons and are intended to select basic actions in the database. To create a button form, on the Work with Databases ribbon tab, select the Button Form Manager command. 25. If there is no button form in the database, you will be prompted to confirm its creation. Click Yes in the confirmation dialog. 25

22 26. You will see the Button Forms Manager, in which click on the Create button. 27. In the Create dialog box (Figure 10), enter a name for the new button form and click OK. Figure: 10. Setting the name of the button form 28. The name of the new button form will be added to the Pages of the button form list in the Button form manager window (Fig. 11). Highlight the name of the new button form and click the Change button. Figure: 11. Button Form Manager 29. In the Edit Button Form Page dialog box, click the New button. The Edit Button Form Element dialog box will appear (Fig. 12). Figure: 12. Creating buttons on the form 26

23 30. In the Text box, enter the caption text for the first button on the button form, and then select a command from the drop-down list in the Command box. In the Form field, select the form for which will be executed given command... 31. Add the Customers, Orders, Exit buttons in the same way. 32. In the Button Form Manager dialog box, select the name of your button form and click the Default button. The inscription "(by default)" will appear next to the button form name. 33. To finish creating the button form, click the Close button. 34. The result should be the form shown in fig. 13. Fig. 13. Main Button Form 35. Add a drawing to the form. 36. In order for the main button form to appear on the screen when starting the application, in the main menu, press the Access Options button (Fig. 14). For the current database, set the "button form" view form. 27

Fig. 24 14. Set parameters for Access 37. Show the work to the teacher. Test questions 1. What are the forms for? 2. Why is the form an indispensable tool in the database? 3.What can you use to create forms? 4. On the basis of what can you create forms? 5. How to create a button on a form? 6. How can you place multiple tables and queries on one form? 7. How to create a main button form? 28

25 Lab 5 Creating Reports Reports are designed for printing information. Often the data in them is in tabular form. In contrast to printouts of tables or queries, a report provides more opportunities for sorting and grouping data, it provides the ability to add totals, as well as explanatory labels, headers and footers, page numbers, styles, and various graphic elements. There are several ways to create reports in an Access database: 19) using the Report Wizard; 20) based on tables or queries; 21) in Constructor mode. 1. In the database window, run the command: ribbon tab Create toolbar Reports Report wizard. 2. Select a table (or query) from the list that will be used as a data source (for example, the Client Addresses query). 3. In the Generating Reports dialog box that appears (Fig. 15), move all available fields to the "Selected Fields" area. Figure: 15. Report Wizard 29

26 4. Using the Report Wizard, create the Birthdays report. Use the Employees table as the data source. 5. If you want to print mailing labels, Access provides this option. To do this, select the Clients table and run the command: ribbon tab Create toolbar Reports Labels. 6. In the dialog box that appears (Fig. 16), specify the size of the sticker, system of units, type of sticker and click Next. Figure: 16. Dialog box Create labels 7. At the next step of creating a report, set the font, size, text color and style. Click "Next. 8. Select the margins to be displayed on the label. For example, Company Name, Address, Phone and Fax. If you want to display specific text on each sticker, enter it into the sticker prototype. 9. If necessary, change the name of the label report and click Finish. 10. Sometimes reports need to calculate totals, average, minimum or maximum values, and percentages. To do this, start the Report Wizard and specify the Order Amount request as the data source. thirty

11. In the dialog box of the Wizard, in which the order of sorting records is set, click the Totals button (Fig. 17). Figure: 17. Calculation of totals in reports 12. In the Totals dialog box (Fig. 18) for the Amount and Tax fields, check the boxes in the sum column to calculate the total amount. Figure: 18. Calculation of the total values \u200b\u200bof the sum 13. Then follow all the steps of the Wizard and click Finish. 14. Create a Birthdays report using the Birthdays query as a data source. 31

28 15. Create a report Completed orders, which will include information about the company and the amount of the order. Calculate the grand total, average (Avg) and maximum amount for each firm. 16. Show the work to the teacher. Test questions 1. What are the reports for? 2. What methods of creating reports do you know? 3. How can the totals be calculated in the reports? 4. What totals can be calculated in the reports? 5. How do I print mailing labels in Access? 32

29 Tasks for self-fulfillment Option 1 1. Develop a database "Electronic library", consisting of three tables with the following structure: Books book code (key field), author, title, publication year, number of copies. Readers library card (key field), surname, name, patronymic, address. Issued books book code, library card, date of issue, date of return, date of actual return. 2. Establish relationships between tables. 3. Use a query to select all books from 1990 to 2007. 4. Create a query with a parameter to select books by a specific author. 5. Create data entry forms, reports, and a main button form. Option 2 1. Develop a database "Grocery store", which consists of four tables with the following structure: Products product code (key field), product name, product quantity. Goods receipt goods code, date of receipt, purchase price of goods per unit, supplier code. Sale of goods item code, month of sale, quantity sold per month, selling price of the item. Suppliers supplier code (key field), supplier name, supplier address, supplier phone. 2. Establish relationships between tables. 33

30 3. Using the request, select products with prices ranging from 100 to 450 rubles. 4. Create a query with a parameter to select products sold in a specific month. 5. Create data entry forms, reports, and a main button form. Option 3 1. Develop a database "Session", consisting of four tables with the following structure: Students student code (key field), last name, first name, patronymic, course, group. Exams student code, date, discipline code, grade. Offsets student code, date, discipline code, credit. Disciplines discipline code (key field), discipline name, number of hours. 2. Establish relationships between tables. 3. Use a query to select students who passed exam 4 or Create a query with a parameter to select students who did or did not get credit. 5. Create data entry forms, reports, and a main button form. Option 4 1. Develop a database "Wholesale warehouse", consisting of four tables with the following structure: Warehouse product code, quantity, date of receipt. Products product code (key field), product name, shelf life. Applications application code (key field), organization name, product code, required quantity. 34

31 Goods issue order code (key field), item code, released quantity, date of goods issue. 2. Establish relationships between tables. 3. Using the request, select goods, the quantity of which is from 50 to 200 pieces. 4. Create a request with a parameter to select goods received at the warehouse of a certain number. 5. Create data entry forms, reports, and a main button form. Option 5 1. Develop a database "Applicants", consisting of four tables with the following structure: Application form applicant number (key field), last name, first name, patronymic, date of birth, graduated secondary educational institution (name, number, locality), date graduation from an educational institution, the presence of a red diploma or a gold / silver medal, address, phone number, specialty code. Specialties specialty code (key field), specialty name. Discipline discipline code (key field), discipline name. Entrance exams applicant number, discipline code, examination score. 2. Establish relationships between tables. 3. Make a request to select students who passed exams without triples. 4. Create a query with a parameter to select students applying for a specific specialty. 5. Create data entry forms, reports, and a main button form. 35

Option 6 1. Develop a database "Transportation", consisting of three tables with the following structure: Transport car brand, state number (key field), fuel consumption. Applications application code (key field), date of application, name of the cargo, amount of cargo, point of departure, point of destination. Delivery by order, date and time of departure, date and time of arrival, application code, license plate of the car, distance traveled. 2. Establish relationships between tables. 3. Using a request, select applications with the amount of cargo from 100 to 500 kg. 4. Create a request with a parameter to select vehicles by car make. 5. Create data entry forms, reports, and a main button form. Option 7 1. Develop a database "Sports equipment rental", consisting of three tables with the following structure: Clients client code (key field), last name, first name, patronymic, telephone, address, passport data, deposit. Warehouse equipment code (key field), name, quantity, collateral value, balance. Rental p / p, client, equipment, date of issue, return period, return mark, rental payment. 2. Establish relationships between tables. 3. Create a request to select equipment with a collateral value of up to RUB. 36

33 4. Create a query with a parameter to select customers who returned equipment. 5. Create data entry forms, reports, and a main button form. Option 8 1. Develop a database "Bank", consisting of three tables with the following structure: Clients client code (key field), last name, first name, patronymic, passport, phone, address, salary. Types of loans loan code (key field), loan name, interest rate, terms of provision. Loans provided, p / p, client, loan, date of provision, term, date of repayment, amount, mark of refund. 2. Establish relationships between tables. 3. Create a request for the selection of clients who have taken out a loan from to rubles. 4. Create a query with a parameter for selecting loans at an interest rate. 5. Create data entry forms, reports, and a main button form. Option 9 1. Develop a database "Travel company", consisting of four tables with the following structure: Clients client code (key field), last name, first name, patronymic, telephone, address, passport. Employees employee code (key field), last name, first name, patronymic, position, phone number, address, passport data. 37

34 Tourist routes route code (key field), name, description of the route, country, ticket price, number of days, type of transport. "Orders" order code (key field), client, route, employee (manager who placed the order), date, payment mark. 2. Establish relationships between tables. 3. Create a request to select routes with a cost from to RUB. 4. Create a query with a parameter to select customers who have chosen a certain type of route. 5. Create data entry forms, reports, and a main button form. Option Develop a database "Polyclinic", consisting of four tables with the following structure: Doctors doctor's code (key field), full name, position, specialization, work experience, address, telephone. Diseases p / p (key field), name of the disease, treatment recommendations, preventive measures. Patients patient code (key field), full name, address, phone number, insurance policy, passport. Diagnosis p / p (key field), patient, disease, attending physician, date of treatment, date of recovery. 2. Establish relationships between tables. 3. Select dentists and orthopedists using the request. 4. Create a query with a parameter to select patients with a specific disease type. 5. Create data entry forms, reports, and a main button form. 38

CREATING DATABASES IN MICROSOFT ACCESS 2007 Practical work 1. Introduction to Access. Database creation. Creating tables 1. Start Microsoft Access 2007. 2. Click the button. 3. Give a name to the new

N 1 CREATION OF DATABASES IN MS ACCESS. Tables and Relationships In the modern world, the amount of information that needs to be stored increases every second. Therefore, the use of databases is an integral part of the professional

Laboratory work 1 STUDY OF THE POSSIBILITIES OF MICROSOFT ACCESS 2013 WHEN CREATING TABLES Objectives of the work: 1. To study the procedure for creating tables with specified attributes and data using MICROSOFT ACCESS 2013.

Laboratory work 2 STUDY OF POSSIBILITIES OF MICROSOFT ACCESS 2013 WHEN CREATING RELATIONS BETWEEN TABLES Objectives of the work: 1. To study the procedure for creating relationships between tables using MICROSOFT ACCESS 2013.

Tasks for self-fulfillment Option 1 1. Develop a database "Electronic library", consisting of three tables with the following structure: Books - book code (key field), author, title,

1 Lab 2 Creating relationships between tables 1. Start Microsoft Access 2007. 2. Create the "Company" database. Employees of this organization work with clients and fulfill their orders. Us

UGLTU DEPARTMENT OF INFORMATION TECHNOLOGIES AND MODELING Informatics Methodical instructions on laboratory work for full-time and part-time students in the direction 38.03.05 Business -

Laboratory work 4 "Creating relationships between tables" Purpose of work: studying the technology of creating the structure of a relational database in Microsoft Office Access 2007 1. Theoretical Brief Word

1 Lab 3 Selecting Data Using Queries Queries are the primary means of viewing, filtering, modifying, and analyzing information that is contained in one or more tables in a database.

Specialty 100400.62 "Tourism" Academic discipline "Information networks and databases" Practical lesson 7 Exploring the possibilities and order of using MS Access 2007 when creating relationships between tables

Practical Activity 17 "Selecting Data Using Queries" 1. Open the previously created database "Organization" 2. Run the command: New ribbon tab Query Wizard Simple Query. 3. In the appeared

Laboratory work 3 RESEARCH OF THE POSSIBILITIES OF MICROSOFT ACCESS 2013 WHEN SELECTING DATA USING REQUESTS Objectives of work: 1. To study the procedure for creating requests for selecting data using MICROSOFT ACCESS 2013.

Specialty 100400.62 "Tourism" Academic discipline "Information networks and databases" Practical lesson 8 Studying the possibilities and order of using MS Access 2007 when selecting data using queries

Laboratory work 4 RESEARCH OF THE POSSIBILITIES OF MICROSOFT ACCESS 2013 WHEN CREATING AND USING FORMS IN THE DATABASE Objectives: 1. To study the procedure for creating and using forms in MICROSOFT ACCESS 2013.

DEPARTMENT OF EDUCATION AND SCIENCE OF THE TYUMEN REGION STATE AUTONOMOUS PROFESSIONAL EDUCATIONAL INSTITUTION "TYUMEN FORESTRY TECHNICUM" NV Bortsova, OV Zhuravleva Control system

MICROSOFT ACCESS: LINKS, CALCULATED FIELDS, CREATING A BUTTON FORM OF A DATABASE I. TABLE RELATIONS Modern databases usually consist of many tables linked together. One of the goals of creating a good structure

MINISTRY OF EDUCATION AND SCIENCE OF THE RUSSIAN FEDERATION Federal State Autonomous Educational Institution of Higher Professional Education NATIONAL RESEARCH TECHNOLOGICAL

Laboratory work 5 RESEARCH OF THE POSSIBILITIES OF MICROSOFT ACCESS 2013 WHEN CREATING AND USING REPORTS IN THE DATABASE Objectives of the work: 1. To study the procedure for creating and using reports in MICROSOFT ACCESS

1. Explanatory note 1.1. Requirements for students The student must have the following initial competencies: basic provisions of mathematical and natural sciences; possess the skills of body work;

BASICS OF WORKING IN MICROSOFT ACCESS. DATABASE DESIGN. DATABASE CREATION. CREATION OF FORMS, REPORTS, INQUIRIES 1. Designing databases 2. Creating a database 3. Creating forms, reports, queries

1 Practical work 18 "Using forms in the database" Initial database "ORGANIZATION" Task-1: 1. Using the Wizard, create the forms Employees, Clients, Orders by including all the available fields.

Specialty 100400.62 "Tourism" Academic discipline "Information networks and databases" Practical lesson 6 Studying the possibilities and order of using MS Access 2007 when creating tables Study questions

Ministry of Education of the Tver Region GBOU SPO Tver College named after A. N. Konyaeva "Tasks for performing independent work in MS Access 2007 in the discipline" Information Technology»For specialty

MINISTRY OF EDUCATION OF THE RUSSIAN FEDERATION FEDERAL STATE BUDGETARY EDUCATIONAL INSTITUTION OF HIGHER EDUCATION "IVANOVSK STATE POLYTECHNICAL UNIVERSITY" DEPARTMENT OF INFORMATION

Lecture plan: Working with database objects in MS ACCESS 1. Working with tables. 2. General information requests. 3. Working with forms 4. Working with reports. 2 Question 1 Ways to create tables: 1. in table mode; 2.in

2 DBMS ACCESS The database management database Access is one of the programs - applications of the integrated Microsoft Office package. The boot file for a program named msaccess.exe is usually located

LABORATORY WORK 2 CREATING FORMS IN MS ACCESS Objectives of the work: studying the main ways of creating forms using MS Access; creating calculated fields in MS Access forms. The content of the theoretical minimum.

TEST: "DBMS ACCESS 2007 - CONTROL TEST". Task # 1 How many fields are selected to be included in the generated REPORT Choose one of 5 answer options: 1) 1 2) 2 3) 3 4) 4 5) 5 Task # 2 Will the specified

Laboratory work 4 Fundamentals of working with data arrays in the Access DBMS Purpose of work: 1) acquiring skills in creating a database and its main objects; 2) sorting data in tables and others

MINISTRY OF AGRICULTURE OF THE RUSSIAN FEDERATION FEDERAL STATE BUDGETARY EDUCATIONAL INSTITUTION OF HIGHER EDUCATION "OMSK STATE AGRARIAN UNIVERSITY NAMED AFTER PA STOLYPI"

1.1. Basics of working with databases 1.1.1. Databases and database management systems. Data models Database (DB) is a named collection of structured data related to a certain

Practice 3 Create a Form A form is a database object that you can use to enter, modify, or display data from a table or query. Forms can be used to manage

Work 2. Creation of the "Students" database Task 1. Creation of the structure of a new table. Use the table designer to create the Students table. 1. Start the MS Access DBMS program and create a new DB

Workshop on working with the database. Task 1. Create a new database and create a table structure with information about the students of the "Computer School". Operating procedure. Start Microsoft Access. In the Microsoft dialog box

Practical work on Access 2007 Grade 10. TASK 1. CREATION OF A MULTI-TABLE DATABASE. Purpose of work: To acquire skills and abilities when working with tables: create tables using a constructor, set

LABORATORY WORK 1 Creation of a new database. Creation of a database using templates Purpose: to develop skills and abilities to create a new database and database based on templates. Lead time:

Database management system OpenOffice.org Base Note: Databases (DB) are software, designed for storing and processing information. For example, the "library" database can store

MOU "Lyceum 43" Saransk Methodical development "RESEARCH DBMS ACCESS WHEN CREATING AND EDITING A DATABASE" Author, teacher of computer science Zhebanov A. A. Saransk 2014 RESEARCHING DBMS ACCESS WHEN

"Informatics and information and communication technologies in professional activity" specialty 44.02.01 "Preschool education" 2.6 course, 24.24 (9), 64 (1.2, 3) groups teacher Gerasimova

NATIONAL AVIATION UNIVERSITY Department of Applied Informatics Coursework Topic: Microsoft DBMS Access Creation and database processing Purpose of work: theoretically and practically master the basic techniques of work

Laboratory work 11. Formation of queries to the database in the MS Access DBMS The purpose of the work is to gain skills in creating various types of queries to the database. To defend the laboratory work, the student must submit

➀ Information systems and data banks. The most important condition for ensuring the effective functioning of any organization is the presence of a developed information system. An information system is a system

Designing a database of Internet purchases Let the Internet shop carry out bulk purchases of goods from trading bases in Moscow and nearby regions to satisfy requests from customers. When purchasing large

Getting Started with Access Access is a database application or database management system (DBMS). Computer databases are used in almost all areas of activity. Skill

Federal Agency for Fisheries Federal State Budgetary Educational Institution of Higher Professional Education "Astrakhan State Technical University" Development

Introduction to ACCESS First of all, Access is a database management system (DBMS). Like other products in this category, Access is designed to store and retrieve presentation data in a convenient form.

WORKING WITH THE MICROSOFT ACCESS DATABASE MANAGEMENT SYSTEM 1 KEY TERMS Query - Queries are a powerful tool for manipulating the data stored in Access tables. Using queries, you can view,

1 Practical work 16 "Using relationships between tables" 1. Start Microsoft Access 2007. 2. Click on the button: 3. Create a database "Organization", whose employees work with clients

Chapter 8 Creating and Using Forms As noted in Chapters 1 and 2 of this book, database objects such as forms are primarily designed to work on only one record at a time.

4. Databases 4.1. Databases and data models. Basics of design of relational databases Database (DB) is a named collection of structured data related to a specific subject

Topic: Practical work 14-2 Creating a database, consisting of one table. Entering data into the table. Using forms. Objective: Learn to create databases in MS Acces (create tables and forms,

Task 4. Creating and editing forms and reports The purpose of the task: To learn how to create and edit forms and reports. The previously developed database "Journals" is used. Sequencing:

Laboratory work 2 "Creating a relational database" Purpose of work: studying tools for database management in Microsoft Office Access 2007 1. Brief theoretical information In the modern world, a person

LABORATORY WORK N 1 BASIC PRINCIPLES OF WORK IN Microsoft Access 97 PURPOSE OF THE WORK The purpose of the work is a general acquaintance with the Microsoft Access 97 database and the acquisition of skills in using the reference

LECTURE 7 Microsoft Access program. Lesson plan: Introduction Database. Database structure. Creating a database using Microsoft Access. Working interface and main menu of Microsoft Access. Filling the first

Work 6 Creation of complex forms and reports Purpose of work: to learn how to create complex forms and reports. Content of work: 1 Development of a complex form 2 Development of a complex report 3 Development of a button form-menu

Task 3. Creating and editing queries Purpose of the task: To learn how to create and edit queries to the database. The previously developed database "Journals" is used. Sequencing:

Practical work 18 Topic: CREATION OF TABLES IN MICROSOFT ACCESS DBMS Purpose of the lesson. Study of information technology for creating a database in a database management system (DBMS) Microsoft Access. The task

9. LABORATORY WORK 9 9.1 Creation of a database of a commercial company MS Access Purpose of work: to study the technology of creating databases in MS Access. Learn to create tables, link them between

@ DB, DBMS 1. A data structure characterized by subordination of lower-level objects to upper-level objects is called A. tabular B. relational * C. hierarchical D. network 2. Distinctive

P. 1 of 109 BAXIŞ Testlər / 1625 # 01 # Y15 # 01 qiyabi / 1625 # 02 # y15 # 01qiyabi / baxış TEST: 1625 # 02 # Y15 # 01QIYABI Test Fənn Təsviri Müəllif Testlərin # vaxtı Suala vaxt Növ 1625 # 02 # 02 # Y15 - Verilənlərin

Access 2007 Lab # 4

Parameter name	Value
Topic of the article:	Access 2007 Lab # 4
Category (thematic category)	Electronics

Topic Creation and application of forms.

Purpose of work˸

ü Learn to create input-output forms;

ü Learn to create button forms.

Theoretical part.

The form Is a tool that simplifies the entry, editing and display of information stored in database tables. It is a window with a set of controls.

The form itself does not store information, it just provides convenient way access to information stored in one or more tables. Forms have the following advantages over data processing in table mode˸

¨ Form allows you to focus attention on a separate record at any moment;

¨ Controls on the form can be arranged in a logical way, making it easier to read and work with data;

¨ Individual controls have the ability to facilitate the entry and modification of individual data;

¨ Some database objects such as pictures, animations, sounds, and video clips can only be displayed in Shape mode, not in Grid mode.

Creating a button form.

Button menu is a form on which controls are located - buttons with explanatory labels. Clicking a button opens the corresponding table, query, form, or report. Menu is a convenient tool for working with databases, and it is almost always present in databases created for enterprises or firms.

The button menu is created using Manager of button forms.

The practical part.

Progress.

1) Open your database.

2) Create a form with<Мастера форм> table-based<Ведомость успеваемости>.

Open the table<Ведомость успеваемости>.

Select a bookmark<Формы >, click on the button<Другие формы>.

<Мастер форм>.

· In field<Таблицы/Запросы> select table<Ведомость успеваемости>, in field<Доступные поля> select fields<Фамилия>, <Имя> and move them with the arrow to the field<Выбранные поля>... Also transfer the fields with the name of the items, click on the button<Далее>.

Select the appearance of the form - Tabular , click on the button<Далее>.

Select the style you want (e.g. Regular ), click on the button<Далее>.

Set the name of the form <Успеваемость> and click on the button<Готово>... As a result, you will get a form in which you can change data and enter new values.

· Close the form.

3) Create a form based on the table <Преподаватели>.

Open the table<Преподаватели>.

Select a bookmark<Формы >, click on the button<Другие формы>.

In the dialog box that appears, select<Мастер форм> .

Select the appearance of the form -< tape >.

· Choose any style.

· Get \u200b\u200bthe finished shape. Save it as <Преподаватели>.

· Close the form.

4) Create a shape <Личные данные> with a tool < Empty form >

Laboratory work on Access 2007 №4 - concept and types. Classification and features of the category "Laboratory work on Access 2007 No. 4" 2015, 2017-2018.

- Topic 5. Working in the Access DBMS

Currently existing relational DBMSs may differ from each other in technical capabilities (for example, security tools), which, in particular, depends on which user the DBMS is focused on. Therefore, the popularity of one or another DBMS in various ...

-

Purpose of work: Acquaintance with the basic concepts of databases. Acquisition of skills in working with an existing database in Microsoft Access. Theoretical information: 1. Database (DB) is a named collection of data organized according to certain rules, ....

- Practical work No. 1. Acquaintance with the Microsoft Access DBMS

Topic no. Topic no. work Date of verification Grade Signature of the teacher Subject, system and tasks of the science of forensic science Criminalistic identification and ....

- Access Wizards

Access allows even an inexperienced user to create his own database, process data using forms, queries and reports, analyze database tables and perform a number of other works. For almost any work with a database in Access, there is a wizard that helps them ....

- Start the Microsoft Access program.

Methodology recommendations for completing the task 1 Try the following method of starting the program: Start button Þ Programs section Þ Microsoft Office folder microsoft program Access 2. Create a database and save it in a folder with the name of your group under the name –...

Introduction

In the modern world, the amount of information requiring storage is increasing every second. Therefore, the use of databases is an integral part of a person's professional activity.

The simplest databases can be created without resorting to special software tools: for example, in Excel spreadsheets. But there are several differences between them. First, database management systems (DBMSs) are designed to efficiently process large amounts of information, much larger than those that spreadsheets can handle. Secondly, the DBMS can easily link two tables so that for the user they will be represented as one table. It is almost impossible to implement this feature in spreadsheets. And third, the DBMS minimizes the overall size of the database. For the same purpose, tables containing duplicate data are split into multiple related tables.

Despite the fact that there are many database management systems in the world, they all have some similarities. Therefore, for our study, we chose the Microsoft Access 2007 DBMS included in the Microsoft Office package. Os-

access's new purpose is to provide the user with an easy and affordable way to create the kind of databases they want. Despite the simplicity of this DBMS, it allows you to create applications with a rather complex structure. If desired, the system can be developed and configured on its own using the Visual Basic programming language.

Another advantage of Access is its integration with Word, Excel and other Microsoft Office programs. Data is easily imported and exported from one application to another.

The methodological instructions include five laboratory works.

Laboratory work No. 1 Acquaintance with Access. Creating tables

Database (DB) is an ordered collection of data intended for storage, accumulation and processing by means of a computer. To create and maintain databases (update them, provide access on request and issue data on them to the user), a set of language and software tools is used, called database management system (DBMS)1 .

Access Database Objects

Access database objects include:

1. Tables - designed for orderly storage of data.

3. Forms - designed for easy viewing, changing and adding data in tables.

4. Reports - used to analyze and print data.

5. Data access pages- designed to view, enter, update and analyze data via the network or from anywhere on the computer.

6. Macros are used to execute a frequently encountered set of macros that process data.

7. Modules - designed to describe instructions and procedures in the VBA language.

The main object of the database is a table, which consists of records (rows) and fields (columns). At the intersection of a record and a field, a cell is formed that contains data.

Each field in the table is assigned a unique name that cannot exceed 64 characters. Each field contains data of the same type.

Text

Memo field

Numerical

Date Time

Monetary

Logical

Substitution wizard

Data types

Description

Used to store character or numeric data that does not require computation. The field Size property sets the maximum number of characters that can be entered in

this field. By default, the size is set to 50 characters. The maximum number of characters that can be contained in a text field is 255.It is intended for entering text information, by the amount of

embroidering 255 characters; can contain up to 65,536 characters Designed to store numerical data used in mathematical calculations. On the General and Substitution tabs, you can set the properties of a numeric field, including

field measures, Field format, Number of decimal places

Used to represent date and time. The choice of a specific date or time format is set in the property

Date format

Designed for storing data, the accuracy of which ranges from 1 to 4 decimal places. Integer part can contain up to 15 decimal places Designed for automatic insertion of unique sequences

numbered (increasing by 1) or random numbers as the new record number. The number assigned to the entry cannot be deleted or changed. Fields with this data type are used as key fields of the table.It is intended to store one of two values, interpreted

defined as "Yes / No", "True / False", "On. / Off " Contains data created by other programs that use the OLE protocol. This can be, for example, Word documents, Excel spreadsheets, pictures, sound and video recordings, and others. OLE objects are linked to or embedded in an Access database. You cannot sort, group, and index fields of OLE objects

Special type for storing hyperlinks. Designed for automatic field detection. It will create a combo box from which you can select data contained in another table or in a set of constant values

Database creation

2. Click the button.

3. Set the name of the new database to "Notebook.accdb".

4. On the ribbon tabCreate in the Tables toolbar, click the K button constructor of tables.

5. Enter the field names and specify the data types to which they relate.

6. Exit Design mode after saving the table under the name"Friends" ; do not specify key fields.

7. Open the table"Friends" double click and fill in 10 lines.

8. Add the fields "Patronymic" and "Date of birth", for this:

1) position the cursor on the field before which you want to insert a new one

2) run command: ribbon tabTable mode → toolbar

ruments Fields and Columns → Insert;

3) by double-clicking onField1, rename it to "Patronymic", and Po-

le2 - "Date of birth".

9. Switch to Design mode with the command: ribbon tabHead-

naya → Modes → Constructor.

10. For the field "Date of birth" set the data type Date / Time; at

field properties select Short date format.

11. Format the table like this: 1) grid color -dark red; 2) background color - blue;

3) text color - dark red, size - 12 pt, style - italic.

12. Rename the field"Hobbies" in "Hobbies".

13. Delete entry number 8.

14. Resize the cells so that all data is visible. To do this, just double-click the left mouse button on the border of the fields.

15. Arrange the fields in the following order:"No.", "Surname", "First name", "Patronymic", "Phone", "Date of birth", "Hobbies", "Address", "Index", "Photo", "E-mail".

16. Fill in empty table cells.

17. In Design view, add a field"Family status", which will contain a fixed set of values \u200b\u200b- married, unmarried, married, unmarried. To create a drop-down list, we will use

substitution Wizard:

1) set data type Substitution wizard; 2) in the dialog that appears, select the line “Fictitious

coherent set of values \u200b\u200b" and press the buttonFurther ; 9) the number of columns - 1;

10) enter the details of the list - married, not married, married, not married; 11) click Finish.

18. Use the drop-down list to fill in the new column. Since the table turned out to be wide, when filling out this column, some inconveniences arise: the surname of the person for whom the field is being filled is not visible"Family status"... In order for the last name to be constantly visible when filling out the table, you must use the command Freeze columnsfrom the context menu of the Last Name field.

19. Show the work to the teacher.

Laboratory work No. 2 Creating relationships between tables

1. Start Microsoft Access 2007.

2. Let's create a database "Firm". Employees of this organization work with clients and fulfill their orders.

If you put all the information in one table, then it will become very inconvenient to work with. It will start repeating data. Every time an employee Ivanov works with a company, you will have to re-register the data about the employee and the client, as a result of which many mistakes can be made. To reduce errors, you can split the original table into multiple tables and establish relationships between them. It will be more rational than before.

Thus, you need to create 3 tables: Employees, Customers and Orders.

	Staff
	Data type
Employee code
	Text
	Text
middle name	Text
Position	Text
	Text
	Text
Date of Birth	Date Time
Wage	Monetary
	OLE object

	Data type
Client code
Company name	Text
	Text
Phone number	Text
	Numerical
E-mail address
	Memo field
	Data type
Order code
Client code	Numerical
Employee code	Numerical
Date posted	Date Time
Execution date	Date Time
	Monetary
Completion mark	Logical

3. Individual tables containing information on a specific topic need to be linked into a single database structure. To link tables, setkey fields. A key consists of one or more fields whose values \u200b\u200buniquely identify each record in the table. The most suitable as a key field is "Counter", since the values

at the given field are unique (i.e., exclude duplicates).

4. Open the tableEmployees in Design mode.

5. Right click on the fieldEmployee ID and in the appeared context menu select the Key field command. If you need to set several key fields in the table, you can select them by holding down the Ctrl key.

6. For the Customers table, set the key field Customer Code, and for the table Orders - Order Code.

7. The Orders table contains the fields Employee Code and Customer Code. When filling them out, some difficulties may arise, since it is not always possible to remember all the enterprises with which the company works, and all employees

from code number. For convenience, you can create drop-down lists using

wizard of substitutions.

8. Open the tableOrders in Design view.

9. For the field Employee code select data typeSubstitution wizard.

10. In the window that appears, select the command "Object"column subst-

new "will use values \u200b\u200bfrom a table or query »And click on the buttonFurther.

11. In the list of tables, select a tableEmployees and click the button

12. From the Available Fields list, select the Employee ID field and click the arrow button to enter the field in the Selected Fields list. Add the Last Name and First Name fields in the same way and click the Next button.

13. Select the sort order of the list by fieldSurname.

14. In the next dialog, set the desired width of the drop-down list columns.

15. Check the boxHide key pillar "and press the button

16. At the last stepSubstitution Wizardsreplace the caption for the lookup field if necessary and click Finish.

17. Similarly create a dropdown for the field

Client code.

18. After creating the key fields, you can start creating links. There are several types of relationships between tables:

1) in a one-to-one relationship, each record of a key field in the first table corresponds to only one record in a related field of another table, and vice versa. This type of relationship is not used very often. In-

where they can be used to split tables containing many fields to separate parts of the table for security reasons;

2) with the ratio One-to-manyeach record in the first table corresponds to several records in the second, but the record in the second table cannot have more than one related record in the first table;

3) with the relation Many-to-manyseveral records in the second table can correspond to one record in the first table, and several records in the first one can correspond to one record in the second table.

19. Close all open tables as you cannot create or modify links between open tables.

20. Run the command: ribbon tabWorking with databases →

21. If previously there were no links between the database tables, then when you open the windowData schema simultaneously opens a window Adding a table

in which select tables Employees, Clientsand Orders.

22. If the links between the tables have already been defined, then to add a new table to the data schema, right-click on the data schema

and in the context menu, selectAdd table.

23. Establish relationships between tablesEmployees and Orders, to do this, select the field Employee Code in the Employees table and drag it to the corresponding field in the Orders table.

24. After dragging, a dialog box will openChanging links

(fig. 1), in which enable the checkbox Ensuring the integrity condition... This will prevent you from deleting records from one table and leaving the associated data from other tables unrelated.

Figure: 1. Creating a relationship between tables

25. Check boxes Cascade update related fields and Cascade delete related records provide simultaneous update or deletion of data in all subordinate tables when they change in the main table.

26. Communication parameters can be changed by clicking the buttonUnion .

27. After setting all the necessary parameters, press the button

OK.

28. Relationship between tablesInstall Clients and Orders by yourself.

29. The result should be the data schema shown on

Figure: 2. Data schema

Using the example of developing a Warehouse database, the program for creating and managing databases Access 2003 is considered. The main attention is paid to such issues as database planning, creating tables, queries, forms, reports. The work is intended for users with a certain skill of working with applications in the Windows environment, who wish to master the Access program.

Download:

Preview:

Ministry of Education of the Tver Region

GBOU SPO "Ostashkovsky Electromechanical College"

Information technology in the industry

Methodical instructions

to perform laboratory work

on this topic " WORK IN MICROSOFT ACCESS "

for students of the specialty№ 140613 "Technical operation and maintenance of electrical and electromechanical equipment"

Ostashkov

2012

Explanatory note
Laboratory work. Formation of the structure of a multi-table database
Laboratory work. Data ordering, filtering
Laboratory work. Designing a multi-table custom data entry form
Laboratory work. Formation of queries for a multi-table database
Laboratory work. Report generation
Bibliography

EXPLANATORY NOTE

The development of modern society is characterized by continuously increasing flows of information, which makes it possible to define it as an “information technology society”. The level of qualifications of specialists cannot be determined only by professional training. A specialist must be able to receive information, determine its importance and track its changes in a timely manner. The tool that allows you to manage information processes is a computer. The basic skills for its use in professional activities are laid down during the study of the discipline "Information technology industry".

The main task of secondary vocational education is to study the general patterns of functioning, creation and use of information systems, mainly automated.

From the point of view of content, this allows us to develop the foundations of a systemic vision of the world, expand the capabilities of information modeling, thereby ensuring a significant expansion and deepening of interdisciplinary connections of the subject "Information technology" with other disciplines.

From the point of view of activity, this makes it possible to form a methodology for using the main automated systems in solving specific problems associated with the analysis and presentation of information processes: automated information Systems storage of information arrays (database management systems, information retrieval systems, geographic information systems).

Databases play a special role in the modern world. Everything that we encounter on a daily basis in life is most likely registered in one database or another. The ability to work with databases is one of the most important skills in working with a computer today.

The program provides laboratory work that must be carried out to master and acquire skills in working with databases using the example of MS Access. Methodological instructions for conducting laboratory work contain the title of the work, the purpose of the work, the procedure for carrying out the work, the content of the report and control questions to test knowledge.

Laboratory work

Formation of the structure of a multi-table database

Objective: To master the creation of databases and tables in Access. Master the creation of tables using the Wizard and in Design mode. Master data entry into a table.

Work order:

1. Create a database "Warehouse" containing three tables: "Products", "Customers", "Orders".

This database contains three tables: "Products", "Customers", "Orders".

		Products		Clients
		Item Number		Client's number
		Product		Surname
Orders		number		The address
Order number		Price		Phone
Item Number		Shelf life
Client's number
number
order date

There are relationships between the tables: "Products" and "Orders" - a relationship "one-to-many", "Orders" and "Customers" - a relationship "many-to-one".

Enter data into tables. Execution order:

1. Download Access. Take advantage of system menu START \\ PROGRAMS \\ MICROSOFT ACCESS or the boot file ACCESS.EXE.
2. In the Microsoft Access window, select New Database and click OK.

The New Database File dialog box appears, and Access prompts you for the database name db1, db2, etc. Name the new Warehouse database. Click Create. The database window will appear.

1. Let's start creating the "Products" table. Go to the Tables tab and click the Create button.

In the New Table window, select the Design table creation mode, click OK.

Specify field names and types. Field names are entered from the keyboard, field types are selected from the drop-down list.

Make the Item No. field a key: right-click on the Item No. field and select Key Field from the context menu.

1. Save the table:

1. select from the menu File / Save;
2. in the window that appears, write the name of the table "Products";
3. click OK.

A new table "Products" has appeared in the database window.

1. Similarly, create a table "Orders", use the Design mode.

Assign the Order number field as a key field. Save the table.

1. Create the Customers table using table mode. On the Tables tab, click the Create button. In the New Table window, select the Tables mode. You will see an empty table consisting of twenty columns named Field1, Field2, etc. Double click on Field1, the text in the field will be highlighted, enter the word Last Name and press Enter. In Field2 enter the Address, in Field3 - Phone.
   

The Key field Customer number will be added later. Adjust the width of the columns: move the mouse to the vertical line between the field names, hold down the left mouse button and, without releasing the button, move the mouse. Enter the first entry.

1. Select File / Save from the menu. Specify the name of the table "Clients", click on OK.
2. A dialog box will appear warning that the key fields are not defined, answer "Yes".

The "Clients" table appeared in the database window. Look at this table in Design view.

To do this, select the "Clients" table and click the Constructor button.

Change the Field Name Code to Customer Number.

Access automatically assigns a type to the generated fields. Make sure the field types are the same. If not, change them. To do this, click in the Data type column on the field whose type does not match and select the required type. Save the table.

1. Enter the data into tables.

Let's start with the Customers table.

Click first on the word Clients, and then on the Open button. Enter data about 4 more clients, for example:

Close the Customers table and save your changes.

Likewise, add 8 different products to the Products table.

Enter the data in the "Orders" table. Please note: the item number must match the item numbers from the "Products" table (possible values \u200b\u200bfrom 1 to 8), and the customer numbers must match the customer numbers from the "Customers" table (1, 2, 3, 4, 5).

Let's establish relationships between tables. Click the Data Schema tool. The Data Schema and Add Table windows appeared on the screen. Select the "Products" table and click on the Add button. Add the Customers and Orders tables in the same way. Close the empty Add Table window.

Let's establish the relationship between the tables "Products" and "Orders", the link field Item number.

To do this, place the pointer in the Item number field (the "Products" table) and, while holding the left button, drag the pointer into the Item number field (the "Orders" table). Release the mouse button.

The Edit Links dialog box appears. Select the check box next to Ensure data integrity, Cascade update of linked fields, Cascade deletion of linked records, and then click New.

A one-to-many relationship appears in the data schema. Cascading deletion and updating of records means that if any product is removed from the Products table, records will be automatically deleted from the Orders table.

Similarly, let's create a relationship between the tables "Customers" and "Orders".

As a result, the data schema became:

Comment. If you were unable to establish relationships between tables, check the key fields of the tables and the entered data.

1. Close the Access data schema.

1. Formulation of the problem.
2. Progress.
3. Conclusions.

Control questions:

1. List the types of fields in Access. Explain each one.
2. What types of data do you think should have the fields Position, Zip, Advance, Tax, Surname, Date of birth, Time of arrival, Train number, Number of years? Explain why.
3. What types of relationships between tables can Access support?
4. How should the link fields be indexed in a one-to-one relationship?
5. How should link fields be indexed in a one-to-many relationship?

Laboratory work

Data ordering, filtering

Objective: to master sorting and filtering data; changing the structure and appearance of the table.

Work order:

1. Sort the data in the "Products" table in ascending order of Prices.
2. Sort the data in the Orders table in ascending order of the Order Date.
3. Sort data in the Customers table in descending order Customer numbers.
4. Select records from the "Products" table with a Price greater than 25 rubles.
5. Select records from the "Products" table with an expiration date of 12/15/03.
6. To make a report.
7. Answer security questions.

To solve these problems, follow these steps:

1. Download Access.
2. In the Microsoft Access window, select Open Database, select Warehouse and click OK.
3. Open the "Products" table, to do this, go to the Tables tab, click on the "Products" table and click the Open button. A window-table "Products" appeared in front of you.
4. Sort the data in ascending order of price by clicking the Price field, and then clicking the Sort Ascending tool.
5. Similarly, arrange the data in the "orders" tables in ascending order of the Order Date.
6. Sort the data in the Customers table in descending order Customer numbers. Use the Descending Sort tool.
7. Let's select records from the table "Products" with a Price of more than 25 rubles.

Open the Products table. Click on the Price field and execute the command Records / Filter / Advanced Filter.

A dialog box will appear on the screen, at the top of which there is a rectangle with a list of fields contained in the "Products" table. Below is the request form. In the Field row, click the drop-down list and select the Price field. In the Criteria line, type\u003e 25. Click the Apply Filter button on the toolbar.

1. Remove filtering by clicking on the Remove Filter tool.
2. Similarly, select all records with an expiration date of 12/15/03.
3. Close Access.

1. Formulation of the problem.
2. Progress.
3. Conclusions.

Control questions:

1. Purpose of DBMS Access.
2. Calling up the Access system and shutting down.
3. Database design stages.
4. Entering, editing, viewing, adding and deleting tabular data.
5. Filtering, sorting, adding and removing columns.

Laboratory work

Developing a multi-table custom data entry form

Objective: To learn how to create forms in Access.

Work order:

1. Create a form for all fields of the "Products" table.
2. Create a form for all fields of the "Customers" table.
3. Create a form giving information about which customer made which orders.
4. Create a form giving information about what orders are available for each product.
5. Write a report.
6. Answer security questions.

1. Download Access.
2. Open your database.
3. Let's create a form for all fields of the "Products" table. Go to the Forms tab. Click the Create button. The New Form window will appear on the screen. Select AutoForm: in column, table "Products" as data source and click OK.
   

A form for the "Products" table will be created. Work with this form: change the data, leaf through the records. Close the form and save your changes. Specify a name for the Products form and click OK.

4. Similarly, create a form for all fields of the "Clients" table, use the tape auto-form.

1. Let's create a form to view orders for each customer. To do this, click on the Create button on the Forms tab. In the dialog box, select Form Wizard from the list. Select the Customers table as the data source. Click the OK button.

The form wizard creates a form in a few steps.

1. In the first step, the form wizard prompts you to select fields for the form. Fields can be selected from several tables, first we select all fields from the "Customers" table. (If the “Customers” table does not appear in the Requests Table field, open the list and select the required table.) Click on the Customer number field, and then click on the button. Add the Surname, Address, Phone fields in the same way.
2. Select all fields in the Orders table. To do this, select the Orders table from the Tables and Queries drop-down list and select all the table fields one by one. Click the Next button.
3. In the second step, select the type of data presentation. The table "Clients" and the line Subordinate forms must be selected. Click the Next button.
4. In the next window, select the appearance of the subform, for example, tabular. Click the Next button.
5. In the next step, select a form style, for example international, and click the Next button.
6. In the final step, specify the names of the Customers1 form and the Sales orders subform. Click the Finish button.

You will see the finished form.

1. You can view the orders of each client using the paging buttons located at the very bottom of the form. Enter at least two orders for each customer. Close the form.
2. Create a form to view orders for each item yourself.

1. Formulation of the problem.
2. Progress.
3. Conclusions.

Control questions:

1. Give a definition of the shape.
2. Why is it convenient to use forms?
3. Name the modes for creating forms in Access.
4. What steps do you need to take to create an AutoForm?
5. Describe the form creation mode Form Wizard.

Laboratory work

Formation of queries for a multi-table database

Objective: To learn how to create forms in Access. Create a simple form, a form using the Constructor.

Work order:

1. Examine the job description.
2. Consider all examples of work.
3. Create a query that displays information about how much a given product has been ordered and by which customers.
4. Create a request to select products with a value over 30 or over 50.
5. Create a request to select data about customers who have ordered more than 10 units of any product.
6. Write a work report.
7. Answer security questions.

Let's consider creating a query in the Designer.

The Constructor window consists of two parts. The upper part contains tables from which data and relationships between tables are selected. The lower part lists the fields of tables, selection conditions and formulas for calculations.

You can add a table to the list by right-clicking the top of the window and choosing Add Table. Use the Delete button to delete a table.

In the Table name row, you can select the required table from the drop-down list.

The Field line contains the name of a field, * or a formula. * allows you to select all fields of the table.

Let's analyze the creation of a formula using an example:

Total: [Products]! [Price] * [Products]! [Quantity]

Total is the name of the result field, followed by a colon, the names of tables and fields are written in square brackets. The inscription [Products]! [Price] means that the Price field is selected from the “Products” table. The table name can be omitted if the field does not appear in other tables.

Those. if our request is formed on one table "Products", then the formula can be written in an abbreviated form:

Total: [Price] * [Quantity]

The formula can include numbers, for example:

Tax: [Price] * 0.05

And standard functions, for example, sin (x) - sine of x, MIN (x1, x2, x3, ...) - search for the minimum element from the list, etc.

The fields Total and Tax are not physically in the table, they are calculated and are calculated every time the query is executed.

Sorting can be ascending, descending, or absent.

The display looks like a checkbox. If checked, the field will be displayed on the screen.

The selection condition sets the selection condition for a specific field.

For example, let's select records with a Price greater than 30.

In the Criteria line, you can prompt the user to enter the desired value. The invitation text is enclosed in square brackets.

The Or string is used if the selection condition is formed from two subconditions that are connected by the logical OR operator. For example, select all orders for the 2nd or 4th Goods.

If you order several conditions for the collection, then it will be connected by the logical AND operator.

Let's consider an example of creating a query using the example of tables you created. To do this, follow these steps:

1. log into Access;
2. open your database;
3. create a request that displays information about how much this product has been ordered and which customers;
4. create a request to select products with a cost of more than 30 or a quantity of more than 50;
5. create a request to select data about customers who have ordered more than 10 units of any product.

Execution order:

1. Download Access.
2. In the MS Access window, select Open Database, select Warehouse and click OK.
3. In the database window, select the Queries tab and click the Create button. Select Design and click OK. Access displays the Query Builder window with the Add Tables dialog box open. Select one by one all tables from the list and click the Add button. Then close the dialog box.

We select the fields for the request. From the table "Products" select the field "Product", from the table "Orders" - the fields Order number, Customer number, Quantity. From the table "Clients" - Surname.

In the Design window, in the Criteria line of the Product field, enter a hint in the form [Enter the name of the product]. Uncheck the Display line for the Item field.

A window will appear with your prompt. Enter the name of a product, for example Tea.

The selected data appears on the screen. Close the request window and save it under the name Products - Orders.

1. Add the calculated field Amount to the Goods - Orders query. To do this, on the Requests tab, click on the Products - Orders request, and then on the Constructor button.

Find a free column (after the Quantity field) and enter the formula in the Field line.

Order cost:

[Products]! [Price] * [Orders]! [Quantity]

Complete your request. Save your query changes.

1. Create a query to select products with a value of more than 30 or a quantity of more than 50. Select all the fields of the "Products" table, for the Price field in the Criteria line specify\u003e 30, for the quantity in the line Or specify\u003e 50. Run the query. Save under the name Products2.
2. Create a request to select customers who have ordered\u003e 10 units of an item. Add all the Customer fields and the Quantity field from the Orders table to the query. Specify a selection condition\u003e 10. Sort the results in descending order of the Quantity field.

1. Formulation of the problem.
2. Progress.
3. Conclusions.

Control questions

1. Give the definition of the request.
2. What data is displayed in the Field line of the Query Builder? How can you define formulas to calculate a calculated field?
3. What does sorting mean?
4. What is the selection condition used for? How can you query the value of a field from a user?
5. What is the Or string used for?

Laboratory work

Report generation

Goal: Get the skills to create reports in the simplest way; with the help of the Wizard; using the Constructor.

Execution order:

1. Select in the dialogDatabases Reports tab and click on the buttonCreate. New report dialog box allows you to create a report automatically (auto report), usingWizards or manually ... Create an auto report based on any table incolumn or ribbon ... The operation is reduced to one click of the left mouse button.

1. Open the database, select the modereport , click onCreate.
2. The New Report menu appears : select the mode in the menuReport wizard and click OK.

A menu appearsselect the layout view for the report: can be clickedFurther .

1. The structure of the report is divided into five sections: the report title, header, data area, footer, and report notes.
2. Title section serves to print the general title of the report.
3. Section headercan be used to print subheadings if the report has a complex structure and spans many pages. You can also place here andcolonnumbers (page numbers) if not done in the footer.
4. In the field of data place controls associated with the content of the fields of the base tables. Data from tables is output to these controls for printing on a printer. The order of placement and alignment of controls is the same as when creating a form structure.
5. Section footer is used for the same purposes as the header section.
6. The first control enters the current date. To do this, use the built-in Access function Now () function. It returns the current date and places it in the field, and the report reproduces it when printed.
7. The second control displays the page number and the total number of pages. The built-in functions Page () and Pages () are used to define them. The text that is written in quotes is reproduced "literally", and the & operator is used to "glue" the text enclosed in quotes with the values \u200b\u200breturned by functions. The & operator is called the concatenation operator.
8. Notes section are used to post additional information.

1. Formulation of the problem.
2. Progress.
3. Conclusions.

Control questions

1. Modes of creating a report from a table (and / or query).
2. Window assignment Available fields, Selected fields.
3. Grouping levels concept.
4. Sorting method selection order.
5. What Layout for the report?
6. Report style concept.

HANDOUT

The main functions of the DBMS: Creating and editing the database structure Create (add) a new record Search for a record (field or other database items) Editing a record Deleting unnecessary recording Sorting records by key fields Information display control: filtering, hiding unnecessary information, split window, etc. Information protection: limitation of unauthorized access, protection against accidental changes, etc. Automatic maintenance of links between structural elements of the database: files, records, etc. The presence of advanced language tools that allow you to perform complex queries to the database Data storage Ensuring communication with other databases and other objects of the global information space

Figure: 8. Functions of the database management system

Bibliography:

1. Access 2003. Practical guide: V. E. Koshelev - St. Petersburg, Binom-Press, 2008 - 464 p.
2. Microsoft Access 2002. Tutorial: T. V. Timoshok - Moscow, Dialectics, 2004 - 352 p.
3. Microsoft Access 2003 for beginners: V. Stepanov - Moscow, Aquarium-Print, Printing House - Vyatka, 2006 - 128 p.
4. Database development in Microsoft Access: N.P. Obukhov - Saint Petersburg, IVESEP, Knowledge, 2008 - 92 p.
5. Application development in MS Access: S. I. Moiseenko, B. V. Sobol - Moscow, Williams, 2006 - 272 p.
6. Express course. Microsoft Access XP: - Moscow, AST, Harvest, 2006 - 32 p.