Microsoft Office Tutorials and References
In Depth Information
You use the new field name in the new query as the current average, and you treat the new name like a
Increased Rate: 1.1 * [Average Rate]
The query within a query is shown in Figure B-32.
A query within a query
Figure B-33 shows the output of the new query. Note that the calculated field is formatted.
Output of an Expression in a Totals query
You do not need to print or save the query output, so return to Design view and close the query.
Using the Date() Function in Queries
Access has two important date function features:
The built-in Date() function gives you today
s date. You can use the function in query criteria or
in a calculated field. The function “returns” the day on which the query is run; in other words, it
inserts the value where the Date() function appears in an expression.
Date arithmetic lets you subtract one date from another to obtain the difference
in number of
days—between two calendar dates. For example, suppose you create the following expression:
10/9/2009 – 10/4/2009
Access would evaluate the expression as the integer 5 (9 minus 4 is 5).
As another example of how date arithmetic works, suppose you want to give each employee a one-dollar
bonus for each day the employee has worked for you. You would need to calculate the number of days
between the employee
s date of hire and the day the query is run, and then multiply that number by $1.
You would find the number of elapsed days by using the following equation:
Also suppose that for each employee, you want to see the last name, employee ID, and bonus amount.
You would set up the query as shown in Figure B-34.