To make informed and effective decisions in production activities, in economic management and in politics, a modern specialist must be able to receive, accumulate, store and process data using computers and communications, presenting the result in the form of visual documents. In modern society information Technology are developing very rapidly, they penetrate into all spheres of human activity.

In different areas of the economy, you often have to work with data from different sources, each of which is associated with a specific type of activity. Some knowledge and organizational skills are required to coordinate all of this data.

Microsoft's product Access combines information from different sources in a single relational database. It creates forms, queries, and reports that allow you to quickly and efficiently update data, get answers to questions, search for the data you need, analyze the data, and print reports, charts, and mailing labels.

The purpose of this course work is to consider design in theory and the creation in practice of a database in a Microsoft product for database management Microsoft Access».

Access System is an end-user toolbox for managing databases. It includes designers for tables, forms, queries and reports. This system can also be viewed as an application development environment. By using macros or modules to automate problem solving, you can create user-centric applications as powerful as applications written directly in programming languages. However, they will include buttons, menus and dialog boxes... By programming in VBA, you can create programs as powerful as Access itself.

Creating applications without programming using access macros... Spreadsheet and database users should be familiar with many of the key concepts used in Access. Before starting to work with any software product, it is important to understand its capabilities and the types of tasks for which it is intended. Microsoft Access (hereinafter simply Access) is a multifaceted product, the use of which is limited only by the user's imagination.

Access fully implements relational database management. The system supports primary and foreign keys and ensures data integrity at the core level (which prevents incompatible update or delete operations). In addition, tables in Access are equipped with data validation tools to prevent incorrect input regardless of how it is performed, and each table field has its own format and standard descriptions, which greatly simplifies data entry. Access supports all required field types, including text, numeric, counter, currency, date / time, MEMO, boolean, hyperlink, and OLE object fields. If no values \u200b\u200bare found in the fields during special processing, the system provides full support for null values.

Basic concepts of MS Access databases

1.1 Summary of MSAccess

Microsoft Access is a desktop DBMS (database management system) of a relational type. The advantage of Access is that it has a very simple graphical interface that allows you not only to create your own database, but also to develop applications using built-in tools.

Unlike other desktop DBMSs, Access stores all data in a single file, although it distributes them across different tables, as a relational DBMS should. This data includes not only the information in the tables, but also other database objects that will be described below.

To perform almost all basic operations, Access offers a large number of Wizards that do the bulk of the work for the user when working with data and developing applications, help to avoid routine actions and facilitate the work of an inexperienced user in programming.

Features of MS Access that differ from the idea of \u200b\u200ban "ideal" relational DBMS.

Creation of a multiuser Access database and simultaneous access of several users to a common database is possible in a local peer-to-peer network or in a network with a file server. The network provides hardware and software support for data exchange between computers. Access monitors the differentiation of access of different users to the database and ensures data protection. With simultaneous work. Since Access is not a client-server DBMS, its capabilities to provide multi-user work are somewhat limited. Usually, to access data over the network from several workstations, the Access database file (with the * .mdb extension) is uploaded to the file server. At the same time, data processing is carried out mainly on the client - where the application is launched, due to the principles of organizing file DBMS. This factor limits the use of Access to ensure the work of many users (more than 15-20) and with a large amount of data in tables, since the load on the network increases many times over.

In terms of maintaining integrity access data meets only small and medium complexity database models. It lacks tools such as triggers and stored procedures, which forces developers to leave the maintenance of the business logic of the database to the client program.

With regard to information protection and access control, Access does not have reliable standard tools. Standard protection methods include database password protection and user password protection. Removing such protection is not difficult for a specialist.

However, with the known disadvantages, MSAccess has many advantages over systems of this class.

First of all, we can note the prevalence, which is due to the fact that Access is a product of Microsoft, software and which operating systems are used by the majority of personal computer users. MSAccess is fully compatible with operating system Windows, constantly updated by the manufacturer, supports many languages.

Overall, MSAccess provides a large number of features at a relatively low cost. It is also necessary to note the focus on the user with different professional training, which is expressed in the presence of a large number of auxiliary tools (Masters, as already noted), a developed help system and an intuitive interface. These tools make it easier to design, create a database and retrieve data from it.

MSAccess provides the non-programmer with a variety of dialog tools that allow him to create applications without resorting to developing queries in SQL or programming macros or modules in VBA.

Access has extensive capabilities for importing / exporting data in various formats, from Excel tables and text files, to almost any server-side DBMS through the ODBC mechanism.

Another important advantage of MSAccess is its advanced built-in application development tools. Most applications distributed to users contain some amount of VBA (Visual Basic for Applications) code. Since VBA is the only way to accomplish many common tasks in Access (working with variables, building SQL commands while the program is running, handling errors, using windows API, etc.), to create more or less complex applications, you need his knowledge and knowledge of the MSAccess object model.

One of the programming tools in Access is the macro language. Programs created in this language are called macros and make it easy to link individual actions implemented using forms, requests, reports. Macros are controlled by events that are triggered by user actions during interactive work with data through forms or system events.

It turns out that Access, having all the features of a DBMS, provides and additional features... It is not only a flexible and easy-to-use database management system, but also a system for developing database-driven applications.

1.2 Databases and database management systems

A database is an organized structure for storing information. Modern databases store not only data, but also information.

This statement is easy to clarify if, for example, consider the library database. It contains all the necessary information about authors, books, readers, etc. Both library staff and readers who need to find a publication can have access to this database. But among them there is hardly a person who has access to the entire database completely and at the same time is capable of single-handedly making arbitrary changes to it. In addition to data, the database contains methods and tools that allow each employee to operate only with those data that are within his competence. As a result of the interaction of the data contained in the database with the methods available to specific employees, information is formed that they consume and on the basis of which, within their own competence, they enter and edit data.

Access 2010 is a database creation and management program. To understand Access, you must first understand databases.

In this article, you will learn about databases and how they are used. You will learn about the differences between data management in Access and Microsoft Excel.

What is a database?

A database is a collection of data that is stored in a computer system. Databases allow their users to quickly and easily enter, access and analyze their data. They are such a useful tool that you see them all the time. Have you ever waited for a doctor's registrar to enter your personal information into a computer, or watched a store employee use a computer to find out if an item was in stock? Then you saw the database in action.

The easiest way to understand what a database is is to think of it as a collection of lists. Think of one of the databases mentioned above: a patient database in a doctor's office. What lists are contained in such a database? Well, for starters, there is a list of patient names. Then there is a list of past appointments, a list with a medical history for each patient, a list of contact information etc.

This applies to all databases, from the simplest to the most complex. For example, if you want to bake cookies, then you can save a database containing the recipes you know how to make and the friends you give those recipes to. This is one of the simplest databases. It contains two lists: your friends list and a cookie baking recipe list.

However, if you were a professional baker, you would still have many more lists to keep track of: customer list, list of products sold, price list, order list ... it goes on and on. The more lists added, the more complex the database will become.

In Access, lists are a little more complex than the ones you write on paper. Access stores its lists of data in tables to store even more detailed information. In the table below, the list of people in the hobbyist baker's database has been expanded to include other relevant information about friends.

If you are familiar with other programs in the Microsoft Office suite, it might remind you of Excel, allowing you to organize your data in a similar way. In fact, you can create a similar table in Excel.

Why use a database?

If a database is essentially a collection of lists stored in tables, and you can create tables in Excel, why would you need a real database? While Excel is great at storing and organizing numbers, Access is much more efficient at handling non-numeric data such as names and descriptions. Non-numeric data plays a significant role in almost any database, and it is important to be able to sort and analyze it.

However, what databases really do, besides any other way of storing data, is connectivity. We refer to a database as the ones you will be working with in Access a relational database. A relational database can understand how lists and the objects within them relate to each other. To explore this idea, let's go back to a simple database with two lists: the names of your friends and the cookie recipes you know how to make. You decided to create a third list to keep track of the batches of cookies you make and who they are for. Since you are only making them, you know the recipe and you only pass them on to your friends, this new list will get all its information from the lists you made earlier.

See how the third list uses the words that appeared in the first two lists? The database is able to understand that Ivan Ivanovich and Sour Cream Cookies on the list are the same things as Ivan Ivanovich and Sour Cream Cookies in the first two lists. This relationship seems obvious, and the person will immediately understand it. However, an Excel workbook will fail.

Difference of Access from Excel

Excel would treat all of these things as separate and unrelated pieces of information. In Excel, you will have to enter every single information about a person or type of cookie every time you mention it, because this database will not be relative like an Access database. Simply put, relational databases can recognize what a person can do: if the same words appear in multiple lists, they refer to the same thing.

The fact that relational databases can process information in this way allows you to enter, search, and analyze data in more than one table at a time. All of these things would be difficult to do in Excel, but in Access even complex tasks can be simplified and made reasonably user-friendly.

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"

Microsoft Access 2007 Database Basics

Khabarovsk 2011

Fundamentals of working with the Microsoft Access 2007 DBMS: guidelines for laboratory work for 1st year bachelors in all areas of full-time education / comp. L. V. Samoilova. - Khabarovsk: RITs KSAEP, 2011 .-- 32 p.

Reviewer D. V. Timoshenko, Ph.D. tech. Sci., Associate Professor, Department of Internal combustion engines, PNU

Approved by the Publishing and Library Council of the Academy as guidelines for 1st year undergraduates of all full-time specialties

Lyudmila Viktorovna Samoilova Basics of working with Microsoft Access 2007

Methodical instructions for laboratory work for 1st year bachelor students in all areas of full-time education

Editor G.S. Odintsova

_____________________________________________________________

Signed for printing Format 60x84 / 16.

Writing paper. Digital printing. Cond.pl. 1.9. Academic and Publishing House 1.3.

Circulation 100 copies. Order No. ___________________

_______________________________________________________________

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

© Khabarovsk State Academy of Economics and Law, 2011

Basic concepts

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

Under database it is customary to understand the totality of logically organized and interconnected data, shared by various tasks within the framework of some unified automated information system.

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

DBMS - a set of language and software tools intended for the creation, maintenance and sharing of a database by 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, program for working with excel tables). 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 input, extract the necessary information from the database, prepare reports, create forms for more convenient work with data. Several users can work simultaneously with the database. Microsoft Acess has huge capabilities, and at the same time, to get started and create your own database, you only need to master a few simple operations.

Microsoft Access is a relational database management system (relation). This means that a database in Access is made up of interconnected tables.

A database table is a regular table with rows and columns.

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

Table rows are called records (tuples). A record contains several table cells that store specific information about objects. Each record contains information about one object. The lines are in no particular order and have no 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. The key defined by one field of the table is called simple... If the key consists of two or more attributes, it is called composite.

The key can be primary or external. Primary key uniquely identifies each record in the table; duplicate key values \u200b\u200bare not allowed. It means, the primary key must identify a single record (row) in the table, that is, be unique.

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

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

MOSCOW STATE UNIVERSITY OF INSTRUMENT MAKING AND INFORMATICS

COURSE WORK

by discandpline:

"INFORMATION SYSTEMS IN THE ECONOMY"

On the topic of: “ MicrosoftAccess”

I've done the work

Evening student

3 courses of specialty 080105

EF4-0515v group

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. Analyzing the contents of tables to create links 13

4. Generating 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 office suite Microsoft. This program is universal in its own way. At the same time, the program is easy to operate and available to an ordinary user. And the interface is responsible for all this. In the process of improving this program, unique features were introduced. Data can be presented in the form of tables or diagrams. 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 we can say that Microsoft's ACCESS is the best tool for solving problems of any complexity.

Database management system Microsoft Access is one of the most popular applications in the family of desktop DBMS. All versions of Access have in their arsenal tools that greatly simplify data entry and processing, data retrieval and information provision in the form of tables, graphs and reports. Beginning with the version of Access 2000, there were also Web pages for accessing data that the user can view using the program 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, the Access user will interact 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 they were created.

1. Description of Microsoft Access base

An Access database is a file that has the mdb extension. This file can contain not only all tables, but also other objects in 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. Access provides forms and reports for this purpose.

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

You can use one of three methods to open an existing database. Select the required file from the list of previously opened files in the File menu.

If there is no such list in the File menu, open the Options dialog box using the Tools, Options command, open the General tab, and select the Recently used file list check box. Select a file from the list in the task pane, which is located on the right side of the application window. Select the Open command from the File menu, and then select desired file in the Open Database File dialog box.

In the latter case, the dialog box usually displays the contents of the My Documents or Personal folder (depending on the operating system installed on the computer) by default. 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 will appear in the main Microsoft Access window.

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

You can select files not only from the list that appears in the Open Database File (Open) dialog box when you open it. You can select the desired folder either from the Look in drop-down list, or use the shortcuts located in the so-called address bar on the left. When you select the History shortcut, a list of shortcuts to the last opened files and folders appears. When you select the Desktop shortcut, the dialog box displays a list containing the 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 you use most often. Many programs allow you to add shortcuts to the Favorites folder, including this can be done directly in the Open database file (Open) 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 Connect command network drive (Map Network Drive) from the command list of 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 the objects in the database and select the mode of working with the object. On the left side of the window there is an object panel that contains shortcuts for each of the Access objects: Tables, Queries, Forms, Reports, Pages, Macros, Modules ).

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

In the form of large icons;

List view;

In the form of a table.

Switching between these display modes is performed 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 \u200b\u200bthe database window. From the context menu, select the Line Up Icons command.

The view of the list of objects in the form of a list does not allow to arrange the icons in an arbitrary way in the database window, but they can be "dragged" outside the database window (this is one of the ways to activate an object, for example, to open a table). The table view allows for each object to view not only its name, but also its description (Description column), date and time of the last modification (Modified column), creation date and time (Created column ) as well as the type of the object. When using any kind of presentation 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 \u200b\u200bthe database window. From the context menu, select the Arrange Icons command. From the drop-down menu, select the ordering method: By Name, By Type, By Created, By Modified.

For the objects in the database window to be arranged automatically, you must

right-click on any free area of \u200b\u200bthe database window. From the context menu, select the Arrange Icons command. Select the Auto Arrange command from the drop-down menu. A check mark appears in front of the command to indicate that auto-ordering is on.

To cancel the automatic ordering, you must select the Auto Arrange command again from the Arrange Icons menu. The command mark is removed and the automatic ordering mode is turned off.

Each database object can be operated in two modes. The first mode will be called 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), Forms mode, 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 Constructor 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 toolbar of the database window, using the commands on the View menu, 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 changes its name depending on which object is selected. When a table, query, form, page, or Favorites group is selected, the button becomes Open. If the report is selected - Preview, if the 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, is constant for all objects and is intended for editing a previously created object. The third New button has a permanent name, but at the same time the icons on it change in accordance with the type of object.

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

To copy or move an object, you can use standard copy / paste programs. For example, to copy a table to the Clipboard, you need to do one of two actions: select the required table in the list and click on the Copy (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 need to do one of the following: right-click on the object's 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 displays the following information:

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

Description - user-defined description of the table;

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 replicability of an object (see section "Database replication", Chapter 15).

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

You can also place folders on the object pane 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 New Group from the context menu. In the New Group dialog box, enter the name of the folder to be created and click OK.

The easiest way to add an object shortcut 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 objects panel. Another way to add an object to a group is to open the list of objects of the required type in the database window. Right-click on the desired object and select the Add to Group command from the context menu. Select the required folder from the drop-down menu or create a new folder using the New Group command.

As well as individual database objects, groups can be deleted and renamed. Labels in a group can also be deleted, renamed, copied. This is done using the appropriate commands of the context menu that 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 how to use the data that has already been accumulated earlier by other software and, therefore, has a different format. Access 2002 addresses this issue 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 MDB format to any format from which you can import data. However, Access is unique in this sense, because it has another way to use data that is stored in other formats. The system allows you to attach tables from databases of other formats to the 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, modify its contents, that is, work with it as with an internal table in an Access database. However, other users can use the spreadsheet 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 which can be linked to an Access database. You can see them by clicking on the File menu and choosing the command Get External Data, Import, and then clicking on the extension of the Files of type field in the Import dialog box. Formats to which you can export data from an Access database. You can see them if you choose the Export command from the File menu and then click 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 database appears in the database window when the application is first opened. In addition, Access creates system tables that store information about all objects in the application, and these tables, if necessary, can also be displayed in the database window.

In the new microsoft versions Access has four modes for working with tables: Datasheet View, Design View, PivotTable View, and PivotChart View.

In the Tables mode, the work with the data in the table is carried out: viewing, editing, adding, sorting, etc. In the Design mode, the structure of the table is created or modified, that is, the names of the fields of the table and their types are set, the fields are described, they are set properties. In the PivotTable and PivotChart modes, it is convenient to analyze data by dynamically changing the way 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 Table Datasheet, Table Design, PivotTable, and PivotChart toolbars. To switch from mode to mode, just press this button.

There are several ways to open a table in the Tables mode:

· 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 at the top 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 the data is entered. One record is always the current one, and next to it is the current record pointer (arrow in the selection field on the left side of the window). At the bottom 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, is also used to create a new record.

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

Figure: 1.1. Creating a table in design mode

Figure 1.2. Phones table

3. Analysis of the contents of tables to create links

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 do the analysis again. In addition, users who are not yet confident in their knowledge and abilities can entrust the analyzer to create ordered tables. In order to analyze the table, you should follow these steps.

1. Select Tools\u003e Analysis\u003e Table.

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

3. The next window gives examples of splitting tables by the parser. Check out an example and click Next.

4. Select the table for analysis and click Next.

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

6. In the next window, the table partitioning scheme suggested by the wizard is displayed. At the moment, the tables are not related to each other.

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

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

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

10. Now it is possible to reinstall the primary key. The wizard will offer to add an automatically assigned unique record code field to the table and use it as the primary key. However, the primary key is not specified for the table, so you have to do it yourself. Select the Name field from the list and click the Key Field button. A key icon appears 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 radio button No, you do not need to create a request. In addition, you can clear the Show help for working with a new table or query check box, otherwise you will have to close another window. Click on the Finish button.

Figure: 1.3. Forms (main and subordinate)

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. Access 2002 provides forms and reports 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 the report can be a table or a query. In addition to data obtained from tables, the report can display values \u200b\u200bcalculated from the source data, for example, totals.

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

A report, like a form, can be created using a wizard. Report sections are similar to form sections and include a report title and note, a data area, and a header and footer. Fields with totals are often included in a report note. Controls can be added to the report using the Toolbox toolbar, which is identical to that used in Form Design mode. Formatting and grouping controls in 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 to use 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 striped.

To create an auto report:

· In the Object pane of the Database window, click the Reports tab and click the New button. The New Report dialog box appears.

· In the list of the New Report dialog box, select one of the items: Autoreport: in a 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. Left-click on the arrow button to open the list, and then select the required item in the list.

· Click OK.

As a result, Access automatically generates a report based on the selected data source, using either the strip or column format. The tape format arranges the fields of the output records into a string. The to column format places 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 File, Save command or click the Save button on the toolbar. Then, in the text box of the Save As dialog box that appears, enter a name for the new report (for example: My Report) and click OK.

There is one more option for saving the report: using the menu command File, Save As. This command displays the Save As dialog box. Enter a name for the report and make sure Report is selected from the As drop-down list in this window before clicking OK. The selected item determines how the new report will be saved, more precisely, 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. You can do this by using another item in the drop-down list, Like, the Data Access Page item.

Figure: 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 specific order, perform calculations on a group of records, and 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 number 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-substitution requests that automatically fill in the fields for a new record; fetch queries that fetch data from tables; change requests, which make it possible to modify data in tables (including delete, update, and add records); table creation queries that create a new table based on data from one or more existing tables; and other types of queries.

A select query contains data selection criteria and returns a selection that matches 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 allows you to select a subset of records or sort them. The similarity between select queries and filters is that both retrieve a subset of the 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 in which case to use the query, and in which to use the filter.

The easiest way to create a query is using 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 on the Create query by using wizard shortcut or click on the New button in the database window and in the New Query dialog box that appears, select Simple Query ( Simple Query Wizard) and click the OK button.

In the appeared window Create simple queries (Simple Query Wizard) in the Tables / Queries combo box, select the table or query that will serve as the data source for the created query.

· Use the right and left arrows to move from the Available Fields list to the Selected Fields list the fields that are required in the constructed query. The order of the fields in the query will match the order of the fields in the Selected Fields list. If you want to include all fields in your 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 to be created in the Specify a name for the query field (What title do you want to your query?) And select further actions: Open the query to view data (Open the query to view information) or Modify the query design (Modify the query design ).

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

· Click on the Finish button.

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

Figure 1.5. Building a query

6 ... Macros

With their help, macros can significantly expand the functionality of the application you are creating and customize it to the needs of specific users.

You can use macros to perform almost all of the actions on Access objects that were described in the previous chapters.

A macro in Access is a structure that consists of one or more macros that execute either sequentially or in an order specified by specific conditions. The set of macros in Access is very wide, with the help of macros you can implement a lot of what VBA procedures allow. Each macro has a specific name and possibly one or more user-supplied arguments. For example, when using the OpenForm macro, you must specify at least the name of the form to open and the mode for displaying it 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 the 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 arguments to the macro. Therefore, if you really do not want to learn the syntax 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 user-friendly interface for the application: so that forms and reports open when you press buttons in a form or on a toolbar, or by choosing a menu command; so that when opening the application, the user sees on the screen not the Database window, filled with a multitude of tables, queries, forms and reports, but some understandable form with which one could immediately perform the desired actions, etc.

Figure: 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 specified tasks - only under this condition will it help to increase the efficiency of work, regardless of what type of activity is in question.

However, the capabilities of the database are not limited to storing information. A professionally developed database allows you to maintain the reliability of data and provide efficient, quick and convenient access to them. There will be no room for confusion and confusion in such a database.

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

Designing a database structure can be a major challenge that many users try to avoid, and will certainly regret it later. Only a few of the most talented and gifted people skip this stage, managing to create effective database applications. Even if you may not find the information presented in this chapter interesting, keep in mind that designing a 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

Access 2010, despite its popularity and ease of use, for most users remains a secret behind seven seals. This program, first published in 1992 for Windows 3.0, was included in the Microsoft Office suite and has become widespread due to its convenient usability and the ability to write applications based on Access itself to work with complex databases.

Although when you first encounter Microsoft Access 2010, it seems as if such a program is used only by advanced users, computer scientists and programmers, in practice it can greatly simplify the life of any working person.

Access 2010 in Russian free download:

To understand the meaning of using Access, it is enough to imagine a simple notebook with a table listing the work performed, their date, cost and results. Such a notebook can be maintained manually, even if there are several types of work at different prices. In this case, the totals are made using a simple calculator.

How to install Access 2010

Run the installer click "Setting"



Check the list not to install everything except Access and funds Office



Click install, wait for the installation to complete.



If the problem is downloading:

Organize and organize

But what if the calculations need to be made at once for a dozen workers performing 2-3 types of work? For a whole month, about a hundred different tables can accumulate, the totals for which will have to be recalculated regularly. One single table with linked data of workers, types of work, their cost and any other necessary information in Access can replace thousands of single tables. Convenience also lies in the fact that data editing in any linked database will be done automatically in the main table and wherever the changed information is mentioned.

Everyone needs it

Even if you do not own a business, do not count money and do not organize someone else's work, this program will help in maintaining a family budget, payments utilities, registration of addresses and telephone numbers, etc.

Features of working with the program

Access is a relational database management system, that is, a set of tools for managing complex two-dimensional tables. To get started, you can use the standard template: faculty, marketing projects, tasks, contacts, and others.

The most convenient thing is the ability to quickly get the information you need by creating queries, and the reports generated from tables optimize the data in an easy-to-print form. In practice, it turns out to be very useful to be able to create web pages with data available on the Internet. This helps to coordinate the work of people working from home in different parts of the city or even in different cities.

Microsoft Access 2010 is perfectly suited not only for homework, but also for programming databases with the subsequent demonstration of the result to the customer. The applications obtained on its basis, written in Visual Basic for Applications, help to coordinate even fairly large-scale accounting calculations, and the ability to create macros makes this work even easier.