📄 ch04.htm
字号:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Symantec Visual Page 1.0">
<META HTTP-EQUIV="Content-Type" CONTENT="text/html;CHARSET=iso-8859-1">
<TITLE>Ch 4 -- Queries and Filters</TITLE>
</HEAD>
<BODY BACKGROUND="../buttonart/bg1.gif" BGCOLOR="#FFFFFF">
<P>
<TABLE BORDER="0" WIDTH="600">
<TR>
<TD WIDTH="80" VALIGN="TOP"><IMG SRC="../buttonart/que.gif" WIDTH="75" HEIGHT="24" ALIGN="BOTTOM" BORDER="0"><BR>
<BR>
<A HREF="index.htm"><IMG
SRC="../buttonart/toc.gif" WIDTH="40" HEIGHT="40" ALIGN="BOTTOM" ALT="TOC" BORDER="0"
NAME="toc4"></A><BR>
<A HREF="ch03.htm"><IMG
SRC="../buttonart/back1.gif" WIDTH="40" HEIGHT="40" ALIGN="BOTTOM" ALT="BACK" BORDER="0"
NAME="toc1"></A><BR>
<A HREF="ch05.htm"><IMG
SRC="../buttonart/forward.gif" WIDTH="40" HEIGHT="40" ALIGN="BOTTOM" ALT="FORWARD"
BORDER="0" NAME="toc2"></A><BR>
<A HREF="index.htm"><IMG
SRC="../buttonart/home.gif" WIDTH="40" HEIGHT="40" ALIGN="BOTTOM" ALT="HOME" BORDER="0"
NAME="toc3"></A></TD>
<TD WIDTH="520" VALIGN="TOP">
<H2><FONT COLOR="#000077">Microsoft® Access 97 Quick Reference</FONT></H2>
<P>
<H1 ALIGN="CENTER"><FONT COLOR="#000077">- 4 -<BR>
Queries and Filters</FONT></H1>
<P>Queries and filters are primary ways to find and organize your data. A <I>filter</I>
is saved with a <I>table</I> and can't be reused for other objects unless you save
it as a <I>query</I>. Queries can be used to answer questions themselves, but can
also be used as the foundation for forms and reports.</P>
<P>Creating queries and filters involves tasks such as sorting data, choosing data
(setting <I>criteria</I>), and selecting <I>tables</I> and <I>fields</I>. Queries
are more versatile than filters and you can use them to mass update, delete, and
add records to a table. You can also do substantial calculations with queries.
<BLOCKQUOTE>
<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>For more on the calculation capabilities
of queries see the Calculations part of this book.
<HR>
</BLOCKQUOTE>
<P>Most of the following procedures start in Query <I>Design View</I>. You can enter
this view by selecting an existing query on the <I>Database window</I> and choosing
the <U>D</U>esign button. You can also get to the design view by selecting New on
the Queries tab of the Database window and choosing Design View.
<H2><FONT COLOR="#000077"><B>Action Query: Append Query</B></FONT></H2>
<P>An append query provides a method for adding records to one or more tables. This
is useful when you want to transfer records from one table, or set of related tables,
to another. You can also use the append query to write data to the same fields in
matching records between two tables. For notes about <I>action queries</I>, see "Action
Query: Create" first.
<H3><FONT COLOR="#000077"><B>Steps</B></FONT></H3>
<DL>
<DD><B>1. </B>In the <I>Database window</I>, highlight the table or query for the
basis of the append query and choose Query from the New Object button on the toolbar.
The New Query <I>dialog box</I> is displayed. Click OK to accept the default.
<P>
<DD><B>2. </B>Click the Query Type button on the toolbar and select the Ap<U> </U>pend
Query option. Enter the name of the target table in the Table <U>N</U>ame box. Click
either <U>C</U>urrent Database or <U>A</U>nother Database to specify the target table;
then choose OK.
<P>
<DD><B>3. </B>Drag the fields from the <I>Field</I> List that you want to append
to the Query Design <I>grid</I>, along with any fields you will use for selection
criteria.
<P>
<DD><B>4. </B>Enter the criteria for creating the result set in the Query Design
grid.
<P>
<DD><B>5. </B>Click the View button on the toolbar to preview the records to append.
<P>
<DD><B>6. </B>Click the Run button to append the records to the table(s) you specified.
</DL>
<BLOCKQUOTE>
<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>If you want the target table to
automatically add new <I>AutoNumber</I> values, do not<I> </I>drag the AutoNumber
field onto the Query Design grid. To copy the AutoNumber values from the source table,
drag the AutoNumber field onto the Query Design grid. If the target table already
has AutoNumbers that match the source table, the Append Query will not append those
records when you Append to the AutoNumber field.<BR>
Also, when all fields have the same name in both tables, drag only the asterisk for
the table to the Query Design grid.
<HR>
</BLOCKQUOTE>
<H2><FONT COLOR="#000077"><B>Action Query: Create</B></FONT></H2>
<P>An <I>action query</I> collects records that meet your search <I>criteria</I>,
and changes the data contained in those records in a single step. Access allows four
different action queries: delete, update, append, and make-table query. A delete
query removes the groups of records returned from your query from your table. An
update query alters the information contained in your result set and writes the changes
back to your table. An append<I> </I>query adds the records from your result set
to your table. Finally, a make-table query creates an entirely new table from your
result set.
<BLOCKQUOTE>
<P>
<HR>
<FONT COLOR="#000077"><B>CAUTION:</B></FONT><B> </B>Because action queries modify
your data, it is a good idea to first create a select <I>query</I> with the <I>fields</I>
and <I>criteria</I> you want. View the results of the query to make sure you have
the correct records. Then, turn the select query into an action query by selecting
one of the options on the Query Type button on the toolbar.
<HR>
</BLOCKQUOTE>
<P>First follow the steps to create a select query. (See "Query: Create with
Design View" or "Query: Create with Wizard.")
<H3><FONT COLOR="#000077"><B>Steps</B></FONT></H3>
<DL>
<DD><B>1. </B>In your query's <I>Design View</I>, enter criteria, add a sort order,
and create any calculated fields. Click the <I>Datasheet View</I> button to make
sure your criteria are OK.
<P>
<DD><B>2. </B>Return to the design view by clicking the Design View button.
<P>
<DD><B>3. </B>Turn the query into an action query by clicking the drop-down arrow
on the Query Type button and choosing one of the four action query types (Ma<U>k</U>e-Table,
<U>U</U>pdate, <U>A</U>ppend, <U>D</U>elete).
<P>
<DD><B>4. </B>If prompted, identify the target table. Click the Run button to perform
the action query.
<P>
<DD><B>5. </B>Click the Save button on the toolbar. Enter a name in the Save <I>dialog
box</I>, then choose OK.
</DL>
<P>The action query will appear on the Queries tab of the <I>Database window</I>.
To indicate that it will run when opened, the icon next to the name includes an exclamation
point.
<BLOCKQUOTE>
<P>
<HR>
<FONT COLOR="#000077"><B>CAUTION:</B></FONT><B> </B>Be careful when you double-click
or choose the Open button for an action query. The query runs every time. You might
want to delete action queries when you no longer need them to avoid accidentally
updating your data. You can also hide the query by choosing the hidden <I>object</I>
<I>property.</I> (See "Database Object: Properties" in the File Management
part of this book.)
<HR>
</BLOCKQUOTE>
<H2><FONT COLOR="#000077"><B>Action Query: Delete Query</B></FONT></H2>
<P>The <I>delete query</I> enables you to remove a set of records from the <I>table</I>
you specify. If you have already created a <I>relationship</I> that enforces referential
integrity with <I>cascade deletes</I>, you will only need to delete records from
parent table. (See "Relationships between Tables" in the Table and Database
Design part of this book.) The records from the related <I>child</I> table will delete
automatically.
<H3><FONT COLOR="#000077"><B>Steps</B></FONT></H3>
<DL>
<DD><B>1. </B>In the <I>Database window</I>, highlight the table or query for the
basis of the delete query and choose <U>Q</U>uery from the New <I>Object</I> button
on the toolbar. Click OK.
<P>
<DD><B>2. </B>Drag any fields you need to identify records into the design <I>grid</I>.
Then click the Query Type button on the toolbar and select the <U>D</U>elete Query
option.
<P>
<DD><B>3. </B>Enter any criteria for deleting records, and they appear under <TT>Where</TT>
in the Delete cell.
<P>
<DD><B>4. </B>Click the View button on the toolbar to preview the records to be deleted.
<P>
<DD><B>5. </B>Click the Run button to delete the records from the table.
</DL>
<P>After closing the <I>dialog box</I>, the records in your result set are removed
from the table you specified.
<H2><FONT COLOR="#000077"><B>Action Query: Make-Table</B></FONT></H2>
<P>The make-table query enables you to create a table for export; it provides the
basis for a <I>report</I>; it provides a method for making backups; and it gives
a snapshot of your data at a point in time. The make-table query also enables you
to improve the performance of your forms and reports by working from a set of records
stored to disk that don't have to be retrieved from a large data set or from a network.
You can also copy a table from the <I>Database window</I> and if necessary, delete
or add any additional fields you want. (See "Database Object: Copy" in
the File Management part of this book.)
<H3><FONT COLOR="#000077"><B>Steps</B></FONT></H3>
<DL>
<DD><B>1. </B>In the Database window, highlight the <I>table</I> or <I>query</I>
for the basis of the make-table query and choose Q_uery from the New Object button
on the toolbar. Click OK.
<P>
<DD><B>2.</B> Drag any <I>fields</I> you need to identify records into the design
<I>grid</I>. You can also add an additional table and any other fields from the second
related table if you want to use fields from more than one table. Then click the
Query Type button on the toolbar and select the Ma<U>k</U>e-Table Query option.
<P>
<DD><B>3. </B>Enter a name in the Table <U>N</U>ame <I>text box</I>, select either
the <U>C</U>urrent Database or <U>A</U>nother Database <I>option button</I>, then
choose OK.
<P>
<DD><B>4.</B> Enter any <I>criteria</I> for choosing <I>records</I> in the Criteria
row. Click the View button on the toolbar to preview the records to be included.
<P>
<DD><B>5. </B>Then Click the Run button to create the new table.
</DL>
<P>After dismissing the <I>dialog box</I> indicating that a new table will be created,
Access writes your result set to disk as a table in the database you specified.
<H2><FONT COLOR="#000077"><B>Action Query: Update Query</B></FONT></H2>
<P>An update query can make <I>global</I> changes to selected records in a <I>table</I>
or a set of related tables. This type of query is useful for replacing information
quickly.</P>
<P>A common example for an update <I>query</I> would be where you would increase
the price for an item. In the Update To cell under the price <I>field</I> in the
Query Design <I>grid</I> you would type the <I>expression</I> <B>[Price]*1.02</B>
(to increase the price by 2%). For more information on calculations, see "Calculated
Fields: Queries Create" in the Calculations part of this book.
<H3><FONT COLOR="#000077"><B>Steps</B></FONT></H3>
<DL>
<DD><B>1.</B> In the <I>Database window</I>, highlight the <I>table</I> or query
for the basis of the update query and choose <U>Q</U>uery from the New <I>Object</I>
button on the toolbar. Click OK.
<P>
<DD><B>2. </B>In the Query <I>Design View</I>, click the down arrow next to the Query
Type button on the toolbar, then select the <U>U</U>pdate Query option.
<P>
<DD><B>3. </B>Drag the fields from the Field List to the Query Design grid that will
be in the new table. Enter the criteria for your result set, and any sort you want.
<P>
<DD><B>4.</B> In the Update To cell, enter the expression or value to be used as
a replacement for the field(s) selected.
<P>
<DD><B>5. </B>Click the View button to see a list of records that will be updated.
Then click the Run button on the toolbar.
</DL>
<P>After closing the <I>dialog box</I>, Access replaces your old data with the new
values in the result set you specified.
<H2><FONT COLOR="#000077"><B>Criteria: Blanks, Nulls, and Empty</B></FONT></H2>
<P>When you create select or action queries or use the Advanced Filter/Sort option
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -