The slide master is an element of the design
template that stores information about the template, including font styles,
placeholder
sizes and positions, background design, and color
schemes.
Font styles for title, body,
and footer text
Placeholder positions for
text and objects
Bullet styles
Background design and color
scheme
The slide master's purpose is to let you make a global change— such as
replacing the font style— and have that change reflected on all the slides
in your presentation.
You would typically go to the slide master to do the following:
- Change the font or bullets
- Insert art— such as a
logo— that you want to appear on multiple slides
- Change placeholder
positions, size, and formatting
To see the slide master, you display master view. You can make changes to
the slide master just as you would change any slide, except remember that the
text on the master is only for styling; actual slide text, such as titles and
lists, should be typed on the slide in normal view or, for headers and footers,
in the Header and Footer dialog box.
When you change the slide master, changes you have made to individual slides
are preserved.
A slide master is added to your presentation when you apply a design
template. Usually, the template also contains a title master, on which you can
make changes that apply to slides with a Title Slide layout.
You can print your entire presentation— the slides, outline, notes
pages, and audience handouts— in color, grayscale, or pure black and
white. You can also print specific slides, handouts, notes pages, or outline
pages.
Microsoft Excel
Change the color
1.
Click
the ink (ink: The writing or drawing strokes made with
a tablet pen or mouse.).
2.
On
the Drawing toolbar (toolbar: A bar with buttons and options that
you use to carry out commands. To display a toolbar, click Customize on
the Tools menu, and then click the Toolbars tab.),
click the arrow next to Line Color 
, and then click the color
you want to use.
Change Font
Change Size
Formatting Rows and Columns
Wrap text
- Select the cells you want
to format.
- On the Format menu,
click Cells, and then click the Alignment tab.
- Under Text control,
select the Wrap text check box.
Align Text
- Select the cells you want
to reposition. To center or align data that spans several columns or rows,
such as column and row labels, first merge a selected range (range: Two or more cells on a sheet. The cells in
a range can be adjacent or nonadjacent.) of cells and then
select the merged
cell (merged cell: A single cell that is
created by combining two or more selected cells. The cell reference for a
merged cell is the upper-left cell in the original selected range.)
for repositioning.
- Do one of the following:
Center, left-align, or
right-align data
On the Formatting toolbar (toolbar: A bar with buttons and options that you use
to carry out commands. To display a toolbar, click Customize on the Tools
menu, and then click the Toolbars tab.), click the
appropriate button.
- Click Align Left
, to align text to
the left.
- , to center text.
- Click Align Right
, to align text to
the right.
To restore the default alignment, select the cells,
click Cells on the Format menu, and then click the Alignment
tab. In the Horizontal box, click General.
Increase or decrease indented
text from the left edge of a cell
On the Formatting toolbar (toolbar: A bar with buttons and options that you use
to carry out commands. To display a toolbar, click Customize on the Tools
menu, and then click the Toolbars tab.), click Increase
Indent or Decrease Indent .
4.
On the Format menu, click Cells, and then
click the Alignment tab.
5.
Do one of the following:
- To indent text
from the left, right, or both sides of the cell, in the Horizontal
box, click Left (Indent), Right (Indent), or Distributed
(Indent). Then, in the Indent box, click the amount of indent
you want.
- To indent text
from the top, bottom, or both top and bottom of the cell, in the Vertical
box, click Top (Indent), Bottom (Indent), or Distributed.
Then, in the Indent box, click the amount of indent you want.
- To horizontally
justify text that is distributed in a cell (indented from both sides),
in the Horizontal box, click Justify.
- To horizontally
justify text that is not distributed in a cell, in the Horizontal
box, click Distributed (Indent), and then click Justify
distributed.
- To vertically
justify text in a cell, in the Vertical box, click Justify.
Align data at the top,
center, or bottom of a cell
6.
On the Format menu, click Cells, and then
click the Alignment tab.
- In the Vertical box, click the
option you want.
8.
On the Format menu, click Cells, and then
click the Alignment tab.
- In the Orientation box, click a
degree point, or drag the indicator to the angle you want.
To display text vertically from top to bottom,
click the vertical Text box under Orientation.
Merge or split cells or data
Spread the content of one
cell over many cells
Text spread and centered over multiple cells
Warning Microsoft Excel places only the upper-leftmost
data in the selected range (range: Two or more cells on a sheet. The cells in a
range can be adjacent or nonadjacent.) into the resulting merged
cell (merged cell: A single cell that is created
by combining two or more selected cells. The cell reference for a merged cell
is the upper-left cell in the original selected range.). If there is
data in other cells, the data is deleted.
- Copy the data you want
into the upper-leftmost cell within the range.
- Select the cells you want
to merge.
- To merge cells in a row or
column and center the cell contents, click Merge and Center on the Formatting
toolbar (toolbar: A bar with buttons and options that you
use to carry out commands. To display a toolbar, click Customize on
the Tools menu, and then click the Toolbars tab.).
You can change the text alignment in the merged cell by clicking Align
Left , Center , or Align Right on the Formatting
toolbar. To make other changes to the text alignment, including the vertical
alignment, use the options on the Alignment tab (Cells command, Format
menu).
Viewing the worksheet
Page Setup
Setting up print area
Preview a page before printing
- On the File menu,
click Print Preview.
- Use the buttons on the
toolbar to look over the page or make adjustments before printing.
About filtering
Filtering is a quick and easy way to find and work with a subset of data in
a range. A filtered range displays only the rows that meet the criteria (criteria: Conditions you specify to limit which
records are included in the result set of a query or filter.) you
specify for a column. Microsoft Excel provides two commands for filtering
ranges:
- AutoFilter, which
includes filter by selection, for simple criteria
- Advanced Filter, for
more complex criteria
Unlike sorting, filtering does not rearrange a range. Filtering temporarily
hides rows you do not want displayed.
When Excel filters rows, you can edit, format, chart, and print your range
subset without rearranging or moving it.
When you use the AutoFilter command, AutoFilter arrows appear to the right of the
column labels in the filtered range.
Unfiltered range
Filtered range
Microsoft Excel indicates the filtered items with blue.
You use custom AutoFilter to display rows that contain either one value or
another. You can also use custom AutoFilter to display rows that meet
more than one condition for a column; for example, you might display rows that
contain values within a specific range (such as a value of Davolio).
The Advanced Filter command can filter a range in place like the AutoFilter
command, but it does not display drop-down lists for the columns. Instead, you
type the criteria (criteria: Conditions you specify to limit which
records are included in the result set of a query or filter.) you
want to filter by in a separate criteria range above the range. A criteria
range allows for more complex criteria to be filtered.
Multiple conditions in a single
column
If you have two or more conditions for a single column, type the criteria
directly below each other in separate rows. For example, the following criteria
range displays the rows that contain either "Davolio,"
"Buchanan," or "Suyama" in the Salesperson column.
|
Salesperson |
|
Davolio |
|
Buchanan |
|
Suyama |
One condition in two or more
columns
To find data that meets one condition in two or more columns, enter all the
criteria in the same row of the criteria range. For example, the following criteria
range displays all rows that contain "Produce" in the Type column,
"Davolio" in the Salesperson column, and sales values greater than
$1,000.
|
Type |
Salesperson |
Sales |
|
Produce |
Davolio |
>1000 |
One condition in one column
or another
To find data that meets either a condition in one column or a condition in
another column, enter the criteria in different rows of the criteria range. For
example, the following criteria range displays all rows that contain either
"Produce" in the Type column, "Davolio" in the Salesperson
column, or sales values greater than $1,000.
|
Type |
Salesperson |
Sales |
|
Produce |
|
|
|
|
Davolio |
|
|
|
|
>1000 |
One of two sets of conditions
for two columns
To find rows that meet one of two sets of conditions, where each set
includes conditions for more than one column, type the criteria in separate
rows. For example, the following criteria range displays the rows that contain
both "Davolio" in the Salesperson column and sales values greater
than $3,000, and also displays the rows for salesperson Buchanan with sales
values greater than $1,500.
|
Salesperson |
Sales |
|
Davolio |
>3000 |
|
Buchanan |
>1500 |
More than two sets of
conditions for one column
To find rows that meet greater than two sets of conditions, include multiple
columns with the same column heading. For example, the following criteria range
displays sales that are between 5,000 and 8,000 in addition to sales that are
less than 500.
|
Sales |
Sales |
|
>5000 |
<8000 |
|
<500 |
|
Conditions created as the
result of a formula
You can use a calculated value that is the result of a formula (formula: A sequence of values, cell references, names,
functions, or operators in a cell that together produce a new value. A formula
always begins with an equal sign (=).) as your criterion. When you
use a formula to create a criterion, do not use a column label for a criteria
label; either keep the criteria label blank or use a label that is not a column
label in the range. For example, the following criteria range displays rows
that have a value in column C greater than the average of cells C7:C10.
|
|
|
=C7>AVERAGE($C$7:$C$10) |
Notes
- The formula you use for a
condition must use a relative
reference (relative reference: In a formula,
the address of a cell based on the relative position of the cell that
contains the formula and the cell referred to. If you copy the formula,
the reference automatically adjusts. A relative reference takes the form
A1.) to refer to the column label (for example, Sales) or the
corresponding field in the first record. All other references in the
formula must be absolute
references (absolute cell reference: In a
formula, the exact address of a cell, regardless of the position of the
cell that contains the formula. An absolute cell reference takes the form
$A$1.), and the formula must evaluate to TRUE or FALSE. In the
formula example, "C7" refers to the field (column C) for the
first record (row 7) of the range.
- You can use a column label
in the formula instead of a relative cell reference or a range name. When
Microsoft Excel displays an error value such as #NAME? or #VALUE! in the
cell that contains the criterion, you can ignore this error because it
does not affect how the range is filtered.
- When evaluating data,
Microsoft Excel does not distinguish between uppercase and lowercase
characters.
About data tables
Data tables are part of a suite of commands sometimes called what-if
analysis (what-if analysis: A process of changing
the values in cells to see how those changes affect the outcome of formulas on
the worksheet. For example, varying the interest rate that is used in an
amortization table to determine the amount of the payments.) tools.
A data table is a range of cells that shows how changing certain values in your
formulas (formula: A sequence of values, cell references, names,
functions, or operators in a cell that together produce a new value. A formula
always begins with an equal sign (=).) affects the results of the
formulas. Data tables provide a shortcut for calculating multiple versions in
one operation and a way to view and compare the results of all of the different
variations together on your worksheet.
One-variable data tables For example, use a
one-variable data table if you want to see how different interest rates affect
a monthly mortgage payment. In the following example, cell D2 contains the
payment formula, =PMT(B3/12,B4,-B5), which refers to the input cell B3.
Two-variable data tables A two-variable data table
can show how different interest rates and loan terms will affect the mortgage
payment. In the following example, cell C2 contains the payment formula,
=PMT(B3/12,B4,-B5), which uses two input cells, B3 and B4.
Data table calculations Data tables recalculate whenever a worksheet
is recalculated, even if they have not changed. To speed up calculation of a
worksheet that contains a data table, you can change the Calculation
options to automatically recalculate the worksheet but not data tables.
Data Validation
About Subtotals
Microsoft Excel can automatically calculate subtotal and grand total values
in a list (list: A series of rows that contains related data or a
series of rows that you designate to function as a datasheet by using the
Create List command.). When you insert automatic subtotals, Excel
outlines the list so that you can display and hide the detail rows for each
subtotal.
To insert subtotals, you first sort your list so that the rows you want to
subtotal are grouped together. You can then calculate subtotals for any column
that contains numbers.
If your data isn't organized as a list, or you only need a single total, you
can use AutoSum instead of automatic
subtotals.
Subtotals Excel calculates subtotal values with a summary
function (summary function: A type of calculation
that combines source data in a PivotTable report or a consolidation table, or
when you are inserting automatic subtotals in a list or database. Examples of
summary functions include Sum, Count, and Average.), such as Sum
or Average. You can display subtotals in a list with more than one type
of calculation at a time.
Grand totals Grand total values are derived from detail
data (detail data: For automatic subtotals and
worksheet outlines, the subtotal rows or columns that are totaled by summary
data. Detail data is typically adjacent to and either above or to the left of
the summary data.), not from the values in the subtotal rows. For
example, if you use the Average summary function, the grand total row
displays an average of all detail rows in the list, not an average of the
values in the subtotal rows.
Automatic recalculation Excel recalculates subtotal
and grand total values automatically as you edit the detail data.
You can insert subtotals for smaller groups within existing subtotal groups.
In the example below, subtotals for each sport are in a list that already has
subtotals for each region.
Outer subtotals
Nested subtotals
Before inserting nested subtotals, be sure to sort the list by all the
columns for which you want subtotal values, so that the rows you want
subtotaled are grouped together.
Create summary reports When you add subtotals to a
list, the list is outlined so that you can see its structure. You can create a
summary report by clicking the outline symbols , , and to hide the details and show only
the totals.
Chart the summary data You can create a chart that
uses only the visible data in a list that contains subtotals. If you show or
hide details in the outlined list, the chart is also updated to show or hide
the data.
Freezing and Splitting Panes
View two parts of a sheet by splitting or freezing panes
Split
- At the top of the
vertical scroll bar or at the right end of the horizontal scroll bar,
point to the split box.
- When the pointer changes
to a split pointer , drag the split box
down or to the left to the position you want.
Freeze panes
Freezing panes
allows you to select data that remains visible when scrolling in a sheet. For
example, keeping row and column labels visible as you scroll.
- To freeze a pane, do one
of the following:
The top horizontal pane
Select the row below where you want the split to appear.
The left vertical pane
Select the column to the right of where you want the split to appear.
Both the upper and left panes
Click the cell below and to the right of where you want the split to appear.
- On the Window
menu, click Freeze Panes.
|
About working with data in a datasheet |
|
|
In a table
or query, Datasheet
view (Datasheet view: A window
that displays data from a table, form, query, view, or stored procedure in a
row-and-column format. In Datasheet view, you can edit fields, add and delete
data, and search for data.) provides the tools you need to
work with data.
The Table
Datasheet and Query Datasheet toolbars (toolbar: A bar with buttons and options that
you use to carry out commands. To display a toolbar, click Customize
on the Tools menu, and then click the Toolbars tab.)
provide many of the tools you need to find, edit, and print records.
You can find
tools for working with columns, rows, and subdatasheets (subdatasheet: A datasheet that is nested
within another datasheet and that contains data related or joined to the
first datasheet.) in the datasheet itself, or by
right-clicking a column
selector (column selector: The
horizontal bar at the top of a column. You can click a column selector to
select an entire column in the query design grid or the filter design grid.).
You can use
the navigation toolbar to move through the records in a datasheet.
|
|
Print or preview data.
Check spelling.
Cut, copy, or paste
selected text, fields, whole records, or the entire datasheet.
Sort records.
Filter records, and find or
replace values.
Add or delete records.
Move between records or fields
Move between records by using
navigation buttons in a datasheet or form
The navigation buttons are located at the bottom of the window in Datasheet (Datasheet view: A window that displays data from a
table, form, query, view, or stored procedure in a row-and-column format. In
Datasheet view, you can edit fields, add and delete data, and search for data.)
or Form
view (Form view: A window that displays a form to
show or accept data. Form view is the primary means of adding and modifying
data in tables. You can also change the design of a form in this view.).
You can use these buttons to move quickly between records.
First record
Previous record
Record number
Next record
Last record
New record
Move between records by using
navigation buttons on a data access page
- Use the navigation
buttons located on the record navigation toolbar to quickly move between
records or a group of records on a grouped
data access page (grouped data access page: A
data access page that has two or more group levels.).
First record
Previous record
Record source and number
Next record
Last record
Note If you don't see the record navigation toolbar on
your page, it means one of the following:
- All the records are
displayed on the page, so the navigation buttons are not necessary.
- The designer of the page
has customized the toolbar. Look for custom instructions on how to use the
page, or contact the designer.
Move between fields with the
Go To Field box in Datasheet view
Adding the Go To Field box makes it easier to move between fields in
long records in Datasheet
view (Datasheet view: A window that displays data
from a table, form, query, view, or stored procedure in a row-and-column
format. In Datasheet view, you can edit fields, add and delete data, and search
for data.).
- Open a table, query, form,
view, or stored procedure in Datasheet view.
- Click Toolbars on
the View menu.
- Select Formatting
(Datasheet).
- Click Close.
The Go To Field box is the leftmost item on the Formatting
(Datasheet) toolbar. Use it to click the name of the field that you want to
move to in the current record.
GoToPage Action
You can use the GoToPage action to move the focus
in the active form
to the first control
on a specified page. You can use this action if you've created a form with page
breaks that contains groups of related information. For example, you might have
an Employees form with personal information on one page, office information on
another page, and sales information on a third page. You can use the GoToPage
action to move to the desired page. You can also present multiple pages of
information on a single form by using tab
controls.
Setting
The GoToPage action has the following arguments.
|
Action argument |
Description |
|
Page Number |
The number of the page to which you want to move the
focus. Enter the page number in the Page Number box in the Action
Arguments section of the Macro
window. If you leave this argument blank, the focus stays on the current
page. You can use the Right and Down arguments to display the part of the
page you want to see. |
|
Right |
The horizontal position of the spot on the page, measured
from the left edge of its containing window, that's to appear at the left
edge of the window. This is required if you specify a Down argument. |
|
Down |
The vertical position of the spot on the page, measured
from the top edge of its containing window, that's to appear at the top edge
of the window. This is required if you specify a Right argument. |
Note The Right and Down arguments are measured in inches
or centimeters, depending on the regional settings in Windows Control Panel.
Remarks
You can use this action to select the first control (as defined by the
form's tab
order) on the specified page. Use the GoToControl action to move to a
particular control on the form.
You can use the Right and Down arguments for forms with pages larger than
the Microsoft Access window. Use the Page Number argument to move to the
desired page, and then use the Right and Down arguments to display the part of
the page you want to see. Access displays the part of the page whose upper-left
corner is offset the specified distance from the upper-left corner of the page.
You can't use the GoToPage action in the following cases:
- To move the focus to a
page on a hidden form.
- To move the focus from
one page to another within the tab control.
To run the GoToPage action in Visual Basic, use the GoToPage method
of the DoCmd object.
About updating data in an Access project
General guidelines for
updating data
The following general guidelines apply when you are updating data in a Microsoft
Access project (Microsoft Access project: An
Access file that connects to a Microsoft SQL Server database and is used to
create client/server applications. A project file doesn't contain any data or
data-definition-based objects such as tables and views.):
- Base tables in a query
must have a primary key defined. When you first create a table in Database
Designer or Table Designer, Access prompts you to create a primary key.
- To update any field
(including the primary key field) in a single table (table: A database object that stores data in
records (rows) and fields (columns). The data is usually about a
particular category of things, such as employees or orders.) or
base table of a join (join: An association between a field in one table
or query and a field of the same data type in another table or query.
Joins tell the program how data is related. Records that don't match may
be included or excluded, depending on the type of join.), you
must have a unique field or combination of fields defined as a primary key
constraint, a unique constraint, or any unique index on that table.
- To update data in a
query, you must have UPDATE, INSERT and DELETE permission on the base
table(s) referenced in the query, as well as SELECT permission on a view
or EXECUTE permission on a stored
procedure (stored procedure: A precompiled
collection of SQL statements and optional control-of-flow statements that
is stored under a name and processed as a unit. The collection is stored
in an SQL database and can be run with one call from an application.)
or user-defined function.
In general, you can update data under the following circumstances:
·
In general, views can be updated subject to the
query restrictions described in the next section below. However, in SQL Server
2000 databases, you can add the WITH VIEW_METADATA clause to the CREATE VIEW
SQL statement (Select the Update using view rules check box in the View
properties page). This clause allows updates to partitioned views, with certain
restrictions, and the use of INSTEAD OF triggers to make the view updateable.
For more information on updateable views, see the CREATE VIEW TRANSACT-SQL
statement in the SQL Server documentation.
·
A query based on a one-to-one relationship.
·
A query based on a one-to-many relationship.
However, there are important differences depending on the version of SQL
Server.
SQL Server 2000 databases
You can update any table in the join because Access automatically detects the
unique table in the relationship (The "many" side of a join).
SQL Server 7.0 and 6.5 databases
You can update data based on a one-to-many (one-to-many relationship: An association between two
tables in which the primary key value of each record in the primary table corresponds
to the value in the matching field or fields of many records in the related
table.) join in a form and page, but only for fields from the unique
table. When you open a query that contains a join in Datasheet view, it is
read-only, by default. However, you can update data from a form or if you set
the form's UniqueTable property (see discussion below).
- A query based on a self-join (self-join: A join in which a table is joined to
itself. Records from the table are combined with other records from the
same table when there are matching values in the joined fields.).
- A linked table based on a
SQL Server Linked Server if the OLE DB data source allows.
- A
In general, you cannot update data under the following circumstances:
·
A query based on data marked as read-only in the
database.
·
A query that violates constraints on the base
tables.
·
A query that includes the DISTINCT keyword to
exclude duplicate rows.
·
A query based on a subquery that contains totals
or aggregate functions.
- A form, datasheet, or
page where the underlying SQL Statement of the record
source (record source: The underlying source
of data for a form, report, or data access page. In an Access database, it
could be a table, query, or SQL statement. In an Access project, it could
be a table, view, SQL statement, or stored procedure.) contains
a GROUP BY or COMPUTE clause.
- A linked table based on
Transact SQL functions, because they create an ad hoc read-only connection
to the external data source.
·
In addition, you might not be able to update
specific columns in the query results. The following list summarizes specific
types of columns that you cannot update in the result set:
o
Columns based on expressions (such as price *
quantity AS extended_price).
o
Rows or columns deleted by another user.
o
Rows or columns locked by another user (locked
rows can usually be updated as soon as they are unlocked).
How Access displays updated
and default column values in a datasheet or form
Display of updated column
values
When you are updating fields in a form, datasheet, or page, Microsoft Access
can automatically insert a value for you, such as a default value, a calculated
column, a timestamp value, an identity/AutoNumber field, or an action performed
by a trigger. However, unlike a Microsoft
Access database (database: A collection of data
related to a particular subject or purpose. Within a database, information
about a particular entity, such as an employee or order, is categorized into
tables, records, and fields.), which in most cases updates the field
value as soon as you leave the field, in an Access project the display of the
value or effect of the operation may not occur until after you commit the
record depending on the version of the database you are using and several
property settings. Access may update or perform the operation at the server
first and then refresh the current display on the client.
After you update a record, such as by calculating columns, changing the
value of a joined field, or updating a foreign key field, Access will fix up
the corresponding join tables. In a SQL Server 2000 database, Access normally
resynchronizes the data on the form or datasheet to reflect the changed values.
However, because this row fix-up requires additional round trips to the server,
you may want to disable this setting with the PerformResync property. In
SQL Server version 7.0 and 6.5 databases, this row fix up must be set by the
form designer using the ResyncCommand property (see discussion below).
For SQL Server 2000 databases, when you update a sorted and filtered query,
you don't lose the sort and filter.
For SQL Server 7.0 and 6.5 databases, Access removes filters and sorts and
it appears as if the record disappears from its current position and moves to a
new location.
Access 2000 databases Access displays default
values when the form or datasheet displays new records by default. However, on
a form, you can set the Fetch Defaults property to No to disable
this for performance reasons.
Access 7.0 and 6.5 databases Access does not
display default values when the form or datasheet displays new records.
However, any defaults you have on controls will be displayed.
Constraint validation and
validation text
Access does not inform a user about column constraint violations or display
the Validation Text property value until the user attempts to save the record.
Working with updatable
snapshots in SQL Server 7.0 or 6.5 databases
Access supports only inserting, deleting, and updating data in a row of a
unique table (the "many" side of a join) in an updatable
snapshot (updateable snapshot: A type of recordset
that works efficiently in a client/server environment by caching data on the
client and minimizing round trips to the server to access and update data.)
that has been created by a one-to-many join operation in a view, row-returning
stored procedure, or SQL SELECT statement. Access doesn't allow you to update
fields on the "one" side of the join.
A Form or page that is based on a one-to-many join must define the UniqueTable
property in order for the recordset (recordset: The collective name given to table-,
dynaset-, and snapshot-type Recordset objects, which are sets of records
that behave as objects.) to be updatable. The unique table (also
called the "most-many" table) is the "many" side of a
one-to-many relationship. It is called a unique table because one record from
the unique table corresponds to, at most, one record in the view, row-returning
stored procedure, or SQL statement. For example, in a view that joins the
Customers and Orders tables, Orders is the unique table because one record from
the Orders table corresponds to, at most, one unique record in the view. By
contrast, one record in the Customers table can correspond to multiple records
in the view.
Additionally, for a record
source (record source: The underlying source of
data for a form, report, or data access page. In an Access database, it could
be a table, query, or SQL statement. In an Access project, it could be a table,
view, SQL statement, or stored procedure.) that is a stored
procedure or SQL statement containing unnamed parameters, if you want to
display the current values of a record after it is updated, you must also set
the ResyncCommand property. Note that if you don't set the ResyncCommand
property in these cases, the only consequence is that you won't see current
values in a record after an update or insert. The update or insert will still
happen correctly. For other types of record sources, Access displays current
values even if the ResyncCommand property is not set.
In an updatable snapshot, you see only current values in an edited or newly
inserted record after it has been successfully saved (see discussion above).
For example, the Orders table contains an identity column as its primary key.
When you insert data into this table, the new OrderID value appears after you
save the record or move to another record (which implicitly saves the record).
Similarly, in a form based on a view joining Customers and Orders, if you
update the OrderID field to a different CustomerID, the Address field will not
show the address of the new customer until after the record has been saved.
Finally, all key fields or the UniqueTable must be in the SQL SELECT
statement select list (although not necessarily visible). You must also select
all NOT NULL columns that do not have server defaults defined and supply values
for these columns in the recordset. This does not apply to fields which have
the Identity property set.
Recommended ways to update
related data
The following are recommended ways to update related data:
- AutoForm is a handy way to
create a form that allows updating of a view or stored procedure based on
a join. Use AutoForm to quickly create the form, then switch to design
view to set the UniqueTable property. You can also change the DefaultView
property if you prefer a Datasheet view to a single Form view.
- Displaying several
"filtered" forms allows you to base one form on a main table and
have buttons on this form that pop up other modal forms that are filtered
from the data in the main form. For example, the main form could be based
on "Customers" and you would have a "View Orders For Customer"
button that would open a second form based on the Orders table that
filters by the CustomerID field on the main form.
- To update all data in a
one-to-many relationship, you can create a form/subform combination. For
example, instead of displaying data on a single form that joins orders and
customers (in this case, the customer fields would be read-only), you can
create a form for customers and a subform for orders. Because a
form/subform combination uses two different recordsets, you can update all
fields. Additionally, you can display this form/subform combination as a
datasheet with a subdatasheet.
- In a page, you can
simulate a form/subform combination by linking two pages using a hyperlink
field, and then use Microsoft FrontPage to create two frames, one for each
page.
Select fields and records
Select fields and records in
a datasheet
The following table lists mouse techniques for selecting data or records in Datasheet
view (Datasheet view: A window that displays data
from a table, form, query, view, or stored procedure in a row-and-column
format. In Datasheet view, you can edit fields, add and delete data, and search
for data.).
|
To select |
Click |
|
Data in a field |
Where you want to start selecting and drag across the
data. |
|
An entire field |
The left edge of the field in a datasheet, where the
pointer changes into |
|
Adjacent fields |
The left edge of a field and drag to extend the selection. |
|
A column |
|
|
Adjacent columns |
The field name at the top of the column and then drag to
extend the selection. |
|
A record |
|
|
Multiple records |
The record selector of the first record, and then drag to
extend the selection. |
|
All records |
Select All Records on the Edit menu. |
Select fields and records in
a data access page
The following table lists mouse techniques for selecting data or records in Page
view (Page view: An Access window in which you can
browse the contents of a data access page. Pages have the same functionality in
Page view as in Internet Explorer 5.0 or later.).
|
To select |
Click |
|
Data in a field |
Where you want to start selecting and drag across the data |
|
An entire field |
Three times within the field |
|
A record |
A field that belongs to the record |
The following table lists keyboard techniques for selecting data or records in
Page view.
|
To select |
Do this |
|
Data in a field |
Move the insertion point to the start of the text you want
to select, and then hold down SHIFT and press the arrow keys to move to the
end of the selection. |
|
An entire field |
Move the insertion point to anywhere in the field, press
HOME, and then press SHIFT+END. |
|
A record |
Press TAB until a field that belongs to the record gets
focus. |
Note Your data access page might have a Microsoft
Office PivotTable Component, Spreadsheet Component, or Chart Component, which
have their own methods of selection. To display Help for one of these
components, click Help on its toolbar.
Save a database object
- Click Save on the Design
toolbar.
- Do one of the following:
- If you are saving a database
object for the first time, type a unique name in the Save As
dialog box, and then click OK. If you click Cancel in the Save
As dialog box, the object is not saved.
- If you are saving a data
access page for the first time, specify a location and name for the
HTML file in the Save As Data Access Page dialog box.
To save the page so that you can use it over a
network, select a network location in the Save in box, or specify a UNC
path in the File name box. If you save the page to location that
specifies an absolute path (a path that contains a drive letter), Microsoft
Access displays a message stating that the page might not be able to connect to
data through the network. To connect through a network, edit the connection
string to specify a network (UNC) path.
Notes
- You don't need to save
new data in records.
Access automatically saves a record when you do any of the following: move
the focus
to a different record; close the active form, datasheet, data access page,
or database; or quit Access.
- Access automatically
saves changes to your data whenever you quit. However, if you have made
changes to the design of any database objects since you last saved them,
Access asks whether you want to save these changes before quitting.
- If you make a change in PivotTable
view, Access updates the PivotChart
view automatically. Similarly, if you make a change in PivotChart
view, Access automatically update the PivotTable view. When you save the
object, you also save the PivotTable and PivotChart views.
Quit Action
You can use the Quit action to exit Microsoft Access. The Quit action can
also specify one of several options for saving database
objects prior to exiting Access.
Setting
The Quit action has the following argument.
|
Action argument |
Description |
|
Options |
Specifies what happens to unsaved objects when you quit
Access. Click Prompt (to display dialog boxes that ask whether to save
each object), Save All (to save all objects without prompting by
dialog boxes), or Exit (to quit without saving any objects) in the Options
box in the Action Arguments section of the Macro
window. The default is Save All. |
Remarks
Access doesn't run any actions that follow the Quit action in a macro.
You can use this action to quit Access without prompts from Save
dialog boxes by using a custom menu command or a button on a form.
For example, you might have a master form that you use to display the objects
in your custom workspace. This form could have a Quit button that runs a
macro containing the Quit action with the Options argument set to Save All.
This action has the same effect as clicking Exit on the File
menu. If you have any unsaved objects when you click this command, the dialog
boxes that appear are the same as those displayed when you use Prompt
for the Options argument of the Quit action.
You can use the Save action in a macro to save a specified object without
having to quit Access or close the object.
To run the Quit action in Visual Basic, use the Quit method of the DoCmd
object.
Set or change the primary key (MDB)
Note The information in this topic applies only to a
Microsoft Access database (.mdb).
- Open a table in Design
view (Design view: A window that shows the
design of these database objects: tables, queries, forms, reports, macros,
and data access pages. In Design view, you can create new database objects
and modify the design of existing ones.).
- Select the field or
fields you want to define as the primary
key (primary key: One or more fields
(columns) whose values uniquely identify each record in a table. A primary
key cannot allow Null values and must always have a unique index. A
primary key is used to relate a table to foreign keys in other tables.).
To select one field, click the row
selector (row selector: A small box or bar that,
when clicked, selects an entire row in table or macro Design view, or when you
sort and group records in report Design view.) for the desired
field.
To select multiple fields, hold down the CTRL key
and then click the row selector for each field.
- Click Primary Key on the toolbar.
Note If you want the order of the fields in a
multiple-field primary key to be different from the order of those fields in
the table, click Indexes on the toolbar to display
the Indexes
window (Indexes window: In an Access database, a
window in which you can view or edit a table's indexes or create multiple-field
indexes.), and then reorder the field names for the index (index: A feature that speeds up searching and sorting
in a table based on key values and can enforce uniqueness on the rows in a
table. The primary key of a table is automatically indexed. Some fields can't
be indexed because of their data type.) named PrimaryKey.
Add or edit data
Add or edit data in a
datasheet or form
- Open a datasheet (Datasheet view: A window that displays data from
a table, form, query, view, or stored procedure in a row-and-column
format. In Datasheet view, you can edit fields, add and delete data, and
search for data.), or open a form in Form
view (Form view: A window that displays a
form to show or accept data. Form view is the primary means of adding and
modifying data in tables. You can also change the design of a form in this
view.).
- Do one of the following:
To add a new record, click New Record on the toolbar, type the
data, and then press TAB to go to the next field. At the end of the record,
press TAB to go to the next record.
To edit data within a field, click in the field
you want to edit, and then type the data.
To replace the entire value, move the pointer to
the leftmost part of the field until it changes into the plus pointer , and then click. Type the
data.
Notes
- To correct a typing
mistake, press BACKSPACE. To cancel your changes in the current field,
press ESC. To cancel your changes in the entire record, press ESC again
before you move out of the field.
- When you move to another
record, Microsoft Access saves your changes.
Add new data in a data access
page
- On the record navigation
toolbar, click New .
Note If you don't see the New
button, it means one of the following:
- The page does not
support data entry.
- The designer of the
page has customized the button. Look for custom instructions on how to
use the page, or contact the designer.
- Type the data you want in
the first field, and then press TAB to go to the next field.
- When you are finished
adding data to each field, save the record.
Note If you make a typing mistake, press BACKSPACE. To
cancel your changes in the current field, press ESC. To cancel your changes to
the entire record, click Undo on the record navigation
toolbar.
Edit data in a data access
page
- Click in the field you
want to edit.
Note You cannot edit group
fields and calculated
fields (calculated field: A field, defined in a
query, that displays the result of an expression rather than displaying stored
data. The value is recalculated each time a value in the expression changes.).
If you can't insert the input cursor inside any of the fields in the page, the
page does not support edits.
- Type the text you want to
insert.
- Save the record.
Note If you make a typing mistake, press BACKSPACE. To
cancel your changes in the current field, press ESC. To cancel your changes to
the entire record, click Undo on the record navigation
toolbar.
About sorting records (MDB)
There are two types of sorts you can do; simple and complex.
·
Simple sorts When you
sort in Form
view (Form view: A window that displays a form to
show or accept data. Form view is the primary means of adding and modifying
data in tables. You can also change the design of a form in this view.),
Datasheet
view (Datasheet view: A window that displays data
from a table, form, query, view, or stored procedure in a row-and-column
format. In Datasheet view, you can edit fields, add and delete data, and search
for data.), or Page
view (Page view: An Access window in which you can
browse the contents of a data access page. Pages have the same functionality in
Page view as in Internet Explorer 5.0 or later.), you do a simple
sort, which means you can sort all records in ascending or descending order
(but you cannot use both sort
orders (sort order: A way to arrange data based on
value or data type. You can sort data alphabetically, numerically, or by date.
Sort orders use an ascending (0 to 100, A to Z) or descending (100 to 0, Z to
A) order.) on more than one field).
·
Complex sorts When you
sort in query Design
view (Design view: A window that shows the design
of these database objects: tables, queries, forms, reports, macros, and data
access pages. In Design view, you can create new database objects and modify
the design of existing ones.), the Advanced
Filter/Sort window (Advanced Filter/Sort window: A
window in which you can create a filter from scratch. You enter criteria
expressions in the filter design grid to restrict the records in the open form
or datasheet to a subset of records that meet the criteria.), report
Design view, Page Design view, PivotTable view, or PivotChart view, you can
perform complex sorts. This means you can sort records in ascending order by
some fields and in descending order by others.
How a sort is saved or
re-applied depends on the object
·
Saving or re-applying a form, datasheet,
report, PivotTable, or PivotChart sort order Regardless
of where you specify the sort
order (sort order: A way to arrange data based on
value or data type. You can sort data alphabetically, numerically, or by date.
Sort orders use an ascending (0 to 100, A to Z) or descending (100 to 0, Z to
A) order.), Microsoft Access saves the sort order when you save the
form, datasheet, report, PivotTable, or PivotChart. In a form, datasheet,
PivotTable, or PivotChart, Access also reapplies the sort automatically when
you reopen that object or view, or base a new form or report on a form or
datasheet.
·
Saving a data access page sort order
In a Page, Access saves the sort order if you create it in Page Design
view (Design view: A window that shows the design of
these database objects: tables, queries, forms, reports, macros, and data
access pages. In Design view, you can create new database objects and modify
the design of existing ones.) (by using Data Outline or setting the DefaultSort
property), but doesn't save the sort order if you create it in Page
view (Page view: An Access window in which you can
browse the contents of a data access page. Pages have the same functionality in
Page view as in Internet Explorer 5.0 or later.) (by using the Sort
buttons).
Considerations when sorting
records
- If you base a new form (form: An Access database object on which you
place controls for taking actions or for entering, displaying, and editing
data in fields.) or report (report: An Access database object that prints
information that is formatted and organized according to your
specifications. Examples of reports are sales summaries, phone lists, and
mailing labels.) on a table (table: A database object that stores data in
records (rows) and fields (columns). The data is usually about a
particular category of things, such as employees or orders.) or
query (query: A question about the data stored in your
tables, or a request to perform an action on the data. A query can bring
together data from multiple tables to serve as the source of data for a
form, report, or data access page.) that has a sort
order (sort order: A way to arrange data
based on value or data type. You can sort data alphabetically,
numerically, or by date. Sort orders use an ascending (0 to 100, A to Z)
or descending (100 to 0, Z to A) order.) saved with it, the new
form or report inherits the sort order.
- Access will sort on up to
255 characters, in one or more fields, in the results of a query or
advanced filter.
- Sort order depends on the
language setting you specify in the New Database Sort Order box (Options
dialog box, General tab) at the time you create your database. If
your database contains linked tables from a database that uses a different
language sort order, Access uses the sort order of the database that
contains the link to the table, not the database where the table is
stored.
- If the design grid for
the query or filter contains the asterisk from the field list, you can't
specify a sort order in the design grid unless you also add the fields you
want to sort on to the design grid.
- To sort dates and times from
earlier to later, use ascending order. Use descending order to sort from
later to earlier.
- Numbers stored in Text (Text data type: In a Microsoft Access database,
this is a field data type. Text fields can contain up to 255 characters or
the number of characters specified by the FieldSize property,
whichever is less.) fields are sorted as strings of characters,
not numeric values. Therefore, to sort them in numeric order, all text
strings must be the same length with shorter numbers padded with leading
zeros. For example, the result of an ascending sort of the text strings
"1", "2", "11", and "22" will be
"1", "11", "2", "22". You must pad
the single-digit numbers with a leading zero for the strings to be sorted
properly: "01", "02", "11", "22".
For fields that don't contain Null (Null: A value you can enter in a field or
use in expressions or queries to indicate missing or unknown data. In
Visual Basic, the Null keyword indicates a Null value. Some
fields, such as primary key fields, can't contain Null.)
values, another solution would be to sort on the numeric values of the
strings by using the Val function. For example, if the Age column
is a Text field that contains numeric values, specifying Val([Age]) in a Field
cell and specifying a sort order in its Sort cell will put the
records in the correct order. If you are only storing numbers or dates in
a Text field, consider changing the field's data type to Number (Number data type: In a Microsoft Access database,
a field data type designed for numerical data that will be used in mathematical
calculations. Use the Currency data type, however, to display or calculate
currency values.), Currency (Currency data type: In a Microsoft Access
database, a data type that is useful for calculations involving money or
for fixed-point calculations in which accuracy is extremely important.),
or Date/Time (Date/Time data type: An Access database data type
that is used to hold date and time information.) in the table
where the field is stored. Then, when you sort on the field, the numbers
or dates will sort in numeric or date order without requiring leading
zeros.
- When you sort a field in
ascending order, any records in which that field is blank (contains a Null
value) are listed first. If a field contains records with both Null
values and zero-length
strings (zero-length string: A string that
contains no characters. You can use a zero-length string to indicate that
you know no value exists for a field. You enter a zero-length string by
typing two double quotation marks with no space between them ("
").), the fields with Null values appear first in
the sort order, immediately followed by the zero-length strings.
Sorting on fields with a data
type of Memo, Hyperlink, or OLE object
You can't sort an OLE
Object (OLE object: An object supporting the OLE
protocol for object linking and embedding. An OLE object from an OLE server
(for example, a Windows Paint picture or a Microsoft Excel spreadsheet) can be
linked or embedded in a field, form, or report.) under any
circumstances. Although you can't sort a Memo (Memo data type: In a Microsoft Access database, this
is a field data type. Memo fields can contain up to 65,535 characters.)
or Hyperlink (Hyperlink data type: A data type for an Access
database field that stores hyperlink addresses. An address can have up to four
parts and is written using the following format: displaytext#address#subaddress#.)
field in a Page, you can sort these fields in a table, query, form, or report.
In a report, you must type the name of the Memo or Hyperlink field into the Field/Expression
box of the Sorting and Grouping dialog box because those fields don't
show up in the list of available fields.
Note Memo fields will only be sorted based on the first
255 characters.
About sorting data in
PivotTable or PivotChart view
Sort in ascending or
descending order
For example, you can sort a sales column in ascending order or an employee
name column in descending order.
You can also sort data separately for each column. The order in which you
sort the columns determines how the data will be organized.
In this example, data is organized by salesperson. The Product column was
sorted first so that the products would be in alphabetical order; the
Salesperson column was sorted second.
When sorting multiple columns, first determine the order in which you want
to sort the columns, and then work backward, sorting the outermost column last.
For example, if you're sorting two columns, determine which is the outer column
and which is the inner column. Sort the inner column first, and then sort the
outer column. To list the products in alphabetical order for each salesperson,
you would first sort the inner Product column and then sort the outer
Salesperson column.
You can define a custom order for a row (row field: A field in the row area of PivotTable view.
Items in row fields are listed down the left side of the view. Inner row fields
are closest to the detail area; outer row fields are to the left of the inner
row fields.) or column
field (column field: A field in the column area of
PivotTable view. Items in column fields are listed across the top of a
PivotTable list. Inner column fields are closest to the detail area; outer
column fields are above the inner column fields.) when sorting in
ascending or descending order does not meet your requirements. For example, you
might want to show the values in the Title column ordered on the basis of the
seniority of the title. If the column had three values— Vice President,
General Manager, and Manager— sorting in ascending or descending order
will not show the data in the order you want. In this case, you might want to
explicitly define the order of the items for the Title column.
If items are added to a field after you define a custom sort order, the new
items will appear at the bottom of the field until you rearrange them.
In a PivotChart view, you can sort numerical data or alphabetical data in
ascending or descending order. For example, you can sort a series (data series: Related data points that are plotted in a
chart. Each data series in a chart has a unique color or pattern. You can plot
one or more data series in a chart.) that contains numerical sales
data to show the highest to lowest sales amounts, or you can sort a category
field (category field: A field that is displayed
in the category area of PivotChart view. Items in a category field appear as
labels on the category axis.) that contains company names to display
in ascending order (A - Z).
In the following example, the series
field (series field: A field that is displayed in
the series area of a chart and that contains series items. A series is a group
of related data points.), which consists of sales amounts, is sorted
in ascending order to show lowest to highest sales. If the category field had
been sorted in ascending order, the salespeople's names would be displayed on
the category axis in alphabetical order instead.
If you have a stacked bar, area, or column chart, you can sort numerically
based on the height of the entire stacked value rather than just one series.
About relationships in an Access database (MDB)
After you've set up different tables for each subject in your Microsoft
Access database (Microsoft Access database: A
collection of data and objects (such as tables, queries, or forms) that is
related to a particular topic or purpose. The Microsoft Jet database engine
manages the data.), you need a way of telling Microsoft Access how
to bring that information back together again. The first step in this process
is to define relationships (relationship: An association that is established
between common fields (columns) in two tables. A relationship can be
one-to-one, one-to-many, or many-to-many.) between your tables.
After you've done that, you can create queries, forms, and reports to display
information from several tables at once. For example, this form includes
information from four tables:
The Customers table
The Orders table
The Products table
The Order Details table
In the previous example, the fields in four tables must be coordinated so
that they show information about the same order. This coordination is
accomplished with relationships between tables. A relationship works by
matching data in key fields— usually a field with the same name in both
tables. In most cases, these matching fields are the primary
key (primary key: One or more fields (columns)
whose values uniquely identify each record in a table. A primary key cannot
allow Null values and must always have a unique index. A primary key is
used to relate a table to foreign keys in other tables.) from one
table, which provides a unique identifier for each record, and a foreign
key (foreign key: One or more table fields
(columns) that refer to the primary key field or fields in another table. A
foreign key indicates how the tables are related.) in the other
table. For example, employees can be associated with orders they're responsible
for by creating a relationship between the EmployeeID fields.
EmployeeID appears in both
tables— -as a primary key ...
... and as a foreign key.
A one-to-many relationship is the most common type of relationship. In a
one-to-many relationship, a record in Table A can have many matching records in
Table B, but a record in Table B has only one matching record in Table A.
One supplier ...
... can supply more than one
product ...
... but each product has only
one supplier.
In a many-to-many relationship, a record in Table A can have many matching
records in Table B, and a record in Table B can have many matching records in
Table A. This type of relationship is only possible by defining a third table
(called a junction table) whose primary
key (primary key: One or more fields (columns)
whose values uniquely identify each record in a table. A primary key cannot
allow Null values and must always have a unique index. A primary key is
used to relate a table to foreign keys in other tables.) consists of
two fields— the foreign
keys (foreign key: One or more table fields
(columns) that refer to the primary key field or fields in another table. A
foreign key indicates how the tables are related.) from both Tables
A and B. A many-to-many relationship is really two one-to-many relationships
with a third table. For example, the Orders table and the Products table have a
many-to-many relationship that's defined by creating two one-to-many
relationships to the Order Details table. One order can have many products, and
each product can appear on many orders.
Primary key from the Orders
table
Primary key from the Products
table
One order can have many
products ...
... and each product can
appear on many orders.
In a one-to-one relationship, each record in Table A can have only one
matching record in Table B, and each record in Table B can have only one
matching record in Table A. This type of relationship is not common, because
most information related in this way would be in one table. You might use a
one-to-one relationship to divide a table with many fields, to isolate part of
a table for security reasons, or to store information that applies only to a
subset of the main table. For example, you might want to create a table to
track employees participating in a fundraising soccer game. Each soccer player
in the Soccer Players table has one matching record in the Employees table.
Each soccer player has one
matching record in the Employees table.
This set of values is a
subset of the EmployeeID field and the Employees table.
The kind of relationship that Microsoft Access creates depends on how the
related fields are defined:
- A one-to-many relationship
is created if only one of the related fields is a primary
key (primary key: One or more fields
(columns) whose values uniquely identify each record in a table. A primary
key cannot allow Null values and must always have a unique index. A
primary key is used to relate a table to foreign keys in other tables.)
or has a unique
index (unique index: An index defined by
setting a field's Indexed property to Yes (No Duplicates). A
unique index will not allow duplicate entries in the indexed field.
Setting a field as the primary key automatically defines the field as
unique.).
- A one-to-one relationship
is created if both of the related fields are primary keys or have unique
indexes.
- A many-to-many
relationship is really two one-to-many relationships with a third table
whose primary key consists of two fields— the foreign
keys (foreign key: One or more table fields
(columns) that refer to the primary key field or fields in another table.
A foreign key indicates how the tables are related.) from the
two other tables.
You can also create a relationship between a table and itself. This is
useful in situations where you need to perform a Lookup within the same table.
In the Employees table, for example, you can define a relationship between the
EmployeeID and ReportsTo fields, so that the ReportsTo field can display
employee data from a matching EmployeeID.
Note If you drag a field that isn't a primary key and
doesn't have a unique index to another field that isn't a primary key and
doesn't have a unique index, an indeterminate relationship is created. In
queries containing tables with an indeterminate relationship, Microsoft Access
displays a default join (join: An association between a field in one table or
query and a field of the same data type in another table or query. Joins tell
the program how data is related. Records that don't match may be included or
excluded, depending on the type of join.) line between the tables,
but referential
integrity (referential integrity: Rules that you
follow to preserve the defined relationships between tables when you enter or
delete records.) won't be enforced, and there's no guarantee that
records are unique in either table.
Referential integrity is a system of rules that Microsoft Access uses to
ensure that relationships between records in related tables are valid, and that
you don't accidentally delete or change related data. You can set referential
integrity when all of the following conditions are met:
- The matching field from
the primary
table (primary table: The "one"
side of two related tables in a one-to-many relationship. A primary table
should have a primary key and each record should be unique.) is
a primary
key (primary key: One or more fields
(columns) whose values uniquely identify each record in a table. A primary
key cannot allow Null values and must always have a unique index. A
primary key is used to relate a table to foreign keys in other tables.)
or has a unique
index (unique index: An index defined by
setting a field's Indexed property to Yes (No Duplicates). A
unique index will not allow duplicate entries in the indexed field.
Setting a field as the primary key automatically defines the field as
unique.).
- The related fields have
the same data
type (data type: The characteristic of a
field that determines what type of data it can hold. Data types include
Boolean, Integer, Long, Currency, Single, Double, Date, String, and
Variant (default).). There are two exceptions. An AutoNumber (AutoNumber data type: In a Microsoft Access
database, a field data type that automatically stores a unique number for
each record as it's added to a table. Three kinds of numbers can be
generated: sequential, random, and Replication ID.) field can be
related to a Number field with a FieldSize property setting of Long
Integer, and an AutoNumber field with a FieldSize property
setting of Replication ID can be related to a Number field with a FieldSize
property setting of Replication ID.
- Both tables belong to the
same Microsoft Access database. If the tables are linked tables, they must
be tables in Microsoft Access format, and you must open the database in
which they are stored to set referential integrity. Referential integrity
can't be enforced for linked tables from databases in other formats.
The following rules apply when you use referential integrity:
- You can't enter a value in
the foreign
key (foreign key: One or more table fields
(columns) that refer to the primary key field or fields in another table.
A foreign key indicates how the tables are related.) field of
the related table that doesn't exist in the primary key of the primary
table. However, you can enter a Null (Null: A value you can enter in a field or
use in expressions or queries to indicate missing or unknown data. In
Visual Basic, the Null keyword indicates a Null value. Some
fields, such as primary key fields, can't contain Null.)
value in the foreign key, specifying that the records are unrelated. For
example, you can't have an order that is assigned to a customer that
doesn't exist, but you can have an order that is assigned to no one by
entering a Null value in the CustomerID field.
- You can't delete a record
from a primary table if matching records exist in a related table. For
example, you can't delete an employee record from the Employees table if
there are orders assigned to the employee in the Orders table.
- You can't change a primary
key value in the primary table, if that record has related records. For
example, you can't change an employee's ID in the Employees table if there
are orders assigned to that employee in the Orders table.
For relationships in which referential
integrity (referential integrity: Rules that you
follow to preserve the defined relationships between tables when you enter or
delete records.) is enforced, you can specify whether you want
Microsoft Access to automatically cascade
update (cascading update: For relationships that
enforce referential integrity between tables, the updating of all related
records in the related table or tables when a record in the primary table is
changed.) and cascade
delete (cascading delete: For relationships that
enforce referential integrity between tables, the deletion of all related
records in the related table or tables when a record in the primary table is
deleted.) related records. If you set these options, delete and
update operations that would normally be prevented by referential integrity
rules are allowed. When you delete records or change primary
key (primary key: One or more fields (columns)
whose values uniquely identify each record in a table. A primary key cannot
allow Null values and must always have a unique index. A primary key is
used to relate a table to foreign keys in other tables.) values in a
primary
table (primary table: The "one" side of
two related tables in a one-to-many relationship. A primary table should have a
primary key and each record should be unique.), Microsoft Access
makes necessary changes to related tables to preserve referential integrity.
If you select the Cascade Update Related Fields check box when
defining a relationship, any time you change the primary key of a record in the
primary table, Microsoft Access automatically updates the primary key to the
new value in all related records. For example, if you change a customer's ID in
the Customers table, the CustomerID field in the Orders table is automatically
updated for every one of that customer's orders so that the relationship isn't
broken. Microsoft Access cascades updates without displaying any message.
Note If the primary key in the primary table is an AutoNumber (AutoNumber data type: In a Microsoft Access database,
a field data type that automatically stores a unique number for each record as
it's added to a table. Three kinds of numbers can be generated: sequential,
random, and Replication ID.) field, setting the Cascade Update
Related Fields check box will have no effect, because you can't change the
value in an AutoNumber field.
If you select the Cascade Delete Related Records check box when
defining a relationship, any time you delete records in the primary table,
Microsoft Access automatically deletes related records in the related table.
For example, if you delete a customer record from the Customers table, all the
customer's orders are automatically deleted from the Orders table (this
includes records in the Order Details table related to the Orders records).
When you delete records from a form or datasheet with the Cascade Delete
Related Records check box selected, Microsoft Access warns you that related
records may also be deleted. However, when you delete records using a delete
query (delete query: A query (SQL statement) that
removes rows matching the criteria that you specify from one or more tables.),
Microsoft Access automatically deletes the records in related tables without
displaying a warning.
Comments
Post a Comment