Ms Access 简明教程

Access calls anything that can have a name an object. Within an Access desktop database, the main objects are tables, queries, forms, reports, macros, data macros, and modules.

If you have worked with other database systems on desktop computers, you might have seen the term database used to refer to only those files in which you store data.

But, in Access, a desktop database (.accdb) also includes all the major objects related to the stored data, including objects you define to automate the use of your data.

In document or a spreadsheet, you generally have complete freedom to define the contents of the document or each cell in the spreadsheet.

In a document, you can include paragraphs of text, a table, a chart, or multiple columns of data displayed with multiple fonts.

In spreadsheet, you can have text data at the top to define a column header for printing or display, and you might have various numeric formats within the same column, depending on the function of the row.

An RDBMS allows you to define the kind of data you have and how the data should be stored.

You can also usually define rules that the RDBMS can use to ensure the integrity of your data.

For example, a validation rule might ensure that the user can’t accidentally store alphabetic characters in a field that should contain a number.

In a word processing document, you can include tabular data and perform a limited set of functions on the data in the document.

You can also search for text strings in the original document and, with ActiveX controls, include tables, charts, or pictures from other applications.

In a spreadsheet, some cells contain functions that determine the result you want, and in other cells, you enter the data that provides the source information for the functions.

You can search a single table for information or request a complex search across several related tables.

You can update a single field or many records with a single command.

You can write programs that use RDBMS commands to fetch data that you want to display and allow the user to update the data.

When you need to share your information with others, RDBMS gives you the flexibility to allow multiple users to read or update your data.

An RDBMS that is designed to allow data sharing also provides features to ensure that no two people can change the same data at the same time.

The best systems also allow you to group changes (which is also known as transaction) so that either all the changes or none of the changes appear in your data.

You might also want to be sure that no one else can view any part of the order until you have entered all of it.

Because you can share your Access data with other users, you might need to set some restrictions on what various users are allowed to see or update.

Each field must have a unique name, and data type.

Tables contain fields or columns that store different kinds of data, such as a name or an address, and records or rows that collect all the information about a particular instance of the subject, such as all the information about a customer or employee etc.

You can define a primary key, one or more fields that have a unique value for each record, and one or more indexes on each table to help retrieve your data more quickly.

Running a query is like asking a detailed question of your database.

When you build a query in Access, you are defining specific search conditions to find exactly the data you want.

In Access, you can use the graphical query by example facility or you can write Structured Query Language (SQL) statements to create your queries.

You can define queries to Select, Update, Insert, or Delete data.

You can also define queries that create new tables from data in one or more existing tables.

Forms are used for entering, modifying, and viewing records.

The reason forms are used so often is that they are an easy way to guide people toward entering data correctly.

When you enter information into a form in Access, the data goes exactly where the database designer wants it to go in one or more related tables.

You can view a report on your screen before you print it.

If forms are for input purposes, then reports are for output.

Anything you plan to print deserves a report, whether it is a list of names and addresses, a financial summary for a period, or a set of mailing labels.

Reports are useful because they allow you to present components of your database in an easy-to-read format.

You can even customize a report’s appearance to make it visually appealing.

Access offers you the ability to create a report from any table or query.

Normalization is the process of organizing data in a database.

This includes creating tables and establishing relationships between those tables according to rules designed both to protect the data and to make the database more flexible by eliminating redundancy and inconsistent dependency.

To work with records from more than one table, you often must create a query that joins the tables.

The query works by matching the values in the primary key field of the first table with a foreign key field in the second table.

When you design a form or report, MS Access uses the information it gathers from the table relationships you have already defined to present you with informed choices and to prepopulate property settings with appropriate default values.

When you design a database, you divide your information into tables, each of which has a primary key and then add foreign keys to related tables that reference those primary keys.

These foreign key-primary key pairings form the basis for table relationships and multi-table queries.

To concatenate in Access, there are two different operators you can use the ampersand (&) and the plus (+) sign.

The ampersand will combine two strings into one whereas the plus sign will combine two strings and propagate NULL values, for example, if one value is NULL the entire expression evaluates to null.

The inner join

The outer join

You can choose the table that will display all rows.

You can create a Left Outer Join which will include all the rows from the first table.

You can create a Right Outer Join that will include all the rows from the second table.

When we create this query in Microsoft Access, Access is taking this relationship from the relationships we have created.

By default, it is creating what’s known as an Inner Join between these two fields, between these two tables, and that is how it is relating this information together from these two tables.

It is showing us only the matches, so when we run this query, there are a lot of other projects listed in tblProjects that do not appear as part of our records set in this query, and that is because of how these two tables are joined together, through that Inner Join, which is again, that default Join for any query.

The left column name and the right column name and the first radio button is to only include rows where the join fields from both tables are equal and that is the inner join and that is what is selected by default when creating relationships, when creating a join in the query, but, you can change it.

We have two other options as well; we can include all records from tblProjects, and only those records from tblTasks where the joined fields are equal and this one is Left Outer Join.

We have a third option, include all records from tblTasks and only those records from tblProjects where the joined fields are equal and this one is Right Outer Join.

The second screen in the wizard will ask what fields might contain duplicate information.

Typically, you will not be using your primary key field, because, again, when you designate a field in Access as a primary key, Access will not allow duplicates to be entered.

We will look at any other field or a combination of fields.

Bound forms are connected to some underlying data source such as a table, query, or SQL statement.

Bound forms are what people typically think of when they think of the purpose of a form.

Forms are to be filled out or used to enter or edit data in a database.

Examples of bound forms will typically be what users use to enter, view or edit data in a database.

These forms are not connected to an underlying record or data source.

Unbound forms could be dialog boxes, switch boards, or navigation forms.

In other words, unbound forms are typically used to navigate or interact with the database at large, as opposed to the data itself.

Objects such as labels.

Unbound or bound text boxes that you can use to add or edit or even calculate an expression.

Command buttons that perform actions like Save, Open an e-mail or Print and these buttons are also known as controls.

It is just a generic term for any object on a form or report.

There is one with horizontal tabs, one with vertical tabs — where all the tabs are aligned to the left, a vertical tabs layout where all the tabs are aligned to the right.

There is a horizontal tabs layout that has two levels to it, so if you have a lot of objects that you want to display across the top, you can make use of this.

You can have one where you have both horizontal tabs and vertical tabs, either aligned to the left or to the right.

The combo box control provides a more compact way to present a list of choices.

The list is hidden until you click the drop-down arrow.

A combo box also gives you the ability to enter a value that is not in the list.

In this way, the combo box control combines the features of a text box and a list box.

Bound controls are ones that are tied to a specific data source within your database such as a field and a table or a query.

Values can be either text, dates, number, check boxes, pictures or even graphs.

You use bound controls to display values that come from fields in your database.

Unbound controls on the other hand are not tied to a data source, and they exist only in the form itself.

These can be text, pictures or shapes such as lines or rectangles.

Just like forms, a report is made up of a variety of different sections.

You have the detail section, which is where all of your data lives for the most part.

You also will see a page header and a page footer section; these appear at the top and at the bottom of every single page in your report.

You can use functions in table properties, for example, if you want to specify a default value for a date/time field, you can use the date or the Now function to call up the current date/time information from your system, and input that value automatically.

You can also use functions in an expression when you create a calculated field, or use the functions inside form or report controls. You can use functions even in macro arguments.

Functions can be quite simple, requiring no other information to be called, or, simply reference one field from a table or query.

On the other hand, they can also get quite complicated, with multiple arguments, field references, and even other functions nested inside another function.

The Date() function is designed to return the current system date. This function does not require any function arguments or additional information. All you have to do is write the name of the function and those open and close parentheses.

There are two very similar built-in functions Time() and Now().

The Time() Function returns the current system time only and the Now() Function returns both the current system date and time.

Depending on the data that you want to track, or store, or query, you have three built-in, easy-to-use functions to help with that task.

Let us now say this query needs to be more flexible in terms of the dates it is pulling starting this week.

We do have a couple of different tasks that began this week, that are not showing up in this current list, because of our criteria. It’s looking at start dates that are equal to today or above.

The first function argument for the DateDiff function is the interval, so type “yyyy”.

The next function argument is the first date that we want to calculate by, which, in this case, will be the Birthday field.

The third function argument is whatever today’s date is.

The first argument is any expression that you want to evaluate.

The next argument stands for the true part, which can be a value or an expression returned if your first expression is true.

The last argument is what you want returned if your expression is false.

Microsoft Office Excel

Microsoft Office Access

ODBC Databases (For example, SQL Server)

Text files (delimited or fixed-width)

XML Files