Timekeeping queries in Access can seem simple until dates and times get involved. Then somebody runs a report for a date range, notices that all of the late-afternoon shifts on the ending date are missing, and suddenly payroll looks a little suspicious. This quiz covers a few important Access date, time, totals, and overtime concepts.
Give yourself a few seconds to answer each question before checking the answer. No peeking. Access may not have a lie detector built in, but your conscience probably does.
Question 1: An employee clocks in at 8:00 AM and clocks out at 4:30 PM. In Access, subtracting Time In from Time Out returns a value measured primarily in what unit: hours, minutes, days, or seconds?
The answer is days. Access stores Date/Time values as numbers. One full day equals 1, so one hour is 1/24 of a day. If an employee works 8.5 hours, the raw subtraction result is actually a fraction of a day. To display or calculate hours, you generally multiply that result by 24.
This catches a lot of people the first time they build a timekeeping query. They subtract two times, see a decimal value such as 0.354166..., and wonder what kind of alien math Access is using. It is not alien math. It is just days.
Question 2: You have several work log records for the same employee because they clocked out for lunch. Which type of query is best for calculating that employee's total hours for the day: crosstab, aggregate, delete, or make-table?
The answer is an aggregate query, also commonly called a totals query. If an employee has multiple work periods in one day, calculate the duration of each period and then use the Sum row in the query totals to add those durations together.
A crosstab query is useful when you want to rearrange data into a spreadsheet-like summary. A delete query removes records, which is generally not the preferred payroll calculation method. A make-table query creates a new table. None of those are what you need just to total someone's daily work time.
Question 3: Your employee time query uses Between Start Date And End Date, but shifts later in the day on the end date are missing. Which criteria correctly includes the entire ending date?
The correct criteria is Greater Than or Equal To Start Date And Less Than End Date Plus 1.
In a query criteria expression, that logic looks like this: >= StartDate And < EndDate+1. The important part is using less than the day after your end date, rather than less than or equal to the end date itself.
Why? Because a date entered without a time is treated as midnight. If you search through September 6 using a criterion such as Between #9/1/2026# And #9/6/2026#, Access interprets that ending value as September 6 at 12:00:00 AM. You get records at midnight, but you miss records from 8:00 AM, 4:30 PM, or 11:59 PM that same day. Not exactly what most people mean by "include September 6."
Using a less-than comparison against the next day solves the problem neatly. It includes every possible time on the ending date, without requiring you to type 11:59:59 PM or play games with fractional seconds. This is one of the best habits you can develop when filtering date ranges in Access.
Question 4: A company pays overtime for hours worked beyond eight hours in a day. Which Access function is commonly used in a calculated query field to split regular hours from overtime hours: IIf, DLookup, Replace, or Date?
The answer is IIf, short for Immediate If. It lets you test a condition and return one value if the condition is true and another if it is false.
For example, if total hours are greater than 8, an IIf expression can return 8 regular hours. Otherwise, it returns the actual total hours worked. A similar expression can calculate overtime by returning total hours minus 8 when the employee worked more than 8 hours, and zero otherwise.
DLookup retrieves a value from another table or query, Replace works with text, and Date returns the current date. Useful tools in the right situation, sure, but they are not your go-to functions for separating regular time from overtime.
Question 5: An employee earns $20 per hour and works two overtime hours at time and a half. What should the overtime portion of the payroll calculation return: $20, $40, $60, or $80?
The answer is $60. Time and a half means the overtime rate is $30 per hour. Two overtime hours at $30 each equals $60. Yes, you do occasionally have to do regular old math when working with Access. The database has not yet figured out how to negotiate your union contract for you.
When building payroll calculations, keep the pieces separate: calculate total hours, determine regular and overtime hours, calculate the overtime rate, and then multiply. Trying to cram all of that into one giant expression can work, but it becomes much harder to troubleshoot when somebody's paycheck is off by $60.
If you missed any of these, do not worry. Date and time calculations are one of those Access topics that become much easier once you understand how Access stores values and how query criteria handle the hidden time portion of a date. Watch the embedded video for the full quiz walkthrough and a little more explanation.
Live long and prosper,
RR
No comments:
Post a Comment