Lab: Access. Lab: Access Lab Report ms access
Ufa State Aviation Technical University
Department of Computer Science
Laboratory work No. 4
Information technologies for creating and processing databases withwith helpMSACCESSXP.
INEC student
Group K-122
Kizka N.D.
Checked:
Filosova E.I.
Report onACCESS.
1) Creating tables.
In order to create tables in ACCESS, we must select the Create a table using the designer element in the Database. In the constructor we fill in the Field Properties and the Fields we directly need. This is how we create the tables we need. (Clients, Implementation)
2) Creating a data schema.
To create a Data Schema, we use the icon on the control panel.
After opening it, we insert the ones we need into the tables using the Add Table command and establish a connection between the fields by simply dragging the field we need from one table to another.
3) Sorting.
To sort, we need to open a non-key table and use Records/Filter/Advanced filter to sort the data.
4) Selecting data using queries.
To create a request, we will use the Query Builder.
a specific value in a text field (for example, select products of one category).
a range of values in any numeric field (for example, select products whose cost is more than 1,000 rubles).
range of values in any text field (for example, find products whose names begin with the letters M-S).
inaccurate match of values in a text field (for example, find clients living in the same city).
logical AND, connecting two values of one numeric field (for example, goods whose cost is more than 200 and less than 500 rubles).
logical AND, connecting the values of two different fields (for example, goods worth over 200 rubles sold today).
logical OR, connecting two values in one text field (for example, products of a particular category).
logical OR, connecting the values of two different fields (for example, goods sold on a certain day, OR goods whose cost is at least 100 rubles).
NOT operator in a text field (for example, all products except a certain category).
5) Creating queries using the expression builder.
Create a database query in which one of the fields is obtained by concatenating the values of several text fields in the source table.
6) Creating final queries.
Create a record update request that doubles the value of one of the numeric fields.
Create a query against a copy of the Products table, in which all records about products of the same category will be deleted.
Create a request to add to a copy of the Products table, in which records will be added to the copy from the Products table according to any condition.
Create a cross-query that calculates the total volume of goods in each category for each client.
Development of reports.
To create a report, you need to use the Report Wizard, in it we will select all the fields of the tables we need and the calculation fields, as well as the Header form and report type.
Control questions.
1. What data model is called relational?
A DBMS for PCs primarily supports a relational model, which is distinguished by the simplicity and uniformity of data presentation in the simplest two-dimensional tables.
The main logical structural unit of data manipulation is a table row - a record. The structure of a record is determined by the composition of its constituent fields. The set of record fields corresponds to logically related details that characterize some essence of the subject area. Typical functions of a DBMS for data manipulation are: retrieving, adding, deleting, changing data.
Microsoft Access calls objects anything that can have a name, such objects are tables, queries, forms, macros and modules.
How to implement a one-to-many relationship in a relational data model?
For communication we will use the Evaluation object. Each student has grades in several disciplines, so the relationship between Students and Grades will be One-to-Many (1: M). Each discipline is taken by many students, so the relationship between Disciplines and Grades will be One to Many.
What is included in the table structure?
The table structure includes: Field name, Data type, Note and Field properties.
4. How to determine the properties of fields in a table?
It is necessary to select the Field Properties in the Table Designer and in them set the qualities of this table that we need.
5. Name the main elements of the Query Builder window.
Field, Table name, Sorting, Display, Selection conditions or.
6. How to write selection conditions with logical AND and logical OR in a request?
You need to use the Expression Builder and specify either And or Or in the condition.
7. How to create a calculated field in a query?
You need to design some function in the field using the Expression Builder to calculate some results.
8. What are summary and cross queries used for?
To summarize any Results, for example, calculating the average or maximum value.
And with the help of a cross-query, you can more clearly present the data of the final queries, which provide for grouping according to several criteria (two, in particular). In this case, the field values according to the first grouping characteristic can become row headers, and according to the second - column headings.
9. How can you use action queries to edit tables?
For example, you can use the Delete Request to delete any record, or Update, Add, Insert from another table.
10. What sections does the Form Builder window consist of?
From such sections that were specified in the process of forming.
MINISTRY OF EDUCATION AND SCIENCE OF THE RF
MOSCOW STATE INSTITUTE OF ELECTRONICS AND MATHEMATICS
(Technical University)
Department of Computer Science
for laboratory work No. 7
« Microsoft Office .
Database management system
ACCESS»
Student: Malofeev S.A. C-12
Teacher: Voskov L.S.
Moscow 2008
Completed the theory:
Basics
DATABASES and Database Management Systems (DBMS)
B basics D Databases (DBs) are designed for storing on a computer and effectively using large volumes of a wide variety of information:
about books, magazines and newspapers in libraries,
about goods in stores and products in factories,
about exhibits in museums and various collections,
about all employees of companies, enterprises and institutions,
about all aircraft flights at airports, ticket prices and seat availability,
about the schedule of all trains from the stations, about the cost of tickets and availability of seats,
about all cars registered with the State Traffic Safety Inspectorate (GAI), including stolen ones,
about all tours, including last-minute ones, in travel agencies and travel agencies,
about the weather anywhere in the world now and many years ago, etc.
All DBMS, including the ACCESS DBMS, allow:
Quickly create Databases, supplement and change them.
Quickly find the specific data you need in the database.
Sort data in the database according to various criteria (for example, alphabetically).
Extract subsets of data to the screen based on your requests, i.e. satisfying a given criterion.
Perform statistical calculations on database data for analysis and decision making.
Print data selected for specific purposes, e.g. prepare certificates, reports, etc.
Like Excel tables, database tables consist of columns and rows. In this case, the columns in the database are called " FIELDS"and they have specific names
(names " FIELDS"), and not letters of the Latin alphabet as the name as in Excel tables. Such columns, i.e. There can be up to 255 fields in database tables (as in Excel).
Each row in the database tables is called " BY RECORDING" and has its own serial number, the same as a row in an Excel spreadsheet. The number of rows, i.e. records in database tables is practically unlimited.
All " RECORDS"(rows) in the database table contain in each cell necessarily the same type of information corresponding to the name" FIELDS". This is the main difference between a database table and an Excel spreadsheet. For example, in a column with field name " FULL NAME"(see database table " Student" on the next page) in all entries (i.e. in all lines), you will see specific names of people;
in the column with the field name " GODR" in all records (i.e. in all rows) you will see the specific years of birth of students;
in the column with the field name " NG" in all entries (i.e. in all rows) you will see specific student group numbers.
A database can include not one, but several related tables. Except Tables – the main object of the Database - the database may contain other objects, namely:
Form (one or more). A form is a way to display data from database tables on the screen, convenient for entering data and viewing it.
Request (one or more). A query is a way to select and screen output data from database tables according to the criteria you specify.
Report (one or more). A report is a way to select and printing
Macro (one or more). A macro is a set of macro commands that perform certain operations with the database, for example, printing reports.
Module (one or more). A module is a program in the Access Basic programming language used in a database to perform non-trivial tasks.
All listed objects, if they were created, are part of the Database file with the extension . mdb.
You will learn Access 2000 - latest version program, but it differs little from the Access 97 version, so both versions are currently used.
This practical work will allow you to master all the basic operations of creating, changing and using Databases, regardless of the version of Access that you use.
STARTING THE ACCESS DBMS
The appearance of the screen after starting the Windows operating system can be very different, but there are always many program icons, files and folders on the monitor screen (your desktop), and at the bottom the taskbar (a narrow strip at the bottom of the screen with a Start button).
If there is an Access program icon somewhere on the screen, place the mouse pointer on it and double-click the mouse to run the program.
If there is no Access program icon on the screen, then by clicking on the Start button, open the main menu of the system, select “Programs” in it, and then in the automatically opened second menu, click on the “Microsoft Access” item and run this program for calculation.
A typical software will open Windows window with the heading “Microsoft Access”, and in it (Fig. 1):
Microsoft Access DIALOG BOX for creating a new database or opening an existing one.
MENU BAR under the title bar (File Edit View Insert Tools Help Window),
A STANDARD TOOLBAR below the menu bar, which is toggled on and off by using the "Toolbars" command in the "View" menu. All tools have a “label” that appears automatically when you place a mouse arrow over the tool.
PROGRAM STATUS BAR at the very bottom of the window. It will display current information about your database and Access operating mode.
THEORY: CREATING AND USING QUESTIONS IN DATABASES
DATA
Most effective method using a Database is creating " Requests". Request– this is a way to select and display data from database tables according to the criteria you specify.THEORY: CREATING AND USING REPORTS IN DATABASES
Except " Tables", "Form" And " Requests" a Database file may contain one or more " Reports". "Report " is a way of choosing and printing data from database tables in the form in which they are required by the user, i.e. To you.THEORY: CREATION OF A DATABASE SCHEMA – 3RD STAGE
Creating a Database schema is defining the relationships between the tables included in this database, or more precisely, between specific fields of these tables.
When tables are linked, changes in one of them are automatically transferred to the other. The main purpose of linking tables is to avoid duplication of information in the database.
The easiest way to understand the meaning of connections is by the example of the specific database you are creating, i.e. Educational process. The connection diagram of this database is shown in Fig. 3.
In the window Educational process: database click button Data Schema on the toolbar. In the window that opens Adding a table, select a bookmark Tables and by pressing the button Add, place it in the window Data Schema all tables. Then press the button Close. As a result, in the window Data Schema all database tables will be presented Educational process with lists of your fields.
Rice. 3. Database schema Educational process
Relationships should be established with empty tables, otherwise problems may arise in coordinating the properties of the relationships.
THEORY: Creating and using reports in databases.
Development of multi-table reports
Along with single-table Access reports allows you to create complex reports that provide data output from several interconnected database tables. To display interrelated data in a multi-table report, the necessary connections are established automatically in accordance with the data schema.
Multi-table reports can contain a main part and an included part, i.e. a subreport. Each of these parts is based on its own table.
Multi-table reports can also have fields from different tables without requiring the inclusion of subreports.
Let's consider building a report for tables that are in one-to-multiple relationships. In this relationship, one table is the master table and the other is the slave table. The construction of such a report has its own characteristics depending on the choice of the main or subordinate table as the basis of the report.
Exercise 31. Multi-table report with the main part based on the main table
Rice. 19. Layout of report on departments
Let's look at the technology for creating a multi-table report using tables as an example DEPARTMENT and TEACHER. Suppose you need to prepare a report containing information about departments, including lists of teachers.
In the tabular section for each department, you need to display a list of teachers. Such data can be obtained from a table Teacher. The proposed layout gives reason to choose the table as the main table of the report DEPARTMENT, and the table TEACHERVATEL- as a data source for a subform with a list of teachers.
Report Layout. Let's assume that the layout for a report with lists of teachers by department should look like the one shown in Fig. 19. In accordance with the draft layout, the report is supposed to display data for each department, including name, code and telephone number, as well as the name and photograph of the head. This data is contained in the table DEPARTMENT.
Completed the exercises:
Exercise 1. Creating a simple Database in the form of a single table
Exercise 2: Changing column widths using the mouse or using the "Column Width" command in the "Format" menu
Exercise 3. “Inserting” and “Deleting” fields, as well as changing the order of “Fields” (columns) in the table.
Exercise 4. Inserting a new Field with “photos” of employees
Exercise 5. Entering new data into the table, editing and deleting records
Exercise 6. Sorting data in a database table
Exercise 7. Searching for records by pattern in large database tables and searching and replacing the contents of a specific Field
Exercise 8. Creating and using a new database object - “Query”.
Exercise 9. Creating and using a “Query with a parameter.”
Exercise 10. Creating a new object in the Database - “Forms”.
Exercise 11. Creating and using a new database object - "Report".
Exercise 12. Creating a Report with a Parameter based on a Query with a Parameter
Exercise 13. Designing a "Group" table in "Table" mode
Exercise 14. Designing the "Teachers" table in the "Wizard" mode
Exercise 15. Designing the "Department" table in "Design" mode
Exercise 16. Designing the Study and Progress tables in Design mode
Exercise 17. Establishing connections between database tables.
Exercise 18: Removing a relationship between tables
Exercise 19. Entering data directly into the "Specialty", "Teachers" and "Groups" tables
Exercise 20. Forming a query in Design mode
Exercise 21. Preparing to create a composite form “List of group students”
Exercise 22: Using the Wizard to Create a Form for Two Tables
Exercise 23. Selecting a form type
Exercise 24: Naming the form and opening it
Exercise 25: Editing a Form in Design View
Exercise 26: Editing a Subform
Exercise 27: Switching to Form Mode and Loading Tables
Exercise 28. Multi-table report with the main part based on the main table
Exercise 29: Creating the Body of the Report
Exercise 30. Creating an auto report.
Exercise 31: Finalizing a Subordinate Report
Exercise 32: Including a subreport.
Report on the completion of the control task:
Database:
Populating database tables.
Creating database connections:
Introduction to ACCESS
We will look at the capabilities of Access and the types of problems that can be solved using this DBMS. In particular:
Access is a database management system (DBMS).
Using Access for storing and retrieving data, presenting information in a convenient form and automating repetitive tasks.
Development using Access of simple and convenient forms for data entry, data processing and generation of complex reports.
Using Access 97 for hosting Access forms as HTML documents on Web pages and data exchange with Internet/Intranet nodes.
Support in Access for the Query by Example (QBE) mechanism, which provides sampling, sorting and searching of data.
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. MicrosoftAccess 97 (hereinafter simply Access) is a multifaceted product whose use is limited only by the user's imagination.
Access - this is, first of all, database management system(DBMS). Like other products in this category, it is designed for storing and retrieving data, presenting information in a convenient way and automating frequently repeated operations (such as accounting, accounting, planning, etc.). With Access You can develop simple and convenient data entry forms, as well as process data and issue complex reports.
Access is a powerful Windows application; For the first time, the performance of a DBMS is seamlessly combined with the convenience that Microsoft Windows users have at their disposal. Since both of these products are the brainchild of Microsoft, they interact perfectly with each other. Access operates under Windows control 95 or WindowsNT, so that when working with it, the user has access to all Windows benefits. You can cut, copy, and paste data from any Windows application into Access and vice versa; You can create a form project in Access and insert it into the form designer.
With OLE (Object Linking and Embedding) objects in Windows 95 and Microsoft Office 97 components (Excel, Word, PowerPoint, and Outlook), you can turn Access into a true database operating environment. With new Internet extensions, you can create forms that interact directly with data from the WorldWideWeb and translate it into a view on HTML language, which works with products such as InternetExplorer and NetscapeNavigator.
With all this, Access is not just a DBMS. How relational The Access DBMS provides access to all types of data and allows you to use several database tables simultaneously. At the same time, you can significantly simplify the data structure, thereby facilitating the implementation of assigned tasks. An Access table can be linked to data stored on a mainframe computer or server. On the other hand, you can use tables created in the Paradox or dBASE environment. The results obtained can be quickly and easily linked and combined with data from Excel spreadsheets. Working in the Microsoft Office 97 environment, the user has access to fully compatible Access and Word, Excel and PowerPoint.
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. In fact, many Access tools (such as wizards and designers) are written in VBA.
The power and accessibility of Access make it the best database management system on the market today. First, let's get to know Access at the end-user level. Then we will move on to more complex elements such as elements of programming in VBA and interaction with the Internet.
What does it offer? Access
1.True relational database model
Fully implemented in Access 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 during special processing there are no values in the fields, the system provides full support empty values.
Relational data processing in Access, due to the flexible system architecture, can satisfy any needs. At the same time, Access can be used as a stand-alone DBMS in file server mode or as a client component of products such as SQLServer. Besides. Access supports the ODBC (OpenDatabaseConnectivity) protocol, which allows you to connect to databases of many different formats, such as SQLServer, Oracle, Sybase and even DB/2 for IBM mainframe computers.
Access supports transaction processing with integrity assurance. In addition, user-level security is provided, which allows you to control access to data for individual users and entire groups.
Context-sensitive help and Office Assistant
Microsoft's help system is still the best in the industry, for both new and experienced users. Access provides context-sensitive help that you can access by just clicking
In Access 97, Microsoft introduced OfficeAssistant and ScreenTips. Tooltips contain short explanations of something. The system provides a whole gallery of ten possible assistant characters (you can choose anyone to your liking). If assistant If you get tired of it, you can turn it off.
Easy to use wizards and constructors
Master(Wizard) can turn hours of work into minutes. Masters ask leading questions regarding the content, style and format of the created object; they then automatically build the desired object. Included in Access about a hundred craftsmen , which help you design databases, applications, tables, forms, reports, charts, labels, controls, and properties. It is even possible to configure wizards to solve different problems.
Importing, exporting and linking external files
Access allows you to import and export many well-known file formats, including dBASE, FoxPro, Excel, SQLServer, Oracle, Btrieve, many ASCII text formats (including those with a fixed line length or specified delimiter), and HTML data. As a result of the import, an Access table is created; Exporting an Access table creates a file in the specified format.
Binding(previously called accession) means you can use external data without creating an Access table. You can establish similar relationships with dBASE, FoxPro, Excel, ASCII, and SQL data. A very powerful feature is linking Access tables with their external tables and then sharing them; this applies to Access, dBASE, FoxPro and SQLServer tables.
Forms and reports WYSIWYG
The form and report designer windows have the same interface and provide the user with many options. The form or report is designed according to the WYSIWYG principle (WhatYouSeeIsWhatYouGet - what you see is what you get). By adding another control element, the user sees how the created form changes.
You can include text boxes, radio buttons, check boxes, lines, and rectangles in forms and reports, and you can design them with color and shadow. Moreover, you can include entire pictures, diagrams, subforms, and subreports. At the same time, all data presentation parameters remain completely under user control. Forms can span many pages, and reports can have many levels of data grouping and summarization.
Forms and reports can be viewed in preview mode, providing a bird's eye view by changing the scale. In design mode, you can view the report with dummy data so you don't have to wait for a large real file to be processed.
The Report Builder is a very powerful tool that allows for up to ten levels of grouping and sorting. Thanks to it, it is possible to create reports showing percentages and totals, which can be obtained in only two passes. You can create many types of reports, which include mailing labels and mail distribution lists.
Multi-table queries and relationships
One of Access's most powerful features is also its most important. Relationships allow you to connect tables graphically. You can even link tables representing files different types(such as an Access table and a dBASE table). After such a link, the tables appear as one whole, and now you can build queries in relation to any data in them. You can select specific fields, define the sort order, create calculated expressions, and enter criteria for selecting the desired records. You can display the results of a query as a table, form, or report. The user is not required to set up connections in advance; instead, simply enter the query builder (for example, when you want to build a specific report).
Queries are also used in other cases. You can create queries that calculate totals, display grouped tables, and build new tables. The query can even be used to update data in tables, delete records, and add one table to another.
Graphs and charts
Access uses the same graphics application as Microsoft Word, Excel, PowerPoint, and Project. It allows you to create hundreds of types of graphs and charts, customizing them based on your specific needs. You can create bar graphs, bar graphs, pie charts, surface charts, and other charts in both 2D and 3D. They can be optionally accompanied by text, decorated in different colors and patterns. Values can be displayed in columns or slices of pie charts. You can rotate the diagram images so that they are reproduced from any convenient viewing angle. All this is provided by the AccessGraph program.
Possibilities DDE And OLE
Using DDE (DynamicDataExchange) and OLE (ObjectLinkingandEmbedding) you can add all sorts of new objects to Access forms and reports. Such objects can be sound, pictures, diagrams, and even video clips. You can embed OLE objects (such as bitmaps) or documents word processors(Word or WordPerfect) or make connections to Excel spreadsheets. By linking these objects to their database, the user can create dynamic forms and reports, and use the same information in different Windows applications.
Access to Internet
Access now provides all the features that allow an application to connect to the Internet/intranet. With one click you can save tables, queries, forms and reports in HTML format. The corresponding wizard allows even a novice to transfer HTML codes from an object to a Web page, making them available for use by anyone who surfs the Internet! Hyperlinks allow you to access data located on a Web page directly from Access forms.
Many people believe that posting data on Web pages should be done by Web administrators. Access 97 clearly demonstrates that this operation can be successfully performed by any user. And he will be helped in this by the placement wizard on a Web page, which converts selected database objects into HTML format and transfers them in this form to a Web page. Using this wizard, you can create static or dynamic pages, transfer them to a Web server, and create your own home page and even use templates to create a standard look and feel for all HTML pages!
Built-in functions
Access contains over a hundred functions(small built-in programs that return a value when executed) that perform a wide variety of tasks. There are functions for manipulating databases, strings, numbers in date and time format, mathematical, business and financial. They can be used to create calculated expressions in forms, reports, and queries.
Macros: programming without programming
For non-programmers (or advanced users who simply don't want to code), Access provides macros. They allow you to automate certain tasks. Near fifty macros make it possible to manipulate data, create menus and dialog boxes, open forms and reports, in a word, automate the execution of almost any task. Using macros, you can solve about 90% of all data processing tasks.
Modules: Visual Basic for Applications database programming
Access is a serious application development environment with a full-featured programming language. The VBA language (formerly known as AccessBasic) implements an object-oriented approach to programming and allows the programmer to do almost anything imaginable. It is a powerful structured programming language. It is fully extensible and supports API procedures in any dynamic link libraries (DLLs) operating systems Windows 95 and WindowsNT.
Fully featured development environment supports many powerful modern capabilities: multi-window mode for editing and debugging, automatic check syntax, breakpoints, step-by-step execution, and even syntax help that displays on-screen options for the commands you enter.
Even if you have already worked with any databases, this does not mean that everything will go smoothly with Access right away. You can be a major expert in relational DBMSs such as dBASE, FoxPro and Paradox, but have no experience working with databases in a Windows environment.
First you should get to know Windows better: work with Paint editor, experiment with Word or Excel, learn how to use the mouse correctly. It is worth creating a diagram in Excel, using the services of one of the wizards, and contacting the help system. All this will help in the future when mastering Access. You also need to get used to new terms.
Information for Spreadsheet Users
An experienced Excel (or 1-2-3) user will notice that Access and Excel are similar in many ways. First, both of these programs are Windows products, so he should already be familiar with the Windows conventions that will be used when working with Access. Appearance of tables and Access queries corresponds to the appearance of spreadsheets and is called data tables ( datasheet ). The sizes of table rows and columns can be changed in the same way as in working Excel sheets. Essentially, the data entry mode in Access 97 corresponds to the input mode Excel data. You just need to enter the data and define the column headings; Access will do the rest automatically.
Access, like Excel, supports WYSIWYG mode and work with graphical application MicrosoftGraph. Thus, the user always has the opportunity to create the same type of diagrams in both systems. Access also includes a chart wizard similar to that used in Excel.
Like Excel, Access 97 includes a Pivot Table Wizard; in fact, you can use it to create Excel pivot tables: information from databases Access data can be easily transferred to Excel spreadsheets. Access databases can be linked to Excel tables. In both products, you can create queries and perform sorting using the same interface. (If you've ever used Excel menus to create queries and sort data, you'll already be familiar with these concepts.) In short, Access 97 is compatible with all Office 97 products.
Lecture 1.
Basic operating principles
with a database in Microsoft Access.
1.3. Database creation.
1.4. Working with tables.
1.5. Data types.
1.6. Field properties.
1.7. Key fields.
1.8. Defining relationships between tables in the database.
1.9. Ensuring data integrity in Microsoft Access.
1.1. What is a "Database".
In business or personal life, you often have 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 Access combines information from different sources into one relational database. Created forms, queries and reports let you quickly and efficiently update data, get answers to questions, search for the data you need, analyze data, and print reports.
In the database, information from each source is stored in a separate table. When working with data from multiple tables, relationships are established between the tables.
A query is created to search and select data that meets certain conditions. Queries also allow you to update or delete multiple records at once, and perform built-in or special calculations.
Forms are used to view, enter or change data directly in the table. A form allows you to select data from one or more tables and display it on the screen using a standard or custom layout.
A report is used to analyze data or print it in a specific way. for example, you can create and print a report that groups data and calculates totals.
To perform certain operations automatically, macros are used, which contain a set of one or more macro commands, such as opening forms or printing reports. Macros can be useful for automating frequently performed tasks. For example, when a user clicks a button, you can run a macro that prints a report.
Sometimes it is necessary to create your own procedure that should be launched from any window in the database or a procedure designed to handle a specific event. A set of such procedures in the Visual Basic language, assembled into one program unit, is called a module. There are two main types of modules: class modules and standard modules. Class modules are form modules and report modules associated with a specific form or report. They often contain procedures for handling events (such as a keypress) and are created automatically when you create the first event procedure. In standard
In the database window you can work with all its objects. To view objects of a certain type, select the appropriate tab (for example, “Tables”).
Using the buttons on the right, you can create and modify existing objects.
1.2. Database design.
Before you create tables, forms, and other objects, you must set the database structure. A good database structure is the basis for creating a compliant, efficient database.
Database design steps:
1.Determine the purpose of creating a database, its main functions and the information it should contain. The database must meet the requirements of those who will directly work with it. To do this, you need to determine the topics that the database should cover, the reports that it should produce, analyze the forms that currently are used to record data, compare the database being created with a well-designed, similar database.
2. Develop on paper the structure of the tables that the database should contain. When designing tables, it is recommended to be guided by the following basic principles:
Information in the table should not be duplicated. There should be no repetitions between tables. When certain information is stored in only one table, then it will only have to be changed in one place. This makes work more efficient and also eliminates the possibility of mismatched information in different tables. For example, one table should contain customer addresses and phone numbers.
Each table should contain information on only one topic. Information on each topic is processed much easier if it is contained in tables independent from each other. For example, customer addresses and orders are stored in separate tables so that when an order is deleted, the customer information remains in the database.
3.Determine the fields required in the table. Each table contains information about a different topic, and each field in the table contains separate information about the topic of the table. For example, a customer data table might contain fields for company name, address, city, country, and phone number. When designing fields for each table, keep in mind:
Each field should be related to the topic of the table.
The table should contain all the necessary information.
Information should be broken down into the smallest logical units (For example, the First Name and Last Name fields, rather than a general First Name field).
4.Set the key field. In order for Microsoft Access to link data from different tables, for example, customer data and his orders, each table must contain a field or set of fields that will specify the individual value of each record in the table. This field or set of fields is called the primary key.
5.Define relationships between tables. After distributing the data across tables and defining key fields, you need to select a schema to link the data in different tables. To do this, you need to define the relationships between the tables.
6. Review the database structure again and identify possible shortcomings. It is advisable to do this at this stage, while the tables are not filled with data.
7.Add data and create other database objects. If the table structures meet the requirements, then all data can be entered. You can then create any queries, forms, reports, macros, and modules.
8.Use analysis tools in Microsoft Access. Microsoft Access has two tools for improving your database structure. The Table Analysis Wizard examines the table,, if necessary, suggests a new structure and relationships, and also reworks it. The performance analyzer examines the entire database, makes recommendations for its improvement, and implements them.
1.3. Database creation.
Microsoft Access supports two ways to create a database. You can create an empty database and then add tables, forms, reports, and other objects to it.
This method is the most flexible, but requires a separate definition of each database element. It is also possible to immediately create a database using the wizard
a certain type with all the necessary tables, forms and reports. This the simplest way initial creation of the database. In both cases, you will have the opportunity to
time to change and expand the created database.
If you are creating a new database (in this case only), the File dialog box will appear. new base data", in which you must specify the name and location of the file for the created
Database. The Microsoft Access database file has the extension .mdb. In order to get a copy of the database, you need to copy the file that contains it, since
When you exit Microsoft Access, your data changes are saved automatically. However, when you change the structure of any database object in Microsoft Access, you are prompted to confirm that you want to save the changes before quitting.
1.4. Working with tables.
A table stores information on a specific issue, for example, the “Products” table contains information only about products, and the “Suppliers” table contains information only about companies supplying goods. To ensure that data is not duplicated and that errors do not occur when issuing it, it is necessary to distribute different types of data into different tables. Data in a table is organized into columns (“fields”) and rows (“records”).
There are two modes of working with the table. You can select Design mode or Table mode by clicking on the special button on the toolbar.
In Table Design view, you can create an entire table by adding new fields or deleting and customizing existing table fields.
To add a field, enter the field name and type at the top of the table window. To rename a field, change its name in the Field Name column.
The data type determines what type of data can be entered into the field. For example, entering text into a currency field is not allowed. Selecting the appropriate data type ensures that data is entered in the correct form for sorting, calculations, and other operations. To define or change the data type, select the Data Type column, click the drop-down symbol in the field, and select a data type from the list.
Each field has properties, changing which you can control the saving, processing and display of field data. For example, for the “Cash” format, a digit group separator and a currency symbol (1,234 g) are automatically added. To set a field property, select it at the top of the table designer window and at the bottom of the window, enter the value of this property or select it from the list. The set of available properties is determined by the field's data type.
A unique label, called a key, is used to identify each entry in the table. Just as a license plate uniquely identifies a car, a key uniquely identifies a record. Key fields in tables are used to create inter-table relationships. To define a key, select the line describing the required field and click the Key button on the toolbar.
In Table view, you add, edit, or view tabular data. You can also check spelling and print tabular data, filter and sort records, change appearance tables or change the table structure by adding or removing columns.
To add records, use the New Record button on the toolbar. When you click this button, a blank row appears at the end of the table.
To delete records, select any field of the record and click the Delete record button on the toolbar.
Rows in a table can be sorted according to the contents of one or more columns. To do this, select the field by which you will sort and click the Sort Ascending or Sort Descending button on the toolbar.
Filtering allows you to view only certain records in a form. Select the data you want to filter by and click the Filter by selection button on the toolbar. To change the filter, click the Change filter button. To set a more detailed filtering condition, select the Filter command in the Records menu and the Advanced filter subcommand. To apply a regular or advanced filter, click the Apply Filter button on the toolbar.
1.5. Data types.
The data type determines what type of data can be entered into the field. For example, entering text into a currency field is not allowed. Selecting the appropriate data type ensures that data is entered in the correct form for sorting, calculations, and other operations. Microsoft Access supports the following data types:
Text
Text or numbers that do not require calculations, such as phone numbers.
MEMO field
Long text or a combination of text and numbers.
Numerical
Numerical data used to make calculations.
Date Time
Dates and times related to the years 100 to 9999, inclusive.
Monetary
Monetary values and numerical data used in mathematical calculations.
Counter
Unique sequentially increasing (by 1) or random numbers, automatically entered when adding each new entry V
Logical
Boolean values, as well as fields that can contain one of two possible values (True/False, Yes/No).
OLE Object Field
Object (such as a spreadsheet Microsoft Excel, document Microsoft Word, picture, sound recording, or other data in binary format) linked or embedded in a Microsoft Access table.
Hyperlink
A string of letters and numbers that represents the address of a hyperlink.
Substitution Wizard
Creates a field that offers a choice of values from a list, or from a combo box that contains a set of constant values or values from another table. Selecting this option from the list in a cell launches the Lookup Wizard, which determines the field type.
Lecture 2
Data entry and editing
Data input
In table mode, all entered data is displayed. If only the table structure has been created, it means that there is no data in the table yet. In an empty table, the record marker (with the image of a triangular pointer pointing to the right) is usually set in the first row of the table.
To enter a value in a field (in table view), move the cursor to that field and type the value. While editing a post, the post marker takes on the appearance of a pencil. When you enter a new entry (the marker of which has a pencil), the next line automatically appears (the marker of which has an asterisk to indicate new-entry). The new record marker always marks the last row of the table.
When entering a new record, the cursor is usually placed in the first field of the table.
Depending on the operation being performed and the type of record, the record token can take one of four forms.
Marker of the current record.
Marker of the edited entry.
Blocked record marker (when working in
multiplayer mode).
New entry marker.
Saving a recording
An added or modified entry is automatically saved when you move to another entry or when you close the table. At the same time, the entry marker changes its shape: the pencil turns into a right-pointing arrow.
To save a record, you must enter a valid value in the primary key field. The primary key value is checked against data type, uniqueness, and other value checking rules entered in the Value Condition property ( Validation Rule )
Cancel button ( Undo Current Field / Record ), located on the toolbar, allows you to undo changes made only to the current record. After moving to the next entry, this button will return to normal undo mode. And once you change the next entry, you will no longer be able to undo the change to the previous one.
You can save the current recording on disk using the Recordings->Save Recording command ( Records -> Save Records ) or key combinations< Shift + Enter >.
Checking the correctness of the entered data
Automatic data type checking
Access checks some data types automatically, without any intervention. For fields containing these types of data , no need to fill in the properties Condition on value. Let's list these data types.
Number
Currency
Date/Time
Logical (Yes/No)
Number and Currency allow you to enter valid numeric values. Although Access allows you to enter a letter in the Number field, if you try to exit this field, a dialog box appears with the message "The value you entered does not match the Data Type or Field Size properties" The error message will also appear if you enter other unacceptable characters or multiple decimal points, or if you try to enter an excessively large number for a particular size data field of the Number type.
Date/Time fields check that date and time values are correct. If you try to enter the time 44:44:44 or just a letter in a Date/Time field, an error dialog box will appear
Logical (Yes/No) You can enter only one of the following set values: Yes, True (On) or any non-zero number for logical Yes and No (No), False (False), Off ( Off) or 0 for logical No. In the Field Format property, you can define your own Boolean (Yes/No) values, but standard values are usually used
Data Entry Methods
Data entry into fields of various types is carried out different ways. For some data types, value validation is performed automatically. However, when creating some tables, you may need to enter data in non-standard (user-defined) formats and set validity rules for them in the ValidationRule property. Next we will look at various ways data entry. |
Standard text input
The three fields entered into the “MyStudents” table were fields of type Text. In the FamNameName1 fields, an input mask is used for data entry. If you enter a value in lowercase letters, it will be displayed: the first letter is uppercase, the rest are lowercase. Text can be checked for special meanings and displayed in a specified format.
Sometimes in a field like Text ( Text ) you need to enter a value consisting of several lines. To add a new line, press the key combination< Ctrl + Enter >. This feature is useful for formatting strings in the address field as well as the Memo field.
Entering date/time data
Values are entered in the format mmm yy. This is how it will be when you enter 4/8/96 and exit this field. Access will display the value as Arg 96. The table actually stores the value 4/8/92, and you can display it by placing the cursor in this field. On the other hand, you can enter 96 in the Arg field, and the value Arg 96 will be saved in the table.
For a field of the Date/Time type, you can also define a condition on the value Between #1/1/70# AndDate(). This condition indicates that the date of birth entered must fall between January 1, 1970 and the current date.
Formats affect how data is displayed, not how much memory it takes up.
Text data entry with data verification
A text field can have a property set to Condition on the value. It limits the input data to values (eg F,M,C). If you try to enter values other than F, M, and C into this field, a dialog box will appear with a message like this: You can only enter a value for M, F, or C in this field. This message is determined by the value of the "message about" property. error" for this field.
Entering numeric data with checking the condition for the value |
Numeric fields have data validation rules defined. For the Resultat field, the Value Condition property is set to limit the evaluation value (>=2 and<=5). При нарушении этого правила появится диалоговое окно с сообщением об ошибке. Это сообщение определяется содержимым свойства сообщения об ошибке для данного поля.
OLE data entry
An OLE object field called Foto can be filled in even without the object (image) itself being displayed on the screen. The following objects can be stored in an OLE type field.
Raster images.
Sound files.
Graphs and (diagrams).
Word or Excel.
An object recognized by the OLE server can be stored in Access as an OLE type field. OLE objects are typically entered into forms so that they can be seen, heard, or used. When you place an OLE object in a table, you will see text describing the object (for example, in an OLE type field you may see PaintbrushPicture). There are two ways to enter an object.
Paste from clipboard.
Insert from the dialog box that appears after executing the Insert->Object command
MEMO data entry
The Recenzia field contains MEMO type data. This type allows you to enter up to 64,000 bytes of text for each entry. The table displays only part of the entered text. By pressing
Move through records in a table
As a rule, after entering data, it often becomes necessary to make some changes. This may be due to the following reasons:
New information received
Errors detected
Need to add new entries
To change data, first open the table. In the database window, open the table in Datasheet view by double-clicking the row with its name in the list of tables.
If you are in TableDesign mode, click the Views button to enter Table view to make changes to the data.
Navigate through entries
To go to any record, you can simply place the cursor on it or click on it with the mouse. But if the tables are very large, then the problem of quickly moving to the desired record becomes especially acute
You can use the vertical scroll bar to move through records. The arrow buttons on the scroll bar only allow you to move the record marker one position per click. Therefore, to move faster (through multiple records at once), it is better to use the scroll bar slider. You can also click the mouse in the area between the slider and the button on the scroll bar to move to many positions ahead.
The Edit->Go command provides several options for quickly moving around the table.
The five jump buttons located at the bottom of the window in Datasheet view can also be used to navigate through records. Clicking these buttons will allow you to jump to any record. If you know the record number (the row number for a given record), click on the record number field, enter entry number and press the key
As you move around the table, pay attention to the scroll bar prompts. Access won't update the record number field until you click on any field in the record.
Setpoint search
Although knowing the record number you can go to it and find a specific field, in most cases you will need to find a specific value in the record. This can be done in three ways:
Select the command Edit->Find (Edrt->Find)
Click on the FindSpecifiedText button located on the toolbar (it shows binoculars).
Use a keyboard shortcut
When using any of these methods, a dialog box will appear. To search only for a specific field, place the cursor in it (and do this before opening the dialog box). Select the SearchOnlyCurrentField check box in the dialog box, and Access will search only in the specified field.
This dialog box allows you to set various search parameters. In the FindWhat text box, enter the value you are looking for. You can enter the value as it appears in the field or using the following: special characters:
* - Matches any number of characters
Matches one character
# - Corresponds to one digit
To understand how these symbols work, suppose you want to find all values that start with AB. To do this, enter AB*
Now let's say you want to find values that end with the characters 001. In this case, enter *001 . To search for any value that starts with the characters AB, ends with the characters 001, and contains only two characters in between, you need to enter AB??001. If you need to find all last names ending in “ko”, then to search for values like Brodsky and Tchaikovsky, enter *co.
The Match drop-down list contains three choices:
With any part of the field (AnyPartofField)
Whole Fields
From the beginning of the field (StartofField)
The standard option is WholeField. For example, the value Pet will be found if the value is equal to Pet. If the With any part of the field (AnyPartofField) option is selected, then the values Petrov, Petrovsky will be retrieved as a result of the search. Shpetny, etc. As a result of the search with the setting From the beginning of the field (StartofО), the values Petrov, Petrovsky will be found.
In the Search drop-down list, you can select one or more options from the Up, Down, All set.
When you select the Only in the current field (SearchOnlyCurrentField) option button, the value will be searched in only one field. The MatchCase checkbox determines whether uppercase and lowercase letters will be distinguished. By default, they do not differ. When searching for the value Pet, pet, Petr and Petrov will be found. When you select the MatchCase checkbox, you must enter a search string that takes into account uppercase and lowercase letters
Obviously, for Number, Currency, and Date/Time data types, case sensitivity does not make sense.) When you select the MatchCase check box, Access ignores the SearchFieldsAsFormatted check box. (If you formatted the table fields, check this box) For example, you need to search the DateBirth field for records of everyone born in April 1982. To do this, check the SearchFieldsasFormatted option and enter Apr 92. If you do not check this checkbox, the search will have to be performed by exact date of birth, for example, search for 4/8/92
Using the SearchFieldsasFormatted checkbox can significantly slow down the search.
The search begins when you click the FindFirst or FindNext button. Once you find a value, Access selects it. To find the first matching value, click the Find button. To find the next value, click the Find Next button. Throughout the search process, the dialog box remains open. Therefore, once you find the value you want, click the Close button to close the dialog box
Changing Values in Datasheet View
To change a value, go to it and make the necessary corrections. There can be several reasons for editing a value.
Adding a new value.
Replacing an existing value
Changing an existing value
If the field is empty, simply enter a new value. When entering any new values in the field, follow the same rules as when entering new entries.
Replacing an existing value
You can select a field using the keyboard or mouse When using the keyboard, the contents of the field are highlighted (It is displayed in inverse form) When you press any key, the contents of the field are deleted and replaced with the symbol corresponding to the key pressed Pressing a key
Using the mouse, the contents of a field can be selected in the following ways:
Click on the left border of the field (where the pointer changes to a large plus sign)
Double-click the field (this method does not work if there is a space in the text)
Click and drag the mouse pointer across the contents of the field from left to right
Select any part of the field content and press the key
To replace an existing value with the default value from the DefaultValue property, highlight the value and press the key combination
To replace an existing value with a value from the same field in a previous entry, press the key combination<Сtrl+"(кавычки)>
Make sure you haven't clicked<С1г1+-(минус)>, since in this case the current entry will be deleted
Changing an existing value
To correct an existing value, rather than replacing the entire value, click in front of any character in that value. By placing the mouse pointer in front of a specific character, you activate insert mode, and as you enter a new value, existing values will shift to the right. After pressing the key
Editing Methods
| Moving within a field | By pressing the keys -> and<- |
| Inserting a value into a field | Select insertion position and enter value |
| Selecting entire field contents | Press F2 or double click |
| Replacing a value with a new one | Select the entire field and enter a new one |
| Replacement from previous field | Ctrl+’(apostrophe) |
| Replace with default value | Ctrl+Alt+space |
| Inserting a line break (text, Memo) | Ctrl+Enter |
| Saving tech string | Shift+Enter or move to another line |
| Inserting the current date | Ctrl+;(semicolon) |
| Insert current time | Ctrl+:(colon) |
| Adding a new entry | Ctrl++(plus) |
| Deleting an entry | Ctrl+-(minus) |
| Switching option buttons | Space |
| Cancel changes to a current record | Esc or cancel button |
Fields that cannot be edited
There are fields that cannot be edited. Types of such fields
Cancel actions
Sometimes the Undo button located on the toolbar is grayed out and cannot be used. But as soon as you start editing the record, it will become available, and you can use it to cancel the entries in the current field. The key is also used to cancel operations.
The Edit->Undo command is also used to undo operations, and The command name changes depending on the action being undone.
To cancel changing a value in a field, select the Edit->UndoTyping command or use the Undo button located on the toolbar. After moving to another field, changes made to the previous field can be undone using the Edit->Restore current field/record command or the Undo button. You can undo all changes for the current record that has not been saved using the Edit->Restore current field/record command (Edit->UndoCurrentField/Record). After saving a record, changes made to it can be undone by selecting the Edit->UndoSavedRecord command. However, once you edit the next entry, those changes cannot be undone.
Copy and paste values
Copying or deleting data to the buffer is performed by the Microsoft Windows 95/NT system itself, and not by any special Access function. A value copied or deleted from the buffer can be pasted into another field or record using the Edit->Paste command or the Paste button located on the toolbar. The operations of deleting into the buffer, copying and pasting from the buffer are used to exchange data both between various Windows applications and within Access itself. Using this method, you can copy entire records to other tables or even other databases, as well as exchange data with Microsoft Word and Excel programs.
Replacing values
The Edit->Replace menu command allows you to search and replace by pattern. After selecting this command or pressing keys < Ctrl + H > a dialog box will appear on the screen,
This dialog box is very similar to the one that appears after choosing the Edit->Find command. Only, in addition to the Sample text field (FindWhat), it also has a Replace With field. Search starts when you press a key
Adding new entries
To add a record in table view, place the cursor in the last line (where the record marker looks like an asterisk) and enter a new record. To go to a new record, you can select the command
Insert->Record ->Record), click on the New Record button located on the toolbar, use the move buttons or the Edit->Go->New Record command
(Edit->GoTo->New). You can also go to the last entry and press< | >Sometimes you need to add several new entries and make existing entries temporarily invisible Menu command
Records->DataEntry will allow you to temporarily clear the screen of all entries, and then you can edit new entries without interference. To restore all records, select the command
Records->RemoveFilter/Sort
Deleting entries
To delete an arbitrary number of entries, select them and press
The default setting for this dialog box is to select the Yes button. Therefore, when you press the key
To select adjacent (i.e. neighboring) records, click on the marker of the first record and drag the mouse pointer to the right) to the last of those records that you want to select
Add, change, or remove columns
The ability to add, remove, and rename columns in Datasheet view in Access 97 is fraught with danger. The point is that these operations change the data structure. As you know, when you make changes in TableDesign mode, the underlying data structure changes. However, in Table Design mode, you may not even realize the full consequences of the changes made. Therefore, when creating databases for other users, Care should be taken to ensure that they cannot make changes in table view.
Delete a column in Datasheet view
To delete a column in Datasheet view, select it and press
Add a Column in Datasheet View
To add a new column in Datasheet view, select Insert^Column, which will create a column to the right of the column in which the cursor is located, with the heading Field"1. You can then enter in that column's entry new data.
When you add a new column, the structure of the table changes (that is, a new field is added to it). When you save a table, Access determines the properties of the added field based on the values entered into it.
Changing the field name (column header)
When adding a new column, it is advisable to change its heading before saving the table. To do this, double-click on it and make the necessary changes. When saving the table, this title will be used as the field name in the table structure.
By changing the column header, you change the name of the field in the table. And if it is used in forms, reports, queries, macros or modules, then these objects will not work until you make the appropriate corrections to them. This method of changing the field name is fraught with unpleasant consequences, so it is advisable that only experienced users use it.
Displaying entries
You can use either the mouse or menu commands to add or change entries. Using the Format menu or mouse commands, you can change the order of fields, hide and freeze columns, change row height and column width, select a different font, and even show or remove grid lines.
Changing the order of fields
By default, Access displays fields in Datasheet view in the same order as they appear in the table or query. However, sometimes you need to place some fields next to each other to make it easier to analyze the data they contain. To change the order of fields, select a column and drag it to a new location.
You can select and drag columns one at a time or in groups. Let's say you want the Name field to be located at the beginning of the table. To do this, follow these steps:
1. Place the mouse pointer over the name of the field (column) Name (it will take the form of a downward arrow).
2. Click and hold the mouse button there. As a result, the entire Name column will be selected. Release the button.
3.Click on the name of one of the columns again and hold it down; The pointer will appear as an arrow with a frame.
Drag this column to the left edge of the table. Release the mouse button and the column will move to the beginning of the table.
This way you can move one or more selected fields to the left, to the right, or even beyond the right! howl window border.
Moving fields in Datasheet view does not change the table structure.
Changing the field width
To change the width of a field (i.e., a column), you need to set it either in the dialog box (where it is determined by the number of characters) or by dragging the right border of the column header (the mouse pointer will change to a cross with a double-headed arrow).
To change the column width, follow two simple steps:
1.Place the cursor between two column headings on their dividing line.
2.Drag the column border to the left to narrow it, or to the right to widen it.
To instantly change the width of a column based on the best fit principle (based on the longest element), you need to double-click on the right border of the column header.
Changing the column width/does not change the size of the field in the table: you simply change the dimensions of the viewed column for the data it contains.
There is another way to change the column width: you need to run the Format^ColumnWidth command or right-click on the column header and select the ColumnWidth command from the context menu. The ColumnWidth dialog box appears. Here you can ask
column width, measured in number of characters. And to define the default width, check the Standard Width checkbox.
Dragging the right border of a column to the left until it reaches the border of the previous column will hide that column. The same thing will happen if you set the value to 0 in the ColumnWidth dialog box. Therefore, to show a hidden column again, run Format^UnhideColumns.
Changing the height of a record (line)
To change the height of a record (i.e., a row), place the mouse pointer on the border of the record (in the record marker area) and drag the top border of the row, or choose Format^RowHeight. The need to increase the line height arises if you need to place text in a larger font or several rows of text.
When you drag the border of an entry, the mouse pointer will change. To change the row height, follow these steps.
1. Place the cursor on the separation line between two lines in the record marker area.
2. Drag the line border up to decrease or down to increase the line height.
Changing the row height causes the height of all rows in table view to change simultaneously.
There is another way to change the row height: select the Format^RowHeight command. A dialog box will appear in which you can enter the line height in points. To select a default height, select the StandardHeight check box.
If, when dragging the border of a record upward, it intersects with the border of a previous record, then all| the lines will be hidden. The same thing will happen if you set the RowHeight dialog box to a value close to 0 (for example, 0.1). In this case, to display the rows, run the command Format^RowHeight (Format=>RowHeight) and set the standard value for the row height.
Grid display
Typically, the screen displays a grid with lines that serve as the boundaries of rows and columns. Using the Format^Cells command, you can determine whether this grid will be displayed and how it should look. (Grid View Dialog Box (CellEffects).)
Changing the display font
Row and column sizes change automatically when you change the display font size. By default, Access displays all data in table view in 8-point MSSansSerif font and Regular weight. You may find that it doesn't print correctly since it's a screen font. Therefore, it is better to use Arial font size 8 points and Regular style. Select the command Format"=>Font(Fonnat^Font) to change the font type, size and style.
Changing the display font affects the entire table. To see more information on the screen, select a small font size. If you have the necessary equipment, switch to more a high resolution. And if you want the letters to be larger, increase the font size.
To change to a 10 point Arial font with a Bold style, follow these steps:
Select the Format^Font command and a dialog box will appear on the screen.
In the Font field, select Arial,
In the FontStyle field, select Bold.
Enter 10 in the Size field.
Click OK.
When you change a font's attributes, a sample of that font appears in the Sample area. This way, before you make any changes, you can see them. If desired, you can change the font color.
Hiding and showing columns
To hide a column, you can drag its border to the previous field or set the column width to 0. You can also use the Format^HideColumns command to hide one or more columns. To hide one column, follow these steps:
1. Place the cursor anywhere in the column that you want to hide.
2. Select Format^HideColumns and the column will disappear. In effect, the ColumnWidth property will be set to 0. To hide multiple columns, you must first select them and then use Format^Hide Columns ^ HideColumns)
A hidden column can be unhide again using the Format^ UnhideColumns command. This displays a dialog box that allows you to selectively hide or show columns by checking or unchecking the appropriate options in the Column list.
Freeze Columns
If you want to keep certain table fields always visible as you scroll, use the Format^Freeze Columns command. For example, you can make the Name field always visible as you scroll the table when you need to find other information. The frozen columns will be placed to the left of all columns. If you want to freeze several columns, then they must be adjacent (Of course, the columns can be moved first so that they are next to each other) And if you need to free these columns, select the command Format^Free all columns (Format ^ UnfreezeAllColumns).
Saving a recording
Access saves an entry when you leave it To save an entry without leaving it, press the keyboard shortcut
Sorting and filtering records in table view
When searching for a certain value, Access finds and displays the corresponding record. But if several records satisfy a given criterion, then for ease of work it becomes necessary to display only them on the screen using the sorting and filtering buttons located on the toolbar (or the Filter and Sort commands). Sort) from the Records menu), you can display on the screen only the records necessary for work, and also quickly sort them in the desired order. To sort the entire table, two sort buttons are used, and to select specific records, three filter buttons are used.
Quick sort
Sometimes you need to sort records in a specific sequence. The toolbar sort buttons allow you to sort columns in ascending or descending order. First, select the fields to sort, and then click the Sort Ascending (A-Z) or Sort Descending (Z) button -A) (SortDescending (Z-A))
To select a sort field, just place the cursor in it at the level of any record. After that, click on the corresponding button, and the data will be instantly sorted
If you want to sort data by multiple fields, select multiple columns. To do this, first select one column and then press
To restore the original order of records, use the Records^RemoveFilter/Sort command.
Printing entries
Table entries can be printed in rows and columns. Use the simplest method - select the command File"=> Print (File^Print) or click on the Print button located on the toolbar. A dialog box will appear.
Let's assume you have installed a printer on a Microsoft Windows 95/NT system. Click OK and print Wy in the font you chose for display (or its closest printer equivalent). The printout will also reflect all options set at the time the table was printed. Hidden columns will not be printed. Gridlines are printed only if the option buttons in the GridlinesShown group are checked in the CellsEffects dialog box (which you can call up using Format^Cells). Printing will require as many pages as necessary to accommodate all the data. Moreover, Access performs the division of data into pages automatically.
Print table
You can control the printing process from the Print dialog box by selecting the following options.
Print (PrintRange) Prints the entire table or only selected pages or records
Copies Determines the number of copies
Collate Determines whether the printout will be collated into copies.
The Properties button opens the printer settings dialog box. The Settings button will allow you to define fields and headers.
Preview
Sometimes some doubts arise before printing a report. For example, wouldn't it be better to change the row height and column width, or choose a different font? Therefore, before printing, it is always worth displaying the report on the screen to make sure that everything is in order with it.
To do this, click on the button located on the toolbar and select the File^PrintPreview command. As a result, a preview window will appear.
Clicking the PrintPreview button will take you to print preview mode. The screen will show the first page to print, and new buttons will appear on the toolbar.
To navigate through pages, you can use the buttons located at the bottom of the preview window (the same as when moving through records in Datasheet view).
Toolbar buttons provide fast access to the following print options:
Close Returns to table view
Print Opens a dialog box
One Page Shows one page in mode
Print Preview
Two Pages Shows two pages in mode
Print Preview
Zoom Selection Increases or decreases the display scale
document
To view more than two pages, use the command View^Number of Pages ( View ^ Pages ), and then select 1, 2, 4, 8 or 12.
If you are satisfied with the preview of the table and want to print it, click the Print button located on the toolbar. If you're confused, click the Close button and you'll be returned to Datasheet view, where you can edit the data or change the layout.
Conclusion
Techniques for entering data in table mode, moving through rows and columns and changing data, moving rows and columns and resizing them, previewing and printing tables are covered.
In table view, data is displayed as rows (records) and columns (fields).
Use scroll bars, cursor keys, menu options, and navigation buttons to quickly move around the table and place the cursor on any record or field.
You can switch to Datasheet mode from any TableDesign window by clicking the Datasheet button (you can also click the Open button in the database window).
When entering data into the table, the corresponding row is marked with a new record marker.
i. Access performs automatic data validation various types(meaning the types Number, Currency, Date/Time and Logical Yes/No). Moreover, at the table or form level, you can add your own data validation rules.
OLE objects (for example, sound, pictures, graphs, Word documents and digital video recordings) can be inserted into an OLE type field using the Insert^Object command
Use the navigation buttons (located at the bottom of the table) to quickly move through the records.
You can find and replace specified values using the Edit^Find or Edit^Replace command.
To insert a default value into the field, click<Сп«1+А11>+spacebar, and to insert a field value from a previous entry -
Data cannot be edited in some types of fields. These are fields of the Counter (AutoNumber) type, calculated, blocked, inaccessible, a field with blocked records, as well as fields from some types of requests.
The Undo function allows you to undo the entry of a field value, the current entry, or a saved entry. To delete an entry, you need to select it and press the key , or select the command Edit=>Delete (Edit^Delete)
You can change the appearance of the table by rearranging fields differently, changing the displayed column width or row height, and choosing different fonts
Columns can be hidden and shown, frozen and freed; You can also delete and display grid lines on the screen.
Using the sort buttons, you can instantly change the order in which records are displayed.
The FilterbySelection and FilterbyForm buttons are used to define the sort order or filtering criteria for records in the table.
The File^Pnnt command is used to print a table, and the File^PrintPreview command is used to preview pages before printing.
Microsoft Access 97 Wizards
Database Creation Wizard
Table Analysis Wizard
Query Wizard
Database Creation Wizard
The Database Creation Wizard helps both beginners and professionals quickly start working in Microsoft Access. You can choose from a library of more than 20 different database types to choose the one that best suits your specific purpose.
Data Import/Export Wizard
If the user already has data stored in some format other than Microsoft Access, he can use a special import/export wizard. This wizard allows you to flexibly manage your data when importing and exporting text or spreadsheet data, as well as when exporting Microsoft Access data to text files. The wizard allows you to select the correct field separators, data types, etc... The wizard includes a preview of the converted data to make it easier to control the conversion process.
Table Analysis Wizard
This wizard allows you to quickly create a relational database with several tables and relationships between them from a large “flat” data table. The wizard will analyze the existing table and suggest the best way to convert it into a database.
Query Wizard
If you need to make a selection from the available data, you can use the Query Wizard. This wizard automatically creates queries to retrieve data from one or more tables. The ability to operate with several tables, linking individual table fields in an arbitrary way, allows you to create flexible branched data structures that are easy to manage and efficient to use. In addition, the wizard allows you to group data and calculate totals, for example, you can count orders grouped by placement date.
Data sharing
A special wizard for dividing a database allows you to divide the database into two files, the first of which contains the actual tables with data, and the second contains queries, forms, macros and modules. This solves the problem of organizing the processing of one data array by several users. At the same time, users working on the network can use a common data source, change forms, reports and other objects used to process data at a specific workplace.
Solutions for Internet and Intranet
Microsoft Access 97 includes properties that allow you to publish data from Microsoft Access to a Web server. For example, HTML is included in the list of formats in which reports can be created, and it is now very easy to publish data as a Microsoft Access report on the server.
Microsoft Access has a special wizard for quickly moving information from a database to a Web server.
Microsoft Introductory Course Outline Access 97 (MSA)
For KNEU teachers
Prerequisites: Familiarity with Windows and Windows applications (Word, Excel, etc.)
1. Introduction.
1.1. Main properties of MSA.
1.1.1 Relational model (relations versus hierarchy)
1.1.2. Multi-user environment.
1.1.3. User interface.
1.2. Typical applications (examples) and brief comments.
1.2.1.Phone book
1.2.2. Database. "Library"
1.2.3. Accounting for invoices/orders.
1.2.4. Demonstration database "Borey".
1.3. MSA Database Architecture.
1.3.1. Tables are data storage.
1.3.3. Forms - a graphical interface to data.
1.3.4. Reports - data printing
1.3.5. Macros and modules - automation.
2. Creation of a database.
2.1. Database design.
2.2.Stages of creating a database.
2.3. New database.
2.4. Import data.
2.5. Saving changes
3. Table design.
3.1. Fields and records.
3.2. Field types
3.3. Default field values
3.4. Validation of input.
3.5. Input masking
3.6. Linking tables.
3.7. Primary keys.
4. Working with tables.
4.1 Formatting rows and columns.
4.2. Freezing, hiding, and arranging columns.
4.3. Quick sorting and filtering.
4.4. Setting the language version when sorting.
5. Building queries.
5.1. Using QBE to develop queries.
5.2. Sorting.
5.3 Field properties
5.4. Selection of criteria.
5.5. Counted fields.
5.6 Expression Builder.
5.7. Grouping and (sub)totals.
5.8 Field properties.
5.9. Multi-table queries (using relationships).
6. Data import.
7 Basics of forms.
7.1 Project window, tools and palette.
7.2. Basic methods of creating and editing forms.
7.3. Control and control properties window.
7.4 Form properties.
7.5. Linking forms and data sources.
7.6. Management and its binding to the data source.
7.6.1. Text box.
7.6.2. List box and combination box
7.6.3. Boxing checks.
7.7 Displaying form properties.
7.8. Using the Form Creation Wizard.
8. Main form/subform (compound form).
9 Basics of macros.
9.1. How to develop them: do not write by writing.
9.2. Overview of macros.
9.3. Auto-execute macro.
10 Control buttons in forms.
10.1 Purpose of macros.
10.2. Using the wizard to create buttons.
11. Reports.
11.1. Similarities and differences with forms.
11.2.Groups, group subtotals and group header/footer.
12. Create a simple application.
ACCESS"
in the course "Informatics"
Student of group C-12:
Teacher:
Moscow 2008
Theoretical part.
1)Databases and Database Management Systems (DBMS)
Databases (DBs) are designed for storing on a computer and effectively using large volumes of a wide variety of information:
about books, magazines and newspapers in libraries,
about goods in stores and products in factories,
about exhibits in museums and various collections,
about all employees of companies, enterprises and institutions,
about all aircraft flights at airports, ticket prices and seat availability,
about the schedule of all trains from the stations, about the cost of tickets and availability of seats,
about all cars registered with the State Traffic Safety Inspectorate (GAI), including stolen ones,
about all tours, including last-minute ones, in travel agencies and travel agencies,
about the weather anywhere in the world now and many years ago, etc.
To work with Databases, application programs called DBMS - Database Management Systems are used. The most popular DBMS is the ACCESS program from Microsoft.
2)All DBMS, including DBMSACCESSallow:
1. Quickly create Databases, supplement and change them.
2. Quickly find the specific data you need in the database.
3. Sort data in the database according to various criteria (for example, alphabetically).
4. Extract to the screen subsets of data based on your requests, i.e., those that satisfy a given criterion.
5. Perform statistical calculations on database data for analysis and decision making.
6. Print data selected for specific purposes, i.e. prepare certificates, reports, etc.
In its simplest form, a Database is a spreadsheet that looks similar to an Excel spreadsheet.
Like Excel tables, database tables consist of columns and rows. In this case, the columns in the database are called " FIELDS" and they have specific names (names " FIELDS"), and not letters of the Latin alphabet as the name as in Excel tables. There can be up to 255 such columns, i.e. fields in database tables (as in Excel).
Each row in the database tables is called " BY RECORDING" and has its own serial number, the same as a row in an Excel spreadsheet. The number of rows, i.e., records in database tables is practically unlimited.
All " RECORDS"(rows) in the database table contain in each cell necessarily the same type of information corresponding to the name" FIELDS". This is the main difference between a database table and an Excel spreadsheet. For example, in a column with field name " FULL NAME" in all records (i.e. in all lines), you will see specific surnames of people;
in the column with the field name " GODR" in all records (i.e. in all rows) you will see the specific years of birth of students;
in the column with the field name " NG" in all entries (i.e. in all rows) you will see specific student group numbers.
A database can include not one, but several related tables.
Except Tables– the main object of the Database - the database may contain other objects, namely:
Form(one or more). A form is a way to display data from database tables on the screen, convenient for entering data and viewing it.
Request(one or more). A query is a way to select and display data from database tables according to the criteria you specify.
Report(one or more). A report is a way to select and print data from database tables in the form in which they are required by the user, i.e. you.
Macro(one or more). A macro is a set of macro commands that perform certain operations with the database, for example, printing reports.
Module(one or more). A module is a program in the Access Basic programming language used in a database to perform non-trivial tasks.
All listed objects, if they were created, are part of the Database file with the extension . mdb.
3)The work of creating a multi-table database includes 4 stages:
1st stage. Designing the database structure, i.e. determining the number of Tables, their purpose and composition.
2nd stage. Designing Database Tables, i.e. specifying Fields, their types and properties for each of the Tables.
3rd stage. Creating a database schema, i.e. defining and establishing connections between database tables.
4th stage. Entering data into empty database tables.
Database structure design – 1st stage
Designing the database structure– this is the determination of the number of Tables in the database, their purpose and composition. You and I have already done this work, as we have identified 6 tables ("Student", "Group", "Department", "Study", "Achievement" and "Teachers") necessary to create the Institute Database, which allows for the admission of students , form groups in individual departments, and also take into account student performance.
Designing empty database tables - stage 2
Designing Database Tables– this is the task of Field Names in each of the Tables and the definition of Data Types and properties of each of the Fields. There are several ways (modes) for constructing Tables:
Constructor mode,
Table Mode,
Table Wizard mode.
Each of these methods has its own advantages and disadvantages. You try to use all three methods to construct the tables of the database you are creating.
In the first part of this manual, a database with the tables “Student” and “Subject” was already created.
4)Creating a Database Schema - Stage 3
Creating a Database schema is defining the relationships between the tables included in this database, or more precisely, between specific fields of these tables.
When tables are linked, changes in one of them are automatically transferred to the other. The main purpose of linking tables is to avoid duplication of information in the database.
Practical part.
The laboratory work workshop consists of 33 exercises: completed –33
I have learned Access elements such as...
Creating a simple database:
mouse" or the "Column Width" command in the "Format" menu:
Creating and using a "Query with a parameter":
Creating a new object in the Database - "Forms":
Creating and using a new database object - "Report":
https://pandia.ru/text/80/040/images/image006_30.jpg" width="623" height="499 src=">
Selecting a form type:
Independent work - test.
https://pandia.ru/text/80/040/images/image012_16.jpg" width="623" height="499 src=">
https://pandia.ru/text/80/040/images/image014_11.jpg" width="623" height="499">
Time spent on laboratory work: 140 minutes, including:
Workshop: 80 min;
Documentation: 45 min;
25.1 Theoretical information
Explore theoretical basis creating forms in the Access database, discussed in the lecture course, in the section "Reports in the Access 2007 - 2010 database" or other sources of information.
25.2. Goal of the work
Creating reports in the Access database using the Wizard and Designer.
25.3. Formulation of the problem
Initial data:
- Subject area: "Dean's office" (student performance).
- Main subject-significant entities: Students, Groups of students, Disciplines, Academic performance.
Work plan:
- Create the necessary reports to display information from the database created in Lab #23.
- Check that the reports work (for reports with parameters, use multiple values).
- Save the reports.
- Prepare a laboratory report.
25.4. Step-by-step work execution
To create a report, follow these steps:
Rice. 25.1
25.4.1. Editing a report
To edit a report, follow these steps:
- remove the student code fields in the header and data area;
- Move all fields in the header and data area to the left.
- Change the text in the page title:
- In the Report Title section, select Students.
- Place the mouse pointer to the right of the word Students so that the pointer changes to a vertical bar (the input cursor) and click at that position.
- Enter NTU “KhPI” and press Enter.
- Move the Caption. In the Footer, select the =Now() field and drag it to the Report Header under the name Students. The date will appear below the title.
- To view the report, click on the "Preview" button on the "Report Designer" toolbar.
25.4.2. Formatting a report
To format the report, do the following:
- Select the heading Students of NTU “KhPI”.
- Change the typeface, font style and color, as well as the background fill color.
- On the Report Designer toolbar, click the Preview button to preview the report.
25.4.3. Changing the report style
To change the report style, do the following:
- On the Report Designer toolbar, click the AutoFormat button to open the AutoFormat dialog box.
- In the 'Report-autoformat' object styles list, click on the Strict item and then click on the OK button. The report will be formatted in the Strict style.
- Switches to Preview mode. The report will be displayed in the style you selected. From now on, all reports created using the AutoReport function will have the Strict style until you specify a different style in the AutoFormat window.
25.4.4. Shutdown
Tell your teacher about your completed work. After allowing shutdown, close the application program Microsoft Access, after which you can begin taking tests on the completed work.
