Access 2010, despite its popularity and ease of use, remains a mystery for most users. This program, first published in 1992 for Windows 3.0, was included in the Microsoft office 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 the first encounter with Microsoft Access 2010 it seems that only advanced users, computer scientists and programmers use such a program, 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 in which a table is kept with a list of work performed, their dates, costs, and results. Such a notebook can also be kept manually, even if there are several types of work at different rates. In this case, the results are knocked down using a simple calculator.

How to install Access 2010

Run the installer click "Setting"

Mark in the list do not install everything except Access and funds office

Click install, wait for the installation to complete.

If the problem is with the download:

Organize and organize

But what if the calculations need to be made immediately for a dozen workers performing 2-3 types of work? For a whole month, about a hundred different tables can accumulate, the results of 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 plates. The convenience also lies in the fact that editing data 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 don't own a business, don't count money or organize someone's work, this program will help you manage family budget, utility bills, registration of addresses and phone numbers, etc.

Features of working with the program

Access is relational system database management, that is, a set of tools for managing a complex of 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 obtain the necessary information by creating queries, and reports generated from tables optimize the data in a form convenient for printing. In practice, it is 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 places in the city or even in different cities.

Microsoft Access 2010 is well suited not only for homework, but also for programming databases with subsequent demonstration of the result to the customer. Applications derived from it, written in Visual Basic for Applications, help coordinate even fairly large-scale accounting calculations, and the ability to create macros makes this job even easier.

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.

Base management system Microsoft data Access is one of the most popular apps in the desktop DBMS family. All versions of Access have in their arsenal tools that greatly simplify data entry and processing, 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 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, 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

base Access data is a file that has the extension mdb. 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.

V new version Access 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 (Desktop) in the dialog box, a list appears containing shortcuts to folders, and files located in this moment 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. In order to map a network drive, you need to execute the command Connect network drive(Map Network Drive) from the command list of the Tools button.

If the required database file cannot be found, it can be searched for 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 presented in four different ways (usually for operating system folders). Windows systems): - in the form of 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. From context menu select the Line Up Icons command.

The list view of the object list 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 a description (Description column), date and time of the 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, click right click mouse 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-arrange 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 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 new folder on 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 created folder 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 to an Internal Access 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 you choose the File menu, choose Get External Data, Import, and then click the Files of type field extension 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 the objects in the application, and these tables can also be displayed in the database window if necessary.

In the new Microsoft versions Access has four modes for working with tables: 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. Alternatively, you can clear the Show help for working with new table or a request, 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. A report note often contains fields with totals. 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: Columnar or 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 source of data for forms and Microsoft reports access. 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 with the left mouse button or click the New button in the database window and in the New Query dialog box that appears, select Simple Query ( 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 generated query.

· 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 designed. 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 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 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 well-defined tasks - only under this condition will it help to increase the efficiency of work, regardless of what type of activity it is.

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, 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

Any user computer systems on the Windows based knows what's in the standard package office programs from Microsoft any version includes a unique editor Access. What kind of program is this, how to work with it, now it will be considered. Naturally, only the initial basics will be given here, since a description of absolutely all the features of the application will take more than one page.

What is Access?

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

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

The main directions in using the application

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



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

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

Structure of Microsoft Access

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

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

Communication with other programs and external databases

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

The linking process itself is carried out on the basis of requests according to the type of how databases work. SQL data(Access also supports them).

Create from templates

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



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

Database from scratch

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



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

Nuances of importing and linking data with other sources

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



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

Conclusion

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

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 Access2007

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 (test word editor, an 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.

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

Application package Microsoft Office - Access - is used to work with databases




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

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

In addition, in order to simplify the creation of a new database as much as possible, templates are provided for the user to choose from, allowing you to create a database focused on a specific task. This, by the way, can help you quickly create the required table shape without manually setting everything up.



Filling the database with information

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

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

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



Creating a database and importing information

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

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

All import settings are located in a separate tab in the control panel called "External Data". Here, in the "Import and links" area, the available formats are listed, among which excel documents, Access, text and XML files, web pages, Outlook folders, etc. After selecting the required format from which information will be transferred, you will need to specify the path to the file location. If it is hosted on a server, the program will require you to enter the server address. As you import, you will come across various settings that are designed to correctly transfer data to Access. Follow the instructions of the program.



Basic keys and table relationships

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

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

Relationships like this will make your database easier to read and will likely increase its usability and efficiency.

To create a relationship, go to the "Database Tools" tab and in the "Relationships" area, select the "Data Schema" button. In the window that appears, you will see all the processed databases. You should pay attention to the fact that databases must have special fields intended for foreign keys. In our example, if in the second table you want to display the day of the week or a number, leave an empty field, naming it "Day". Also set the format of the fields, as it should be the same for both tables.

Then, with two tables open, drag the field you want to link into the specially prepared field for foreign key. The "Edit Relationships" window will appear, in which you will see separately selected fields. To ensure data changes in both related fields and tables, check the Enforce Data Integrity box.

Creation and types of requests

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

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

By selecting "Query Wizard" in the "Create" tab, the program will carry out the process of creating a specific type of query. Follow the instructions.

Queries can go a long way in helping you organize your data and always refer to specific information.

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

This is just one of the options for using queries. In fact, they can also be used to create new tables, filter data by criteria, etc.

Setting up and using forms

Through the use of forms, the user can easily view information for each field and switch between existing records. When entering information for a long time, the use of forms makes it easier to work with data.

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

Create a report

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

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

1. Report - an auto-report will be created using all the information provided in the table, however, the data will not be grouped.
2. An empty report is an empty form, the data for which you can choose yourself from the required fields.
3. Report Wizard - guides you through the process of creating a report and grouping and formatting data.

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



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