Microsoft Office Tutorials and References
In Depth Information
CREATING QUERIES
￿
Spell, capitalize, and space a field
s name exactly as you did in the table definition. If the table
definition differs from what you type, Access thinks you are defining a new field by that name.
Access then prompts you to enter values for the new field, which it calls a Parameter Query
field. This problem is easy to debug because of the tag Parameter Query. If Access asks you to
enter values for a parameter, you almost certainly misspelled a field name in an expression in a
calculated field or criterion.
For example, here are some errors you might make for Wage Rate:
Misspelling: (Wag Rate)
Case change: (wage Rate / WAGE RATE)
Spacing change: (WageRate / Wage Rate)
￿
Do not use parentheses or curly braces instead of the square brackets. Also, do not put
parentheses inside square brackets. You can, however, use parentheses outside the square brackets in
the normal algebraic manner.
For example, suppose that you want to multiply Hours by Wage Rate to get a field called Wages
Owed. This is the correct expression:
Wages Owed: [Wage Rate] * [Hours]
￿
The following expression also would be correct:
Wages Owed: ([Wage Rate] * [Hours])
￿
But it would not be correct to omit the inside brackets, which is a common error:
Wages Owed: [Wage Rate * Hours]
Relating Two or More Tables by the Join Operation
Often, the data you need for a query is in more than one table. To complete the query, you must join the
tables by linking the common fields. One rule of thumb is that joins are made on fields that have common
values, and those fields often can be key fields. The names of the join fields are irrelevant; also, the names of
the tables or fields to be joined may be the same, but it is not required for an effective join.
Make a join by bringing in (adding) the tables needed. Next, decide which fields you will join. Then click
one field name and hold down the left mouse button while you drag the cursor over to the other field
s name
in its window. Release the button. Access inserts a line to signify the join. (If a relationship between two
tables has been formed elsewhere, Access inserts the line automatically, and you do not have to perform the
click-and-drag operation. Access often inserts join lines without the user forming relationships.)
You can join more than two tables. The common fields need not be the same in all tables; that is, you can
daisy-chain them together.
A common join error is to add a table to the query and then fail to link it to another table. In that case,
you will have a table floating in the top part of the QBE (query by example) screen. When you run the query,
your output will show the same records over and over. The error is unmistakable because there is so much
redundant output. The two rules are to add only the tables you need and to link all tables.
Next, you will work through an example of a query that needs a join.
AT THE KEYBOARD
Suppose you want to see the last names, employee IDs, wage rates, salary status, and citizenship only for U.S.
citizens and hourly workers. Because the data is spread across two tables, Employee and Wage Data, you
should add both tables and pull down the five fields you need. Then you should add the Criteria expressions.
Set up your work to resemble that in Figure B-17. Make sure the tables are joined on the common field,
Employee ID.
Search JabSto ::




Custom Search