Creating a Query Manually


A query can do more than simply return a list of records from a table. You can use functions in a query to perform calculations on the information in a table to produce the sum, average, count, and other mathematical values.

When you want to work with more than one table, you need to move beyond filters and into the realm of queries. Common types of queries include the following:

  • A select query retrieves data from one or more tables and displays the results in a datasheet. You can also use a select query to group records and calculate sums, counts, averages, and other types of totals. You can work with the results of a select query in Datasheet view to update records in one or more related tables at the same time. This is the most common type of query.

  • A duplicate query is a form of select query that locates records that have the same information in one or more fields that you specify. The Find Duplicates Query wizard guides you through the process of specifying the table and fields to use in the query.

  • An unmatched query is a form of select query that locates records in one table that don’t have related records in another table. For example, you could use this to locate people in the customer table who don’t have an order in the order table. The Find Unmatched Query wizard guides you through the process of specifying the tables and fields to use in the query.

  • A parameter query prompts you for information to be used in the query-for example, a range of dates. This type of query is particularly useful when used as the basis for a report that is run periodically.

  • A crosstab query calculates and restructures data for easier analysis. You can use a crosstab query to calculate a sum, average, count, or other type of total for data that is grouped by two types of information-one down the left side of the datasheet and one across the top. The cell at the junction of each row and column displays the results of the query’s calculation.

  • An action query updates multiple records in one operation. It is essentially a select query that performs an action on the results of the selection process. Four types of actions are available:

    • Delete queries, which delete records from one or more tables

    • Update queries, which make changes to records in one or more tables

    • Append queries, which add records from one or more tables to the end of one or more other tables

    • Make-table queries, which create a new table from all or part of the data in one or more tables

Tip 

In addition to these, you can create Structured Query Language (SQL) queries. SQL queries are beyond the scope of this book.

Access includes wizards that guide you through the creation of the common queries, but you create less common queries by hand in Design view, using the design grid.

image from book

The field lists (in the small windows at the top of the query window) list the fields in the four tables that can be included in this query. The lines connecting the tables indicate that they are related by virtue of common fields. The first row of the grid contains the names of the fields actually included in the query, and the second row shows which table each field belongs to. The third row (labeled Total) performs calculations on the field values, and the fourth row indicates whether the query results will be sorted on this field. A selected check box in the fifth row (labeled Show) means that the field will be displayed in the results datasheet. (If the check box isn’t selected, the field can be used in determining the query results, but it won’t be displayed.) The sixth row (labeled Criteria) contains criteria that determine which records will be displayed, and the seventh row (labeled or) sets up alternate criteria.

You can create a query by hand or by using a wizard. Regardless of what method you use to create the query, what you create is a statement describing the conditions that must be met for records to be matched in one or more tables. When you run the query, the matching records appear in a new datasheet.

image from book
Filters and Sorts vs. Queries

The major differences between filtering a table, sorting a table, and querying a table are:

  • The Filter and Sort commands are usually faster to implement than queries.

  • The Filter and Sort commands are not saved, or are saved only temporarily.

    You can save a query permanently and run it again at any time.

  • The Filter and Sort commands are applied only to the table or form that is currently open. A query can be based on multiple tables and on other queries, which don’t have to be open.

image from book

In this exercise, you will create a form based on a select query that combines information from two tables into a datasheet and calculates the extended price of an item based on the unit price, quantity ordered, and discount.

Use the 05_QueryDesign database. This practice file is located in the Chapter11 subfolder under SBS_Office2007.

Open the 05_QueryDesign database.

1. On the Create tab, in the Other group, click the Query Design button.

A query window opens in Design view, and the Show Table dialog box opens. In this dialog box, you can specify which tables and saved queries to include in the current query. image from book

image from book

2. In the Show Table dialog box, on the Tables tab, double-click Order Details and then Products to add each table to the query window. Then close the dialog box.

Each of the selected tables is represented in the top portion of the window by a small field list window with the name of the table-in this case, Order Details and Products-in its title bar.

image from book

An asterisk at the top of each list represents all the fields in the list. The primary key field in each list is indicated by a key icon. The line from ProductID in the Order Details table to ProductID in the Products table indicates that these two fields are related.

Tip 

To add more tables to a query, reopen the Show Table dialog box by rightclicking a blank space in the top portion of the query window and then clicking Show Table, or by clicking the Show Table button in the Query Setup group on the Design contextual tab.

The lower area of the query window contains the design grid where you will specify the query’s criteria.

3. Drag the following fields from the field lists to consecutive columns in the design grid:

Open table as spreadsheet

From this table

Drag this field

Order Details

OrderID

Products

ProductName

Order Details

UnitPrice

Order Details

Quantity

Order Details

Discount

The query will include only the fields that are in the design grid.

Tip 

You can quickly copy a field to the next available column in the design grid by double-clicking the field.

To copy all fields to the grid, double-click the title bar above the field list to select the entire list, and then drag the selection over the grid. When you release the mouse button, Access adds the fields to the columns in order. You can drag the asterisk to a column in the grid to include all the fields in the query, but you also have to drag individual fields to the grid if you want to sort on those fields or add conditions to them.

image from book

4. On the Design contextual tab, in the Results group, click the Run button. image from book

Access runs the query and displays the results in Datasheet view.

image from book

The results show that the query is working thus far. There are two things left to do: sort the results on the OrderID field and add a field for calculating the extended price, which is the unit price times the quantity sold minus any discount.

5. Switch to Design view.

The third row in the design grid is labeled Sort. You can select Ascending, Descending, or (not sorted) in this cell for any of the query fields.

6. In the OrderID column, click the Sort arrow, and then in the list, click Ascending.

Neither of the tables includes an extended price field. Rather than creating the field in a table, you will use the Expression Builder to insert an expression in the design grid that computes this price from existing information.

7. Right-click the Field cell in the first blank column in the design grid (the sixth column), and then click Build.

The Expression Builder dialog box opens.

image from book

Here is the expression you will build:

 CCur([Order Details]![UnitPrice]*[Order Details]![Quantity]*(1-[Order Details]![Discount]))

The CCur function converts the results of the math inside its parentheses to currency format.

Tip 

If you were to type this expression directly into the field, you could simplify it a bit to this:

ExtendedPrice: CCur([Order Details]![UnitPrice]*[Quantity]*(1[Discount]))

The [Order Details]! part is required only with fields that appear in both tables. It tells the query which table to use.

8. In the first column of the elements area, double-click the Functions folder to display its contents, and then click Built-In Functions.

Categories of built-in functions appear in the second column; actual functions within each category appear in the third column.

9. In the second column, click Conversion to limit the functions in the third column to those in that category. Then in the third column, double-click Ccur.

image from book

You’ve inserted the currency conversion function into the expression box. The <<expr>> inside the parentheses represents the other expressions that will eventually result in the number Access should convert to currency format.

10. In the expression box, click <<expr>> to select it so that the next thing you enter will replace it.

The next element you want in the expression is the UnitPrice field from the Order Details table.

11. In the first column, double-click the Tables folder, and click Order Details. Then in the second column, double-click UnitPrice.

The insertion point is currently situated after UnitPrice, which is exactly where you want it. Now you want to multiply the amount in the UnitPrice field by the amount in the Quantity field.

12. In the row of operator buttons below the expression box, click the Multiply button. image from book

Access inserts the multiplication sign and another <<Expr>> placeholder.

13. In the expression box, click <<Expr>> to select it, and then in the second column, double-click Quantity.

What you have entered so far calculates the total cost by multiplying the price of an item by the quantity ordered. However, suppose the sale price is discounted due to quantity or another factor. The discount, which is stored in the Order Details table, is expressed as the percentage to deduct. But it is easier to compute the percentage to be paid than it is to compute the discount and subtract it from the total cost.

14. In the expression box, type *(1-. In the second column, double-click Discount, and type ). If the entire expression isn’t visible in the window, widen the window by dragging its right edge.

image from book

Although the discount is formatted in the datasheet as a percentage, it is actually stored in the database as a decimal number between 0 and 1. (For example, a discount displayed as 10% is stored as 0.1). So if the discount is 10%, the result of *(1-Discount) is *.9. In other words, the formula multiplies the unit price by the quantity and then multiplies that result by 0.9.

15. In the Expression Builder dialog box, click OK.

Access enters the expression in the design grid.

16. Press image from book to move the insertion point out of the field and complete the entry of the expression.

Access labels the expression Expr1, which isn’t particularly meaningful.

Tip 

You can quickly make a column in the design grid as wide as its contents by double-clicking the line in the gray selection bar that separates the column from the column to its right.

17. In the design grid, double-click Expr1, and then type ExtendedPrice as the label for the expression.

18. Switch to Datasheet view.

image from book

The orders are now sorted by the OrderID field, and the extended price is calculated in the last field.

19. Scroll down to see a few records with discounts.

If you check the math, you will see that the query calculates the extended price correctly.

20. Close the query window. In the Microsoft Office Access message box asking whether to save the query, click Yes. In the Save As dialog box, type Order Details Extended, and then click OK.

Close the 05_QueryDesign database.

image from book
Expression Builder

To create an expression as a filter or query option, you can either type the expression or use the Expression Builder. You can open the Expression Builder by clicking Build on a shortcut menu, clicking Builder in the Query Setup group, or clicking the Build button (which resembles an ellipsis) at the right end of a box that can accept an expression.

The Expression Builder isn’t a wizard; it doesn’t lead you through the process of building an expression. But it does provide a hierarchical list of the most common elements that you can include in an expression. You can either type your expression in the expression box, or you can select functions, operators, and other elements to copy them to the expression box.

image from book



2007 Microsoft Office System Step by Step
2007 MicrosoftВ® Office System Step by Step
ISBN: 0735622787
EAN: 2147483647
Year: 2004
Pages: 231

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net