Advanced courses on access online. Creating and filling databases in Microsoft Access

IN modern world We need tools that would allow us to store, organize and process large amounts of information that are difficult to work with in Excel or Word.

Such repositories are used to develop information websites, online stores and accounting add-ons. The main tools that implement this approach are MS SQL and MySQL.

The product from Microsoft Office is a simplified version in terms of functionality and is more understandable for inexperienced users. Let's take a step-by-step look at creating a database in Access 2007.

Description of MS Access

Microsoft Access 2007 is a database management system (DBMS) that implements a full-fledged graphical user interface, the principle of creating entities and relationships between them, as well as the structural query language SQL. The only disadvantage of this DBMS is the inability to work on an industrial scale. It is not designed to store huge amounts of data. Therefore MS Access 2007 is used for small projects and for personal non-commercial purposes.

But before showing the step-by-step creation of a database, you need to familiarize yourself with basic concepts from database theory.

Definitions of basic concepts

Without basic knowledge about the controls and objects used when creating and configuring a database, it is impossible to successfully understand the principle and features of setting up a subject area. So now I'll try in simple language explain the essence of everything important elements. So, let's begin:

1. A subject area is a set of created tables in a database that are interconnected using primary and secondary keys.
2. An entity is a separate database table.
3. Attribute – the title of a separate column in the table.
4. A tuple is a string that takes the value of all attributes.
5. A primary key is a unique value (id) that is assigned to each tuple.
6. The secondary key of table "B" is a unique value from table "A" that is used in table "B".
7. An SQL query is a special expression that performs specific action with the database: adding, editing, deleting fields, creating selections.

Now that in general outline If you have an idea of ​​what we will be working with, we can start creating a database.

Creating a database

For clarity of the whole theory, we will create a training database “Students-Exams”, which will contain 2 tables: “Students” and “Exams”. The main key will be the “Record Number” field, because this parameter is unique for each student. The remaining fields are intended for more complete information about students.

So do the following:

That's it, now all that remains is to create, fill and link tables. Continue to the next point.

Creating and populating tables

After successfully creating the database, an empty table will appear on the screen. To form its structure and fill it out, do the following:

Advice! To fine-tune the data format, go to the “Table Mode” tab on the ribbon and pay attention to the “Formatting and Data Type” block. There you can customize the format of the displayed data.

Creating and editing data schemas

Before you start linking two entities, by analogy with the previous paragraph, you need to create and fill out the “Exams” table. It has the following attributes: “Record number”, “Exam1”, “Exam2”, “Exam3”.

To execute queries we need to link our tables. In other words, this is a kind of dependency that is implemented using key fields. To do this you need:

The constructor should automatically create the relationship, depending on the context. If this does not happen, then:

Executing queries

What should we do if we need students who study only in Moscow? Yes, there are only 6 people in our database, but what if there are 6000 of them? Without additional tools it will be difficult to find out.

It is in this situation that SQL queries come to our aid, helping to extract only the necessary information.

Types of requests

SQL syntax implements the CRUD principle (abbreviated from the English create, read, update, delete - “create, read, update, delete”). Those. with queries you can implement all these functions.

For sampling

In this case, the “read” principle comes into play. For example, we need to find all students who study in Kharkov. To do this you need:

What should we do if we are interested in students from Kharkov who have more than 1000 scholarships? Then our query will look like this:

SELECT * FROM Students WHERE Address = “Kharkov” AND Scholarship > 1000;

and the resulting table will look like this:

To create an entity

In addition to adding a table using the built-in constructor, sometimes you may need to perform this operation using an SQL query. In most cases, this is necessary while performing laboratory or coursework as part of a university course, because in real life there is no need for this. Unless, of course, you are engaged in professional application development. So, to create a request you need:

1. Go to the “Creation” tab.
2. Click the “Query Builder” button in the “Other” block.
3. In the new window, click on the SQL button, then enter the command in the text field:

CREATE TABLE Teachers
(Teacher Code INT PRIMARY KEY,
Last name CHAR(20),
Name CHAR(15),
Middle name CHAR (15),
Gender CHAR (1),
Date of birth DATE,
main_subject CHAR(200));

where "CREATE TABLE" means creating the "Teachers" table, and "CHAR", "DATE" and "INT" are the data types for the corresponding values.

Attention! Each request must have a “;” at the end. Without it, running the script will result in an error.

To add, delete, edit

Everything is much simpler here. Go to the Create a Request field again and enter the following commands:

Creating a Form

With a huge number of fields in the table, filling the database becomes difficult. You may accidentally omit a value, enter an incorrect value, or enter a different type. In this situation, forms come to the rescue, with the help of which you can quickly fill out entities, and the likelihood of making a mistake is minimized. This will require the following steps:

We have already covered all the basic functions of MS Access 2007. There is one last important component left – report generation.

Generating a report

A report is a special MS Access function that allows you to format and prepare data from a database for printing. This is mainly used for creating delivery notes, accounting reports and other office documentation.

If you have never encountered such a function, it is recommended to use the built-in “Report Wizard”. To do this, do the following:

1. Go to the "Creation" tab.
2. Click on the “Report Wizard” button in the “Reports” block.
   
   
3. Select the table of interest and the fields you need to print.
   
   
4. Add the required grouping level.
   
   
5. Select the sort type for each field.
   
   

If the display does not suit you, you can adjust it a little. For this:

Conclusion

So, we can say with confidence that we have completely analyzed the creation of a database in MS Access 2007. Now you know all the basic functions of a DBMS: from creating and filling tables to writing query queries and creating reports. This knowledge is enough to perform simple laboratory work as part of a university program or for use in small personal projects.

To design more complex databases, you need to understand object-oriented programming and study DBMSs such as MS SQL and MySQL. And for those who need practice writing queries, I recommend visiting the SQL-EX website, where you will find many practical, entertaining problems.

Good luck in mastering the new material and if you have any questions, please leave a comment!

Introduction

Database management systems (DBMS) are perhaps the most common type of software. DBMSs have more than thirty years of development history while maintaining continuity and sustainable traditions. The ideological value of a DBMS is explained by the fact that programs of this kind are based on the concept of a data model, that is, some abstraction of data representation. In most cases, data is assumed to be in the form of files consisting of records. The structure of all records in the files is the same, and the number of records in the file is variable. The data elements that make up each record are called fields. Since all records have the same fields (with different meanings), fields are convenient to give unique names. Many practically important cases fit well into this representation of data. For example, in the personnel department, information about employees is of this nature. Employees are hired and fired, but the form of the personal personnel record sheet remains the same for each employee. Inventory items come and go, but the form of the inventory card remains the same. The number of examples can be easily multiplied. It is clear that a DBMS is an adequate tool in all cases where the source information can be presented in the form of a table of constant structure, but of indefinite length, or in the form of a card index containing an indefinite number of cards of constant structure.

All DBMSs support in one form or another four basic operations:

add one or more records to the database;

delete one or more records from the database;

find one or more records in the database that satisfy a given condition;

update the value of some fields in the database.

Most DBMSs also support a mechanism for connections between various files included in the database. For example, a connection can be established explicitly when the value of some fields is a link to another file; such DBMSs are called network DBMSs, or a connection can be established implicitly, for example, by the coincidence of field values ​​in different files. Such DBMSs are called relational.

MS Access is a relational type DBMS, which reasonably balances all the tools and capabilities typical of modern DBMSs. A relational database makes it easier to find, analyze, maintain, and protect data because it is stored in one place. Access translated from English means “access”. MS Access is a functionally complete relational DBMS. In addition, MS Access is one of the most powerful, flexible and easy to use DBMS. You can create most applications in it without writing a single line of program, but if you need to create something very complex, then MS Access provides a powerful programming language - Visual Basic Application.

The popularity of the Microsoft Access DBMS is due to the following reasons:

accessibility and clarity allow Access to be one of best systems quick creation database management applications;

The DBMS is completely Russified;

the ability to use OLE technology;

integration with Microsoft Office packages;

support for WWW ideology (Access 97 only);

visual technology allows you to constantly see the results of your actions and correct them; in addition, working with the form designer can significantly facilitate further study of programming systems such as Visual Basic or Delphi;

the help system is widely and clearly presented;

the presence of a large set of “masters” in the development of objects.

There are several ways to start Access:

launch from the main menu in WINDOWS 95/98;

launch using a shortcut on the desktop or MS Office panel;

opening a database automatically launches Access.

If you have questions about Microsoft Access, the first place to try to find answers is in the documentation or help system.

In this work, we offer a set of tasks that will allow you to master the basic techniques of working with the Access DBMS. Help cards can help you when working with the Access DBMS. They are intended for training directly in the process of working with Microsoft Access and, after opening the database, will constantly accompany any user actions.

Lesson 1

Database creation. Entering and formatting data

Download WINDOWS system. Download the ACCESS DBMS. Hint cards will appear. Roll them up. If you need a hint, you can call up the cards at any time in the Help menu or the corresponding button on the toolbar.

First you need to create a new database.

Let's perform the following sequence of actions: in the File menu, select the New command. File name: skaz.mdb. OK. The “Database” dialog box appears in front of you.

Carefully read the purpose of the buttons on the toolbar by slowly moving the mouse cursor over the buttons.

After this, create a table using the following sequence of actions: Table/Create/New Table.

Creating a table, that is, determining the fields included in the table, is done by filling out a special table:

Fill out this table with the following information:

The No. field is optional; we enter it in order to determine the key field, since any table must have a key.

Enter information into the Table/Character/Open table and enter data in the usual way, for example:

Use your mouse to highlight:

a) entry 5,

b) entry 3,

c) from the third to the seventh entry. Deselect it.

d) Select all entries. Deselect it.

e) Select the “Character” field.

f) Select the following fields at the same time: “Profession”, “Special Features” and “Hero”, deselect them.

g) Select all fields. This can be done using the mouse or from the Edit menu, select the Select all records command.

Deselect it.

Highlight:

a) In the “Special Features” field, mark the sixth entry.

b) In the “Character” field, select the fourth to sixth entries.

c) Without releasing the mouse button, mark the same entries in the “Special Features” and “Hero” fields.

Deselect it.

Select the entire table.

Deselect it.

Change the width of each column so that the width of the columns is minimal but all the text is visible.

This can be done using the mouse, expanding the columns, or as follows.

Select the desired column and right-click, in the context menu select the “Column Width” command; In the window that opens, click the Fit to data width button.

Do the same with all fields.

The line height can be changed in the same way using the mouse or in the Format menu with the Line Height command. Moreover, it is enough to edit one line, the height of the remaining lines changes automatically.

Change the line height in any way and make it equal to 30.

Change the table font to Arial Cyr, font size 14, bold.

You can change the font as follows: move the mouse pointer outside the table and click the left mouse button, select Font in the context menu, or select the Font command in the Edit menu on the toolbar.

Change the text font to Times New Roman Cyr, font size 10.

Change the width of the margins.

a) Make the “Character” column 20 wide.

b) The “Special Features” column is 25 wide.

You can see that the text in these fields is printed on two lines.

Adjust the width of the columns so that the text fits completely.

Sort the table by the Character field in reverse alphabetical order.

It can be done like this. Highlight the Character field and click the Sort Descending button on the toolbar.

Return the table to its original state.

Save the "Character" table.

Close the Character table.

Lesson 2

Editing the Database

Open the Character table and add the following entries to the end of the table:

This can be done in three ways:

a) Move the cursor to the end of the table and enter new entries.

b) On the toolbar, click the New Record button.

c) In the Records menu, select the Data Entry command.

Copy the first entry in place of the sixth entry.

Delete the fifth entry.

Copy the first entry to the end of the table.

Change Duremar's profession to leech seller.

This can be done like this: mark the pharmacist entry with the mouse cursor, delete it to the buffer and enter the seller of leeches from the keyboard. Or in the following way: open the Edit menu on the toolbar, select the Replace command... A replacement dialog box will appear on the screen. Enter the replacement format.

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

What is Microsoft Access

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

Access also includes 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 connections between databases and control any change, making timely adjustments.

Some users believe 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 databases in the form of tables.

The operating principle of Excel is based on organizing data within a separate table; Access allows you to do more complex tasks working with a large amount of information. And in conclusion it should be noted important feature, Excel is designed for one user, since changing information is local in nature, and Access implies multi-user work with databases.

Why is it used?

The editor is used to fully automate work with the database in different areas activities, business, personnel management, etc. It is worth noting that due to the fact that 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

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 record and store data;
• request. The element is designed to obtain information from one or more tables. It is a means for accessing related databases and third-party software;
• form. The object is used to represent entered information in a more user-friendly manner;
• report. Allows you to obtain the final result in the form of a finished document;
• macro. It is an element containing a sequential description for performing a particular action. Using it, you can specify a command that will perform a specific task, for example, checking for changes in data 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 its 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, for example, dBase, MySQL, Paradox, FoxPro, Excel. For user convenience, it is possible not only to use import, but also link 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 a ribbon that provides quick access to main functions and commands.

User interface:

• « Backstage" (create). An interface element appears after the program starts and allows the user to select a template for creating a database. During work, to go to this tab, you need to follow the path “File” and “Create”.
• « File" Allows you to save, open, print the finished document, as well as set Access parameters and install suitable topic registration
  
  
• « 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 panel quick access, which 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 clarify 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 presentation 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

Creating a database

You can create a database in several ways: from scratch or using a ready-made template. In the first case, the user needs to independently create elements and database objects; in the second option, everything is much simpler. Let's launch ready-made template and enter your information. Next, we will consider in detail both options for working with databases.

Clean base

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

Create from a template

Working with the template looks like this:

Filling the database

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 import. To do this, select the “External” tab and 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 a key that will help quickly process information and click “ Further" After this, the object will appear in the main window and you can begin 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 package is used). If we are talking about the price of computer peripherals, then, as a rule, to express it comparative cost conventional units are used. 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 the tables is established using the structure used in the project. In this case, the structure is displayed graphically, which makes data processing much easier.

We establish relationships between projects:

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

Creating requests

Classic sampling

A sample query allows you to select data from the database according to pre-created conditions. In our project, a selection will be created that will allow you to find products by their names. We will carry out the work in the “Products” database.

Creating an Entity

A query to create an entity in Microsoft Access selects from 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 as follows:

Request to add:

For editing:

Creation and design of forms

Forms are one of the elements that are designed to proper organization data storage.

What forms are needed for:

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

Use "" to create an object:

After this, 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.

Let's consider the option of creating forms using the Designer:

We create a form from scratch using "". Using this option, you can customize the design, change the fill of the field, add multimedia files, etc.

You can also configure 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 Constructor:

The main purpose of this program is to create and work with databases that can be linked to both small projects and big business. With its help, you will be able to conveniently manage data, edit and store information.

The Microsoft Office suite application – Access – is used to work with databases

Naturally, before you begin, you will need to create or open an existing database.

Open the program and go to the main menu by clicking on the “File” command, and then select “Create”. When creating a new database, you will be presented with a choice empty page, which will have one table or a web database that allows you to use the program’s built-in tools for, for example, your publications on the Internet.

In addition, to make creating a new database as easy as possible, the user is provided with templates to choose from that allow him to create a database focused on a specific task. This, by the way, can help you quickly create the necessary table form without setting everything up manually.

Filling the database with information

Having created the database, you need to fill it with relevant information, the structure of which should be thought out in advance, because the functionality of the program allows you to format data in several forms:

1. Nowadays the most convenient and common type of information structuring is a table. In terms of their capabilities and appearance, tables in Access are not very different from those in Excel, which, in turn, greatly simplifies the transfer of data from one program to another.
2. The second way to enter information is through forms; they are somewhat similar to tables, however, they provide a more visual display of data.
3. To calculate and display information from your database, reports are provided that will allow you to analyze and calculate, for example, your income or the number of contractors with whom you work. They are very flexible and allow you to make any calculations, depending on the entered data.
4. Receiving and sorting new data in the program is carried out through queries. With their help, you can find specific data among several tables, as well as create or update data.

All of the above functions are located in the toolbar, in the “Creation” tab. There you can select which element you want to create, and then, in the “Designer” that opens, customize it for yourself.

Creating a database and importing information

When you create a new database, the only thing you will see is an empty table. You can fill it out manually or fill it out by copying the necessary information from the Internet. Please note that each piece of information you enter must be placed in a separate column, and each entry must have a personal line. By the way, columns can be renamed to better navigate their contents.

If all the information you need is in another program or source, the program allows you to configure the import of data.

All import settings are located in a separate tab in the control panel called “External Data”. Here in the Import and Links area, the available formats are listed, including Excel documents, Access, text and XML files, Internet pages, Outlook folders, etc. Having selected the required format from which information will be transferred, you will need to specify the path to the file location. If it is hosted on a server, the program will require you to enter the server address. As you import, you will encounter various settings that are designed to correctly transfer your data into Access. Follow the program's instructions.

Basic keys and table relationships

When creating a table, the program automatically assigns each record a unique key. By default, it has a column of names, which expands as new data is entered. This column is the primary key. In addition to these primary keys, the database may also contain fields related to information contained in another table.

For example, you have two tables containing related information. For example, they are called “Day” and “Plan”. By selecting the “Monday” field in the first table, you can link it to any field in the “Plan” table and when you hover over one of these fields, you will see information and related cells.

Such relationships will make your database easier to read and will certainly increase its usability and efficiency.

To create a relationship, go to the “Database Tools” tab and in the “Relationships” area, select the “Data Schema” button. In the window that appears, you will see all the databases being processed. Please note that databases must have special fields designated for foreign keys. In our example, if in the second table you want to display the day of the week or a number, leave an empty field, calling it “Day”. Also configure the field format as it should be the same for both tables.

Then, with the two tables open, drag the field you want to link into the specially prepared foreign key field. The “Edit Links” window will appear, in which you will see separately selected fields. To ensure data changes in both related fields and tables, check the box next to “Ensure data integrity.”

Creation and types of requests

A query is an action in a program that allows a user to edit or enter information into a database. In fact, requests are divided into 2 types:

1. Selective queries, thanks to which the program retrieves certain information and makes calculations on it.
2. Action requests that add information to the database or remove it.

By selecting “Query Wizard” in the “Creation” tab, the program will guide you through the process of creating a specific type of request. Follow the instructions.

Queries can greatly help you organize your data and always access specific information.

For example, you can create a custom query based on certain parameters. If you want to see information on a specific date or day of the “Day” table for the entire period of time, you can set up a similar query. Select the “Query Builder” item, and in it the table you need. By default, the query will be selective; this becomes clear if you look at the toolbar with the “Selection” button highlighted there. In order for the program to search for exactly the date or day that you need, find the line “Selection condition” and enter the phrase [what day?] there. Remember, the request must be placed in square arms and end with a question mark or colon.

This is just one use case for queries. In fact, they can also be used to create new tables, select data based on criteria, etc.

Setting up and using forms

Thanks to the use of forms, the user can easily view information for each field and switch between existing records. When entering information for a long time, using forms simplifies working with data.

Open the “Creation” tab and find the “Form” item, clicking on which will display a standard form based on the data in your table. The information fields that appear are subject to all sorts of changes, including height, width, etc. Please note that if there are relationships in the table above, you will see them and can reconfigure them in the same window. At the bottom of the program you will see arrows that will allow you to sequentially open each column of your table or immediately move to the first and last. Now each of them is a separate record, the fields of which you can customize by clicking on the “Add fields” button. The information changed and entered in this way will be displayed in the table and in all tables attached to it. After setting up the form, you need to save it by pressing the key combination “Ctrl+S”.

Creating a report

The main purpose of reports is to provide the user with an overall summary of the table. You can create absolutely any report, depending on the data.

The program allows you to choose the type of report, providing several to choose from:

1. Report - an auto-report will be created using all the information provided in the table, however, the data will not be grouped.
2. A blank report is an unfilled form for which you can select data yourself from the required fields.
3. Report Wizard - will guide you through the process of creating a report and will group and format the data.

In an empty report, you can add, delete or edit fields by filling them in necessary information, create special groups that will help separate certain data from the rest, and much more.

Above are all the basics that will help you cope and customize the Access program for yourself, however, its functionality is quite wide and provides for more fine-tuning of the functions discussed here.