Excellent notes courseware-Access Database

Access Database
Chapter 1 access Overview
Chapter 2 create a database
Chapter 3 design access tables
Chapter 4 Process Access Table Data
Chapter 5 sorting and searching for Data
Chapter 6 create a query object
Chapter 7 familiarize yourself with and use the operation to query objects
Chapter 8 reports
3
3
Chapter 1 access Overview
• Access Object
• Access startup
• Access Interface Composition
• Adjust the access interface
4
4
1.1 database and access
1. Access is a relational
Database Management System.
One-to-one and one-to-one
Multiple connections and many-to-many connections.
5
5
2.
Access
Object
Data input and loss
Output display format Control
Set of operation commands
Each operation is specific
Function
Save data in the database,
Is the core of the database
Root data from some data tables
The request criteria require specific Extraction
Information
Query records in a table
Display or print
Use Visual Basic
Application
Compiled Functions
Allows users to view, edit, and
Reports that reside in the browser
Data and HTML
Page
6
6
Macro/Module
Form query report
Table
Data Stream
Control Flow
Relationship between objects in access
7
7
1.2 start and enter Access
1. install access
2. Start access
There are three methods:
• "START" → "Program" → "Microsoft Access" on the taskbar"
• In the "Resource Manager" application, double-click any. MDB
Access database file with suffix
• Choose Start> document on the taskbar and select the database to open.
File
8
8
3. access interface composition
Title Bar
Status Bar
Toolbar
Menu Bar
Opened database subwindow
9
9
Chapter 2 create a database
• Database creation method
• Open a database
• View Databases
• Database Operations
• Set the Database Password
10
10
Create a database
There are two methods:
• Create an empty database, and then add tables and windows
Objects such as body, query, and report
• Use the Database Wizard. You can select
Tables, forms, and reports required for creating database types
And so on
11
11
Open Database
There are multiple ways to open a database:
• Open
• Read-Only
• Exclusive
• Exclusive read-only mode
12
12
View Database
1. view database objects in the Database window
2. Change the appearance and attributes of database objects
• Change the appearance of database objects (large icon, small icon,
List)
• Change the attributes of database objects (hide/hide)
3. Compress and restore Databases
13
13
Set Database Password
The main steps are as follows:
1. Shut down the database
2. Copy a backup for the database and store it in a secure
Local
3. Open the database exclusively
4. Select "Tools"> "security"> "number of settings" in the menu bar.
Database Password command
14
14
Operate Database Files
1. Rename the database file
2. Copy and move database files
3. delete database files
15
15
Chapter 3 design access tables
• Create a table object
• Modify Table Structure
• Delete, rename, and copy table objects
• Table relationships
16
16
Two views of a table
• Data Table View
Displays the record values of table objects. You can view and edit records.
You can also delete or add fields.
• Table Design view
Displays the table object structure, without table data content.
17
17
Switching between two views
1. In the database window, select a table and click the OPEN button or
Double-click the table name to go to the data table view. Click design and press
Button to go to the table design view
2. In the data table view or table design view, click
The first button enables mutual conversion.
3. In the "Create Table" dialog box, click the corresponding option to enter
Two views
18
18
Create a table object
Access provides six types of table creation:
(Create a table object while creating a database
(Use the designer to create a table
(Use the table wizard
(Directly input data to create a table
(Create a table object by importing external data.
(Create a table by using access to generate a Table query object.
Object
19
19
Table Structure
• Field name
• Data Type
• Description
• Field Properties
20
20
Data Type
The remarks can contain up to 64000 characters of long text and numbers.
The size is 1, 2, 4, or 8 bytes. When number
When the word type is "synchronous replication ID", it is large
Up to 16 bytes
Numeric data that can be used for arithmetic computation, but gold
Except for money data
Number
8 bytes of date/time Date and Time
The currency value is 8 bytes.
4 bytes. When the number type is "synchronous replay"
The size is 16 bytes.
Automatic serial number is automatically added when a record is added
Yes/No these fields only contain one of the two values
A combination of text or numbers can contain a maximum of 255 characters.
Number that does not need to be calculated
Text
The maximum size of an OLE object created using the OLE protocol is 1 GB.
Object size used for data types
The length is the same as that of the primary key field.
4 bytes
Create a field that allows the use of a combo
To select values in another table or list.
View wizard
The name of the hyperlink field that stores the hyperlink cannot exceed 64000 characters.
21
21
Field attribute description (1)
• Field Size
Determine the field length (select the value type from the drop-down menu)
A value determines the type of the number stored in this field ).
• Format
Determines how data is printed and displayed on the screen.
.
• Decimal places
Select"
Number"
Type or"
Currency"
Data Type, configurable
"
Decimal places"
Attribute, which affects the data display mode.
The precision of the stored values.
22
22
Date/time
Data Type example
• Settings
Display
• DDD "," Mmm D "," YYYY Mon, Jun 2, 1997
• Mmmm dd "," YYYY June 02,199 7
• "This is week number" ww
• This is week number 22
• "Today is" dddd today is Tuesday
23
23
Field attribute description (2)
• Input mask
Set a specific format or to write a database application
Special provisions.
• Title
Used to identify"
Data Table"
Fields in the view can also be identified
Body and fields in the report.
• Default Value
The new record is automatically displayed as a field when it is added to the table.
Can be any value that matches the Data Type of the field.
.
24
24
• Input mask
Example Value
• (000) 000-0000 (206) 555-0248
• (999) 999-9999 (206) 555-0248
• () 555-0248
• (000) AAA-AAAA (206) 555-tele
• #999-20 or 2000
•> L ???? L? 000l0 greengr339m3
• May R 452b7
•> L0l 0l0 t2f 8m4
• 00000-9999 98115-
• 98115-3007
•> L <?????????????? Maria or Brendan
• SSN 000-00-0000 SSN 555-55-5555
•> LL00000-0000 DB51392-0493
25
25
Field attribute description (3)
• Validity rules
Allow users to enter an expression to limit accepted incoming words
Segment value.
•
Valid text
You can enter a text segment.
When setting a validity rule: Access
Automatically display this section
Text.
• Required Fields
Select"
Yes"
, The user is required to enter a value for this field, select
Select"
No"
This field can be empty without entering any content.
26
26
Field attribute description (4)
• Index
Determines whether to index A field. You can add
Fast query, search, and sort operations. If both the index and master
Key. The display record is displayed based on the fields specified by the primary key.
Yes (with duplicates): fields will be indexed and can be recorded more
Enter the same value.
Yes (no duplicates): fields will be indexed and cannot be recorded more
Enter the same value.
None: The field is not indexed.
27
27
Use the Wizard to create a table
Table creation steps:
• Click "table" and "create table using wizard ".
• Select sample tables and fields
• Set primary keywords
• Establish a relationship with other table objects in the database
(It does not matter by default)
• Select whether to modify the table structure, directly input data, or use
Export the created form object to input data to the table
28
28
Use the designer to create a table
Table creation steps:
• Click the "table" button and the "Create Table with designer" list item
• Input field name
• Data Type of input fields
• Input description (optional)
• Define field attributes
• Define primary keywords
29
29
Create a table using input data
Table creation steps:
• Click "table" and "create table through input data ".
• Modify the names of fields to be used
• Enter values of records and fields one by one
• Save
30
30
Modify Table Structure
You can click the corresponding button in the toolbar or click "edit" in the menu bar.
And insert.
• Insert Fields
• Delete a field
• Move Fields
• Copy Fields
31
31
Delete, rename, and copy table objects
1. delete a table object
2. rename a table object
3. Copy a table object
32
32
Create Table relationships
To establish a link, follow these steps:
1. Open the database file and click "Link" on the toolbar.
2. In the displayed table dialog box, select the table tab and select
Click Add or double-click the table name,
Add the table with the required relationship
3. Drag the mouse to establish a link.
33
33
Edit Link
• Join type
• Integrity of reference
34
34
Definition integrity
• Complete implementation references
-No associated records in the master table cannot be added to the relevant table;
-It is forbidden to change the value of the joined field of the primary table, which causes no matching in the relevant table.
;
-When a related table has a related record, the record cannot be deleted from the master table.
• Cascading Deletion
Deletes the primary table record and automatically deletes all related records in the relevant table.
• Cascading update
When the primary table record is changed, all related records of the relevant table are automatically changed.
35
35
Conditions for implementing integrity of reference
• The matching fields in the primary table must be primary keys.
• The External Field Value in the relevant table must be the master of the master table.
Existing values in the key field
• The related fields in the two tables must have the same data class
Type and the same field size
• The two tables must belong to the same access database.
36
36
Chapter 4 Process Access Table Data
• Input data
• Copy or move data
• Modify data
• Delete data
37
37
Chapter 5 sorting and searching for Data
• Data Sorting
• Filter data
• Freeze and hide Columns
• Search for and replace Data
38
38
Sort
Sorting is based on one or more fields specified in advance.
The content of the value, which duplicates the record set in a specific order.
New sorting. Sorting can be performed in alphabetical order,
Number size, date, or specific condition. Sorted
Type in ascending or descending order
39
39
Sort type
• Simple sorting
Records are arranged in the order of certain fields.
1. Both are in ascending or descending order. The two cannot be performed simultaneously. 1.
In the data table, form, or query view.
• Complex sorting
Sort by some fields in different order. Complex sorting
The order is generally in the "query design" view or in the "advanced filtering/sorting" window.
.
40
40
Simple sorting
Note:
1. Set simple sorting in the "data table ".
Fields. You can also select multiple adjacent fields.
2. If you set simple sorting in the form, you can only press
Use a field.
41
41
Complex sorting
Used to sort multiple non-adjacent fields with different sorting methods.
The procedure is as follows:
1. Open "data table" (or "query" or "form ")
2. Select "record"> "filter"> "Advanced filtering/sorting" on the menu bar.
"," Filter "window appears
3. Select the fields or sorting methods to be sorted in "filter ".
4. Click the "application filtering" button on the toolbar or select the menu bar.
"Filter" → "application filtering/sorting"
5. Close the "filter" window and save the sorting.
42
42
Filter data
Filtering is to remove records that do not meet the conditions specified by the filter.
The process of recording the criterion.
There are four types of filter records:
(Filter by selected content
(Filter by form)
(Filter targets by input
(Complex filtering
43
43
Filter by selected content
Filter the data of the specified fields and the selected content.
Matched records.
Target objects: forms, subforms, and data tables
44
44
Filter by Form
Select
Data of fields.
45
45
Filter targets by input
Enter a criterion directly in the form or data table view.
To filter records
46
46
Complex Filtering
Obtain the required discipline by setting the criterion.
. The procedure is as follows:
• Choose "record"> "filter"> "Advanced filtering/sorting" on the menu bar
"
• Fill fields used to filter records in the grid
• If You Want To specify the sorting order of a field, you can select
• Enter the value to be searched in the "criterion" cell of the field
Or expression
• Application Filtering
47
47
Freeze or hide a column
• Freeze Columns
Fix a column on the screen without moving it
• Hide Columns
Yes. A column is not displayed on the screen.
48
48
Search and replace Data
• Search for Data
• Replace data
49
49
Chapter 6 create and select a query object
• Basic Concepts
• Create a simple selection query object
• Create a comprehensive query object
• Create a parameter query object
• Create a cross tabulation query object
• Create a duplicate search object
• Create an unmatched search object
50
50
Query type
Query object
SELECT query
Operation Query
Simple selection of query objects
Cross tabulation query object
Parameter query object
Generate a Table query object
Append query object
Delete query object
Update query object
51
51
Create simple SELECT query object
1. Use the designer
Create SELECT query object for single table data selection
SELECT query object for creating multiple table data
2. Use the wizard
Create SELECT query object for single table data selection
SELECT query object for creating multiple table data
52
52
Query corresponding SQL statements
Each selected query object corresponds to a select
To view the query object designed by the designer
For the corresponding SQL statement, click View in the toolbar and press
Button → "SQL View" command. Or in the SQL View
Write SQL statements directly.
53
53
Calculated Field
The fields in the selected query object can be
Fields of the source table object can contain expression classes.
Type, such as sum and mean.
In the design view of the query table object
Enter the field name and expression in the field column.
54
54
Create a comprehensive query object
The integrated query object is a special choice for query
Such as, sometimes do not care about the data of each record in the table,
It is concerned with the statistical value of a field.
Comprehensive query instances include:
Simple grouping data statistics
Complex grouping data statistics
55
55
Create a parameter query object
Access allows you to use
Replace one variable or parameter. Parameters are enclosed in brackets
For example, "[name]".
You can enter multiple parameters for a query object.
Duplicate names can be used.
56
56
Create a cross tabulation query object
The crosstab chart organizes data in rows and columns.
You can easily view and compare data.
Note:
The data source of the crosstab chart can only be one table.
When you use fields in multiple tables, you must first query the cross tabulation.
Use multiple tables to create a simple query.
Fields of interest in the table are concentrated in a query, and then
Query to create a cross tabulation query.
57
57
Use the Wizard to create a cross tabulation Query
Steps:
• Select a query object using a table or a previously generated query object
• Select the row title. The so-called row title is in
Field Names in one column. If there are multiple row headers
Name of the split field placed after the first column (maximum
Three)
• Select the column title. The column title is the row title.
Split titles after Column
• Select the number calculated from the intersection of each column in each row
• Name, complete.
58
58
Create a duplicate search object
Used to find repeated records or fields in a table object
The number of values depends on the query result.
Whether there are duplicate or confirmed records in the table
Whether to share the same field value.
59
59
Create an unmatched search object
Used to find records that do not match two table objects. The procedure is as follows:
1. Open the data window containing table objects and click "query.
2. Click "new ".
3. Select the items in the "Search for unmatched items query wizard" list.
4. In the dialog box, select a table as the table pair for which you want to find the unmatched items.
Image
5. select another table as the table object containing related records.
6. Select the two table fields as the matching fields.
7. display all records that do not match in the corresponding data table View
Recording
60
60
Chapter 7 familiarize yourself with and use the operation to query objects
• Delete a query object
• Append the query object
• Update the query object
• Generate a Table query object
61
61
Delete query object
Deleting a query can delete many records in a table at a time.
. The delete query is often used as a record in the source table.
Add these records to the target table from the source table
Once deleted.
62
62
Update query object
You can modify one or more update queries at a time.
A group of records in table objects. Note when updating
Limits defined on the table.
Note: Each update query changes the corresponding
Data table.
63
63
Append query object
Append query refers to the selection of one or more table objects
Add a group of records that compose certain conditions to another
Or the end of multiple table objects.
There are roughly three situations for logging to the target table in the source table:
• Completely consistent data structure
• Roughly consistent data structures
• There is a big difference in the data structure, and only some fields can be
Add content
64
64
Generate a Table query object
To generate a Table query object
Create a new table
Image.
65
65
Chapter 8 Form objects
• Basic Concepts
• Create a form object
• Print the form
66
66
Form Style
The form can be input, edited, browsed, and typed
The database object of the printed data.
The form can have the following patterns:
• Vertical column table
• Data Tables
• Tables
• Adjustment table
67
67
Three Views of the form
There are three forms of views in the form:
• Design view
Consists of the form end, form footer, subject, page end, and page
Surface and footer.
• Form View
It consists of the end of the form, the body, and the form footer.
• Data Table View
Table object or query object corresponding to the Data Source selected in the form
.
68
68
Design view
• Form header: the place where the form title is displayed, or the form can be placed.
And other forms that can be linked or the command to print the form
Button.
• Form footer: used to display forms, command buttons, or add uncombined Widgets
Location.
• Page header: displays various labels at the top of each page under the form header.
Question and other descriptive information, which only appears on the header of the printed form, in the window view
The image is invisible.
• Page footer: usually used to display page numbers and other required information.
Form.
• Subject: all regions that contain fields.
69
69
Form Control
* Controls are used to display data on forms or reports,
Basic Object for performing operations or displaying information
* Controls are classified into three types:
• Combined type
• Unbound
• Computing type
70
70
Create a form
You can create a form object in three ways:
• Use the "Automatic form function"
• Use the designer
• Wizard
71
71
Create a form object using the wizard
Steps:
1. Select the Form Wizard to work
2. Select data sources and fields (multiple tables are allowed)
3. Select Layout
Vertical Column table and adjustment table: You can only view, edit, and input tables at a time.
A record, but clear and beautiful
Data Tables and tables: You can view, edit, and enter multiple data tables at the same time.
Record.
4. Select a background Style
5. Complete the creation of the form
72
72
Create a subform
* A subform is a form in a form. Basic Form Main Window
Body. The form in the main form is a subform.
* Three methods for creating subforms:
• Create both the main form and subform
• Create a subform and add it to an existing form
• Add existing forms to another existing form,
To create a form with a tape form
73
73
Pop-up form
* The pop-up form is used to display information or prompt the number of user inputs.
Data.
* There are two types of pop-up forms:
• Exclusive mode: No unless this form is closed or hidden
Access other objects or menu commands.
• Non-exclusive: access to other objects or menu commands.
74
74
Create a pop-up form
The procedure is as follows:
1. Create a form object to be used as a pop-up form
2. Open the newly created design view
3. Set the form attribute to pop-up
4. Save
75
75
Define the macro or event process for opening a form
* A macro is a collection of multiple or one operation. Enable Automatic task
Complete.
* The event process is an automatic execution process.
76
76
Create a chart form
The chart form intuitively shows the number of tables or query objects.
Data Change process. Create a chart form using the wizard
As follows:
1. Click the drop-down button of the "new object" button on the toolbar and click
"Form"
2. Select chart wizard
3. Select the chart type and set the axis, series, and data
4. Set the title
5. The created chart control is displayed in the design view.
77
77
Print form
When printing a form, the page header and page of the form
The content of the control inserted in the surface and footer is
The top and bottom of a page are displayed.
The
The control content is displayed at the top and the most
The bottom of the next page is displayed.
78
78
Chapter 9 controls
• Basic Concepts
• Insert controls
• Edit controls
• Delete controls
79
79
Insert Control
* Insert tags
* Insert a text box
* Insert the check box, switch button, and ticket
* Insert a sequence group
* Command button
* Insert a list box and a combo box
* Insert an image
* Insert unassociated object boxes and associated object boxes
* Insert a paging character.
* Insert tab
* Draw a line or rectangle on a form
* Insert ActiveX Control
80
80
Edit and delete controls
• Selected controls
• Adjust the position and size of a group of controls
• Set the control tab key order
• Modify Control Properties
81
81
Chapter 10 reports
• Create a report
• Sorting and grouping of records
• Use the computing total in the report
• Use subreports
• Create a multi-column list
• Preview and print
82
82
Workspace of the report
• Report header: contains the title information of a report and
Controls are similar to expressions in a text dialog box.
• Page header: contains the messages to be printed on each page
. For example, the column title.
• Subject: displays records from tables or queries.
• Group injection: refers to the substatistical item, which is often used as an expression.
• Page footer: Used for the text or expression to be printed on each page
. Such as the page number.
• Report footer: used to contain only the entries displayed at the end of the report.
. For example, the overall summary information.
83
83
Reports
Change the data in the data table
Output as a table
Report
Table
Window
Port
Of
Group
Cheng
Report Header
Page header
Subject (from
Table data)
Page footer report footer
84
84
Whether to modify the report
Group records?
Determine top, bottom, title, page
Code
Order of records in the report
Sorting
Create your own report
Fields included in the Report
Confirm Data Source
85
85
How to create a report
Report Wizard
Automatic Report
Chart wizard
Tag wizard
Based on the wizard
Shows how to create a report
Design view
No wizard prompt required
Automatic Report Creation
In tables and queries
Information Production chart
Create
Tag Printing
Name report
Customized reports
86
86
Sorting and grouping of records
• Sort records in reports
• Group records in reports
Concept: A group shares a public information (such as a region)
.
A group report is displayed as a group (such as a province or a city ).
And the guidance information and summary information of each group.
87
87
Sort and group operations
• Change the order of sorting and grouping
• Insert other sorting, grouping fields, or expressions into the report
• Delete sorting, grouping fields, or expressions in a report
• Put a group of data on the same page or column in a report
• Create a new row or column for each group of data
88
88
Use the computing total in the report
• Calculate the sum of a record
• Calculate the total or average values of all or a group of records
• Calculate percentage
• Number of records
• Count records in each group
89
89
Use subreports
1. Create a subreport
You can create a subreport using either of the following methods:
• Create a New subreport in an existing report
• Add an existing report to another report
Form a subreport
2. link the main report and subreport
90
90
Create multi-column list
Multi-column lists are output in the form of multiple columns on the same page
Record Content. The method is as follows:
• Open the report to be set
• Click "file"> "page settings"
• Set "column" → "Grid Settings"
Where:
Column: Enter the number of columns required for each page
Line spacing: Fill in the vertical distance required between each record in the "subject" section.
Column Spacing: Enter the required distance between columns.
• Set the "column size" option
• Set page options
91
91
Preview and print
• Preview a report
• Print reports
92
92
Chapter 2 macros
• Macro and macro Group Creation
• Macro execution and debugging
93
93
Create a macro
A macro is a set of one or more operations. Each operation
To implement a specific function. Using macros can make some
Service is automatically completed. To create a macro, follow these steps:
• Click "macro"> "new" in the "Database" window.
• Select the operation to perform
• Set operation instructions
• Operation Parameters
• Multiple operations can be added
• Save
Note: It is best to set parameters in order. Current Parameter Selected
It will limit the selection of the following parameters to make the range selected later more
Small.
94
94
Create a macro Group
A macro group is a collection of related macros. So that more
The operation is executed together. To create a macro group, follow these steps:
• Click "macro"> "new" in the "Database" window.
• Press the "macro name" button
• Add macro names and operations
• Multiple macro names can be added
• Save
95
95
Set execution Conditions
You can set the conditions under which macro operations are executed. Set
The conditions are as follows:
• Press the "condition" button
• Set conditions in the condition bar
Note: SQL expressions cannot be used in the condition bar.
96
96
Macro execution
There are multiple ways to execute macros:
• From the macro window, click the "execute" button to execute the macro
• In the database window, double-click the corresponding macro name to execute the macro
• Execute Macros in the menu
• Execute from forms, reports, or controls
When specifying a macro in a macro group, the structure is: [Macro group name. macro name]
• Execute macros from other macros or vbprograms
Use runmacro to specify the macro to be executed
97
97
Debugging macros
The macro debugging method is as follows:
• Open the corresponding macro
• Press the "One step" button
• Click the "execute" button to perform each operation in one step
98
98
Chapter 4 modules
• Create standard modules
• Event creation process
• Forms programming example