Laboratory work on Microsoft Access. I. Laboratory work on the Access database Download ready-made laboratory tasks ms access

ACCESS

Laboratory work No. 3

Subject: Database creation.

Building the second table SESSION in the database SESS .

Goal of the work:

Learn how to form a table structure, enter and edit data, and determine a key to link two tables.

Completing of the work:

Run the program:

button "Start" " Programs " « M Microsoft Access ».

After launch, the application window appears on the screen.

Open the created empty database SESS . To do this, in the window that opens, set the switch"Open database" select the name of the created database and click the button« OK ».

In the empty database window, you need to create a second table that is part of this database. This table will contain information about the results of the session and will be called SESSION .

In the open database window, click on the tab “Tables” and press the button " Create ".

Select from the dialog box list “New table” way to create a table:

We will create a table usingDesigner. Select in window Constructor and press “ OK”.

In the pop-up window, enter the name of the fields, select the data type and define the properties of the fields.

In a collumn Field name field names are specified:

1. Number; 4. Evaluation3;

2. Evaluation1; 5. Evaluation4;

3. Evaluation2; 6. Result;

The table should not have two fields with the same names.

For each field included in the record, you must specify data type.

Data type determines the type of information that will be stored in this field. The data type is selected in the column Data type .

Table SESSION contains information about the results of students passing four exams.

The result of the session can take one of the following values:

OTL - for all excellent grades;

XP1 - for one four and all other fives

CHORUS - for two fours or more;

NHR - for satisfactory and unsatisfactory grades;

Table structure SESSION .

Key sign

Field name

Data type

Field size

Field Format

Key

number

Text

score1

Numerical

Long integer (set by default)

Fixed

score2

Numerical

Long integer

Fixed

score3

Numerical

Long integer

Fixed

score4

Numerical

Long integer

Fixed

result

Text

In this table, we declare the key field to be the field Number.

To do this you need:

highlight field Number or simply place the cursor there;

press the button Key field on the standard toolbar with a key icon or

menu“Edit” “Key field”

A small image of a key will appear to the left of this field - a sign of the key field.

Once entered, the table should look like this:

After determining the composition, type, size of the fields and the purpose of the key, close the table and give it a name SESSION.

Having determined the composition of the table, you need to open it and enter the data.

To do this, select in the open database SESSION and press the button OPEN .

After entering the data, the table should look like this:

After entering the data, the table must be closed. To do this you need to enter the command twice

menu “File” “Close”

Exercise:

Create table SESSION.

Control questions.

Table creation sequence.

Rules for creating tables.

Conditions for assigning a key.

Saving tables.

Subject: creating reports.

Goals of work:

ü learn to create reports using the Report Wizard;

ü make changes to ready-made reports using the Constructor;

ü master the basic techniques of making inscriptions on envelopes and stickers.

Theoretical part.

Report is a flexible and effective tool for organizing viewing and printing of summary information. The report can provide the results of complex calculations, statistical comparisons, and also include pictures and diagrams. The user has the opportunity to develop a report independently (in the Designer) or create a report using Masters, i.e. semi-automatically.

Practical part.

Exercise 1. Open your database.

Task 2. Create a report using Report Wizards.

· Open a tab Creation, menu Reports.

· Select Report Wizard and table "Personal data".

· Select the required fields that will participate in the report, click the “Next” button.

· In the new window, select the fields to group so that the “Last Name” field is indicated first, click the “Next” button.

· At this step, sort the data alphabetically, click the “Next” button.

· Select layout type Stepped and click on the “Next” button.

· Select report style: Open and click on the “Next” button.

· Set the report name: "Report1" and click on the “Done” button. You will be taken to report viewing mode.

· Close the report by agreeing to save it.

On one's own Create two more query reports, Query 3 and Query 5, choosing from different layouts: block ; structure , choosing from different styles. Save the reports as “Report 2” and “Report 3”.

Task 3. Create Blank report to a column based on a table "Certificate Report" and save it with the name "Achievement."

Using the Designer, change the color of the title letters, their size and font.

Task 4. Create mailing labels.

· Open a tab Creation, menu Reports.

· Select the “Personal Data” table, command Stickers.

· In the next window, select the font, font size, saturation and color, click on the “Next” button again.

· In the next window, create a sticker prototype by typing the word PERSONALITY and selecting the appropriate fields, click on the “Next” button.

· In the next window, specify the fields for sorting (Last Name, First Name), click on the “Next” button.

· Enter a report name "Stickers" and click on the “Done” button.

Review the Stickers (Figure 8).

Figure 8

Submit all reports to your instructor.

ACCESS 2007 tests

Test No. 1

Goal of the work: consolidate the knowledge gained in the process of performing previous work on creating tables, entering data into tables, creating a database schema, using a database.

Stages of work

Database structure design.

The database will consist of three tables: Videotapes, Genre and Country. Corresponding fields in the table Videotapes will become lookup fields from other tables.

Building empty database tables.

Create a Video Cassette table in Design mode. It will have six fields: Film title, Genre, Main actor. Country, Language and Duration. The Movie Title field is a key (unique field). The Country table has only one Country field, and the Genre table has only one Genre field. These fields are key.

Creating a database schema.

Entering data into tables.

First the tables are filled Country and Genre, then - table Videotapes. You can fill it out in the mode tables, Or you can create an input form.

Using the database.

First the query is created, and then the report. Next, you should create a button form to work with the database.

Progress

Exercise 1. Create a new empty database.

Open the Access program.

Click on the icon New database. In field File name for the database name enter the word Video library and save it in your folder.

Click the button Create. A window with the main elements of the database will appear on the screen.

Task 2. Create the structure of the Genre table in Design mode.

Select mode Constructor

Enter field name Genre and data type - text. (Data type is selected using the drop-down menu).

Make the Genre field a key field by placing the cursor over the name of this field and clicking the button with the key image on the toolbar.

Genre.

Task 3. Create the structure of the Country table in Design mode.

Select a tab Creation, team Table

Select mode Constructor

Enter field name A country and data type - text.

Make a field A country key.

Save the table as A country.

Task 4. Create the structure of the Videotape table in Design mode.

Select a tab Creation, team Table

Select mode Constructor

Enter the field name and type according to the table:

Make a field Movie title key.

For field A country select bookmark Master of Substitutions, control type - Combo box, string source - A country.

For field Genre select bookmark Master of Substitutions, control type - Combo box, string source - Genre.

Save the table as Videotapes.

Task 5. Create relationships according to the data schema.

Click on the tab Working with the database, button - Data schema. A window will appear Adding a table. Select tables Genre, Videotapes, Country and click on the button Add.

Click the button Close window Adding a table. In the window Data Schema A conditional view of these tables will appear.

Place your mouse on the field name Genre in the table Genre Genre in the table Videotapes. Connections

Turn on icon

Turn on

Turn on Cascade deletion of related fields.

Click the button Create. A connection will appear "one-to-many".

Place your mouse on the field name A country in the table A country and, without releasing the mouse button, drag it onto the field A country in the table Videotapes. Release the mouse. A dialog box will appear Connections

Turn on icon Ensuring data integrity.

Turn on Cascade update of related fields.

Turn on Cascade deletion of related fields.

Click the button Create. A connection will appear "one-to-many".

Task 6. Complete the supporting tables.

Fill the table Genre data: action, comedy, tragedy, fiction.

Fill the table A country data: Russia, USA, France, England, Hungary.

Task 7. Create a form to fill out the Videotapes table.

Create the form in any way convenient for you

Task 8. Fill out the Videotapes table using the form provided.

Fill the database with data from the following table:

Movie title	Leading actor	Genre	A country	Language	Duration
Matrix	Keanu Reeves	Fantastic	USA	English	136min
Highlander	Christopher Lambert	Action	USA	English	120 min
Titanic	DiCaprio	Tragedy	USA	English	120 min
Mask	Jim carrey	Comedy	USA	English	60 min
Terminator	Schwarzenegger	Action	USA	Russian	60 min
Commandos	Schwarzenegger	Action	USA	Russian	60 min
Junior	Schwarzenegger	Comedy	USA	Russian	60 min
Toy	Pierre Richard	Comedy	France	French	60 min
Height	Vladimir Vysotsky	Worse national	Russia	Russian	60 min

Task 9. Use the Query Wizard to retrieve all videotapes of Schwarzenegger in action films.

Build a Simple Query Based on the Videotape Table

In line Selection condition, located under the field Genre, dial Action, and under the field Main performer roles - Schwarzenegger.

Save the request with a name Schwarzenegger in action films.

Task 10. Prepare the report "Schwarzenegger in Action Movies" for publication.

Prepare a report based on the request.

Save the report with a name Schwarzenegger in action films.

Task 11. Create a form to request Schwarzenegger in action movies.

Create a form using Masters of Forms based on the created request.

Save the form with a name Schwarzenegger in action films.

Task 12. Create a button form to work with the database.

Call window Button Form Manager(Working with Databases tab).

Click in the window Button Form Manager button Change, and in the dialog box that opens Changing the Button Form Page- button Create. As a result, a dialog box will appear Changing a button form element.

In field Text dial Schwarzenegger in action films.

In field Team select Opening the form for modification.

In field Button form select Schwarzenegger in action films.

Click the button OK.

Create another button form element in the same way. Video cassettes, designed to open a form Videotapes in change mode.

Create a button to close the database in mode Exit the application.

Close dialog boxes Changing the Button Form Page and the Button Form Manager.

Open Button form in mode Constructor, change the caption to Video library, keep the shape.

Check the operation of all pushbuttons. After pressing the button Exit you will be exited from Access.

23.1 Theoretical information

Study the theoretical foundations of creating tables and their relationships in the Microsoft Access 2003 DBMS, discussed in the lecture course, in the Microsoft Access 2007 - 2010 DBMS or other sources of information.

23.2. Goal of the work

Creating a database (tables and connections between them) using Microsoft Access DBMS.

23.3. Formulation of the problem

Subject area : Dean's office (student performance).
Main subject-significant entities: Students, Groups of students, Disciplines, Progress.

Basic subject-significant attributes of entities:

Basic requirements for system functions:

select student performance by groups and disciplines.

Characteristics of the table-object: Groups of students. Table 1

Characteristics of the object table Students: Table 2

Description	Field name	Data type	Field properties:
Description	Field name	Data type	Field size	Indexed
Student code (key field)	Student Code		long integer	Will install automatically
Group code (foreign key)	Group Code	numerical	long integer	Yes. Similarities are allowed
		text
		text
Surname	Surname	text
	text
Date of Birth	Date of birth	date Time
Place of Birth	Place of birth

Characteristics of the table-object: Disciplines. Table 3

Characteristics of the table-object: Academic performance. Table 4

Description	Field name	Field type	Field properties:
Description	Field name	Field type	Field size	Indexed
Evaluation code (key field)	CodeRatings		long integer	Will install automatically
Discipline code (foreign key)	Discipline Code	numerical	long integer	Yes (Allowed)
Student code (foreign key)	Student Code	numerical	long integer	Yes (Allowed)
		text
	Type of control	text

Work plan:

Create a new database.
Create the necessary tables according to the subject area.
Set the data types (counter, text, numeric, etc.), description and other required field properties (field size, input mask, signature, default value, etc.) of the created tables.
Define primary keys in the created tables.
Determine the necessary relationships between tables, set the necessary parameters for ensuring data integrity and the type of join.
In the data schema, check that the tables created and the relationships between them are correct.
Fill the created tables with data (minimum 10 records per table).
Create the necessary selection queries that fulfill the basic requirements for system functions:
- Create a selection request. Display all names of students who received excellent/A grades in all disciplines (Student Achievement).
- Create a parametric query. Create a query that will display the student's last name, group name, discipline and Grade (ex/A) received by the student in the discipline.
Prepare a laboratory report.

23.4. Step-by-step work execution

23.4.1. Download Microsoft Access

Follow these steps: Start -> Programs - Microsoft Access (or by clicking on the corresponding icon in the Microsoft Office panel).

23.4.2. Create a new database

After loading MS Access, the main window will appear on the screen. When you launch Access for the first time, the main window displays a task pane in “Getting Started” mode, with which you can open existing databases and “Create a file.”
When you select the “Create File” command in the task area, the mode will change to “Create File”.

When you select the “New Database” command, the “New Database File” dialog box will open, in which you must select the name of the disk and directory for storing the database, as well as the name of the database (the file type is set by default to “Microsoft Office Access Databases”) and click on the “Create” button, a file with the extension will be saved .mdb

A database window with an assigned name will appear in the main application window, for example “Dean’s Office: database (Access 2000 format).

23.4.3. Create table structures

In the Dean's Office: database window that appears on the screen, select the Tables tab and click on the Designer icon on the toolbar. The Table Designer window will appear.

23.4.3.1. Create table structure Groups of students.

In the Table Designer window, fill in the Field Name, Data Type and Field Properties sections in accordance with Table 1.
To move from section to section, use the key<ТаЬ>or click the mouse on the desired cell.
In the Data Type section, to change the type, expand the type selection window by clicking on the list expansion button, then click on the line containing the corresponding type.

In this case, information about the properties of this field appears at the bottom of the screen in the Field Properties section. If necessary, you can make changes there by clicking on the corresponding line, deleting the previous value and entering a new one. Additionally, you can set the field format, condition on the value, etc.

After filling out the sections, you must specify a key field. Select the GroupCode field as the key field.
To create a key field, select the GroupCode field by clicking to the left of the field name in the selection bar. Select Edit - Key Field from the menu or click on the Key Field icon on the application toolbar. An image of the key will appear to the left of the field name.

After creating the table structure, you need to save it. File. - Save, or. Save as... In the Save dialog box, enter a name to save the created table: Groups of students, then OK.

23.4.3.2. Create table structure Students:

In the Table Designer window, fill in the Field Name, Data Type and Field Properties sections in accordance with Table 2.

For the Group Code field, select the type Substitution Wizard. This will make it easier to fill out this field with data, since the Students table will display not the group codes, but their names. After selecting the Lookup Wizard type, the first Create Lookup dialog box opens.

In this window, you select how the lookup column will get its values: from a table or a query, then click Next.

In the next dialog box, select the table containing the lookup column – Student Group, click on the Next button.

In the next window, select the field (Name) used as a lookup column and click on the button to move it to the Selected Fields window. Click the Next button.

The next window contains messages about what actions to perform on the column if necessary. Click the Next button.

In the next window, click the Finish button. A message appears indicating that you must save the table before creating the relationship. To do this, click on the Yes button.

In the Data Type section, the numeric type will be indicated, i.e. type corresponding to the type of the lookup field from the Student Group table.

For the GroupCode field, set the property to Indexed Field. To do this, in the Field Property section, select the Indexed field line. Click on the list expansion button and select the line - Yes (matches are allowed).

After creating the table structure, you need to set the key field - Student Code. Save table structures by entering a table name, Students.

23.4.3.3. Create table structure Disciplines.

In the Table Designer window, fill in the Field Name, Data Type and Field Properties sections in accordance with Table 3.

After creating the table structure, you must set the key field - Discipline Code. Save table structures by entering a table name, Disciplines.

23.4.3.4. Create table structure Academic performance.

In the Table Designer window, fill in the Field Name, Data Type and Field Properties sections in accordance with Table 4.

For the Discipline Code and Student Code fields, select the Substitution Wizard type. This will make it easier to fill out these fields with data, since the Academic Progress table will display not the discipline and student codes, but their names.

In the table, you must select the Discipline Code and Student Code fields as indexed fields. Set the Indexed field property to Yes (matches allowed).

After creating the table structure, you need to specify a key field - Evaluation Code (you can refuse to create a key field)
Save table structure with name Academic performance.

23.4.4. Establish relationships between tables:

Select the command Tools - Data Schema or select the Data Schema icon on the toolbar. The Data Schema window appears, containing the Add Table dialog box, which displays a list of tables. Select all tables: Student Groups, Students, Disciplines and Progress and click on the Add button. Tables will appear in the Data Schema window, after which you need to close the Add Table window.

In the Edit Relationships dialog box that appears, you need to activate the checkboxes: Ensure data integrity, cascade update of related fields and cascade delete of related records, make sure that the relationship type is one-to-many and click the Create button.

In the Document Outline window, a one-to-many relationship appears between the Student Groups and Students tables. Similarly, link the Students and Progress tables by dragging the Student Code field of the Students table to the corresponding field in the Progress table and checking the same checkboxes. In addition, you need to link the Discipline and Achievement tables using the Discipline Code.

Close the Communications window. When prompted to save, click the Yes button.

23.4.5. Filling tables

To fill out the Student Groups and Students tables, data is selected independently, and for the Disciplines and Achievement tables, the data is presented in Tables 5 and 6.

It is advisable to start filling out tables from the Student Groups table, since the Group Code field of the Students table is used as a substitution column for filling out the corresponding field in the Students table.

In the Database window, select the desired table, then click the Open button.

The structure of the database table will appear on the screen in table mode. Filling is done according to records, i.e. information is entered for the entire line. When the current line is filled, a new empty line will appear.<Таb>.

Move to the next field by pressing the key To fill out the MEMO field in the Students table, press the key combination

, having previously placed the cursor in the MEMO field. The Input Area text box opens. After entering or editing data in this window, click on the OK button.

To fill the Group Code field in the Students table with data, use the substitution field list by opening it by clicking on the expand list button. The group name is selected by clicking the mouse in the corresponding line of the list.

Table-object Disciplines Table 5

Data for filling out the table Achievement Table 6

6. Data integrity check (cascade deletion).

In the Students table, delete code 1 and make sure that the corresponding entries are deleted from the Student Groups and Disciplines tables. Recover deleted data in all tables.

7. Formation of requests

1) Create a selection request.

Task: create a query “Student Performance” (display all names of students who received excellent/A grades in disciplines), containing the fields: Groups of students, Last name, First name, Patronymic, Disciplines, Grade. The list should be sorted by last name in ascending order. Specify selection conditions in the evaluation field: exc/A
In the database window, select the Queries tab and double-click on the Create a query in design mode icon. The active Add Table window appears with the inactive Query 1: Select Query window in the background. In the Add Table window, select the tables (Student Groups; Students; Achievement; Disciplines) on the basis of which the data will be selected, and click on the Add button. After this, close the Add Table window, the “Query 1: Select Query” window will become active.
Set the sorting principle. Move the mouse cursor to the Sorting line for the Last Name field, a button will appear to open a list of sorting modes: ascending and descending. Set the sorting mode in the Last Name field to ascending.
In the Selection Conditions line, you must enter search criteria. In the Score field, enter “excellent/A”, i.e. display all names of students who received excellent/A grades.
After completing the query generation, close the Select Request window. The Save dialog box will open - answer Yes (enter the name of the created query: Student Progress), and click OK. Return to the database window. The created query will appear in the database window when the Queries tab is selected.

To run a query: Click on the Student Progress query, and then click the Open button. A table will appear on the screen in which records should be displayed with the names of students, names of groups, names of disciplines and grades received, records sorted by students' last names in ascending order.
Note: to make changes to a request you must: select it by clicking the mouse, click on the Design button, make changes. Save the request and run it again.

2) Create a parametric query

Task: create a query that will display the student's last name, group name, discipline and grade (ex/A) received by the student in the discipline.

To do this, you must perform the following sequence of actions:

set a query in design mode or open an existing query: “Student performance” in design mode;
in the Request Form, in the Selection Conditions line, enter the selection condition in the form of an invitation in square brackets, for example [Enter last name];
close the Request to select window, answer the question about saving the change - Yes. Return to the database window, where the created query will be displayed;
execute the request by clicking on the button: Open. In the “Enter parameter value” dialog box that appears on the screen, you must enter the name of the student whose academic performance information you want to obtain, and click on the OK button;
A table with data about the selected student will appear on the screen.

23.4.6. Shutdown

Tell your teacher about your completed work. After allowing shutdown, close the application program Microsoft Access, after which you can begin taking tests on the completed work.

By studying the material, you will learn: What is a database? What types of databases are there? How to create databases in Microsoft Access? How is Microsoft Access different from Microsoft Excel?

Initial level of student training: To study this material, you must have user skills on a PC and know Microsoft Excel spreadsheets.

Required number of hours: 12

Educational material

Database is an information model implemented using a computer that reflects the state of objects and their relationships. An information model (or data structure) is a collection of interrelated data. Databases, according to the types of information structures, are divided into three classes: tabular (relational), network, hierarchical.

Closely related to the concept of a database is the concept of a database management system (DBMS). This is a set of software tools designed to create the structure of a new database, fill it with content, edit the content and select the displayed data in accordance with a given criterion, organize, design and subsequently issue it to output devices or transmission via communication channels.

There are many database management systems in the world: dBase, Paradox, FoxPro, Clipper, Oracle, etc. Despite the fact that they can work differently with different objects and provide the user with different functions and tools, most DBMSs rely on a single a well-established set of basic concepts. This makes it possible to consider one system and generalize its concepts, techniques and methods to the entire class of DBMS. We chose the Microsoft Access DBMS as such a system.

Relational database. The database created in the Access DBMS is a relational database. The main object of this database is interconnected two-dimensional tables consisting of rows and records of the same type. Each row, in turn, is made up of fields and is called a record. If there are no records in the table, this means that the database structure is formed only by a set of fields. By changing the composition of the fields of the base table, we change the structure of the database and, accordingly, get a new database.

To uniquely identify each record, the table must have a unique key (primary key). This key can consist of one or more fields. A single record is found based on the key value.

Relationships between database tables make it possible to share data from different tables. In a normalized relational database, relationships are characterized by one-to-one (1:1) or one-to-many (1:∞) relationships. The connection of each pair of tables is ensured by the same fields in them - the connection key. The relationship key is always the unique key of the main table in the relationship. In a subordinate table, it is called a foreign key.

Schemedata. In Access, the process of creating a relational database involves creating a data schema. The data diagram visually displays tables and relationships between them and ensures the use of relationships when processing data. The data schema sets the parameters for ensuring cohesive integrity in the database.

Since the Access DBMS is one of the Windows applications included in the integrated Office system, the program window interface and its main components - menus, toolbars, help system, as well as keyboard and mouse techniques are used in work similarly to other applications (Word, Excel) . The general view of the program window is shown in Fig. 1. Database fields not only determine the structure of the database - they also determine the group properties of the data written to the cells belonging to each of the fields.

Fig 1 – Microsoft Access DBMS window

Basic properties of fields in Microsoft Access DBMS database tables:

field name– determines how the data of this field should be accessed during automatic operations with the database (by default, field names are used as table column headings);

field type– determines the type of data that can be contained in this field;

field size– defines the maximum length (in characters) of data that can be placed in this field;

field format– determines the method of formatting data in cells belonging to the field;

input mask– defines the form in which data is entered into the field (data entry automation tool);

signature - defines the table column heading for a given field (if a caption is not specified, then the Field Name property is used as the column heading),

default value– the value that is entered into the field cells automatically (data entry automation tool);

condition on value– a constraint used to check the correctness of data entry;

error message– a text message that is displayed automatically when you try to enter incorrect data in the field;

Obligatory field– a property that determines whether this field must be filled in when filling out the database;

empty lines– a property that allows the entry of empty string data (this mainly concerns text data);

indexed field– if a field has this property, all operations related to searching or sorting records by the value stored in this field are significantly accelerated. This field is also used to check record values for duplicates.

Field properties vary depending on the data type. Database Microsoft Access work with the following data types:

text– a data type used to store plain, unformatted text of limited size (up to 255 characters);

Memo field– a special type for storing large amounts of text (up to
65,535 characters). Physically, the field stores a pointer to another location in the database in which the text itself is stored, but such a division is invisible to the user;

numerical– data type for storing real numbers;

date Time– data type for storing the calendar date and current time;

monetary– data type for storing monetary amounts;

counter– a special data type for storing unique (not repeated in the field) natural numbers with automatic growth;

logical– a type for storing logical data (can only take two values, for example, Yes or No);

object fieldOLE– a special data type designed to store OLE objects, such as multimedia objects;

Before creating a database, the logical structure of the database must be determined - the composition of tables, their structure and inter-table relationships.

In addition to tables, database objects also include queries, forms, reports, macros and modules, the creation of which significantly simplifies the user’s work with data arrays.

Requests provide fast and efficient access to data from one or more related tables. The result of the query is a table that can be used along with other database tables when processing data. Queries help prevent unauthorized access to confidential information contained in the main tables. When working with queries, data can be organized, filtered, combined, and the necessary final calculations can be performed on it. The query can be generated using QBE - pattern queries or using SQL instructions - structured query language.

Forms serve to organize the entry of information into tables by various users; their use makes it easy to automate data entry and eliminate entry errors. Forms can also be used to view queries and tables on screen.

Report formed to create a paper document, i.e. to print data.

To implement practical user tasks, the developer has to use programming tools: macro language and Visual Basic for Applications (VBA). Macros and modules created by the programmer operate with queries, forms and reports and combine disparate actions into a single user task.

In the Access database window, along with a list of objects, there are shortcuts for quickly launching Wizards or Designers for creating a new object.

Access wizards automate the process of creating database tables, forms, queries, reports, and data access pages.

Database placement. All database tables, as well as other Access objects - forms, queries, reports, macros and modules built for this database, and embedded objects - can be located on disk in a single database file in the .mdb format. This simplifies the technology for maintaining the database and the user's application. This ensures highly compact placement of all database objects on disk and efficient data processing.

A database application that is created by a VBA program can be compiled and saved as an application file in the .mde format. This removes the source VBA programs and compresses the database, significantly reducing file size. Once compiled, database objects cannot be modified.

When working with a shared database on a network with a file server, Access provides the ability to write the objects that make up the user's application to a separate file from the database on the server. This file is placed on all computers of users working with a common database. The application can be modified according to the user's needs.

Access includes tools for developing a project - an application that works with a database hosted on a SQL server. The project is located in the .adp file on the user's computer. When creating a project, the user can create a database on a SQL server or use an existing one.

Differences between Access DBMS and Excel spreadsheets

At first glance, the Access DBMS is very similar to the Microsoft Excel spreadsheet program in its purpose and capabilities. However, there are fundamental differences between them:

When working with an Excel spreadsheet, you can enter any information, text, or drawing into a table cell. If necessary, different data can be entered in one column of the table - numbers, text, dates. A table in an Access database differs from a table in Excel in that it has a data type defined for each field in the record, i.e., you cannot enter data of different types in different rows in the same column of the table.
Access allows you not only to enter data into tables, but also to control the correctness of the entered data. To do this, you can set validation rules directly at the table level. Then, no matter how the data is entered - directly into a table, in a form, or on a data access page, Access will not allow you to save data in a record that does not meet the specified conditions.
It is convenient to work with Excel tables if they contain a limited number of rows. Database tables can contain a huge number of records, and the DBMS provides convenient ways to extract the necessary information from this set.
If all the data necessary for work is stored in Word documents and spreadsheets, then as information accumulates, you can simply get confused in a large number of files. Access allows you to store all data in one file and access this data page by page, i.e., limits on computer memory resources are not exceeded.
Access allows you to create relationships between tables, allowing you to share data from different tables. In this case, for the user they will be presented as one table. Implementing such a feature in spreadsheet management systems is difficult, and sometimes simply impossible.
By establishing relationships between individual tables, Access allows you to avoid data duplication, save computer memory, and increase the speed and accuracy of information processing. This is why tables containing duplicate data are split into several related tables.
Excel allows multiple users to work on the same document, but these capabilities are very limited. Access can support 50 simultaneous users working with the database, and all users are guaranteed to work with up-to-date data.
Access has a developed system of protection against unauthorized access, which allows each user to see and change only those objects to which he has been granted rights by the system administrator. For example, you can prevent the use of certain menu commands, opening certain forms, or changing data in tables or forms. Spreadsheets also allow you to password-protect your data or enable view-only data, but these tools are much simpler.

Thus, the Access DBMS is used in cases where the application task requires storing and processing heterogeneous information about a large number of objects and assumes the possibility of multi-user operation.

Practical task

How to develop a database information model? How to create database objects?

Exercise: You need to create a database containing information about students.

Work execution technology

Let's determine the logical structure of the created database. Since almost all database fields are unique, we create one pivot table consisting of records that include the fields Last name, First name, Group, Date of birthAndHome address.
Download Microsoft Access.
In the Microsoft Access window that appears, select New database. Click the button .
In the drop-down list Folder window New database file data select your folder. In the typesetting field File name Enter the name of the Student database to be created. Click the button Create.
The database window will open. It is the main window of the database and allows you to open, add and delete any database objects.

Creating a new table structure

In the window that appears Student: Database select object Tables and press the button Create a table in design view.
In the first row in the column Field name enter: Surname. Press the right key →.
Click the cell drop-down arrow that appears Data type and select Text.
At the bottom of the screen, in Field properties, on the tab Are common, in line Field size set to 20.
In line Obligatory field use the drop-down menu button to specify Yes.
Place the cursor on the first row in a column Description. Enter: Student's last name.
In the second row in the column Field name enter: Name. Set the data type to text.
In field Description enter: Student name. Install Field size – 10.
In the third row in the column Field name enter: Group. Install data type – numerical.
In the dropdown list Field size select Whole.
In field Description enter: Group number.
In the fourth row in the column Field name enter: Date of Birth.
Set the data type Date Time. Place the cursor in the typing field Field Format.
In the dropdown list Field Format install – Short date format.
Place the cursor in the field Input mask located at the bottom of the screen and click on the button with three dots. When prompted, confirm saving the table under the name Table 1 and creating a key field.
In the dialog box that appears Creating input masks select Short date format. Click the button Further.
In the next window, you can select a placeholder character to display in the field. Click the button Ready.
Please note that in the first line Microsoft Access has automatically added an additional key field Code, which plays the role of a unique identifier for records, and set it to Data type Counter.
In the sixth line in the column Field name enter: Home address.
Set the data type FieldMEMO. In field Description enter: Home address.
Close the current window Table 1: table with saving changes.

Filling out the table

In the window Student: Database select object Tables, place the cursor on the title Table 1 and press the button Open.
In field Surname enter your last name in the field Name enter your name and in the field Group enter your group number.
Move the cursor to the field Date of Birth and enter your date of birth in the format DD.MM.YYYY, for example 05/12/85. Note: You should enter only numbers, and Microsoft Access will fill in the rest automatically using the specified mask.
Move the cursor to the field Home address and enter your home address.
Enter 7 more entries in the same way. In field Group In any two lines, enter the group number - 1 group, in the rest - 2. If you need to change the column width, this can be done using the mouse, similar to working in Excel.
Please note that in the field Code the numbers change automatically as new lines are entered, increasing by 1 each time.
Click on the close icon for the current window Table 1: table.
In the window Student: Database position mouse cursor on word Table 1 and right-click to bring up the context menu.
Select a team Preview. If necessary, change the scale to more conveniently view the created table using the magnifying glass icon or the scale drop-down list. Close the viewing window.
In the window Student: Database place the cursor on the word again Table 1 and call the context menu.
Select a team Rename. Enter a new table name T_Students.

Laboratory work on Ms Access

Laboratory work No. 1: “Creating a database “Invoices”

Part I. Creating a database.

Progress

1) Start the MS Access 2007 user DBMS by running the command: StartProgramsMicrosoft OfficeMicrosoft Office Access 2007.

2) Select New Database.

3) Give the new database the name “Invoices” and create it.

4) Open the table that appears in Design mode; to do this, on the “Home” tab, select the desired mode.

5) Fill out the form that opens by entering field names, selecting field types from the drop-down list, and specifying field sizes.

$ - key field in the table

6) Define table keys by selecting the key field(s) and clicking on the “Key Field” button on the “Design” tab. Close the form and name the table “Recipients.”

7) Run the command: CreateTable Designer.

8) Create three more database tables: “Product”, “Invoices”, “Issued”.

Table "Product"

Table"Invoices"

Table"Let go"

Note:

9) To edit the desired table, you need to select it in the list of tables and select the “Design” mode.

10) Run the command: Working with DatabasesData Schema.

11) Add all the Invoices database tables.

12) To establish a connection between two tables: place the cursor on the connection fields in the main table and, holding the left mouse button, drag it to the corresponding connection field in the subordinate table.

13) In the dialog box that opens, check the connection type and check the box.

14) Create all the necessary database connections as shown in the diagram.

Editing the data schema:

To add a table, call the context menu on an empty area and select “Add table”.

To delete a table, select the table and press the Delete button on your keyboard

To delete a connection, highlight the connection and press the Delete button on your keyboard

To change the connection type, select the connection and call the context menu.

When changing the connection type, please note that the type is determined automatically and set

“one-to-one” - if two key fields or unique indexes are linked

“one-to-many” - if one of the fields is a key or a unique index

“many-to-many” - in all other cases.

15) Close and save the data schema.

16) To automate data entry, create drop-down lists for some fields. Create a substitution for the “TIN” field of the “Invoices” table from the “TIN” field of the “Recipients” table. To do this, open the “Invoices” table in Design mode, select the “TIN” field and select the “Substitution” tab. Fill in all the necessary parameters as shown in the figure:

17) Similarly, perform two more substitutions: in the “Number” field of the “Issued” table from the “Number” field of the “Invoices” table; in the “Item” field of the “Issued” table from the “Item” field of the “Item” table.

18) Save your changes.

Part II. Creating forms and filling out the database.

Progress

1) Select the “Recipients” table and run the command: CreateMultiple elements.

2) A new form for entering and editing data will appear in Design mode. Close and save it as “Recipients.”

3) Open a new form in Form view.

4) Enter recipient information.

5) Also create a form for the Products table.

6) Now let’s create the “Invoice” form, which will have a more complex structure. The user is used to dealing with paper documents rather than spreadsheets. Therefore, the developer is obliged to bring the screen view when entering information closer to a paper document. For this purpose, use forms that are more convenient to create with the Wizard and edit with the Constructor.

7) Execute the command: CreateOther FormsForm Wizard.

8) Select the tables and fields in the order they appear on the paper invoice:

9) Specify the required table (or query) and the list of required fields, answer the Wizard’s questions.

Number, date, TIN from the table Invoice

Recipient, address from table Recipient

Product, quantity from the table Released

Unit measurements, price from table Product

10) After clicking the "Next" button, select the "Invoice" view type and the subforms.

11) Set the appearance of the subform − tabular.

12) Style – Standard.

13) Using the constructor, bring the form to the desired form.

Part III. Data processing. Creation of queries and reports.

Progress

1) Run the command: CreateQuery Builder.

2) Add all the necessary tables and queries to the query data schema. Establish connections between them if necessary. Drag and drop the required fields from the tables into the request form (lower part of the window). Create additional conditions. Save your request. To view the query result, open it. As a result of opening a query, only those records that meet the specified condition are displayed on the screen.

3) Run fetch queries.

Query 1. Select goods measured in pieces.

Request 2. List of invoices issued in 2009.

Query 3. List of Moscow companies that bought sour cream. The request is based on three tables: Recipients, Issued, Invoices.

Query 4. List of goods included in the invoice with a number entered from the keyboard.

4) Run a query with calculated fields. To create a new field, the value of which is calculated using a formula, you need to build an expression in the start column of the request form using the context menu or entering the formula from the keyboard.

5) Create summary queries that calculate the maximum and minimum value of a field across all selected records, the sum of a field across all records, the number of selected records, etc. To create summary queries, use the Group Operations button on the toolbar.

Request 6. Number of Moscow companies

Request7. List of all invoices with total invoice amounts. The request is based on three tables: Product, Issued, Invoices.

Part IV. Outputting information, creating reports.

Progress

1) It is better to create reports using the Wizard, and then, if necessary, edit the report using the Designer. Data for the report is requested in the Wizard dialog and can be obtained both from tables and from queries. The data in the report can be grouped, sorted, and individual fields can be summarized.

2) Create a report containing a list of invoices with totals.

3) Select Query 7 and launch the Report Wizard.

4) Using the Wizard, select all fields of Request 7 and then set all the necessary parameters.

5) Edit the created report in the Constructor, correcting the title to “List of invoices”.