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.