Clock In & Clock Out Question

[Adam]

Hello,

I have created a database that will be used as a time tracking function. I
have it set up so that people will click a button to clock in, which creates
a new record and then they click a different button to clock out that adds
another new record. I want to get the overall time that an employee worked
on a each job. I know how to set up the formulas to get a difference in two
times. I am having problems knowing how to set it up so that my formula
grabs the two times only for that certain person and certain job. What is
the best way to set my database up to perform this function?

Any help is much appreciated.

 

[Conan Kelly]

Adam,

Out of curiosity, these clock in/out buttons...are they on a form? When you
say create a new record for every time punch (whether in or out), is it
recording the time, some sort of employee ID, and some sort of Job ID? How
does it know what the employee/job ID is? Where does it get this info from?
How does it distinguish from a time punch in and a time punch out?

In order for us to help you out, please post more info...table name & field
names.

HTH,

Conan

 

[Adam]

Yes, the clock in/out buttons are on forms. When they clock in or out they
select their employee id and then click the button that records the
date/time. I didn't know if I needed to make it Date() or Now(). I probably
need to add the job id to be selected so that it will distinguish between
other jobs. I have two separate forms, one to clock in and one to clock out.
That's how it knows if they clocked in or out. Let me know if you need any
other info to answer my questions.

Adam

 

[Conan Kelly]

Adam,

FIRST, PLEASE POST YOUR TABLE NAME AND FIELD NAMES, then answer the
following questions:

.... I have two separate forms, one to clock in and one to clock out.
That's how it knows if they clocked in or out. ...

Every time you click a button, it creates a record in a table, correct? Are
these 2 different forms creating records in 2 different tables, or are they
creating records in the same table? If they are creating records in the
same table, is there a field in the table that distinguishes between
clocking in and clocking out?

Using Date() or Now() just depends on how accurate you want your
calculations. Do you want your calculations to be accurate to the day or to
the hour/minute/second? Date() returns today's date, Now() returns the date
and time right now (or when ever the button is pushed). Will your
calculations need to exclude weekends/holidays? If your calculations need
to be acurate to the hour/minute/second, how long is the work day? ...what
time is start time (finally-drag-your-butt-to-work time)? ...what time is
end time (beer:30...go-home time)?

 

[Adam]

Table Name: wh_temptrack
Field Names: Temp_ID, Clock_Status_In, Clock_Status_Out, Job, Department,
Item, Units_Produced, Work_Order_Num, Clock_In_Time, Clock_Out_Time, Job_Time

I need my calculations to be given as a total time worked down to the
second. I just need it to caluculate on the weekdays, no holidays or
weekends. Start and end time can vary from 6am to 6pm.

Would it help if I left it as one timestamp per record and used a self join
query?

 

[Conan Kelly]

Adam,

What kind of info is stored in Clock_Status_In & Clock_Status_Out?

Would it help if I left it as one timestamp per record and used a self
join
query?

I'm thinkin' that it would probably be easier if you had one column as a
time stamp column and one column distinguishing between what kind of time
stamp it is (in/out). I'm guessing how it works now is that each record in
your table has a value in either Clock_In_Time or Clock_Out_Time, but never
values in both. Is that correct?

I think that we might be able to get it to work like it is. For some
reason, it just seems like it makes more sense to me that it be set up the
other way.

By-the-by, what is "Job_Time"? Isn't that the time we are trying to
calculate with these queries? If that is the case, then there really is no
need for there to be a field in the table.

One last thing, If an employee starts a 25 hour job at 3:00 in the afternoon
on Friday of a 3-day weekend, do they clock out only when the job is
finished, some time on Wednesday of the next week? ...or do they clock out
at 6:00 pm Friday, then clock back in for the same job Tuesday morning,
clock out Tuesday evening, clock in for the same job Wednesday, clock out
when the job is finished Wednesday afternoon? ...do they clock in and out
for lunch too?

I'll come up with some queries that might work as-is and post back later
today or tomorrow. If you decide to make some changes, then we can adjust
them accordingly.

HTH,

Conan

 

[Conan Kelly]

Adam,

First Query (based on table wh_temtrack):

SELECT tbl1.Temp_ID, tbl1.Job, tbl1.Clock_In_Time as ClockInTime, (SELECT
min(tbl2.Clock_Out_Time) FROM wh_temptrack as tbl2 WHERE tbl1.Temp_ID =
tbl2.Temp_ID and tbl1.Job = tbl2.Job and tbl2.Clock_Out_Time >
tbl1.Clock_In_Time) as ClockOutTime
FROM wh_temptrack AS tbl1
WHERE Clock_In_Time is null

(this query was typed in freehand and not tested, there could be typos or
other errors)
Save this query with a meaningful name, and REMEMBER THE NAME. You will
need it for the next query. This query will have Temp_ID, Job,
Clock_In_Time, and Clock_Out_Time all in one record to make it easier to
calculate the job time in the next query. If you need/want to add other
columns to this query, you can. This query looks up the clock out time
based on matching Temp_ID and Job. If there are other fields that need to
be included in the match (Department, Item, or Work_Order_Num), let me know
and we can adjust it accordingly.

Second Query (based on first query...I'll use the name "qry1", you'll need
to change it accordingly):

SELECT Temp_ID, Job, ClockInTime, ClockOutTime, ClockOutTime-ClockInTime as
JobTime
FROM qry1

(this query was typed in freehand and not tested, there could be typos or
other errors)
This query will return the 4 columns from the first query and calculate a
new column "JobTime". You might have to set the format for this column to
something like "[h]:mm:ss" (I don't know if that is a valid format for
Access, but it is one for XL. If that doesn't work, I'll figure out what
will). That format will "accumulate" hours (if that is the right term), so
1 full day, 5 hours, and 30 minutes will show up as "29:30:00". If you need
to break it down to something like "1 day, 5 hours, 30 minutes" or "1 day,
5:30:00", we'll have to figure something else out.

Third Query (based off of second query...I'll use "qry2"):

SELECT Temp_ID, Job, sum(JobTime) as TotalJobTime
FROM qry2
GROUP BY Temp_ID, Job
ORDER BY Temp_ID, Job

(this query was typed in freehand and not tested, there could be typos or
other errors)
This query will list one record for each employee and job they worked on and
then sum total job time.

HTH,

Let me know how things work out.

Conan

 

[Adam]

Conan,

The Clock_Status_In and out fields are yes/no fields. I could delete them
because the only reason I created them was to query for open jobs, etc. I
could easily do the same if I entered YES in the criteria for Clock_Time_In
and NO for Clock_Time_Out.

You are correct when you said, "I'm guessing how it works now is that each
record in your table has a value in either Clock_In_Time or Clock_Out_Time,
but never values in both. Is that correct?"

Job_Time is the total time an employee works on a specific job. I could
delete it from the form. The only place this will be seen is on a report I'm
going to create later.

Yes, an employee will clock in, clock out for lunch, clock back in, and
clock out once done with the job. Will this cause a problem if there are
more than 2 timestamps for certain person and certain job? I don't think an
employee will ever work on a job that will take them into the following day.
I'll check though.

Adam

 

[Conan Kelly]

Adam,

Yes, an employee will clock in, clock out for lunch, clock back in, and
clock out once done with the job. Will this cause a problem if there are
more than 2 timestamps for certain person and certain job? I don't think
an
employee will ever work on a job that will take them into the following
day.
I'll check though.

No, I don't think that will cause any problems at all. In fact, it probably
works out better that way. I was just asking because before I asked:

.... Will your
calculations need to exclude weekends/holidays? If your calculations need
to be acurate to the hour/minute/second, how long is the work day?
...what
time is start time (finally-drag-your-butt-to-work time)? ...what time is
end time (beer:30...go-home time)?

None of that matters now that employees will clock in/out for lunch, and
should a job span more than one day, they clock out to go home and clock
back in when they come in, in the morning.

I came up with some queries for you in my other post that should work with
your table as-is...no need to make any changes right now. Check that out
and see if it will work for you. Let me know how things work out.

HTH,

Conan

 

[Adam]

Conan,

I actually changed the format of everything this morning. Before I had 2
different forms to clock in and clock out. I now have one form with one
timestamp field and one clock in/clock out field that is a combo box. What
would be the easiest way to get the total time worked on a job now?

This is the basic process so far. First, the job will be created by a
manager on a form. Then the temp will open a different form where they will
select the job from a combo box. In the same form they will select their
Temp_ID and then select Clock In from a combo box. When they are done with
this job they will open this form back up and select the job, their temp_id,
and select clock out from the combo box.

How will I get a total time worked on the job if they clock in and out
multiple times to go to lunch?

Adam

 

[Conan Kelly]

Adam,

Did you look at my other reply with the queries? Did you adjust the
structure of the table at all? Those queries in my other reply are only
concerned with 4 columns/fields in the table: Temp_ID, Job, Clock_In_Time,
& Clock_Out_Time. If you haven't made any changes to the table (or, more
importantly, haven't made any changes to those 4 fields/columns), then those
queries should work for you, hopefully.

If you have made changes to the table, specifically those 4 columns, then
please repost your your table structure and I will redesign those queries.

WOOPPS!!! I just noticed an error in my first query in the WHERE clause.
Make sure to change...

WHERE Clock_In_Time is null

....to...

WHERE Clock_In_Time is not null

HTH,

Conan

 

[Adam]

Conan,

I tried opening your last post and it shows a blank screen. I'm not sure
what the problem is. Can you try re-sending it?

Adam

 

[Adam]

Conan,

No need to re-send your post. I can see it now. I did change the structure
to the table. Here are the fields in the WH_tempTrack table.... TempID,
Job_ID, Department, ItemNumber, UnitsProduced, RequestID, WorkOrderNumber,
TimeStamp, and In_Out.

[TimeStamp] is set to Now() and [In_Out] is a combo box with "Clock In" and
"Clock Out" as the options.

Also, in your previous post where you gave me the queries to try out. The
first query referred to tbl1 and tbl2. However, I only have one table that
the information is pulling from. Is there a reason you had tbl2 in it?

Let me know if you need anything else to make the new queries.

Adam

 

[Conan Kelly]

Adam,

I'll redesign those queries to fit your new table structure and post them
back after a bit, but I'm hungry so I'm gonna go get some lunch right now.

Also, in your previous post where you gave me the queries to try out. The
first query referred to tbl1 and tbl2. However, I only have one table
that
the information is pulling from. Is there a reason you had tbl2 in it?

If you look closely at the query, I used your table in a couple of different
ways, so I gave an alias to your table twice: once as tbl1 and once as
tbl2.

I'll post those back after a bit.

Conan

Conan,

No need to re-send your post. I can see it now. I did change the
structure
to the table. Here are the fields in the WH_tempTrack table.... TempID,
Job_ID, Department, ItemNumber, UnitsProduced, RequestID, WorkOrderNumber,
TimeStamp, and In_Out.

[TimeStamp] is set to Now() and [In_Out] is a combo box with "Clock In"
and
"Clock Out" as the options.

Also, in your previous post where you gave me the queries to try out. The
first query referred to tbl1 and tbl2. However, I only have one table
that
the information is pulling from. Is there a reason you had tbl2 in it?

Let me know if you need anything else to make the new queries.

Adam

Adam,

Did you look at my other reply with the queries? Did you adjust the
structure of the table at all? Those queries in my other reply are only
concerned with 4 columns/fields in the table: Temp_ID, Job,
Clock_In_Time,
& Clock_Out_Time. If you haven't made any changes to the table (or, more
importantly, haven't made any changes to those 4 fields/columns), then
those
queries should work for you, hopefully.

If you have made changes to the table, specifically those 4 columns, then
please repost your your table structure and I will redesign those
queries.

WOOPPS!!! I just noticed an error in my first query in the WHERE clause.
Make sure to change...

WHERE Clock_In_Time is null

....to...

WHERE Clock_In_Time is not null

HTH,

Conan

 

[Conan Kelly]

Adam,

Try this for query 1:

SELECT tbl1.Temp_ID, tbl1.Job_ID, tbl1.TimeStamp as ClockInTime, (SELECT
min(tbl2.TimeStamp) FROM wh_temptrack as tbl2 WHERE tbl1.Temp_ID =
tbl2.Temp_ID and tbl1.Job_ID = tbl2.Job_ID and tbl2.TimeStamp >
tbl1.TimeStamp and In_Out = "Clock Out") as ClockOutTime
FROM wh_temptrack AS tbl1
WHERE In_Out = "Clock In"

Hopefully I got that right. Now that should work in place of the first
query I posted previously, and it should also work with the second and third
queries I posted without any changes (except query names accordinigly).

Use this SQL statement to create your first query and save it. If you want,
give it a meaningful name and remember the name so you can edit the second
query. If you don't want to bother with names right now, save it as "qry1",
then the second query should work as-is. Use the 2nd & 3rd query SQL
statements to create the 2nd & 3rd queries. If you want, you can open each
one individually to see if they are returning the correct data, but the one
that will give you the results you are looking for is the third query. All
you will need to do is open that one and it will list all employees, the
jobs they have worked on and the total time they have worked on the job.

Let me know how this works out.

HTH,

Conan

Conan,

No need to re-send your post. I can see it now. I did change the
structure
to the table. Here are the fields in the WH_tempTrack table.... TempID,
Job_ID, Department, ItemNumber, UnitsProduced, RequestID, WorkOrderNumber,
TimeStamp, and In_Out.

[TimeStamp] is set to Now() and [In_Out] is a combo box with "Clock In"
and
"Clock Out" as the options.

Also, in your previous post where you gave me the queries to try out. The
first query referred to tbl1 and tbl2. However, I only have one table
that
the information is pulling from. Is there a reason you had tbl2 in it?

Let me know if you need anything else to make the new queries.

Adam

Adam,

Did you look at my other reply with the queries? Did you adjust the
structure of the table at all? Those queries in my other reply are only
concerned with 4 columns/fields in the table: Temp_ID, Job,
Clock_In_Time,
& Clock_Out_Time. If you haven't made any changes to the table (or, more
importantly, haven't made any changes to those 4 fields/columns), then
those
queries should work for you, hopefully.

If you have made changes to the table, specifically those 4 columns, then
please repost your your table structure and I will redesign those
queries.

WOOPPS!!! I just noticed an error in my first query in the WHERE clause.
Make sure to change...

WHERE Clock_In_Time is null

....to...

WHERE Clock_In_Time is not null

HTH,

Conan