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. 1.

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.

Ministry of Education and Science of the Russian Federation

Federal State Budgetary Educational Institution of Higher

vocational education

"Khabarovsk State Academy of Economics and Law"

Basics of working with subd Microsoft Access 2007

Khabarovsk 2011

Basics of working with DBMS Microsoft Access 2007: guidelines for performing laboratory work for 1st year bachelors of all areas of full-time education / comp. L. V. Samoilova. - Khabarovsk: RIC KhGAEP, 2011. - 32 p.

Reviewer D. V. Timoshenko, Ph.D. tech. Sci., Associate Professor, Department of ICE, PNU

Approved by the publishing and library council of the academy as guidelines for 1st year bachelors of all specialties of full-time education

Lyudmila Viktorovna Samoilova Fundamentals of working with subd Microsoft Access 2007

Guidelines for performing laboratory work for 1st year bachelors of all areas of full-time education

Editor G.S. Odintsova

_____________________________________________________________

Signed for printing Format 60x84/16.

Writing paper. Digital printing. R.l. 1.9. Uch.-ed.l. 1.3.

Circulation 100 copies. Order No. ___________________

_______________________________________________________________

680042, Khabarovsk, st. Pacific, 134, hgaep, ritz

© Khabarovsk State Academy of Economics and Law, 2011

Basic concepts

In the modern world, a person has to deal with huge arrays of homogeneous information. This information must be ordered in some way, processed by the same type of methods, and as a result, summarized data or searched for specific information in the mass. Databases serve this purpose.

Under database it is customary to understand a set of logically organized and interconnected data shared by various tasks within a single automated information system.

The software that performs operations on databases is called a DBMS - database management system. DBMS allows you to structure, systematize and organize data for their computer storage and processing.

DBMS- a set of language and software tools designed to create, maintain and share a database with many users.

Program Microsoft Access 2007 is a database management system. It is part of Microsoft Office Professional 2007, which ensures its connection with other office applications (Word test editor, Excel spreadsheet program). Using the Microsoft Access DBMS, you can easily store and process large amounts of information, control the correctness of data at the stage of their entry, extract the necessary information from the database, prepare reports, and create forms for more convenient work with data. Several users can work with the database at the same time. Microsoft Access is powerful, yet it takes just a few simple steps to get started and create your own database.

The Microsoft Access program is a relational DBMS (from the English relation - relation). This means that a database in Access is made up of interrelated tables.

A database table is a regular table made up of rows and columns.

The columns of the table are called fields (attributes). They store the attributes of an object. Each table field has a unique name and contains a strictly defined data type.

The table rows are called records (tuples). The record contains several table cells that store certain information about the objects. Each entry contains information about one object. The lines are in random order and do not have numbers. The search for strings is performed not by numbers, but by identifiers ( keys).

Key is the field by which the tables are linked.

The key can be simple or compound. A key defined by one field of a table is called simple. If a key consists of two or more attributes, it is called composite.

The key can be primary or foreign. primary key uniquely identifies each entry in the table; Duplicate key values ​​are not allowed. This means, the primary key must define a single record (row) in the table, i.e. be unique.

External key is an attribute of one relation that is the primary key of another relation. Foreign keys are used to organize relationships between database tables (master and slave) and to maintain data referential integrity constraints.

To fill tables with information, you can enter data manually in the table editing mode, create form to enter data or import data from external sources. To search, select, sort data, you can create requests, and for visual presentation of data and printing - reports.

Microsoft Access, issued in 2013 year is a program that is part of the Microsoft Office package and allows you to manage, modify, and also create individual databases. Due to its versatility and simple, intuitive interface, even an inexperienced user will be able to master the program in a short time.

Also, in case of questions or technical problems, it comes with a complete guide and a built-in animated character that acts as a quick assistant. Microsoft Access 2013 includes free templates for creating a simple repository of information. Due to the maximum automation, the user does not need to know the VBA language to work with this program.

Access 2013 in Russian free download:

Version Microsoft Access sample 2013 year differs from its previous versions in an improved appearance, as well as the ability to edit the ribbon menu. Also, written add-ons can be added to the Office Store, and the distribution of the product can be both free and for money. In addition, the speed of importing from earlier versions of MS Access has been accelerated.

How to install Access 2013

Click start installation

Click Setting.

Select component Access And facilities, click install.

If the problem is with the download:

Features and Specifications of Microsoft Access 2013

The program is designed to store, modify and maintain the integrity of data stored in the database, as well as to perform various operations on individual groups of records. The main components are:

• builders of tables, screen forms, as well as reports that are printed;
• query builder

This environment is capable of generating scripts in the VBA language, which allows you to literally re-write your own database, as well as additional software, if necessary.

When creating a new, empty database, you must choose one of four interaction options and the structure according to which the information will be stored:

1. Tables are the main way to store data. They are almost identical to Excel, which makes importing from such programs an easier task.
2. Forms allow you to enter the necessary data directly into the database, unlike tables, forms provide a more visual input of the necessary information.
3. Reports are mainly used for subsequent printing, they are able to calculate all the data in the database and show a generalized result, how much profit was received, for example.
4. With the help of queries, you can enter, sort and change the information stored in tables. They are also used to search for specific records.

Terms of work with the program

Access rights in this database are divided into three levels: guest (can only view tables), author (can make changes to form data), developer (it is possible to change tables). The updated SQL server allows you to significantly increase productivity. And the use of programs such as SharePoint makes it possible to create a stable database on the selected web site.

MOSCOW STATE UNIVERSITY OF INSTRUMENT MAKING AND INFORMATION

COURSE WORK

according to dissAndpline:

"INFORMATION SYSTEMS IN THE ECONOMY"

On the topic of: “ MicrosoftAccess”

I've done the work

Evening student

3 courses of specialty 080105

Group EF4-0515v

Zhabina I.A.

Checked work:

Shevereva E.A.

Moscow 2008

Introduction 3

1. Description of the Microsoft Access 4 base

2. Working with tables 10

3. Analyze table content to create links 13

4. Creating and printing reports 14

5. Selecting and sorting records using queries 17

6. Macros 20

Conclusion 22

List of sources used 23

Introduction

The ACCESS program is included in the package of the most common Microsoft office software package. This program is universal in its own way. At the same time, the program is easy to use and accessible to the average user. And the interface is responsible for all this. In the process of improving this program, unique features were introduced. The data can be presented in the form of tables or charts. And if we take into account that this program can be used by any user (from a beginner to a professional developer), then without a doubt it can be argued that Microsoft's ACCESS is the best tool for solving problems of any complexity.

The Microsoft Access database management system is one of the most popular applications in the desktop DBMS family. All versions of Access have in their arsenal tools that greatly simplify the input and processing of data, data search and providing information in the form of tables, graphs and reports. Starting with Access 2000, there are also data access Web pages that a user can view using Internet Explorer. In addition, Access allows you to use spreadsheets and tables from other desktop and server databases to store the information your application needs. By attaching external tables, an Access user will work with the databases in those tables as if they were Access tables. At the same time, other users can continue to work with this data in the environment in which it was created.

1. Description of the Microsoft Access database

An Access database is a file that has the mdb extension. This file can contain not only all tables, but also other objects of Access applications - queries, forms, reports, data access pages, macros and modules.

One of the main tasks of creating and using databases is to provide users with the necessary information based on existing data. In Access, forms and reports are designed for this purpose.

When you start Access, the main Microsoft Access window appears.

To open an existing database, you can use one of three methods. Select the required file from the File menu from the list of previously opened files.

If there is no such list in the File menu, you need to use the Tools, Options command to open the Options dialog box, open the General tab and check the box Remember file list (Recently used file list). Select a file from the list in the task area, which is located on the right side of the application window. Select the command Open (Open) in the menu File (File), and then select the desired file in the dialog box Open database file (Open).

In the latter case, the dialog box usually displays the contents of the My Documents or Personal folder by default (depending on the operating system installed on the computer). Having found the required database in the list, you need to select the file and click the Open button or double-click on the list item. The selected database window appears in the main Microsoft Access window.

In the new version of Access, you can open database files created in Access 2000 and work with them in the same way as before.

Files can be selected not only from the list that appears in the Open dialog box for a database file (Open) when it is opened. You can select the desired folder either from the Look in drop-down list, or use the shortcuts located on the so-called address bar on the left. When you select the shortcut History (History), a list of shortcuts to the last opened files and folders appears. When you select the Desktop shortcut, a dialog box displays a list containing folder shortcuts and files currently on the Windows desktop. The file type is selected from the File of type drop-down list at the bottom of the window. In the Favorites folder, you can view shortcuts to those folders and files that are used most often. Many programs allow you to add shortcuts to the Favorites folder, including this can be done directly in the Open database file window. To do this, select the desired file in the list, click the Tools button on the toolbar at the top of the window, and select the Add to Favorites command from the list.

You can open a database file located on a network drive. To map a network drive, you need to execute the Map Network Drive command from the list of commands on the Tools button.

If you cannot find the required database file, you can search for it by specifying search criteria in a special dialog box that appears when you click the Tools button and select the Search command from the list.

A special window in Access is the database window, which allows you to access all database objects and select the mode of working with the object. In the left part of the window there is an object panel that contains shortcuts for each of the Access objects: Tables (Tables), Queries (Queries), Forms (Forms), Reports (Reports), Pages (Pages), Macros (Macros), Modules (Modules ).

By clicking on the shortcut with the mouse, you will open a list of corresponding objects in the right part of the window. The list of objects can be represented in four different ways (common for folders in the Windows operating system): - as small icons;

In the form of large icons;

in the form of a list;

In the form of a table.

Switching between these display modes is done using the four right buttons on the toolbar along the top edge of the window.

When objects are represented as icons, these icons can be dragged with the mouse and positioned within the database window in any convenient way. If you want to arrange the icons so that they are next to each other, you need to right-click on any free area of ​​the database window. Select Line Up Icons from the context menu.

The list view of the objects does not allow icons to be placed arbitrarily in the database window, but they can be "dragged" outside the database window (this is one way to activate an object, such as opening a table). The table view allows you to see for each object not only its name, but also its description (Description column), date and time of last modification (Modified date column), creation date and time (Created column). ), as well as the type of the object. When using any kind of representation of objects in the database window, they can be sorted by name, type, creation date, and modification date. To do this, right-click on any free area of ​​the database window. From the context menu, select the Arrange Icons command. From the drop-down menu, select the ordering method: by name (By Name), by type (By Type), by creation date (By Created), by modification date (By Modified).

In order for the objects in the database window to be ordered automatically, you must

right-click on any free area of ​​the database window. From the context menu, select the Arrange Icons command. In the drop-down menu, select the command automatically (Auto Arrange). A check mark will appear in front of the command to indicate that auto-arranging is enabled.

To cancel automatic ordering, select the Auto Arrange command again from the Arrange Icons menu. The command label will be removed and the auto-order mode will be turned off.

You can work with each database object in two modes. Let's call the first mode the execution mode (conditionally, since it differs somewhat for different types of objects):

for tables, queries, forms and data access pages, this mode means opening the corresponding object and is called, respectively, the Tables mode (for tables and queries), the Forms mode, the Pages mode;

for a report, this is the preview mode;

for a macro, this is really the execution mode;

this mode is disabled for the module.

The second mode is the Design mode. This mode applies to all types of objects and is intended for creating and modifying objects.

You can select the desired mode using the buttons on the left side of the database window toolbar, using the View menu commands, or using the buttons on the Access toolbar.

There are three main buttons on the toolbar of the Database window on the left: the first button has a changing name, depending on which object is selected. If a table, query, form, page, or Favorites group is selected, the button changes to Open. If a report is selected - Preview, if a macro or module - Run. The icon next to the name changes accordingly. The name of this button in each option clearly reflects the purpose. The second button Design (Design) is constant for all objects and is intended for editing a previously created object. The third button Create (New) has a constant name, but the icons on it change, in accordance with the type of object.

To create new objects, you can also use special shortcuts included in the list of objects of each type.

You can use standard copy/paste programs to copy or move an object. For example, to copy a table to the Clipboard, you need to do one of two things: select the required table in the list and click the Copy button on the standard Database toolbar. This panel is displayed on the screen when the database window is active.

To view the properties of an object, you must do one of the following: right-click on the object name and select the Properties command from the context menu; select an object from the list in the database window and select the View, Properties command from the main Access menu.

It reflects the following information:

Type (Type) - object type (in this case, Table (Table));

Description - description of the table, defined by the user;

Created - the date the table was created;

Modified - the date the table was last modified;

Owner - the owner (creator) of the table;

Attributes: Hidden - allows you to hide the table from the database window, Replicated - allows you to control the object's replication (see section "Database replication" chap. 15).

The user can only change the description of the table and the values ​​of its attributes in the properties window.

On the object panel, you can also place folders that contain shortcuts to various database objects. Thus, it is possible to combine different types of objects into groups. By default, this part of the object panel contains one folder - Favorites. By clicking on a folder, you can see a list of objects included in this group.

To add a new folder to the object panel, you need to: right-click on the object panel and select the New Group command from the context menu. In the New Group dialog box, enter a name for the folder to be created and click OK.

The easiest way to add an object label to a group is as follows. It is necessary to expand the list of objects of this type, find the required object in it and drag it with the mouse to the appropriate folder on the object panel. Another way to add an object to a group is to open the list of objects of the desired type in the database window. Right-click on the desired object and select the Add to Group command from the context menu. Select the desired folder from the drop-down menu or create a new folder using the New Group command.

Just like individual database objects, groups can be deleted and renamed. Labels in the group can also be deleted, renamed, copied. This is done using the appropriate commands of the context menu, which appears when you right-click on the object that you want to delete, rename or copy.

When working with any data processing application, the question is always relevant, how to use the data that has already been accumulated before by other software and, therefore, has a different format. Access 2002 solves this problem in a standard way by importing an existing database table, spreadsheet worksheet, or text file created by MS-DOS or Windows applications into the Access Internal Database Format (MDB). Naturally, Access 2002 can also export data from database tables in the MDB format to any format from which data can be imported. However, Access is unique in this sense because it has another way of using data that is stored in other formats. The system allows you to attach tables from databases of other formats to an Access database and work with them in their original format. After creating a database connection with an external table, you can view the attached table, change its contents, that is, work with it as with an internal table in the Access database. However, other users can use the table file in their applications.

In addition to database files, Access can work directly with spreadsheet files, text files, HTML documents, address books, or import data from these files and XML documents.

File types from which data can be imported into an Access database or that can be linked to an Access database. You can see them if on the File menu, choose the command External data, Import (Get External Data, Import), and then click on the extension of the Files of type field in the Import dialog box. Formats to which data can be exported from an Access database. You can see them if you select the Export command from the File menu and then click on the extension of the Files of type field.

2. Working with tables

Tables are the main object of Access. The list of tables that make up the application's database appears in the database window when you first open the application. In addition, Access creates system tables that store information about all objects in the application, and these tables can also be displayed in the database window if necessary.

There are four modes of working with tables in the new version of Microsoft Access: Datasheet View, Design View, PivotTable View and PivotChart View.

In the Table mode, you work with the data in the table: viewing, editing, adding, sorting, etc. In the Design mode, the structure of the table is created or modified, i.e., the names of the table fields and their types are set, the fields are described, their properties. The PivotTable and PivotChart modes are useful for analyzing data by dynamically changing how it is presented. There is also an additional mode - the Preview mode, which allows you to see the location of the data on the sheet before printing the table. To quickly switch from one mode to another, use the View button on the toolbars Table in table mode (Table Datasheet), Table Design (Table Design), PivotTable (PivotTable) and PivotChart (PivotChart). To switch from mode to mode, just press this button.

There are several ways to open a table in Datasheet view:

· double-click on the table name in the list of tables in the database window;

· select a table in the list of tables in the database window and click the Open button in the upper part of the database window;

· Right-click on the table name and select the Open command from the context menu.

At the top of the table are the names of the fields (cells located in one column of the table), below are the records (rows of the table) in which data is entered. One record is always the current one, and next to it there is a pointer to the current record (the arrow in the selection field in the left part of the window). In the lower part of the window there are navigation buttons that allow you to move the pointer of the current record in the table (to the first record, to the previous record, to the next record, to the last record). There is also a field for the number of the current record, a button for creating a new record, and an indicator of the total number of records in the table. The last row of the table, marked with an asterisk in the selection field, also serves to create a new record.

The horizontal scroll bar of the table fields allows you to see those table fields that do not fit in the table window. Similarly, the vertical scroll bar of the table entries allows you to see the entries that are outside the window.

Rice. 1.1. Create a table in design view

Fig.1.2. Table Phones

3. Analysis of the contents of tables to create relationships

The analyzer identifies duplicate data and helps you copy it into a new linked table. Not every table will need to be modified, but it never hurts to analyze once again. In addition, users who are not yet confident in their knowledge and abilities can trust the analyzer to create ordered tables. To parse a table, follow these steps:

1. Select the command Tools>Analysis>Table.

2. The first window of the wizard contains options for describing problems associated with possible data duplication. Review the examples provided there and click the Next button.

3. In the next window, examples of splitting by the table analyzer are given. Review an example and click the Next button.

4. Select the table to analyze and click the Next button.

5. Of course, I would like the master to do the lion's share of the work on his own. Therefore, let's let him choose the fields that will be transferred to the new table by activating the Yes check box, field separation is done by the wizard. Click the Next button.

6. In the next window, the table partition scheme proposed by the wizard is displayed. At the moment the tables are not linked.

7. It would be possible to complete the task, but it makes sense to continue to get acquainted with the analyzer. The database has a normal structure - only the relationship between two tables is missing.

8. Open the Tables window and click the Rename Table button in it. Enter a name and click the OK button.

9. Rename the Table window, then click the Next button.

10. Now it is possible to set the primary key again. The wizard prompts you to add an automatically assigned unique record ID field to the table and use it as the primary key. However, you don't specify a primary key for the table, so you'll have to do it yourself. Select the Name field from the list and click the Key field button. A key icon will appear next to the field. Click the Next button.

11. In the last window, the wizard will offer to create a request. You must select the No, you do not need to create a query check box. You can also uncheck Show help for working with a new table or query, otherwise you will have to close another window. Click on the Done button.

Rice. 1.3. Forms (master and slave)

4. Create and print reports

One of the main tasks of creating and using databases is to provide users with the necessary information based on existing data. In Access 2002, forms and reports are designed for this purpose. Reports allow you to select the information required by the user from the database and arrange it in the form of documents that can be viewed and printed. The data source for a report can be a table or a query. In addition to data obtained from tables, the report can display values ​​calculated from the source data, such as totals.

Access reports and forms have a lot in common. However, unlike forms, reports are not intended for entering and editing data in tables. They only allow you to view and print data. In a report, it is not possible to change the source data using controls, as it can be done using forms. Although reports can use the same controls to indicate the state of radio buttons, check boxes, and list boxes.

A report, like a form, can be created using a wizard. Report sections are similar to form sections and include a report header and note, a data area, and a header and footer. In a report note, fields with totals are often placed. Controls can be added to a report using the Toolbox toolbar, which is identical to the one used in Form Design mode. Formatting and grouping controls on a report is similar to formatting and grouping controls on a form. Forms can contain subforms and reports can contain subreports.

Access offers several ways to create reports. The simplest of these is the use of automatic report generation tools. A report that is automatically generated from a table or query is called an auto report. Access allows you to automatically generate reports in two formats: columnar and ribbon.

To create an auto report:

· In the object panel of the Database window, click the Reports tab and click the New button. The New Report dialog box will appear.

· In the list of the New Report dialog box, select one of the items: AutoReport: To Column (AutoReport: Columnar) or AutoReport: Tape (AutoReport: Tabular).

· The combo box at the bottom of the New Report dialog box contains the names of all tables and database queries that can be used as a data source for a report. Click the left mouse button on the arrow button to open the list, and then highlight the required item in the list.

· Click OK.

As a result, Access automatically generates a report based on the selected data source using the tape or column format. The tape format lays out the fields of the output records in a row. The to-column format arranges the fields of the output records into a column. An auto report created using either of these two formats will include all the fields and records available in the selected data source.

In order for the created report to be used in the future, it must be saved. To do this, select the command File, Save (File, Save) or click the button Save (Save) on the toolbar. Then, in the text field of the Save As dialog box that appears, enter a name for the new report (for example: My Report) and click OK.

There is another option for saving the report: using the menu command File, Save As (File, Save As). This command displays the Save As dialog box. Enter a name for the report, and before you click OK, make sure Report is selected from the As drop-down list in this window. The item you select determines how the new report will be saved, more specifically, as which Access database object. The fact is that in the new version of Access 2002, it became possible to save a report as another database object - a data access page. Another element of the As drop-down list allows you to do this - the Data Access Page element.

Rice. 1.4. An example of building a report

5. Selecting and sorting records using queries

One of the seven standard Microsoft Access objects is a query. Queries are used to view, analyze, and modify data in one or more tables. For example, you can use a query to display data from one or more tables and sort them in a certain order, perform calculations on a group of records, select from a table according to certain conditions. Queries can serve as a data source for Microsoft Access forms and reports. The query itself does not contain data, but allows you to select data from tables and perform a series of operations on them. There are several types of queries in Microsoft Access: queries to the server, which are used to retrieve data from the server; auto-populate queries that automatically populate fields for a new record; select queries that retrieve data from tables; change requests, which make it possible to modify data in tables (including deleting, updating, and adding records); create table queries that create a new table based on data from one or more existing tables, and other types of queries.

The query for a selection contains data selection conditions and returns a selection that meets the specified conditions without changing the returned data. In Microsoft Access, there is also the concept of a filter, which in turn is a set of conditions that allow you to select a subset of records or sort them. The similarity between select queries and filters is that both retrieve a subset of records from the underlying table or query. However, there are differences between them that you need to understand in order to make the right choice of when to use a query and when to use a filter.

The easiest way to create a query is with the help of the Query Wizard. To create a simple query using the Query Wizard, you need to:

· In the database window on the object panel, select the Queries shortcut.

In the list of queries, double-click the Create query by using wizard shortcut or click the New button in the database window and select Simple Query in the New Query dialog box that appears ( Simple Query Wizard) and click OK.

· In the Create Simple Query Wizard window that appears, in the Tables/Queries combo box, select the table or query that will serve as the data source for the query being created.

· Use the left and right arrows to move from the Available Fields list to the Selected Fields list those fields that are required in the query being constructed. In this case, the order of the fields in the query will correspond to the order of the fields in the Selected Fields list. If you want to include all fields in the query, you can use the button with two right arrows.

· The next dialog box will be the last one. In it you need to enter the name of the query you are creating in the field Specify the name of the query (What title do you want to your query?) and select further actions: Open the query to view information or Modify the query design ).

· Optionally, you can select the Show help for working with the query? (Display Help on working with the query) to display help information on working with queries.

· Click the Finish button.

Upon completion of the Simple Query Wizard, depending on the choice of how to further work with the query, either the query window in view mode or the Query Builder window will open, in which you can modify the query.

Fig.1.5. Building a query

6 . Macros

Using macros, you can significantly expand the functionality of the application you create and customize it to the needs of specific users.

With the help of macros, you can perform almost all the actions on Access objects that were described in previous chapters.

A macro in Access is a structure consisting of one or more macro commands that are executed either sequentially or in an order specified by certain conditions. The set of macro commands in Access is very wide; with the help of macros, you can implement much of what VBA procedures allow you to do. Each macro has a specific name and optionally one or more user-specified arguments. For example, when using the OpenForm macro, you must specify at least the name of the form to open and its display mode as arguments.

The use of macros is justified by the fact that they are easy to create, and for this you do not need to learn the syntax of a programming language. To create a macro, you only need to know the basic techniques of working in Microsoft Access and Windows, such as dragging objects from the Database window into a special window - Macro Design, selecting an action from the list, and entering expressions as macro command arguments. Therefore, if you really don’t want to learn the syntax of the VBA language or it seems too difficult, feel free to use macros and you will get a fairly functional application.

The main purpose of macros is to create a convenient application interface: so that forms and reports open when you click buttons in a form or on a toolbar, or by the usual choice of a menu command; so that when opening the application, the user sees on the screen not the Database window (Database), filled with many tables, queries, forms and reports, but some understandable form with which one could immediately perform the desired actions, etc.

Rice. 1.6. Configuring Launch Options

Conclusion

Essentially, Access is just a tool. Its use, of course, makes our work, and therefore our life a little easier. Therefore, we must remember that the database should serve to perform clearly defined tasks - only under this condition will it help to increase the efficiency of work, regardless of what type of activity is involved.

However, the capabilities of the database are not limited to storing information. A professionally designed database allows you to maintain the reliability of the data and provide efficient, fast and convenient access to them. In such a database there will be no room for disorder and confusion.

The basic principle on which relational database systems are based is the creation of relationships between tables. Relationships help you find data from one table using another, and data integrity helps prevent accidental changes or deletions of data.

Designing a database structure can be a major ordeal that many users try to avoid and eventually regret. Only a few, the most talented and gifted people, skip this step and manage to create efficient database applications. Even if the information presented in this chapter does not seem interesting to you, do not forget that the design of the database structure is a very important task.

List of sources used

Alexander Starshinin Microsoft Office at a glance., St. Petersburg, 2007

Ed Bott Microsoft XP., BINOM, Moscow, 2006

In the modern world, tools are needed that would allow storing, organizing and processing large amounts of information that are difficult to work with in Excel or Word.

Such repositories are used to develop information sites, 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 walk through creating a database in Access 2007 step by step.


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 SQL query language. 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 personal non-commercial purposes.

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



Definitions of basic concepts

Without basic knowledge about the controls and objects used to create and configure the database, it is impossible to successfully understand the principle and features of customizing the subject area. Therefore, now I will try to explain in simple terms the essence of all important elements. So, let's begin:

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

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

Database creation

For clarity of the whole theory, let's 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:




Everything, now it remains only to create, fill in and link tables. Move on to the next item.

Creating and populating tables

After successfully creating the database, an empty table will appear on the screen. To form its structure and fill it, 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. For this you need:




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




Executing queries

What to 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, which help to remove only the necessary information.

Request types

The SQL syntax implements the CRUD principle (abbreviated from the English create, read, update, delete - “create, read, update, delete”). Those. With requests, you can implement all of these features.

per sample

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




But what if we are interested in students from Kharkiv who have more than 1000 scholarships? Then our request will look like this:

SELECT * FROM Students WHERE Address = Kharkiv 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 during the performance of laboratory or term papers as part of a university course, because in real life there is no need for this. Unless, of course, you are a professional app developer. So, to create a request, you need:

1. Go to the "Create" tab.
2. Click the "Query Builder" button in the "Others" block.
3. In the new window, click on the SQL button, then enter the command in the text field:

CREATE TABLE Teachers
(TeacherCode INT PRIMARY KEY,
Surname CHAR(20),
Name CHAR (15),
Middle name CHAR (15),
Gender CHAR (1),
date of birth DATE,
main_subject CHAR(200));

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






Attention! At the end of each request, there must be a ";" character. Without it, script execution will result in an error.

To add, delete, edit

Everything is much simpler here. Again go to the field to create a request and enter the following commands:




Form creation

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




We have already considered all the basic functions of MS Access 2007. The last important component remains - report generation.

Report generation

A report is a special function of MS Access that allows you to format and prepare data from a database for printing. This is mainly used to create invoices, 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 "Create" tab.
2. Click the "Report Wizard" button in the "Reports" block.
   
   
3. Select the table of interest and the fields you want to print.
   
   
4. Add the required level of grouping.
   
   
5. Select the sort type for each field.
   
   
6. Customize the layout view for the report.
   
   

   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 populating tables to writing select queries and creating reports. This knowledge is enough to perform simple laboratory work as part of a university program or use it in small personal projects.

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

   Good luck in mastering new material and if you have any questions - you are welcome in the comments!