Conclusion to the laboratory on microsoft access. Report on laboratory work on the topic management system

Designing a report in a DBMSAccess2000 in Design view

The purpose of the work: obtaining practical skills in creating reports with calculations in the lines And overall results in mode constructor, reporting, preview reports.

Setting the task:

To ensure the convenience of any trade organization with data on goods and their suppliers, as well as for the analysis of this information and their visual representation, it is necessary to create a report containing information about the name of the goods included in the order, the number of units of goods in each lot, the price per unit of goods , batch cost and total order value, as shown in table 6.1.

Table 6.1

Order Information

Task 6.1. Create reportOrders , containing data on the name of the product, its quantity, price per unit, cost of the batch of goods and the total cost of the order, in the report designer mode and based on the request SQLPrice .

Open the database file created in lab 4.

Go to tab Reports and click on the button Create.

A window for creating a new report will appear on the screen (see Figure 5.1 - Lab 5.)

Select the mode Constructor and as a data source - SQL query Price .

Add header And Note by executing the command Type/Title/Note of the report.

Add Headers and footers report by running the command View / Headers.

Add Elements panel by executing the command View/ Toolbox.

Add Data source (see Fig. 6.1) by running the command Type/Source of data.

Rice. 6.1. Report Data Source

The report designer window will look as shown in Fig. 6.2.

Rice. 6.2. Report Designer window

Click on the toolbar button ( Inscription) and place in the area Report title text Orders.

Select all fields of the data source Price by double-clicking on the title of the source.

Set the mouse cursor on the selected fields of the data source and, holding the left mouse button, move them to the center Data areas .

Select field labels with the mouse Name ,Ordered , Price , Lot_cost while holding the key Shift.

Cut out field labels.

Select by mouse click Page header report.

Insert field labels removed to the clipboard.

Arrange field labels in one line with the mouse.

Select field labels by holding the key Shift.

Make horizontal and vertical intervals between field labels equal by executing the command Format/ Horizontal Spacing/ Make Equal And Format/ Vertical Spacing/ Make Equal.

Place fields in Data areas report directly below their captions in one line and align the intervals horizontally and vertically in the same way.

Press the button on the toolbar.

Sketch in area Report note the frame of the new field (see Figure 5.7).

Right-click on the label (left side) of the field, thereby calling the context-sensitive menu, and select the command Properties.

The inscription properties window will appear on the screen, shown in Fig. 6.3.

Rice. 6.3. Label properties window

Enter on tab Everything in line Signature title TOTAL .

Call field properties free (right side of the field) in a similar way.

Enter on tab Data in line Data formula starting with a sign equals :

= sum([cost]![batch_cost])

Press the key Enter.

Close the field properties window.

Add an inscription from toolbox to area Report note and enter text February 2004 Order Report .

Press the key Enter.

Run command Insert/ Page numbers.

Set the following parameters:

PageNfromM

footer

Centered

Display number on first page

Click on the button OK.

Set the desired background color and font color for each area of ​​the report and all its elements using the toolbar buttons and .

Report Designer window Orders will have the form shown in Fig. 6.4.

Rice. 6.4. Report Designer window Orders

Perform a preview of the report by executing the command File/ Preview or by clicking the button on the toolbar.

Laboratory work № 27

Topic: Working with data and creating reports in Access DBMS

Objective- learning how to design and print reports in the Access 2007 environment.

Equipment: PC, Windows XP Professional, MS Access.

The task:

1 Learn the purpose and methods of designing reports in Access.

2 Develop reports in accordance with the assignment.

3 Answer security questions.

4 Make a conclusion about the work done

Order of execution:

1 In the database window, run the command: Ribbon tab Create - Toolbar Reports - Report Wizard.

2 Select from the list the table (or query) that will be used as the data source (for example, the Customer Addresses query).

3 In the Reporting dialog that appears, move all available fields to the "Selected Fields" area.

4 Use the Report Wizard to create the Birthdays report. Use the Employees table as the data source.

5 If you need to print mailing labels, Access provides this option. To do this, select the Clients table and execute the command: Creation ribbon tab - Reports toolbar - Labels.

6 In the dialog box that appears, specify the label size, unit system, label type, and click Next.

7 In the next step of creating a report, set the font, size, text color, and style. Press "Next.

8 Select the fields that will be placed on the sticker. For example, Company Name, Address, Phone and Fax. If you want to display specific text on each sticker, then enter it in the sticker prototype.

9 Change the name of the label report, if necessary, and click Finish.

10 Sometimes reports need to calculate totals, averages, minimums, maximums, and percentages. To do this, run the Report Wizard and specify the Order Amount query as the data source.

11 In the Wizard's dialog box, which specifies the sort order for records, click the Totals button.

12 In the Totals dialog box, for the Amount and Tax fields, select the checkboxes in the sum column to calculate the total.

14 Create the Birthdays report using the Birthdays query as the data source.

15 Create a Completed Orders report, which will contain data on the company and the amount of the order. Calculate the total, the average (Avg), and the maximum amount for each firm.

16 Show your work to the teacher.

17 Answer security questions.

18 Draw a conclusion about the work done.

Test questions:

1 What are the reports for?

2 What reporting methods do you know?

4 What totals can be calculated in reports?

5 How to print mailing labels in Access?

MINISTRY OF EDUCATION AND SCIENCE OF THE RUSSIAN FEDERATION
MOSCOW STATE INSTITUTE OF ELECTRONICS AND MATHEMATICS

(Technical University)

« Microsoft office .

Database management system
ACCESS»

Student: Malofeev S.A. C-12

Lecturer: Voskov L.S.

Moscow 2008

Basics

DATABASES and Database Management Systems (DBMS)
B basics D data (DB) are designed for storage on a computer and efficient use of large volumes of a wide variety of information:

• 
  about books, magazines and newspapers in libraries,
• 
  about goods in stores and products in factories,
• 
  about exhibits in museums and various collections,
• 
  about all employees of firms, enterprises and institutions,
• 
  about all aircraft flights at airports, about ticket prices and availability,
• 
  about the schedule of all trains from stations, about ticket prices and availability,
• 
  about all cars registered in the traffic police (GAI), including stolen ones,
• 
  about all vouchers, including "burning" ones, in travel agencies and travel agencies,
• 
  about the weather in any point of the globe now and many years ago, etc.

1. 
   Quickly create databases, supplement and modify them.
2. 
   Quickly find specific data you need in the database.
3. 
   Sort the data in the database according to different criteria (for example, alphabetically).
4. 
   Extract subsets of data on the screen according to your requests, i.e. satisfying the given criterion.
5. 
   Perform statistical calculations on database data for analysis and decision making.
6. 
   Print data selected for specific purposes, i.e. prepare certificates, reports, etc.

Like Excel tables, database tables consist of columns and rows. In this case, the columns in the database are called " FIELDS and they have specific names

Each row in the database tables is called " RECORD" and has its own serial number, the same as a row in an Excel spreadsheet. The number of rows, i.e. records in database tables, is practically unlimited.

Everything " RECORDS" (rows) in the database table contain in each cell necessarily the same type of information corresponding to the name " FIELDS". This is the main difference between a database table and spreadsheet Excel. For example, in a column with the field name " FULL NAME"(see database table " Student" on the next page) in all entries (i.e. in all lines), you will see specific names of people;

in the column with field name " GODR" in all records (i.e. in all lines) you will see the specific years of birth of students;

in the column with field name " NG" in all records (i.e. in all rows) you will see the specific numbers of student groups.

The database may include not one, but several related tables. except tables - the main object of the Database - the database may contain other objects, namely:

form (one or more). A form is a way of displaying data from database tables on the screen, convenient for entering data and for viewing them.

Inquiry (one or more). A query is a way to select and screen output data from database tables according to the criteria you specified.

Report (one or more). The report is a way to select and printout

Macro (one or more). A macro is a set of macro commands that perform certain operations with a database, such as printing reports.

Module (one or more). A module is a program in the Access Basic programming language used in a database to perform non-trivial tasks.

All the listed objects, if they were created, are included in the Database file with the extension . mdb.

You will be learning Access 2000 - latest version program, but it differs little from the Access 97 version, so both versions are currently used.

This practical work will allow you to master all the basic operations of creating, modifying and using Databases, regardless of the version of Access that you use.

If there is an icon somewhere on the screen Access programs, place the mouse pointer on it and double-click to run this program for calculation.

If there is no Access program icon on the screen, then click on the Start button to open the main menu of the system, select the "Programs" item in it, and then in the second menu that automatically opens, click on the "Microsoft Access" item to start this program for calculation.

The typical software will open. Windows window with the title "Microsoft Access", and in it (Fig. 1):

DIALOGUE BOX Microsoft Access to create a new database or open an existing one.

MENU BAR below the title bar (File Edit View Insert Service Help Window),

STANDARD TOOLBAR below the menu bar, which is toggled on and off by the Toolbars command on the View menu. All tools have a “label” that appears automatically when placed on top of the mouse arrow tool.

PROGRAM STATUS LINE at the very bottom of the window. It will display current information about your database and the mode of Access.

THEORY: CREATING AND USING QUERY IN BASES

DATA

THEORY: CREATING AND USING REPORTS IN DATABASES

THEORY: CREATING THE DATABASE SCHEMA - STAGE 3
Creating a database schema is the definition of relationships between tables included in this database, or rather, between specific fields of these tables.

When tables are linked, changes in one of them are automatically transferred to the other. The main point of linking tables is to avoid duplication of information in the database.

The easiest way to understand the meaning of relationships is to use the example of the specific database that you create, i.e. Studying proccess. The link diagram of this database is shown in fig. 3.

In the window Studying proccess: database click button Data Schema on the toolbar. In the opened window Adding a table, select bookmark tables and by pressing the button Add, place in the window Data Schema all tables. Then press the button close. As a result, in the window Data Schema all database tables will be presented Studying proccess with lists of their fields.

Rice. 3. DB schema Studying proccess
Relationships should be established with empty tables, otherwise there may be problems matching relationship properties.
THEORY: Creation and use of reports in databases.

Development of multi-table reports
Along with single-table Access reports allows you to create its complex reports that provide data output from several interrelated database tables. To display related data in a multi-table report, the necessary links are established automatically in accordance with the data schema.

Multi-table reports can contain a main part and an included part, i.e. a subreport. Each of these parts is built on its own table.

Multi-table reports can also have fields from different tables without requiring subreports to be included.

Consider building a report for tables that are in one-to-many relationships. In these relationships, one table is master and the other is slave. The construction of such a report has its own characteristics depending on the choice of the main or subordinate table as the basis of the report.

Rice. 19. Department Report Layout
Consider the technology of creating a multi-table report on the example of tables DEPARTMENT AND TEACHER. Suppose it is necessary to prepare a report containing information about departments, including lists of teachers.

Report layout. Suppose that the layout for the report with the lists of teachers by department should look like the one shown in fig. 19. In accordance with the draft layout, the report is supposed to display data for each department, including the name, code and phone number, as well as the name and photo of the head. These data are contained in the table CHAIR.

1. 
   In the tabular section for each department, you must display a list of teachers. Such data can be obtained from the table Teacher. The proposed layout gives reason to choose the table as the main table of the report. CHAIR, and the table TEACHERVATEL- as a data source for a subform with a list of teachers.

Completed the exercises:

Exercise 1: Creating a Basic Database with a Single Table

Exercise 2: Changing column widths with the mouse or using the Column Width command on the Format menu

Exercise 3. "Inserting" and "Deleting" fields, as well as changing the order of "Fields" (columns) in the table.

Exercise 4: Inserting a New Field with "Photos" of Employees

Exercise 5: Entering new data in a table, editing and deleting records

Exercise 6: Sorting data in a database table

Exercise 7

Exercise 8. Creating and using a new database object - "Request".

Exercise 9: Create and use a Parameter Query.

Exercise 10. Creating a new object in the Database - "Forms".

Exercise 11. Creating and using a new database object - "Report".

Exercise 12: Creating a Parameter Report Based on a Parameter Query

Exercise 13: Designing the Group Table in Table View

Exercise 14

Exercise 15: Designing the Chair Table in Design View

Exercise 16: Designing the Study and Progress Tables in Design View

Exercise 18: Deleting a relationship between tables

Exercise 19: Entering data directly into the Major, Instructors, and Groups tables

Exercise 20: Forming a query in Design view

Exercise 21: Preparing to Create the Group Student List Composite Form

Exercise 22: Using the wizard to create a form for two tables

Exercise 23: Choosing a Form Type

Exercise 24: Naming a form and opening it

Exercise 25: Editing a Form in Design View

Exercise 26: Editing a Subform

Exercise 27: Switching to form mode and loading tables

Exercise 28: Multi-table report with main body based on main table

Exercise 29: Creating the body of a report

Exercise 30

Exercise 31: Refine a subreport

Exercise 32: Enable a subreport.

Populating database tables.

Creating links in the database:

Ufa State Aviation Technical University

Department of Informatics

Lab #4

Information technologies for creating and processing databases withhelpMSACCESSXP.

INEC student

Group K-122

Kizka N.D.

Checked:

Filosova E.I.

Report onACCESS.

1) Creation of tables.

In order to create tables in ACCESS, we must select the Create table using the constructor element in the Database. In the constructor, we fill in the Properties of the field and the Fields we directly need. So we create the tables we need. (Clients, Implementation)

2) Creating a data schema.

To create a Data Schema, we use the icon on the control panel.

After opening it, we insert the required tables into the tables with the Add Table command and establish a connection between the fields by simply dragging the field we need from one table to another.

3) Sorting.

To sort, we need to open a non-key table and use Records / Filter / Advanced filter to sort the data.

4) Selecting data using queries.

To create a query, use the Query Builder.

 a specific value in a text field (for example, select products of one category).

 a range of values ​​in any numeric field (for example, select goods whose cost is more than 1,000 rubles).

 range of values ​​in any text field (for example, find products whose names begin with the letters M-S).

 inaccurate matching of values ​​in a text field (for example, find customers living in the same city).

 logical AND linking two values ​​of one numeric field (for example, goods costing more than 200 AND less than 500 rubles).

 logical AND, connecting the values ​​of two different fields (for example, goods worth more than 200 rubles sold today).

 logical OR linking two values ​​in one text field (for example, Products of one or another category).

 logical OR linking the values ​​of two different fields (for example, goods sold on a certain day, OR goods whose cost is at least 100 rubles).

 NOT operator in a text field (for example, all products except a certain category).

5) Creating queries using the expression builder.

Create a database query in which one of the fields is obtained by concatenating the values ​​of several text fields of the source table.

6) Creation of final queries.

Create a record update query that doubles the value of one of the numeric fields.

Create a query against a copy of the Products table in which all records for products of the same category will be deleted.

Create a query to add to the copy of the Goods table, in which records will be added to the copy from the Goods table by any condition.

Create a cross query that calculates for each customer the total volume of each product category.

Development of reports.

To create a report, you need to use the Report Wizard, in which we will select all the fields of the tables we need and the calculation fields, as well as the Header and report type forms.

Test questions.

1. What data model is called relational?

In the DBMS for PC, a predominantly relational model is supported, which is distinguished by the simplicity and uniformity of data presentation by the simplest two-dimensional tables.

The main logical structural unit of data manipulation is a table row - a record. The structure of a record is determined by the composition of its fields. The set of record fields corresponds to logically related attributes that characterize some essence of the subject area. Typical DBMS functions for data manipulation are: selection, addition, deletion, data modification.

Microsoft Access calls objects anything that can have a name, such objects are tables, queries, forms, macros, and modules.

How to implement a one-to-many relationship in a relational data model?

For communication, we will use the Assessment object. Each student has grades in multiple disciplines, so the relationship between Students and Grades will be One-to-Many (1:M). There are many students taking each discipline, so the relationship between disciplines and grades will be One to Many.

What is included in the table structure?

The table structure includes: Field Name, Data Type, Note and Field Properties.

4. How to define field properties in a table?

It is necessary to select Properties of fields in the Table Designer and set the qualities of this table that we need in them.

5. Name the main elements of the Query Builder window.

Field, Table name, Sorting, Display, Selection criteria or.

6. How to write selection conditions with logical AND and logical OR in a query?

You must use the Expression Builder and specify either And or Or in the condition.

7. How do I create a calculated field in a query?

It is necessary to design some function in the field using the Expression Builder to calculate some results.

8. What are summary and cross queries used for?

For summing up any Results, for example, calculating the average or maximum value.

And with the help of a cross query, you can more clearly present the data of the final queries, which provide for grouping according to several criteria (two, in particular). In this case, the values ​​of the fields according to the first grouping attribute can become row headers, and according to the second - column headers.

9. How can you use action queries to edit tables?

For example, using a Delete Request, you can delete any record, or Update, Add, Insert from another table.

10. What sections does the Form Designer window consist of?

Of such sections that were set in the process of forming forms.

25.1 Theory

Explore theoretical basis creating forms in the Access database, discussed in the lecture course, in the section "Reports in the Access database 2007 - 2010" or other sources of information.

25.2. Objective

Creating reports in the Access database using the Wizard and Designer.

25.3. Formulation of the problem

Initial data:

1. Subject area: "Deanery" (student progress).
2. The main subject-significant entities: Students, Groups of students, Disciplines, Progress.

Work plan:

1. Create the necessary reports to display information from the database created in Lab 23.
2. Test the reports (use multiple values ​​for reports with parameters).
3. Save your reports.
4. Submit a lab report.

25.4. Step by step work

To create a report, follow these steps:

Rice. 25.1

25.4.1. Editing a Report

To edit a report, do the following:

1. remove the student code fields in the header and data area;
2. move all fields in the header and data area to the left.
3. Change the title of the page:
   • In the Report Title section, highlight the inscription Students.
   • Position the mouse pointer to the right of the word Students so that the pointer changes to a vertical bar (input cursor) and click at that position.
   • Enter NTU "KhPI" and press Enter.
4. Move the inscription. IN Footer select the =Now() field and drag it to the Report Header named Students. The date will be displayed below the title.
5. To view the report, click on the "Preview" button on the "Report Designer" toolbar.

25.4.2. Report Formatting

To format a report, do the following:

1. Highlight the heading Students of NTU “KhPI”.
2. Change the typeface, font style, color, and background fill color.
3. On the Report Designer toolbar, click the Preview button to preview the report.

25.4.3. Change the style of a report

To change the style of a report, do the following:

1. On the Report Designer toolbar, click the AutoFormat button to open the AutoFormat dialog box.
2. In the Styles list of the AutoFormat Report object, click Strict and then click OK. The report will be formatted in the Strict style.
3. Switches to Preview mode. The report will be displayed in the style you selected. Henceforth, all reports created using the AutoReport feature will have the Strict style until you specify a different style in the AutoFormat window.

25.4.4. Shutdown

Report to the teacher about the work done. After permission to shut down, close application program Microsoft Access, after which you can start passing tests on the work performed.