`
`
`
`
`
`Merrill Communications LLC
`d/b/a Merrill Corporation
`Exhibit 1005 pt 8
`
`

`

`Oueruing Your Tables
`
`ueries allow you to answer questions
`about your data, to extract specific
`information from tables, and to
`change selected data in various ways. In fact, the ability to perform queries is a key rea(cid:173)
`son for using database management programs-rather than spreadsheets or word
`processing programs-to manage large amounts of related data. In this chapter you'll
`learn how to use the Query Wizards and the graphical query by example (QBE) tools in
`Access to query your data with relative ease.
`As you read through this chapter, keep in mind that many techniques for customiz(cid:173)
`ing datasheet view and creating filters apply to queries as well. So if you haven't worked
`through Chapter 9, you should do so before diving into queries. You also should under(cid:173)
`stand the basic table design concepts (Chapter 6) and know how to edit in datasheet
`view (Chapter 8).
`
`What ~ueries Let You Do
`
`Queries let you see the data you want, in the order you want it. They also allow you to
`perform calculations on your data; to create sources of data for forms, reports, charts,
`and other queries; to make global changes to tables; and to create new tables.
`
`

`

`360
`
`CHAPTER 10 • QUERYING YOUR TABLES
`
`•
`
`If the main reason you're creating a query is to use it as a source of records for
`a multitable form or report, you may not need to set up a query at all. Often,
`the Form Wizard or Report Wizard offers a faster way to simultaneously design the
`form or report and create an SQL statement that defines the record source for you.
`Chapters 11 and 12 describe these versatile Wizards in more detail. Of course, if
`you have many records, a form or report will operate faster if it uses a saved query
`(rather than an SQL statement) as its record source.
`
`When you run most types of queries or apply a filter, Access collects the data you ask
`for in a dynaset. Although the dynaset looks and acts like a table, it's actually a dynamic
`or "live" view of one or more tables. Therefore, changes that you make after running a
`query usually will affect the underlying tables in the database itself.
`
`A dynaset is an updatable type of recordset, which is any collection of records
`·.a-=-"'~ that you can treat as an object. Some types of queries-such as those that cre(cid:173)
`ate cross-tabs and other summaries-generate recordsets in which some or all
`fields are not updatable.
`
`T~pes of Oueries
`
`You can create several types of queries as summarized below:
`
`Select Query The most commonly used query type, a Select query lets you select
`records, create new calculated fields, and summarize your data. Select queries are
`similar to the filters discussed in Chapter 9.
`However, unlike filters, Select queries also let you:
`
`• Query more than one table
`• Create new calculated fields
`• Summarize and group your data
`• Choose which fields to show or hide
`
`Crosstab Query Groups data into categories and displays values in a spreadsheet(cid:173)
`like format with summary totals. You can use Crosstab queries to compare values
`and see trends in your data, to view summary data such as monthly, quarterly, or
`yearly sales figures, and to answer questions such as Who has ordered how many of
`what? Crosstabs are especially useful as the basis for reports and charts.
`
`

`

`USING QUERY WIZARDS
`
`1361
`
`Make Table Query Creates a new table from a dynaset. Use Make Table queries
`to create a backup copy of a table, save a history table of old records you'll be
`deleting from another table, select a subset of data to report on, or create a table
`for exporting to other applications.
`Update Query Lets you make global changes to data in one or more tables.
`These queries offer a powerful, fast, and consistent way to change many records in
`one fell swoop. For example, you can use an Update query to increase the price of
`all tether ball items by 25 percent, or to empty out certain fields.
`Append Query Adds records from one or more tables to the end of an existing
`table. Append queries are especially useful for adding old records to the end of a
`history table. You can then convert the Append query to a Delete query (described
`next) and delete the old records from the original table.
`Delete Query Deletes a group of records from one or more tables. For instance,
`you can delete all customer records for people who haven't bought anything in five
`years or more. Or, after appending data to a history table, you can use a Delete
`query to remove the old records from the original table.
`Pass-Through Query Strictly for SQL mavens, Pass-Through queries send com(cid:173)
`mands directly to a SQL database server using the syntax required by that server.
`For information on this topic, search for Queries, Pass-Through Queries in the Access
`Help Index.
`Data-Definition Query Also for SQL mavens, data-definition queries use SQL
`language statements to create or change database objects in the current database.
`To learn about this topic, search for Queries, Data-Definition Queries in the Access
`Help Index.
`Union Query Another one for SQL mavens, Union queries use SQL language
`statements to combine corresponding fields from two or more tables or queries into
`one field . See Queries, Union Queries in the Access Help Index for details.
`
`Cll "' Ill
`.Q
`·::~
`Ill
`Q
`Ill
`
`c:n c
`';C
`Ill
`I!!
`u
`
`Access offers two ways to create queries: the Query Wizards and the "from scratch"
`methods. We'll look at the Query Wizards first.
`
`Using Ouer~ Wizards
`
`Query Wizards offer a quick and easy way to perform the special-purpose queries listed
`below:
`
`Simple Query Wizard Creates a Select query for one or more tables. The resulting
`query can do simple selection, or it can calculate sums, averages, counts, and other
`types of totals.
`
`

`

`362 ll CHAPTER 1 0 • QUERYING YOUR TABLES
`
`Crosstab Query Wizard Creates a crosstab for a single table or query.
`Find Duplicates Query Wizard Finds duplicate records in a single table or query.
`Find Unmatched Query Wizard Finds records in one table that have no related
`records in another. For example, you can use this Wizard to create queries that
`find customers who haven't placed orders or to find employees who haven't sold
`orders to any customers.
`
`Often the Query Wizards will be able to set up the perfect query for your needs. But even
`if the query that the Wizard creates is only "almost good enough," using the Wizards can
`save you time in the initial query design. You can always switch to design view (discussed
`soon) and tweak the Wizard-generated query as needed.
`To use the Query Wizards:
`
`1. Click on the drop-down arrow next to the New Object toolbar button (the second-to(cid:173)
`last button on the toolbar) from any window in an open database and then choose
`Query. Or click on the Queries tab from the database window and then click on the
`New button, or choose Insert >- Query.
`2. Double-click on one of the options described above (Simple Query Wizard, Crosstab
`Query Wizard, Find Duplicates Query Wizard, or Find Unmatched Query Wizard) in
`the New Query dialog box.
`3. Follow the Wizard's instructions and complete the dialog boxes that appear. (See
`Chapter 3 for information about basic techniques you can use with Access Wizards.)
`
`In a flash, you'll have a new query that you can either use as is or customize as needed.
`
`Creating, Running, Saving, and Changing a Ouer~
`
`Now let's look at the basic steps for creating queries from scratch and for running, sav(cid:173)
`ing, and changing queries. Later in the chapter you'lllearn how to refine the basics to
`set up any query you want.
`
`Creating a Query from Scratch
`The Query Wizards can create the specialized queries discussed in the previous section.
`To create other types of queries, however, you'll need to start from scratch. Here are the
`steps to follow:
`
`1. Click on the drop-down arrow next to the New Object toolbar button (second-to(cid:173)
`last button on the toolbar) from any window in an open database, and then choose
`Query. Or click on the Queries tab from the database window and then click on the
`New button, or choose Insert>- Query.
`
`

`

`CREATING, RUNNING, SAVING, AND CHANGING A QUERY
`
`363
`
`2. Double-click on Design View in the New Query dialog box. A query design window
`named Queryl : Select Query opens and the Show Table dialog box appears (see
`Figure 10.1).
`
`If you already know which table you want to query, go to the database window, click
`on the Tables tab, click on the table you want to create the new query from, click on
`the drop-down arrow next to the New Object tool bar button, click on Query, and then
`double-click on Design View. The Show Table dialog box won't appear when you
`follow these steps, but you can display it (if necessary) by choosing Query >(cid:173)
`Show Table.
`
`3. Use any of these techniques to add the tables you want to query:
`
`• To choose which objects to list in the Show Table dialog box, click on the
`Tables, Queries, or Both tabs at the top of the Show Table dialog box.
`• To add an object to the query design window, double-click on the object you
`want to add, or highlight the object and then click on the Add button.
`• To add multiple adjacent objects to the query design window, click on the
`first object you want to select; then Shift-click on the last object you want (or
`drag your mouse through the object names). Click on Add.
`• To add multiple nonadjacent objects to the query design window, click on
`the first object you want to select; then hold down the Ctrl key and click on each
`remaining object. Click on Add.
`
`•
`
`Field: f-- - ---+- -·
`Table: f---- - - - - t - - ·
`5011: f----,...----+- -·
`Show:
`Criletia: f-----'-" - - - t- - ·
`01:
`
`J
`
`I
`Add
`~ ~
`
`Iilli T abies l ~ Queries I ~ Both ]
`
`CajUn Products
`Customers
`Employees
`Expense Categories
`Expense Details
`Expense Report5
`Gra<Jes
`My CQmpafly Information
`CWder Pe~.,l~
`Orders
`Paytnent; Methods
`Payments
`
`7:
`; ,
`
`~ 1
`..:.J
`
`*#rlYil'''''
`
`The Select
`Query window
`and Show Table
`dialog box
`appear when
`you create a
`new query
`from scratch.
`
`

`

`364 I CHAPTER 10 • QUERYING YOUR TABLES
`
`..
`
`4. Repeat step 3 as needed. When you're done, click on Close. Join lines will appear
`automatically if you created relationships between tables (Chapter 6) or if Access
`can figure out the relationships on its own. You also can join the tables manually,
`as described later in this chapter.
`5. Click on the drop-down list next to the Query Type toolbar button (shown below)
`to pick the type of query you want to set up or choose options from the Query
`menu. Your options are Select (the default and most commonly used query type),
`,Crosstab, Make Table, Update, Append, and Delete. (See "About the Query Design
`Window's Toolbar" for details about the toolbar buttons.)
`
`lljl ~tQ-y
`v
`n'l Cumtet!
`· I M!!!ti·Tabll< Quorv ...
`I \kid& Query
`l+tiiQp!lnd GYct ~ ..
`)(l ~leto~
`
`To convert one type of query to another, simply choose the appropriate type from
`the Query Type toolbar button or the Query menu.
`
`6. Double-click on fields that you want to display in the dynaset or use in selection
`criteria in the tables area. Or drilJ the fields from the tables area to the QBE grid. Or
`select the fields from the drop-down list in the Field row of the QBE grid.
`7. Specify any selection criteria that you want to use for isolating records in the dynaset
`in the Criteria rows under the appropriate column in the QBE grid. The techniques are
`similar to those for designing a filter (see Chapter 9).
`8. Fill in other areas of the QBE grid as needed (see "Refining Your Query" later in
`the chapter).
`9. (Optional) Specify properties for the query itself or for an individual field. See
`"Refining Your Query."
`
`The example in Figure 10.2 shows a complex Select query that's based on several
`related tables. This query will
`
`• Select records in which the order was placed in February 1997 and the quantity
`times the unit price ([Quantity]*[Order Details]! [Unit Price]) is greater than $75.
`• Display the ContactLastName and ContactFirstName fields from the Customers table
`(see the Field and Table rows in Figure 10.2), the OrderDate from the Orders table, the
`Quantity and UnitPrice from the Order Details table, a calculated field named $,
`
`

`

`CREATING, RUNNING, SAVING, AND CHANGING A QUERY
`
`365
`
`Tables area
`
`Tool bar
`
`Fields list
`
`•
`
`1i'rlW11'14
`A Select query
`to find orders
`for a specified
`date and
`dollar amount
`of items.
`
`or:
`
`•
`
`I
`
`• I I "
`
`Lookup I
`General
`Description
`Format . • •
`Input Mask .
`
`• .
`
`s_~m;~
`
`Sizing bar
`
`QBE grid
`
`Field Properties sheet
`
`and the ProductName field from the Products table. In the dynaset, the $ field will
`have the column heading Total Price and will appear in Currency format (see the
`Field Properties sheet in the figure).
`• Sort the results by ContactLastName and ContactFirstName (in ascending order)
`and OrderDate (in descending order) for multiple orders from the same customer.
`
`Because both the Order Details and the Products tables have a UnitPrice field, we
`had to explicitly tell Access to use the UnitPrice field in the Order Details table
`when multiplying the Quantity times the Unit Price in the query. To specify the
`table name and field name, use the format [Tab l eName] ! [Fie 1 dName], as in [Order
`Details]! [UnitPrice] .
`
`Figure 10.3 shows the dynaset produced by this query.
`
`

`

`366
`
`CHAPTER 1 0 • QUERYING YOUR TABLES
`
`•naw•«·••
`
`The dynaset
`produced by the
`query in
`Figure 1 0.2.
`
`Field Properties shown in Figure 10.2 control this field's caption and format.
`
`..
`
`, , , .
`
`Wilbur
`
`Record~j
`
`- ltJI)(ll
`Produd N11me
`
`Tips for Using the Query Design Window
`Here are some tips and shortcuts for designing a query:
`
`• To clear all the items from the QBE grid, choose Edit >- Clear Grid. Caution:
`You cannot undo Clear Grid.
`• To expand the editing area for an input cell in the QBE grid, click in the cell
`you want to expand and then press Shift+F2. Or right-click on the cell and choose
`Zoom. In the Zoom Box that appears, edit the text as needed and then click on
`OK to close the box.
`
`The Zoom Box is available anywhere an expanded input area might be handy, includ(cid:173)
`ing property sheets, a table's design or datasheet view, the QBE grid, the filter window,
`and the macro window.
`
`• To see which table each field in the QBE grid is from or to hide that information,
`choose View>- Table Names or right-click on the QBE grid and choose Table Names.
`When you select (check) the View>- Table Names option or the Table Names short(cid:173)
`cut menu option, a Table row will appear in the QBE grid (see Figure 10.2). When you
`deselect the option, this row is hidden.
`• To change a field name in the QBE grid, click to the left of the field name's first let(cid:173)
`ter in the QBE grid and then enter the new name followed by a colon. We used this
`technique to change the original name of an expression field to$ in Figure 10.2. The
`new field name will be used in the dynaset unless you've specified a Caption in the
`Field Properties sheet (see Figures 10.2 and 10.3).
`
`

`

`CREATING, RUNNING, SAVING, AND CHANGING A QUERY
`
`1367
`
`• To add tables to the query design window, click on the Show Table toolbar button
`(shown at left); or right-click on an empty place in the tables area and choose Show
`Table; or choose Query > Show Table; or open the database window (Fll) and then
`choose Window> Tile Vertically so that you can see the database and query design
`windows. Next click on the database window's Tables tab and then drag the table
`name from the database window to the tables area of the query design window.
`• To remove a table from the query design window, click on the table and then
`press the Delete key. (This step doesn't remove the table from the database, only
`from the query design window.)
`• When a query gets too complicated, consider using two queries. Create, test,
`and save the first query. Then, with the first query open in the query design or
`datasheet window, click on the drop-down arrow next to the New Object toolbar
`button, choose Query, and then double-click on Design View. Now design, test,
`and save your second query (which will be based on the first one). In the future,
`you can just run the second query to get the results you want.
`
`For help with designing queries, look up any of the subtopics under Queries in the
`Access Help Index. Or go to the Help Contents, open the Working With Queries book,
`open the Creating A Query book, and explore subtopics there (a great jumping off point
`is the Create A Que1y subtopic).
`
`Viewing the Dynaset
`
`You can view the dynaset anytime:
`
`• Click on the Query View toolbar button (shown at left) or choose View >
`Datasheet View.
`
`If you need to cancel a query while it's running, press Ctri+Break. (This cancel
`procedure may not work for very large queries.)
`
`The dynaset will appear in Datasheet view (see Figure 10.3). From here you can do
`any of the following:
`
`• Edit records in the dynaset. Your changes will update the underlying tables.
`• Customize the dynaset's appearance. Use the same techniques you use to cus(cid:173)
`tomize a datasheet (see Chapter 8). You can save your layout changes when you
`save the query.
`• Sort the dynaset. Use the same techniques you use to sort a table (see Chapter 9).
`
`

`

`368
`
`CHAPTER 1 0 • QU ERYING YOUR TABLES
`
`..
`
`• Filter the dynaset. Use the same techniques you use to filter a table (see Chapter 9).
`
`• Preview and print the dynaset as for any other table. To preview the data, click
`on the Print Preview toolbar button or choose File >- Print Preview. When you're
`ready to print the data, click on the Print toolbar button or choose File >- Print.
`Chapter 9 offers more details on printing.
`• Return to the query design window. To do so, click on the Query View toolbar
`button (shown at left) or choose View>- Design View. Your query will reappear in
`the query design window exactly as you left it.
`
`Viewing the SQL Statement behind a Query
`If you're familiar with SQL (or you'd like to become better acquainted), you can view
`or edit your query in SQL view. To switch to SQL view, open the query in design or
`datasheet view. Then click on the drop-down arrow next to the Query View toolbar
`button and choose SQL View; or choose View >- SQL View. View or change the SQL
`statement as needed and then click on the Query View tool bar button to see the results
`in datasheet view.
`
`Structured Query Language (SQL) is used behind the scenes in queries and other
`Access objects. However, it's not for the faint of heart or the inexperienced data(cid:173)
`base user. Be careful when changing SQL statements; if you make mistakes, your
`queries and other objects may not work properly. For more about SQL, look up
`SQL Statements, Using In Queries in the Access Help Index.
`
`Running an Action Query
`If you've designed an action query (Make Table, Update, Append, or Delete query),
`switching to datasheet view will let you see which records will be affected when you run
`the query. But no changes will be made to any tables, and no new tables will be created.
`Previewing in datasheet view gives you a chance to look before you leap.
`When you're sure the dynaset shows exactly which records the action should act on,
`you can run the query this way:
`
`1. Return to the query design window and click on the Run toolbar button (shown at
`left), or choose Query>- Run .
`2. Respond to any prompts from the action query to confirm your changes. (There's
`more about action queries later in the chapter.)
`
`• •
`
`

`

`CREATIN G, RUNNING, SAVIN G, AND CHANGING A QUERY
`
`369
`
`If the action query's confirmation messages annoy you, or if you're not getting
`confirmation messages, choose Tools>- Options and then click on the Edit/Find
`tab. To make sure confirmation messages appear (the safest choice), check the
`Action Queries box under Confirm. To suppress those messages, clear the Action
`Queries box. Click on OK to close the Options dialog box.
`
`The action query will do its job, and you'll be returned to the query design window.
`
`Running a Select query or Crosstab query is the same as viewing the query's
`datasheet.
`
`For help with viewing and running action queries, look up subtopics under Action
`Queries in the Access Help Index.
`
`Saving ·Your Query
`
`To save a new or changed query, use any of these techniques:
`
`• If you're in the query or datasheet window, click on the Save toolbar button
`(shown at left) or choose File )- Save or press Ctrl+S.
`• If you're done with the query for now, close the query design window or
`datasheet view (for example, choose File,... Close or press Ctrl+W). When asked if
`you want to save your changes, click on Yes.
`
`If this query is new, you'll be prompted for a query name. Type a standard Access
`name (up to 64 characters including blank spaces) and then click on OK. Note that
`Access won't let you save the query with the same name as an existing table or query.
`When you save a query, Access saves only the query design, not the resulting record
`set. That way, the query will operate on whatever data is in your tables at the time you
`run the query.
`
`Opening a Saved Query
`
`To reopen a saved query:
`
`I. Start in the database window and click on the Queries tab.
`
`

`

`370
`
`CHAPTER 1 0 • QUERYING YOUR TABLES
`
`2. Do one of the following:
`
`• To view the query's dynaset or run an action query, double-click on the
`query name, or highlight the query name and then click on Open.
`• To open the query's design window, highlight the query name and then click
`on Design.
`
`After opening the query, you can switch between datasheet view and design view by
`clicking on the Query View toolbar button.
`
`About the Query Design Window's Toolbar
`
`As you saw in Figure 10.2, the query design window's toolbar contains many buttons.
`Proceeding from left to right on the tool bar, Table 10.1 briefly describes what each one
`does. (In Table 10.1 we've omitted the standard Copy, Cut, and Paste buttons, which
`appear on most Access toolbars, and the Print, Print Preview, Spelling, and Format
`Painter buttons, which are dimmed and unavailable.)
`
`I
`
`TABLE 10.1: THE QUERY WINDOW'S MOST IMPORTANT TOOLBAR BUTTONS
`
`BUTTON
`
`BUTTON NAME
`
`WHAT IT DOES
`
`• ~ DesignView
`SQL SQL View
`l!illl Datasheet View
`
`Query View
`(Design View)
`
`Query View
`(SQL View)
`
`Query View
`(Datasheet View)
`
`Save
`
`Undo
`
`Switches to the query window, where you can
`design or change your query.
`Switches to SQL view, where you can use SQL
`statements to design or change your query.
`Switches to datasheet view, where you can
`view the dynaset. If you're designing an action
`query, use this button to preview which
`records the query will affect.
`
`Saves your latest changes to the query.
`
`Undoes your most recent change to a criterion
`entry (when you are still in that field).
`
`Continued I ~
`
`

`

`CREATING, RUNNING, SAVING, AND CHANGING A Q UERY
`
`371
`
`I
`
`TABLE 10.1: THE QUERY WINDOW'S MOST IMPORTANT TOOLBAR BUTTONS (CONTINUED)
`
`BUTTON
`
`BUTTON NAME
`
`WHAT IT DOES
`
`liflll!l
`
`Iii ~m:\ Query
`11!1 Ct~s~Query
`~ ~ ! M.Tiltlle~ry ...
`, D I !.!Pdete Query
`1+1 AJ;!pend<:,Juer.,. ...
`
`~ · Qe~tll~\1
`
`em
`
`Query Type
`(Select Query)
`
`Query Type
`(Crosstab Query)
`Query Type (Make-
`Table Query)
`
`Query Type
`(Update Query)
`
`Query Type
`(Append Query)
`
`Query Type
`(Delete Query)
`
`Run
`
`Show Table
`
`Totals
`
`Top Values
`
`Displays the QBE grid for a Select query.
`
`Displays the QBE grid for a Crosstab query.
`
`Displays the QBE grid for a Make-Table query.
`
`Displays the QBE grid for an Update query.
`
`Displays the QBE grid for an Append query.
`
`Displays the QBE grid for a Delete query.
`
`Runs an action query. For Select and Crosstab
`queries, this button has the same effect as the
`Query View (Datasheet View) button.
`
`Lets you add more tables to the tables area of
`the query window.
`
`Displays a Total row in the QBE grid. Use this
`row to specify how data will be grouped and
`summarized.
`
`Lets you choose whether to return a specified
`number of records, a percentage of records, or
`all values. Access uses the left-most sorted field
`to choose which top values to display. You can
`choose a setting from the Top Values button's
`drop-down list, or type a number (e.g., 25) or
`a percentage (e.g., 47%) into the Top Values
`combo box.
`
`Continued I ~
`
`Cll
`"' Ill
`Ill ...
`
`.Q
`
`Ill
`0
`Ill
`C'l
`c
`:;;
`Ill
`
`Cll ... u
`
`

`

`372 I CHAPTER 1 0 • QUERYING YOUR TABLES
`
`I
`
`TABLE 10.1: THE QUERY WINDOW'S MOST IMPORTANT TOOLBAR BUTTONS (CONTINUED)
`
`BUTTON
`
`BUTTON NAME
`
`WHAT IT DOES
`
`~
`~
`~
`
`~[
`o;fjJ AutoFQI'm
`ff AutoR~ort
`~ Iable
`@ Query
`
`, B.eport
`t: M~cro
`'n ~odu!e
`~ ~lass Module
`
`Properties
`
`Build
`
`Opens the property sheet, where you can
`change field or query properties.
`
`Opens the Expression Builder, which makes it
`easier to enter complicated expressions.
`
`Database Window
`
`Opens the database window.
`
`New Object
`(AutoForm)
`
`New Object
`(Auto Report)
`
`New Object
`(Table)
`
`New Object
`(Query)
`
`New Object (Form)
`
`New Object
`(Report)
`
`New Object
`(Macro)
`
`New Object
`(Module)
`
`Creates a new automatic form that's based on
`the current query.
`Creates a new automatic report that's based
`on the current query.
`Lets you create a new table. The table is not
`based on the current query.
`
`Lets you create a new query that's .based on
`the current query.
`Lets you create a new form that's based on the
`current query.
`Lets you create a new report that's based on
`the current query.
`
`Lets you create a new macro. The macro is not
`based on the current query.
`
`Lets you create a new module. The module is
`not based on the current query.
`
`New Object
`(Class Module)
`
`Lets you create a new class module not based
`on a form or report.
`
`Office Assistant
`
`Opens the Office Assistant.
`
`

`

`REFINING YOUR QUERY 1373
`
`Refining Your Ouer~
`
`In the next few sections, we'll explain how to refine your queries.
`
`Filling In the QBE Grid
`
`Filli ng In the QBE gdd is perhnps th e trickiest part of tlesignlng a query. Figure 10.2 shows
`a typical Select query and its QBE grid. The rows that appear in the grid depend on U1e
`type of query you're designing. For example, a Delete q uery will not· l,ave <l Sort row .
`Here are some techniques you can use to fill the row:> in the Q13f. grid for a Select
`query. You'll find more examples in later sections.
`
`•
`
`In t h e Fie ld row, enter U1c fields you want to work with. Use doublc-d.id.ing or
`dragging t<> add fields from the Oclds list (~ee Chapter 9). Or to 1cll Accl·Ss to d isplay
`all fields even lf the table structure changes, use the asterisk (*) field name (see
`"Using the Asterist.. in a QBE Grid" below). Or douhle-cllck on the t<lble name to
`~e lect all fie lds and t hen drag any field to the Field row (all fields will come a long
`for the ride). Or usc the drop-down List in the Field row to select the field you want.
`You also can create new calculated fields In the Field row (see "Totals, Averages, and
`Other Calculations").
`• ln th e Sort row, choose the sort order you want for cad1 Odd. Your optio ns are
`Ascending, IJesccnding, or Not Sorted. Like fil ler' (Chapter 9), query fields arc sorted
`in left-to-right order, so you may need to reposition columns in the grid.
`
`Using the Asterisk in a QBE Grid
`You can use the asterisk in a QBE grid to tell Access that you want all fields to appear
`in the dynaset. The resulting dynasct will always conlaLo alii he fields in the table, even
`if you odd or delete fie lds in the table structure later.
`To add the asterisk to a Field column in the QBEgrid, double-click on the asterisk(*)
`at Ule top or the appropriate fie lds li~t or drag the asterisk to thf:! grid. The F.icld column
`will show the table name followed by a period and thl• asterisk, like this:
`
`Customers ."
`
`lf you use the asterisk, you ~till can sort on, select, group hy, calculate wilb, and do
`other operations w ltb il specific fie ld. To do so, add the appropriate field to the Q13E
`grid. Then to prevent that field from appearing twict' In the dynw.et, deselect the field's
`Show box.
`
`

`

`372
`
`CHAPTER 10 • QUERYING YOUR TABLES
`
`I
`
`TABLE 10.1: THE QUERY WINDOW'S MOST IMPORTANT TOOLBAR BUTTONS (CONTINUED)
`
`BUTTON
`
`BUTTON NAME
`
`WHAT IT DOES
`
`~:
`~ AutoFQ.rm
`If AutoR~ort
`ttl Iable
`"iiP Query
`'Gil Eorm
`'fl B.eport
`'z:% M~cro n ModUe
`
`~ !:.lass Module
`
`Properties
`
`Build
`
`Opens the property sheet, where you can
`change field or query properties.
`
`Opens the Expression Builder, which makes it
`easier to enter complicated expressions.
`
`Database Window
`
`Opens the database window.
`
`New Object
`(AutoForm)
`
`New Object
`(AutoReport)
`
`New Object
`(Table)
`
`New Object
`(Query)
`
`New Object (Form)
`
`New Object
`(Report)
`
`New Object
`(Macro)
`
`New Object
`(Module)
`
`Creates a new automatic form that's based on
`the current query.
`Creates a new automatic report that's based
`on the current query.
`Lets you create a new table. The table is not
`based on the current query.
`Lets you create a new query that's based on
`the current query.
`Lets you create a new form that's based on the
`current query.
`Lets you create a new report that's based on
`the current query.
`
`Lets you create a new macro. The macro is not
`based on the current query.
`Lets you create a new module. The module is
`not based on the current query.
`
`New Object
`(Class Module)
`
`Lets you create a new class module not based
`on a form or report.
`
`Office Assistant
`
`Opens the Office Assistant.
`
`

`

`REFINING YOUR QUERY
`
`Refining Your Ouerq
`
`In Lhc next few sections, we'll explain how 10 refine your querie!>.
`
`Filling In the QBE Grid
`
`Filling In the QBEgrid is perl1aps the trickics'l part of design1rtg a query. Pigure l0.2shows
`a typical Select query and its QBE grid. nw rows thal appear in the grid depend 011 the
`Lype of query you're designing. For example, a Delete query will not have a Sort row.
`Here are some techniques you can use to fill the rows in Lhe QllE grid for a Select
`query. You'll find more examples in later Sl'Ctions.
`
`•
`
`•
`
`In the Field row, enter Lhe field~ you want to work with. Use double-clicking or
`dragging to add fie lds from the fields list (see Chapter 9). Or to teH Access to display
`all fi elds even if the table str·ucture changes, usc the asterisk (*) field name (see
`"Using the Asterisk. in a QBE Grid" below). Or double-click on the table name to
`select all fields ancl then drag any field to tht• Field row (all fields will come along
`for the ride). Or usc the drop-down list In the Field row to select the field you want.
`You also can create new cn lculated flelcls In the field row tsee "Totals, Averages, and
`Other Calculations").
`ln lhe Sort row, choose Ule sort order you want for (•ach field. Your options are
`Ascending, Descending, or Nol Sorted. l.lke fi lters {Chapter 9), query fields are sorted
`in left-to-right order, so you may need to reposillon columns in the grid.
`
`Using the Asterisk in a QBE Grid
`You can use the asterisk in a QBE grid to tel l Access tl1at you want all fields to appear
`In the dynaset. The resulting dyoasct will always contain all the fieltls in the table, even
`if you add or delete flc lds in the tabl~ structure later.
`To add the asterisk to a Field column in the QBE grid, double-dick on the asterisk(*)
`at the top of the appl'Opriate fields list or dr<1g the ast·erisk to the grid. The Field column
`will show the table name followed by a period and the asterisk, like this:
`
`Customers .*
`
`U you use the asterisk, you still can sort on, select, group by, calculate with, and do
`other opera tiot1S with a specific fJ eld. To clo so, add the appropriate rle.Jtl t·o the Q13E
`grid. Then to prevent that field from appearing twice In the dynaset, deselect the field's
`Show box.
`
`

`

`374
`
`CHAPTER 10 • QUERYING YOUR TABLES
`
`When you design Make Table, Delete, or Append queries, consider using the aster(cid:173)
`isk so that the query always operates on the current structure of the underlying
`table, and the operation includes all the fields.
`
`Changing Field Properties
`Field properties govern the appearance of fields in the dynaset. By default, fields added to
`the QBE grid will inherit properties of the underlying table. However, calculated fields,
`such as the field$: [Quantity] * [Order Detai 1 s] ! [UnitPri ce] shown back in Figure 10.2, do
`not inherit properties. To assign or change fi