Time Sheet Query

[mrh]

Hi,

Following help from other members on here, I'm finally getting somewhere!

I now have a table like this:-

20/10/2008 Boris Clocked In 07:15
20/10/2008 Boris Clocked Out 10:15
20/10/2008 Boris Clocked In 11:15
20/10/2008 Boris Clocked Out 16:30
20/10/2008 Ben Clocked In 07:15
20/10/2008 Ben Clocked Out 10:15
20/10/2008 Ben Clocked In 11:15
20/10/2008 Ben Clocked Out 16:30
20/10/2008 Bill Clocked In 07:15
20/10/2008 Bill Clocked Out 18:00

I need to calculate the total hours each employee is clocked in for each day.

The problem have is that each person can clock in and out a different number of times - i.e. if they clock out to go to the doctor's and then clock back in when they get back.

Does anyone have any ideas how to build a query to do this?

Many thanks


Matthew

 

[raskew]

Hi -

You could make this infinitely simpler if each record contained both Clocked_In and Clocked_Out, e.g.
20/10/2008 Boris Clocked_In 07:15 Clocked_Out 10:15

Then, when querying, adding a calculated field, e.g.

TimeWorked: DateDiff("n", Clocked_In, Clocked_Out)

Depending on the data type of the Clocked_In and Clocked_Out fields, it may (if the fields are Text) be necessary to use the TimeValue() function to convert the text to Date/Time.

HTH - Bob

 

[mrh]

Hi -

You could make this infinitely simpler if each record contained both Clocked_In and Clocked_Out, e.g.
20/10/2008 Boris Clocked_In 07:15 Clocked_Out 10:15

Then, when querying, adding a calculated field, e.g.

TimeWorked: DateDiff("n", Clocked_In, Clocked_Out)

Depending on the data type of the Clocked_In and Clocked_Out fields, it may (if the fields are Text) be necessary to use the TimeValue() function to convert the text to Date/Time.

HTH - Bob

I agree!

I've sorted all the time calculations - including rounding forwards and backwards and applied them to create the above tables.

Wonder if it is worth me starting again, although that will then give headaches to collect the data.....

I'll keep thinking!

Many thanks

Matthew

 

[raskew]

Hi Matthew -

If you'll post your precise table structure (including data types) we may be able to create a MakeTable query to convert your current structure to a consolidated one such as I proposed.

Bob

 

[mrh]

Hi Matthew -

If you'll post your precise table structure (including data types) we may be able to create a MakeTable query to convert your current structure to a consolidated one such as I proposed.

Bob

Many thanks

It's a very simple structure:-

Operator Text
Time Date/Time
Status Text
Index Autonumber

A sample of the data:-
Operator Time Status Index
Boris 13/10/2008 07:10:00 Clocked In 11
Boris 13/10/2008 06:49:45 Clocked In 1
Bert 13/10/2008 07:09:44 Clocked In 21
Bert 13/10/2008 07:10:37 Clocked In 20
Bert 13/10/2008 22:07:34 Clocked In 19
Bert 13/10/2008 22:07:32 Clocked In 18
Ben 13/10/2008 22:03:14 Clocked In 17
Ben 13/10/2008 22:03:08 Clocked In 16
Bert 13/10/2008 22:01:06 Clocked In 15


Many thanks for looking,


Matthew

 

[mrh]

Hi Guys,

Thanks for the suggestions etc.

Have managed to sort the query - now works as I hoped!


Just need to find a way of running a query at 10pm each night.

I understand I can use the ontimer, but I'm not sure how to make it time specific.

Many thanks


Matthew