Lessons on creating access databases. Creating and filling databases in Microsoft Access. Print information using reports

Welcome to the world of databases

In this chapter... | Perhaps looking at Microsoft icon Access in the Start menu, you have already tried to understand why it is needed. And someone probably came up with the idea of ​​​​creating a database, but it is not clear where to start. Either way, Access is exactly what you need!

Overview of key Access features

In this chapter... | Often, it is getting started with Access that causes difficulties for beginners. Even readers who had to use spreadsheets And text editors, but have never encountered Access before, it is not always clear what the database does and how it functions.

Exploring the main Access work window

In this chapter... | Chapter 2, "Overview of Basic Access Features," already covered the Access user interface. In particular, elements such as the taskbar, toolbars, and menus were described. Now let's get acquainted with its main element - the Database window.

Database planning

In this chapter... | A database is a collection of permanent or interrelated objects. For example, information about household appliances and other property can be stored in a regular notebook with a square. And personal information about friends and relatives, such as their addresses, driving dates and telephone numbers, can be kept in notebooks or organizers. In fact, and notebook, and notebook are simplified type databases.

Creating the first tables

In this chapter... | It's time to move from getting to know Access to actively working with it. We have reviewed the main functions and capabilities of the program, developed a logical concept for the database, and now we can start creating tables. As you know, tables contain Access data.

Using Relationships

In this chapter... | The term relationship is often interpreted ambiguously. In the world of relational databases, it means a relationship between two records. On the one hand, the use of relationships in databases provides completely new opportunities, but on the other hand, it often causes difficulties even for experienced users. Access's tangled connections can frustrate anyone.

Retrieving data using queries

In this chapter... | At this stage, entering information into the table will not be difficult for you. In addition, from previous chapters you already know how to create tables and work with them, how to connect data in different tables using relationships.

Creating and using data forms

In this chapter... | A database is capable of performing various tasks, and storing information is only one of them. Having mastered the principles of creating tables, you need to learn how to enter and change data. The most effective means of working with them are forms, which allow you to define the type of data displayed and provide various methods for changing it.

Print information using reports

In this chapter... | On at the moment You have already created a database, as well as several tables containing basic data, and learned how to create queries to obtain the necessary information. Now you know how you can use the Access forms user interface to enter, display, and search data.

Publishing data to the Internet using pages

In this chapter... | For some people, the Internet is a place of entertainment or favorite hobby. Others connect to World Wide Web in order to solve the most serious problems. Readers learning Access from this book will likely not be able to create an Internet-enabled application, but that is no reason not to even consider the possibility.

Setting up tables

In this chapter... | By now user interface Access has ceased to be something mysterious for us. We learned how to create objects that form the basis of the database, and gained some experience in setting them up.

Expanding the scope of queries

In this chapter... | Previous chapters described methods for creating and using almost all Access objects. And although a considerable path has already been passed, it is very far from the end. | In this chapter, we'll talk about queries that do more than just retrieve data.

Microsoft Access 2016 training in Moscow

Educational Microsoft Access 2016 courses are intended for students who want to master the skills of building databases, learn how to create, edit, and update databases.

Course participants study the program from the very beginning: from building simple databases to complex ones, studying the intricacies and nuances of creating databases, and mastering the capabilities of building queries and reports. Program training course Microsoft Access is built on a close fusion of theory and practice. During classes, students constantly perform exercises to develop skills in working in MS Access 2016, and receive homework to review the material covered in class.

During the MS Access 2016 course, students are given a set of exercises necessary to work in class. Students attend computer courses for PC users at a time convenient for them, training takes place in computer classes using modern computer equipment.

Course curriculum

Module 1. Tables inMSAccess 2016

• Table structure. Their creation.
• What types of fields are there?
• Working in tables with data.
• Hyperlinks.
• Input masks
• Condition on the value.

Practical work

Module 2: Formatting Access Tables 2016

• Search, replace, use filters.
• Removing and cutting data.
• Data formats.
• Import and attach tables, MS Excel workbook sheets, text files(Word)
• Working with a large table.
• Keys.

Practical work

Module 3: How Queries WorkMSAccess 2016?

• Query builder.
• Basic and constituent conditions for selecting records.
• Create a calculated field in the Expression Builder.
• Types of requests and their processing

Practical work

Module 4. SQL - QueriesMS AccEss 2016

• SQL - expressions.
• Select statement syntax.
• Statistical functions: Count, Avg, Last, Min, Max, First.
• Statistical function Sum.
• Query to create, add, update and delete tables.

Practical work

Module 5: Multi-Table Databases

• How to split data into separate tables and create connecting fields?
• Eliminate redundancy, data inconsistency and dependencies.
• Subtables as an element of relationships
• Organizing connections and maintaining data integrity.

Practical work

Module 6. Form as an editable object inAccess 2016

• Creating a form using the Form Wizard. Using Autoforms.
• Creating forms of any complexity using the Form Builder.
• Creating a main and subform.
• Types of form linking.

Practical work

Module 7. Reports and printing inMSAccess 2016

• Print tables and forms. Saving the form as a report.
• Sorting and grouping reports.
• Pagination. Expanded inscriptions.
• Printing options.
• Export reports.

Practical work

Final certification.

The self-instruction manual is intended for mastering new version MS Office Access. Covers the basics of database development and SQL queries, forms, reports and macros, as well as other standard relational database technologies. Much attention is paid to mastering the interface and tools of Access 2016. New tools for developing web applications are considered, available to users from a browser from any device, and the ability to automatically publish them on SharePoint server sites or in the MS Office 365 cloud. Solutions to problems of operational analysis based on Access database data using Excel and its PowerPivot add-in, which directly displays the relational database model, are provided. The material in the book is illustrated with numerous examples.

CHAPTER 1. Introduction to Access 2016.

The vast majority of business transactions today are recorded, tracked and analyzed as data stored in relational database management systems. Relational database management system Microsoft data Office Access meets the needs of most different groups users. With the help of Access wizards and graphical tools, even users without special skills can develop very successfully useful applications databases. Studies of small and medium-sized enterprises conducted by various services have shown that Access is one of the most popular database programs.

Preface.
Chapter 1: Introduction to Access 2016.
Chapter 2. Designing a relational database.
Chapter 3. Creating a database.
Chapter 4. Queries.
Chapter 5. Forms.
Chapter 6. Development of an interface for entering, viewing and editing documents.
Chapter 7. Reports.
Chapter 8. Operational analysis of Access relational database data.
Chapter 9. Application development using macros.
Chapter 10. Development of an interface for managing the application.
Chapter 11: Creating Web Applications
Appendix 1. Structure of tables in the “Supply of Goods” database.
Appendix 2. Example of completed documents for uploading to the “Supply of Goods” database.
Subject index.

Free download e-book in a convenient format, watch and read:
Download the book Self-instruction manual MS Office Access 2016, Bekarevich Yu.B., Pushkina N.V., 2017 - fileskachat.com, fast and free download.

• Russian literature, 7th grade, Savelyeva V.V., Lukpanova G.G., Yarmukhamedova A.B., 2017
• Russian language, grade 10, Islambekova S.Yu., Musurmanova Yu.Yu., Abduraimova Z.H., Tagieva L.N., 2017
• Russian literature, 7th grade, Part 2, Zakharova S.N., Petrovskaya L.K., 2017

The following textbooks and books.

Introduction

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

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

add one or more records to the database;

delete one or more records from the database;

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

update the value of some fields in the database.

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

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

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

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

The DBMS is completely Russified;

the ability to use OLE technology;

integration with packages Microsoft Office;

support for WWW ideology (Access 97 only);

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

the help system is widely and clearly presented;

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

There are several ways to start Access:

launch from the main menu in WINDOWS 95/98;

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

opening a database automatically launches Access.

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

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

Lesson 1

Creating a database. Entering and formatting data

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

First you need to create new base data.

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

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

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

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

Fill out this table with the following information:

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

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

Use your mouse to highlight:

a) entry 5,

b) entry 3,

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

d) Select all entries. Deselect it.

e) Select the “Character” field.

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

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

Deselect it.

Highlight:

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

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

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

Deselect it.

Select the entire table.

Deselect it.

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

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

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

Do the same with all fields.

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

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

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

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

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

Change the width of the margins.

a) Make the “Character” column 20 wide.

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

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

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

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

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

Return the table to its original state.

Save the "Character" table.

Close the Character table.

Lesson 2

Editing the Database

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

This can be done in three ways:

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

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

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

Copy the first entry in place of the sixth entry.

Delete the fifth entry.

Copy the first entry to the end of the table.

Change Duremar's profession to leech seller.

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