Creation and filling of databases in Microsoft Access. Basic tasks for an Access desktop database How to work in Access

Lab #3

Topic: Working with Microsoft Access.

Objective: Familiarize yourself with and gain skills in working with Microsoft Access 2010.

Brief information from the theory

The Access application is a program included in the Microsoft Office package and is designed to work with databases.

Database. The term database can be applied to any collection of related information organized according to certain rules, providing general principles for storing, searching and manipulating data that relate to a particular subject area.

Database management system (DBMS)– application software that provides database creation and data organization.

Relational DB. Relational databases store information in one or more tables.

Table is a set of data on a specific topic. The data in the table is arranged in rows (records). Each table row is a set of named fields that can store a variety of information. Fields of the same type in different records form a table column.

Figure 1 - Access program window

Field this is an elementary unit of the logical organization of data, which corresponds to a separate indivisible unit of information - attribute. To each field field name is given - identifier.

Recording is a collection of logically related fields. Each entry must be uniquely identified by a unique record key.

Exercise 1. Creating a database for accounting books in the library.

Follow the action: "Start - All Programs - Microsoft Access". After that, the program window opens, as shown in Figure 1.

Enter the name of the database in the "file name" line. In our case, "Library". Click on the folder icon and select the path to the file "Library" To complete the operation, click the "Create" button. A window appears on the screen (Fig. 2).

Figure 2 - The main window of Microsoft Access

Create a table in Design view.

The main window of Microsoft Access allows you to create tables, queries, forms, reports, pages in design mode and using the wizard.

Click on "Create Table in Design View".

Enter data into the cells, as shown in Figure 3. Design view sets the structure of the table in which the data will be stored.

Field name- the name of the field is given.

Data type– type of data stored in the field (text, numeric, boolean, currency, OLE field, hyperlink, lookup wizard).

Description- describes the type of input data.

Field size– number of symbols allocated for data display.

Figure 3 - Creating a table layout in Design view

Table 1. The structure of the first database table - "Library"

Figure 4 - Creating the structure of the table "Table 1"

Each Microsoft Access table must contain at least 1 key field. To set a key field, right-click on the "name" field and select "key field" from the context menu. To complete the action, click the "Save" button.

Create another table in Design view with the following settings and name it Books. Set the key field "Code".

Table 2. Parameters of the "Books" table

Figure 5-Creating the structure of the "Books" table

Setting up a relationship between tables

Our database structure already has two tables. However, they still exist independently and our goal is to link them. To do this, you need to set the parameters of relationships between tables.

Open the Data Schema window(See Fig. 6). To do this, click the button on the Microsoft Access toolbar or select the menu item Working with the database - Data schema.

The first time you access the "Data Schema" window, you will immediately be prompted for the window for adding new tables. In the future, this window can be called through the context menu of the data schema window, the "Links/Add Table" menu item, or the toolbar button.

Add "books" and "categories" tables to the data schema. Close the window for adding tables.

Figure 6 - Data Schema window

Open the window Changing links for a custom link (Figure 7). This can be done through the link context menu or by selecting the link line with a mouse click and giving the menu command Links - Edit link.

Figure 7 - Changing relationships between tables

Check the box - Ensuring Data Integrity. You can also opt to cascade update and remove related fields, but this is not necessary for our database. Confirm the change of associations (click the "OK" button).

Enabling a checkbox Ensuring Data Integrity allows you to protect against cases of deleting records from one table, in which the data related to them in other fields will be unrelated. For an integrity condition to exist, the base table field must be a key field, and both fields must be of the same type. The checkboxes "cascade update related fields" and "cascade delete related fields" ensure that the data in all subordinate tables is updated or deleted at the same time when they are changed in the main table.

Also note that the ends of the communication line in the data scheme window are marked with "1" and "infinity" after enabling the checkbox to ensure data integrity. This means that only values ​​from the corresponding field of the main table can act as field values ​​from the related table, and each value from the field of the main table can occur many times in the field of the related table (one-to-many relationship).

Save the Data Schema Layout by giving the menu command File - Save or by clicking the button on the toolbar. Close the window Data schema.

Let's start filling our database with a table Books, since the data of this table will be used in the future when filling out the table 1 .

Open the Books table. This can be done by double-clicking on the table icon in the Database window, or by selecting the icon and clicking the button Open specified window. Use the mouse to set the size of the table window, as well as the size of individual fields.

Figure 8 - Filling in the "Books" table

After filling the table with data, close it. You will be prompted to save the table layout. Please note that this is about saving layout(i.e. parameters such as column widths, etc.), and not entered data. The input data is recorded immediately after completion of filling in all the fields of the record (when proceeding to filling in the fields of a new record). If the layout of the table has not changed in the course of work, then no additional questions are asked.

Open table 1, fill it with the data below:

In the "Title" and "Author" columns, you enter the relevant data for the book. In the "Availability" column, you check the box if the book is in the library, and leave it empty if it is in the reader's hands. In the category_code column, you select the genre of the book (a number from 1 to 100).

After filling, save the table. The table can be supplemented and changed as needed.

Create a request

To create a request, use the window shown in Fig. 9. Select the "Queries" tab and the "Create Query in Design View" menu. The following window will appear:

Figure 9 - Creating a request

Add tables "Table 1" and "Books". The linked table schema will appear in the window:

Figure 10 - Creating a query in Datasheets mode

The query table is populated by selecting the required values ​​from the drop-down menu. If the value will be displayed on the screen, the required fields are checked. Save the query, close it, and double click on it and it will open in datasheet view. The appearance is shown in Figure 10.

However, in this form, the query will include all the books included in the database, which is not always convenient.

Similarly, we create a query to retrieve information about the novels in the database.

In the "Category_code" field, we enter the selection condition "Rum", which corresponds to the novel genre. Moreover, the field values ​​are not displayed on the screen. We save the query under the name "novels", close it and open it in table mode.

Thus, the query contains information only about books related to the genre of novels. Similarly, you can make a query about any other genre entered in the "categories" table.

Forms.

You can view, add, edit data in the database if you create a form. An example of a form in Design view is shown in Figure 11.

Figure 11 - Forms window in Design mode

It was created as follows: it opens in the window shown in Figure 2, the “Forms” tab - “Creating a form using the wizard”. We transfer the necessary fields from the "Available fields" window to the "Selected fields" window. In our example, all must be selected.

Report.

If the form window is designed to work on a computer, then the purpose of the report is to generate a document containing all the required data for printing. It is created in the same way as creating a “Form”: “Reports” - “Creating a report using the wizard”. To do this, select the source for generating the report, select the required fields, format it in the desired way and print it out.

Figure 12 – Appearance of the Report

Task 2(creative).

Create a database according to your choice. For each option you need:

Prepare data for tables on your own, describe fields, select data types, set field sizes;

Fill tables with data (8 rows in each table), set key fields;

Create a data link diagram;

Create different queries (two) based on one and two tables;

Create forms (two), edit in design mode;

Create reports (two), edit in design mode;

Create a button form for the database.

Task options

Option 1. Create a database in Access "Sports Stars".

Option 2. Create a database in Access "Household goods"

Option 3. Create a database in Access "Pharmacy".

Option 4. Create a database in Access "Computers".

Option 5. Create a database in Access "Air ticket sales".

Option 6. Create a database in Access "Variety Stars".

Option 7. Create a database in Access "Phone directory".

Option 8. Create a database in Access "Cars".

Option 9. Create a database in Access "Travel Agency".

Option 10. Create a database in Access "Human Resources".

Option 11. Create a database in Access "Healthy lifestyle".

Option 12. Create a database in Access "In the world of animals."

test questions

What is a database?

What is a Table?

What are the data types for table fields?

What is a record, field?

What is the key field used for?

Name the main types of DBMS.

What are the main types of data models in the DBMS.

What are forms for?

What are requests for?

How to create a report?

1. Guidelines

   database management Microsoft Access. Skill work in program Microsoft Access are one of... reporting documentation. Study Plan Topics: Baseline control Shape... can be done. Summary Topics Information in databases...

2. A program for recording and analyzing the development of children under the comprehensive educational programs "Childhood"

   Program

   Rights to change information topics users who will work With program. 5. On other computers... with which works program"Kindergarten: Development" is saved in a database file of the format Microsoft Access 2000 ...

3. "Designing and creating a database in ms access"

   Lesson

   ... work with application Microsoft Access; understand the concept of a database and how to create them in Microsoft Access... magazine, announcement of a new Topics and her goals. ... Access included in the package Microsoft office. Start MS Access. Start a Programs à Microsoft Access ...

Any user of Windows-based computer systems knows that the standard Microsoft Office suite of any version includes a unique Access editor. What kind of program is this, how to work with it, now it will be considered. Naturally, only the initial basics will be given here, since a description of absolutely all the features of the application will take more than one page.

What is Access?

What is this program? Access is a full-featured system for working with any type based on a relational model with the ability to dynamically exchange data with other applications or Internet publications. It provides for the use of tools for automating the processing of information of any type, which is presented in a structured form.

In addition, Access is also a package that provides support for ActiveX controls, which significantly expands the program's capabilities in terms of the fact that it can use not only tabular or text components, but also multimedia and objects on the Internet. Relationships established in the application between allow accurate tracking of changes in any of them with automatic adjustment of parameters in others.

The main directions in using the application

It is not surprising that in most cases it is used to fully automate the processes of analyzing some processes in accounting, business, etc. Due to the universal structure, the program can eliminate the appearance of so-called data redundancy when you need to change one parameter not by entering a new one. , but through correcting the old one, and so that its change is reflected in all related databases.

For example, an enterprise uses Access to keep records of suppliers, customers, and the activities in which they are involved. One supplier's bank details change. It is enough to change them, as automatic adjustment will affect all other databases. This will replace the data, not enter new ones along with the existing ones. And this change will affect the same related activities. That is, in a sense, the user receives full automation.

The same applies, for example, When a certain group of goods is sold through the corresponding division of the enterprise, the headings are automatically written off in the database of the goods that are available in the warehouse. But these are the simplest examples. In fact, the application has much more features.

Structure of Microsoft Access

As for the convenience of work, it is achieved due to the presence of the main elements that play a crucial role in the analysis and processing of database data. Among the main elements are the following:

• table - an element that stores basic information in a specific format (numeric, text, graphic, etc.);
• query - a means of accessing related elements, other databases or third-party programs;
• form - presentation of information or data in a user-friendly form;
• report - output of processed results;
• macro - an executable element that allows you to perform certain actions when an event occurs, a request is created, a report is generated;
• module - Visual Basic language tools that allow you to significantly expand the capabilities of the program based on the creation of procedures and the use of numerous functions.

Communication with other programs and external databases

As already clear, Access is a program that allows you not only to use your own data entered by the user, but also to link them together. The capabilities of the application are such that information can be imported from other applications (FoxPro, Paradox, Excel, Word, etc.). To simplify the procedures, the data can not be imported, but linked, and not only with the indicated programs, but also with sources in a network environment or on the Internet.

The binding process itself is performed on the basis of queries, similar to how SQL databases work (Access also supports them).

Create from templates

In Access, the table is the main element. In appearance, this component is very similar to Excel tables, but the possibilities of Access are much wider, and the principles of working with such elements have their own distinctive features.

However, it is quite easy to create your own database when you start the program. After the welcome window appears, the user is given a choice of templates, on the basis of which the future database structure will be created in the form of a table. This is the so-called Backstage view. Here you can find built-in blanks that are useful for performing specific tasks, or refer to the search on the official Microsoft resource if none of them in the list matches the user's needs (although this is unlikely).

Database from scratch

If nothing suits the user and he wants to make a database on his own, when creating a new file in the corresponding menu, you need to select an empty database. Here it is worth considering some limitations. For example, desktop databases do not support web publishing, and web databases are not consistent with some of the features of the previous ones.

Having created the initial table, you can proceed to enter data. Note that data can only be entered in adjacent columns and rows. Also, do not add empty cells between them, the way it is done in Excel. In addition, the most important condition is that each column must contain only one type of data, that is, if the format initially uses date and time, information with exponent-based calculations entered in the column will not be recognized. So, if possible, you need to plan the table in this perspective. To simplify the work, you can use a special mode of the designer.

Nuances of importing and linking data with other sources

As for data import, the program's possibilities are practically unlimited. The main condition is only that the imported data must be divided into tabular types (like tables in Excel or Word). If the import is performed, for example, in a text version from Notepad, you can create a similar structure using the tabulator (Tab key).

You can use SharePoint lists, and you can also link data to make things easier. To do this, use a special command on the external data tab, located in the import and link group. Ready-made solutions are offered here (Excel, Word, etc.). When choosing, it remains only to specify the location of the desired file, the location of saving in the current database and confirm the choice.

Conclusion

This is the Access application. The program is very popular among a wide range of users, since its developers have tried to combine the possibilities of other software products of this type in it as much as possible. And this is what made this application very flexible in customization and automated application of most of the functions. It remains to be added that Access is a very powerful data processing tool, although only the most basic information about the application has been considered here.

application program Microsoft Access 2007 designed to create Database. Access 2007 uses logically related tables. DBMS Microsoft Access 2007 provides convenient and reliable management of data stored in tables.

The essence of creating a database in Microsoft Access 2007 is that you first need to create a database structure (create a table structure and establish relationships between them), and then you need to fill in the tables of the new database.

In addition, it is desirable to create forms to enter data into tables, requests to search for information in the database and reports to display the necessary information from the database in a convenient form on the screen, print or file.

The database can be created either manually (a new empty database) or based on templates. Templates do not always correspond to the required database, but with their help you can quickly create a database close to what is required, and then you can modify it and fill it with your data.

When you launch an Access 2007 application, the screen will display the new "Getting Started with Microsoft Access 2007" start page, shown in Figure 1-1. one.

Rice. 1. New start page

When creating a database based on templates, you can select the required template on the Getting Started with Microsoft Access 2007 page from the Template Categories or the From Microsoft Office Online templates. When you select, for example, the Faculty template in the Local templates category, a description of the database will appear on the right side of the application window. Below, in the text field, the file name is indicated: Faculty.accdb and a folder icon with which you can save the file to the desired directory (the default folder is My Documents).

The file name and directory for storing the file can be changed. Then you need to click on the Create button. As a result, the database window Faculty: database (Access 2007) - Microsoft Access will appear. The Editing Area will display the Faculty List to be completed. If the Navigation Pane is closed, it must be opened by pressing the F11 key or by clicking on the "Open/Close Navigation Pane Border" button.

Microsoft Office is an editor that allows you to create and edit office documents. It also has an embedded application that allows users to work with databases. Databases are, first of all, a convenient tool for storing, organizing and extracting the necessary information. In this material, the Microsoft Access editor will be analyzed in detail and step-by-step instructions for working with the application will be published.

What is Microsoft Access

Microsoft Office Access is a powerful editor, which is a database management system based on the relational model. It should also be noted that it uses a dynamic exchange model between network resources and applications. Wherein editor uses advanced tools for processing any type of information and presenting them in a clear coherent structure.

Access also introduces support for dynamic ActiveX libraries. This technology, which helps not only to present information in the form of text, but also in the form of multimedia objects. The relational model allows you to establish links between databases and control any change, making timely adjustments.

Some users think that one of the Microsoft Office Excel editors is similar to the Access program, but this is a misconception. Excel is a tool for working with spreadsheets, and the latter, in turn, is used to create a database in the form of tables.

The principle of operation of Excel is based on the organization of data within a separate table, Access allows you to perform more complex tasks, working with a large array of information. And in conclusion, it is necessary to note an important feature, Excel is designed for a single user, since the change of information is local in nature, and Access implies multi-user work with databases.

Why is it used

The editor is used to fully automate the work with the database in various fields of activity, business, personnel management, etc. It is worth noting that due to the fact that the software has a universal structure, it helps to get rid of an overabundance of data when you need to change the desired parameter, not by entering a new one, but by adjusting the old one. Moreover, the changes will be reflected not only in the main database, but also in those associated with it.

Application structure

The convenience of working with the program is achieved due to the presence of elements that allow automate the process creating a base. The following is a list of the main components of the software.

Components:

• table. The application component is designed to write and store data;
• request. The element is designed to receive information from one or more tables. It is a means for accessing related databases and third-party software;
• the form. The object is used to present the entered information in a more user-friendly way;
• report. Allows you to get the final result in the form of a finished document;
• macro. It is an element that contains a sequential description for performing an action. With it, you can set a command that will perform a specific task, for example, checking for data changes in one of the tables;
• module. A component that contains software written in the Visual Basic programming language. With its help, the editor significantly expands the functionality. This is achieved through the use of functions and procedures designed to respond to certain changes;
• access page. With its help, you can access remote databases stored on other personal computers.

Relationship with other DBMS

Access allows you not only to use your own entered information, but also to establish a relationship with another DBMS. There is also the ability to import from other applications, such as dBase, MySQL, Paradox, FoxPro, Excel. For the convenience of the user, the possibility is implemented not only to use import, but also bind data with other programs and network resources.

User interface overview

Important! The interface will be reviewed using Microsoft Access 2013 as an example. However, Microsoft Access 2007 and 2010 are almost identical

The interface in this version of the software is designed to improve the convenience of working with numerous elements, it is based on the ribbon, which provides quick access to the main functions and commands.

User interface:

• « Backstage" (create). The interface element appears after the program starts and allows the user to select a template for creating a database. In the process of work, to go to this tab, you must follow the path "File" and "Create".
• « File". Allows you to save, open, print the finished document, as well as set the Access options and set the appropriate design theme.
  
  
• « Ribbon". It is the main element when working with the editor. It contains modules with tools for creating and editing a database. It also includes a Quick Access Toolbar that contains the most commonly used components.
• « Navigation area". Allows you to see the result of the actions performed and reflects the structure of the database.
• « Dialog window". An interface element with which the user can specify the parameters of an object.
  
• ". To display the component, you will need to right-click on the object element. It includes commands that depend on the task being performed.
  
• ". Used to switch the document view mode, and to display the status of the current operation.

The interface was reviewed using Microsoft Access 2013 as an example. It may differ in lower editions.

Working with databases in Access

Base creation

There are several ways to create a database: from scratch or using a ready-made template. In the first case, the user needs to create database elements and objects on his own; in the second case, everything is much simpler. We launch a ready-made template and enter your information. Next, we will consider in detail both options for working with databases.

net base

Let's start creating a new database and then filling it with information:

Create from template

Working with a template looks like this:

Filling the base

Filling the database involves creating a table and entering the necessary information into it.

To begin with, it should be noted that using MS Access, you can to import. To do this, select the "External" tab, click on the "Import" icon. Next, a new window will open where you need to specify the path to the file and click "OK". Then the import window will appear again, click " Further» and set information about each object. We set the key that will help to quickly process the information, and click " Further". After that, the object will appear in the main window and you can start creating it.

Process of creation:

It should be noted that the "Price" field has a numeric parameter. This is due to the fact that information in monetary terms has a dimension expressed in rubles (if the official localized version of the office suite is used). If we are talking about the price of computer peripherals, then, as a rule, conventional units are used to express its comparative cost. For such a case, a numerical parameter is used, this will avoid reconfiguring the entire database.

Project No. 1 "Products":

Project No. 2 "Supply":

Data schemas

The relationship between tables is established using the structure used in the project. In this case, the structure is displayed graphically, which makes it possible to significantly facilitate data processing.

Establishing a relationship between projects:

Now we need establish a connection between two objects, we do it like this:

Creating requests

Classic selection

The selection request allows you to select data in the database according to pre-created conditions. In our project, a selection will be created that allows you to find a product by its name. The work will be done in the database "Products".

Creating an Entity

A Create Entity query in Microsoft Access fetches related tables and other previously created queries. Unlike the first option, the result will be saved in a new permanent table.

The process looks like this:

To add, delete, edit

This type of query implies the creation and execution of some action, as a result of which the parameters in the table will change.

We create a request like this:

Request to add:

For editing:

Creation and design of forms

Forms are one of the elements that are designed to properly organize data storage.

What forms are for:

• The purpose of forms is data output on the screen in a user-friendly way;
• launch control. In this case, the main purpose of forms is to run macros;
• show dialog boxes. With the help of forms, you can display a warning about possible errors.

We use "" to create an object:

After that, a new tab will appear in front of the user, where the table will be presented in the form of a form. As you can see, the perception of information has become much more convenient.

Consider the option of creating forms through the Constructor:

Create a form from scratch with "". With this option, you can customize the appearance, change the fill of the field, add media files, and more.

You can also set the required parameters for the picture: "Background Color", "Background Type", "Borders", etc.

We generate reports

To work with reports, we will use "":

Report using the Designer:

Access desktop databases can help you store and track just about any kind of information, such as inventory, contacts, or business processes. Let’s take a walk through the paths you can take to create an Access desktop database, add data to it, and then learn about next steps towards customizing and using your new database.

In this article

Choose a template

Access templates have built-in tables, queries, forms, and reports that are ready to use. A choice of templates is the first thing you'll notice when you start Access, and you can search online for more templates.

In Access click file > New.

Select a desktop database template and enter a name for your database under File Name. (If you don't seea template that would work for you, usetheSearch online templatesbox.)

File Name

Click Create.

Depending on the template, you might need to do any of the following to get started:

If Access displays a Login dialog box with an empty list of users:

1. Click new user.

   Fill in the User Details form.

   Click Save&Close.

   Select the user name you just entered, and then click Login.

If Access displays a Security Warning message in the message bar, and you trust the source of the template, click Enable content. If the database requires a login, log in again.

For more, see.

Create a database from scratch

If none of the templates fit your needs, you might start with a blank desktop database.

From Access, click New > Blank desktop database.

Type a name for your database in the File Name box.

You can either use the default location that Access shows below the File Name box or click the folder icon to pick one.

Click Create.

Add a table

In a database, your information is stored in multiple related tables. To create a table:

When you open your database for the first time, you'll see a blank table in Datasheet view where you can add data. To add another table, click the Create tab > table. You can either start entering data in the empty field (cell) or paste data from another source like an Excel workbook.

To rename a column (field), double-click the column heading, and then type the new name.

tip: Meaningful names help you know what each field contains without seeing its contents.

Click file > Save.

To add more fields, type in the Click to Add column

To move a column, select it by clicking its column heading, and then drag it to where you want it. You can also select contiguous columns and drag them all to a new location.

Copy and paste data

You can copy and paste data from another program like Excel or Word into an Access table. This works best if the data is separated into columns. If the data is in a word processing program, such as Word, either use tags to separate the columns or convert into a table format before copying.

If the data needs editing, such as separating full names into first and last names, do that first in the source program.

Open the source and copy (Ctrl + C) the data.

Open the Access table where you want to add the data in Datasheet view and paste it (Ctrl + V).

Double-click each column heading and type a meaningful name.

Click file > Save and give your new table a name.

Note: Access sets the data type of each field based on the information you paste into the first row of each column, so make sure that the information in the following rows match the first row.

Import or link to data

You can either import data from other sources , or you can link to the data from Access without moving the information from where it is stored. Linking can be a good option if you have multiple users updating the data and you want to make sure that you are seeing the latest version or if you want to save storage space. You can choose whether you want to link to or import data for most formats. See Import or link to data in another Access database for more information.

The process differs slightly depending on the data source, but these instructions will get you started:

On the external data tab, click the data format you'll be importing from or linking to. If you don't see the right format, click More.

Note: If you still can't find the right format, you might need to export the data first to a file format that Access supports (such as a delimited text file).

Follow the instructions in the Get External Data dialog box.

When you link, some formats are available as read-only. Here are the external sources that you can import data or link from: