Creating a Time and Attendance Query/Report in Access 2003

Posted on 2006-04-20
Last Modified: 2008-03-10

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!
Question by:advantagecomputers
    LVL 58

    Expert Comment

    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:

    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

    Author Comment

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

    Accepted Solution

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

    Expert Comment

    Glad it worked, good luck with your application!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Suggested Solutions

    In the previous article, Using a Critera Form to Filter Records (, the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
    Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
    In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
    In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now