• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 309
  • Last Modified:

Creating a Time and Attendance Query/Report in Access 2003

Hi

I have table in access that contains a number of "clock in" and "clock out" entries, for time and attendance purposes.

Due to the nature of the work there will be several "clock in's" and several "clock out's" per day.

The table contains the following data.

UserID <long integer>
TimeStamp <general date>
Status <long integer> 1=Clock In, 0=Clock Out, 2=Attemtped Clock In/Out outside of permitted hours

I need to be able to produce a report for a particular time period (e.g. a week, or a month) that will group the report by user. It will then show in chronological order all the clock in's/out's and work out the time worked for each period. The report will then calculate a total per day and then a grand total for the week/month etc.

Example Report.

Time Report for week commencing May 3rd 2006.

Mr Smith

Monday May 3rd

Clock In      Clock Out      Time Worked
09:00         10:30           1:30
10:45         12:30           1:45
13:00         17:30           4:30

Monday Total = 7:45

Tuesday May 4th

Clock In      Clock Out      Time Worked
09:30         10:30           1:00
11:00         12:30           1:30
13:00         18:00           5:00

Tuesday Total = 7:30

Wed........
Thu.........
Fri...........

Mr Smith Total Hours for Week = 37:30

Mr Jones

Mon........
Tue.........
Wed........
Thu.........
Fri...........

Mr Jones Total Hours for Week = 41:00

etc., etc.

It can be assumed that there will always be a corresponding "clock out" for every "clock in". If the user hasn't clocked out for whatever reason, this can be manually added to the data before the report is created.

This appears to me to be a very complicated report, any help would be greatly appreciated.

Thanks in advance!
0
advantagecomputers
Asked:
advantagecomputers
  • 3
1 Solution
 
harfangCommented:
UserID <long integer>
TimeStamp <general date>
Status <long integer> 1=Clock In, 0=Clock Out, 2=Attemtped Clock In/Out outside of permitted hours

Hello,

You can start with a general-purpose query that will check the integrity of your data:

SELECT UserID,
    CDate(Int(TimeStamp)) As Day,
    Min(TimeStamp) As MinTime,
    Max(TimeStamp) As MaxTime,
    Sum(IIf(Status=1, 1, 0) As CountIn,
    Sum(IIf(Status=0, 1, 0) As CountOut,
    Sum(Switch(Status=1, -TimeStamp, Status=0, TimeStamp, True, 0) As TotTime
FROM tblYourTable
GROUP BY UserID, Int(TimeStamp)

The query to find the corresponding Out record for every In record could be:

SELECT UserID, (
    Select Top 1 TimeStamp
    From tblYourTable As PREV
    Where UserID = tblYourTable.UserID
        And TimeStamp < tblYourTable.TimeStamp
        And Status = 1
    Order By TimeStamp
    ) As ClockIn,
    TimeStamp As ClockOut,
    ClockOut - ClockIn As TimeWorked
FROM tblYourTable
WHERE Status = 0;

When you build the reporst, you will have options to create groups over days, weeks, months, etc. You can repeat the same group expression using different units for the "group over" property as well.

I hope this gets you started
(°v°)
0
 
advantagecomputersAuthor Commented:
The syntax doesn't appear to be correct. The query to find corresponding out records and each in record, always displays the first in record in the table. It correctly shows the out records, but for each row always shows the first in record.
0
 
harfangCommented:
Oops! I think I meant:

    ....
    Order By TimeStamp DESC      <--- descending order, of course!
    ) As ClockIn,

Sorry about that. I hope it will work this time.
Cheers!
(°v°)
0
 
harfangCommented:
Glad it worked, good luck with your application!
(°v°)
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now