Microsoft Office Tutorials and References
In Depth Information
CREATING QUERIES
You want a query showing the elapsed time that your employees were on the premises for the day. When
you add the tables, your screen may show the links differently. Click and drag the Job Assignments,
Employee, and Wage Data table icons to look like those in Figure B-38.
FIGURE B-38
Query setup for time arithmetic
Figure B-39 shows the output, which looks correct. For example, employee 09911 was at work from 8:30 a.m.
to 4:30 p.m., which is eight hours. But how does the odd expression that follows yield the correct answers?
FIGURE B-39
Query output for time arithmetic
([ClockOut]
[ClockIn]) * 24
Why wouldn
t the following expression work?
[ClockOut]
[ClockIn]
Here is the answer: In Access, subtracting one time from the other yields the decimal portion of a
24-hour day. Returning to the example, you can see that employee 09911 worked eight hours, which is
one-third of a day, so the time arithmetic function yields .3333. That is why you must multiply by 24
to
convert from decimals to an hourly basis. Hence, for employee 09911, the expression performs the following
calculation: 1/3
8.
Note that parentheses are needed to force Access to do the subtraction first, before the multiplication.
Without parentheses, multiplication takes precedence over subtraction. For example, consider the following
expression:
24
¼
[ClockOut]
[ClockIn] * 24
In this example, ClockIn would be multiplied by 24, the resulting value would be subtracted from
ClockOut, and the output would be a nonsensical decimal number.
Deleting and Updating Queries
The queries presented in this tutorial thus far have been Select queries. They select certain data from specific
tables based on a given criterion. You also can create queries to update the original data in a database. Businesses
use such queries often, and in real time. For example, when you order an item from a Web site, the company
s
database is updated to reflect your purchase through the deletion of that item from the company
sinventory.
 
Search JabSto ::




Custom Search