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

In different areas of the economy, it is often necessary to work with data from different sources, each of which is associated with a specific type of activity. Coordinating all this data requires specific knowledge and organizational skills.

Microsoft Corporation's product Access combines information from different sources into one relational database. The forms, queries, and reports it creates allow you to quickly and efficiently update data, get answers to questions, search for needed data, analyze data, and print reports, charts, and mailing labels.

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

Access is a set of end-user tools for managing databases. It includes designers of tables, forms, queries and reports. This system can also be considered as an application development environment. By using macros or modules to automate tasks, you can create user-centric applications that are as powerful as applications written directly in programming languages. They will include buttons, menus, and dialog boxes. By programming in VBA, you can create programs as powerful as Access itself.

Create applications without coding using Access macros. Spreadsheet and database users should be familiar with many of the key concepts used in Access. Before you start working with any software product, it is important to understand its capabilities and the types of problems it is designed to solve. Microsoft Access (hereinafter simply Access) is a multifaceted product whose use is limited only by the user's imagination.

Access fully implements relational database management. The system maintains primary and foreign keys and enforces data integrity at the kernel level (which prevents inconsistent update or delete operations). In addition, tables in Access are equipped with data validation tools that prevent incorrect input regardless of how it is entered, and each table field has its own format and standard descriptions, which greatly simplifies data entry. Access supports all the required field types, including text, numeric, counter, currency, date/time, MEMO, Boolean, hyperlink, and OLE object fields. If special processing does not result in any values ​​in the fields, the system provides full support for empty values.

Basic concepts about MS Access databases

1.1 Brief characteristics 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 one file, although it distributes it across different tables, as befits a relational DBMS. This data includes not only information in tables, but also other database objects, which will be described below.

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

Features of MS Access that differ from the idea of ​​an “ideal” relational DBMS.

Creating a multi-user Access database and obtaining simultaneous access of several users to a common database is possible on a local peer-to-peer network or on a network with a file server. The network provides hardware and software support for data exchange between computers. Access monitors the delimitation of access of different users to the database and ensures data protection. When working simultaneously. Since Access is not a server DBMS client, its ability to provide multi-user work is somewhat limited. Typically, to access data over a network from several workstations, an Access database file (with *.mdb extension) is uploaded to a file server. In this case, data processing is carried out mainly on the client - where the application is running, due to the principles of organizing file DBMSs. This factor limits the use of Access to support the work of many users (more than 15-20) and with a large amount of data in tables, since the network load increases many times over.

In terms of maintaining data integrity, Access is only suitable for low to medium complexity database models. It lacks tools such as triggers and stored procedures, which forces developers to entrust the maintenance of the database business logic to the client program.

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

However, despite the known disadvantages, MSAccess has many advantages compared to systems of a similar class.

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

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

MSAccess provides the non-programming user with a variety of interactive tools that allow him to create applications without having to develop queries in SQL or program macros or modules in VBA.

Access has extensive capabilities for importing/exporting data into various formats, from Excel tables and text files to almost any server DBMS via 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 tool for performing many standard tasks in Access (working with variables, building SQL commands while the program is running, error handling, using the Windows API, etc.), knowledge and knowledge of it is required to create more or less complex applications MSAccess object model.

One of the programming tools in Access is the macro language. Programs created in this language are called macros and allow you to easily link individual actions implemented using forms, queries, and reports. Macros are controlled by events that are caused by user actions when interacting with data through forms or by system events.

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

1.2 Databases and database management systems

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

This statement is easy to explain if, for example, we consider a library database. It contains all the necessary information about authors, books, readers, etc. Both library workers and readers who need to find any publication can have access to this database. But among them there is hardly a person who has access to the entire database and at the same time is able to single-handedly make arbitrary changes to it. In addition to data, the database contains methods and tools that allow each employee to operate only with the data that is within their competence. As a result of the interaction of the data contained in the database with the methods available to specific employees, information is generated 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 the differences between managing data 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 while a doctor's receptionist entered your personal information into a computer or watched a store employee use a computer to see 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 about 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's a list of patient names. Then there is a list of past appointments, a list with 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, you can keep a database containing recipes you know how to make and friends you give those recipes to. This is one of the simplest databases. It contains two lists: a list of your friends and a list of cookie baking recipes.

However, if you were a professional baker, you would have many more lists to keep track of: a list of clients, a list of products sold, a list of prices, a list of orders... the list goes on. The more lists you add, the more complex the database will be.

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

If you're familiar with other programs in the Microsoft Office suite, this may remind you of Excel, which allows you to organize 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 do 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, over and above any other way of storing data, is connectivity. We call a database like the ones you'll work with in Access a relational database. A relational database can understand how lists and the objects within them are related to each other. To explore this idea, let's go back to a simple database with two lists: your friends' names and cookie recipes you know how to make. You decide to create a third list to keep track of the batches of cookies you make and who they are for. Since you're just making them, you know the recipe, and you're only passing them along to your friends, this new list will get all of its information from the lists you've made previously.

See how the third list uses words that appeared in the first two lists? The database is able to understand that Ivan Ivanovich and Sour Cream Cookies in 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 cannot.

Difference between Access and Excel

Excel would treat all of these things as separate and unrelated pieces of information. In Excel, you will need 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 quite user-friendly.

MOSCOW STATE UNIVERSITY OF INSTRUMENT ENGINEERING AND INFORMATION SCIENCE

COURSE WORK

according to discAndline:

"INFORMATION SYSTEMS IN ECONOMY"

On the topic of: “ MicrosoftAccess”

I've done the work

Evening student

3rd year specialty 080105

Group EF4-0515v

Zhabina I.A.

Work checked:

Shevereva E.A.

Moscow 2008

Introduction 3

1. Description of the Microsoft Access 4 database

2. Working with tables 10

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

ACCESS is included in the most widely used Microsoft office suite. This program is universal in its kind. 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. 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 we can say that ACCESS from Microsoft is the best tool for solving problems of any complexity.

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

1. Description of the Microsoft Access database

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

One of the main goals of creating and using databases is to provide users with the information they need 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 Remember the list of files (Recently used file list) checkbox. Select a file from the list in the task area, which is located on the right side of the application window. Select the Open command from the File menu, and then select the 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 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 will appear in the main Microsoft Access window.

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

Files can be selected in addition to the list that appears in the Open database file 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 on the so-called address bar on the left. When you select the History shortcut, a list of shortcuts to the most recently opened files and folders appears. When you select the Desktop shortcut, a list appears in the dialog box containing shortcuts to folders and files currently located 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 on 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 must execute the Map Network Drive command from the list of commands on the Tools button.

If it is impossible to find the desired database file, you can find it by specifying search criteria in a special dialog box that appears if you click on 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. 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 corresponding objects. The list of objects can be presented in four different ways (common for Windows operating system folders): - 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 located 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 desired manner. 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 the context menu, select the Line Up Icons command.

Presenting a list of objects in the form of a list does not allow icons to be arranged arbitrarily 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 you to view for each object not only its name, but also its description (Description column), the date and time of the last change (Modified column), and the date and time of creation (Created column). ), as well as the object type. When using any type of object representation in the database window, you can organize them by name, type, creation date, and modification date. To do this, right-click on any free area of ​​the database window. From the context menu, select the Arrange Icons command. From the drop-down menu, select the ordering method: By Name, By Type, By Created, By Modified.

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

Right-click on any free area of ​​the database window. From the context menu, select the Arrange Icons command. Select the Auto Arrange command from the drop-down menu. A check mark will appear in front of the command indicating that automatic ordering is enabled.

To cancel automatic arrangement, you must select Auto Arrange again from the Arrange Icons menu. The command label is removed and auto-arrange mode is turned off.

Each database object can be worked in two modes. Let's call the first mode execution mode (conventionally, 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 the Designer 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 located on the left side of the toolbar of the database window, using the View menu commands, or using the buttons on the Access toolbar.

There are three main buttons on the Database window toolbar on the left: the first button has a changing name, depending on which object is selected. When 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 its purpose. The second button, Design, is constant for all objects and is intended for editing a previously created object. The third button, 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 actions: 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 operations: 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 Access main menu.

It reflects the following information:

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

Description - user-defined description of the table;

Created - date of table creation;

Modified - date of the last modification of the table;

Owner - owner (creator) of the table;

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

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

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

To add a new folder to the object panel, you need to: right-click on the object panel and select the New Group command from the context menu. In the New Group dialog box, enter 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. You need to expand the list of objects of this type, find the desired object in it and drag it with the mouse to the appropriate folder in the objects panel. Another way to add an object to a group is to open a list of objects of the desired type in the database window. Right-click on the desired object and select Add to Group 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. Shortcuts in a group can also be deleted, renamed, or copied. This is done using the corresponding commands of the context menu, which appears if you right-click on the object that you want to delete, rename or copy.

When working with any data processing application, the relevant question is always how to use the data that has already been accumulated previously by other software and, therefore, has a different format. Access 2002 allows you to solve this problem in a standard way - by importing an existing database table, spreadsheet worksheet, or text file created by MS-DOS or Windows applications into Access's internal database (MDB) format. Naturally, Access 2002 can also export data from MDB format database tables into 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 of 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.

Types of files 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 select External Data, Import from the File menu, and then click on the Files of type field extension in the Import dialog box. Formats into which you can export data from an Access database. They can be seen if you select Export from the File menu and then click on the Files of type field extension.

2. Working with tables

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

The new version of Microsoft 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 specified, the fields are described, their properties. The PivotTable and PivotChart modes make it easy to analyze data by dynamically changing the way it is presented. There is also an additional mode - 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 Tables mode:

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

· select the 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 Open from the context menu.

At the top of the table are the names of the fields (cells located in the same table column), followed by the records (table rows) into which data is entered. One record is always the current one, and next to it there is an indicator of the current record (an 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 current record pointer around 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 in the selection field with an asterisk, is also used to create a new record.

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

Rice. 1.1. Create a table in design view

Fig.1.2. Table Phones

3. Analyze the contents of tables to create relationships

The analyzer identifies duplicate data and helps you copy it into a new related table. Not every table will need to be modified, but it never hurts to conduct an analysis once again. In addition, users who are not yet confident in their knowledge and abilities can trust the analyzer to create ordered tables. In order to analyze the table, you should perform the following steps.

1. Select the Tools>Analysis>Table command.

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

3. The next window shows examples of table division by the analyzer. Review any example and click Next.

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 himself. Therefore, let him select the fields that will be transferred to the new table by activating the switch Yes, the separation of fields is performed by the wizard. Click the Next button.

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

7. You could complete the task, but it makes sense to continue getting acquainted with the analyzer. The database has a normal structure - the only thing missing is the connection between the two tables.

8. You need to 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 set the primary key again. The wizard will prompt you to add an automatically assigned unique record code field to the table and use it as the primary key. However, the table does not have a primary key, so you will 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 prompts you to create a request. You must select the No radio button, you do not need to create a request. You can also 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.

Rice. 1.3. Forms (main and sub)

4. Create and print reports

One of the main goals of creating and using databases is to provide users with the information they need based on existing data. Access 2002 provides forms and reports for this purpose. Reports allow you to select the information the user requires from a 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 ​​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. It is not possible to change the source data in a report using controls, as you can with 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 title and note, a data area, and a header and footer. Fields with total values ​​are often placed in report notes. Controls can be added to a report using the Toolbox, which is identical to the one used in Form Builder 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 them is to use automatic report generation tools. A report that is automatically generated based on a table or query is called an auto report. Access allows you to automatically create reports in two formats: column and ribbon.

To create an auto report:

· On the object panel of the Database window, click the Reports shortcut and click the New button. The New Report dialog box appears.

· In the list of the New Report dialog box, select one of the elements: AutoReport: Columnar or AutoReport: Tabular.

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

· Click OK.

As a result, Access will automatically create a report based on the selected data source, using a ribbon or column format. The tape format arranges the fields of the output records in a line. The column format arranges the fields of the output records into a column. An AutoReport created using either of these two formats will include all fields and records present 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 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. This command displays the Save As dialog box. Enter a name for the report and, before clicking OK, make sure that Report is selected from the As drop-down list in this window. The selected element determines how the new report will be saved, more precisely, as an 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 How drop-down list allows you to do this - the Data Access Page element.

Rice. 1.4. Example of report construction

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, or select from a table based on certain conditions. Queries can serve as a source of data 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 it. There are several types of queries in Microsoft Access: server queries, which are used to retrieve data from the server; auto-substitution requests that automatically fill in the 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, as well as other types of queries.

A select query contains data selection conditions 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 allow you to select a subset of records or sort them. The similarity between select queries and filters is that they both retrieve a subset of records from the underlying table or query. However, there are differences between them that need to be understood in order to make the right choice in which case to use a query and in which to use a filter.

The easiest way to create a query is to use the Query Wizard. To create a simple query using the Query Wizard:

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

· In the list of queries, double-click with the left mouse button 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 OK.

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

· Using the right and left arrows, move from the Available Fields list to the Selected Fields list those fields that are required in the query being constructed. In this case, the order of the fields in the request will correspond to 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 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 ).

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

· Click on the Finish button.

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

Fig.1.5. Building a Query

6 . Macros

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

Using macros, you can perform almost all 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 procedures in VBA can do. Each macro has a specific name and possibly one or more arguments that are specified by the user. For example, when using the OpenForm macro command, you must specify as arguments at least the name of the form to open and the mode for displaying it on the screen.

The use of macros is justified by the fact that they are easy to create and do not require learning 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 a list and entering expressions as arguments to the macro. Therefore, if you really don’t want to learn the syntax of the VBA language or it seems too difficult, feel free to use macros and you will get a fairly functional application.

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

Rice. 1.6. Setting startup options

Conclusion

At its core, 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 must serve to perform clearly defined tasks - only under this condition will it help improve work efficiency, regardless of what type of activity we are talking about.

However, the database's capabilities are not limited to storing information. A professionally developed database allows you to maintain the reliability of data and provide effective, fast and convenient access to it. 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 data from being accidentally changed or deleted.

Designing a database structure can be a major challenge that many users try to avoid and end up regretting later. Only some of the most talented and gifted specialists skip this stage, managing to create effective database applications. Even if you don't find the information in this chapter interesting, remember 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

Microsoft Access, released in 2013 year is a program included in the Microsoft Office package that allows you to manage, change, and create separate databases. Thanks to its versatility and simple, intuitive interface, even an inexperienced user can master the program in a short time.

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

Access 2013 in Russian free download:

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

How to install Access 2013

Click start installation



Click Settings.



Select component Access And facilities, click install.



If there is a problem with downloading:

Features and Specifications of Microsoft Access 2013

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

• builders of tables, screen forms, and printed reports;
• query builder

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


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

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

Conditions for using the program

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

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

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




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

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

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



Filling the database with information

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

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

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



Creating a database and importing information

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

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

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



Basic keys and table relationships

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

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

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

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

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

Creation and types of requests

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

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

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

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

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

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

Setting up and using forms

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

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

Creating a report

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

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

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

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



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