Creating a Time and Attendance Query/Report in Access 2003


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


Mr Smith Total Hours for Week = 37:30

Mr Jones


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!
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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


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

    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 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
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.
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Glad it worked, good luck with your application!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.