MS Access database support - Time and Attendance Recording

Author

Time and Attendance Recording

John Ortt

2005-07-27, 8:25 pm

Hi There,

My company run a flexi time system whereby you can work extra hours to gain
additionnal days off.

In order to better plan my time I would like to design a database which
works out the flexitime based on the values you enter and assuming a 7.4
hour working day (based on a 37 hour week).

The data I would have is the daily start and finish times and a flexi
balance as a starting point.

I would also like the ability to add further flexi balance records to check
the figures in the future.

I have given an example below (numbers calculated mentally so forgive any
errors)

TABLE - TIMES (Copied from system or enterred speculatively to help plan
holidays)
---------------------

DateVal StartTime EndTime

01/01/01 07.45 18.00
02/01/01 09.00 17.42
03/01/01... 08.45 17.45
etc

TABLE - FLEXI (Copied from System as a starting point and as references)
--------------------

DateVal FlexiBalance
01/01/01 -2.00
03/01/05 02.09
etc

QUERY - FLEXIBALANCE (works out the flexi balance on a daily basis)
------------------------------------

DateVal StartTime EndTime StartFlex EndFlex FlexChk

01/01/01 07.45 18.00 -02.00 00.51 -02.00
02/01/01 09.00 17.42 00.51 02.09
03/01/01 08.45 17.45 02.09 03.45 02.09

John Ortt

2005-07-27, 8:25 pm

Sorry, sent that too quickly...

I meant to put a "thanks in advance" and my name at the bottom (which is
John by the way).

"John Ortt" < JohnOrtt@Idontwantsp
amsonoreturnaddress.com> wrote in message
news:42e7b3a8$1_1@gl
kas0286.greenlnk.net...
> Hi There,
>
> My company run a flexi time system whereby you can work extra hours to
gain
> additionnal days off.
>
> In order to better plan my time I would like to design a database which
> works out the flexitime based on the values you enter and assuming a 7.4
> hour working day (based on a 37 hour week).
>
> The data I would have is the daily start and finish times and a flexi
> balance as a starting point.
>
> I would also like the ability to add further flexi balance records to
check

> the figures in the future.
>
> I have given an example below (numbers calculated mentally so forgive any
> errors)
>
>
> TABLE - TIMES (Copied from system or enterred speculatively to help plan
> holidays)
> ---------------------
>
> DateVal StartTime EndTime
>
> 01/01/01 07.45 18.00
> 02/01/01 09.00 17.42
> 03/01/01... 08.45 17.45
> etc
>
>
> TABLE - FLEXI (Copied from System as a starting point and as references)
> --------------------
>
> DateVal FlexiBalance
> 01/01/01 -2.00
> 03/01/05 02.09
> etc
>
>
> QUERY - FLEXIBALANCE (works out the flexi balance on a daily basis)
> ------------------------------------
>
> DateVal StartTime EndTime StartFlex EndFlex FlexChk
>
> 01/01/01 07.45 18.00 -02.00
00.51 -02.00
> 02/01/01 09.00 17.42 00.51 02.09
> 03/01/01 08.45 17.45 02.09 03.45
02.09
>
>

Allen Browne

2005-07-27, 8:25 pm

Hi John.

You can calculate the number of minutes worked like this:
Minutes: DateDiff("n", [StartTime], [EndTime])

You are assuming 7.4 hours per day, so the extra minutes is:
ExtraMinutes: 444 - DateDiff("n", [StartTime], [EndTime])

Assuming you have exactly 5 records per worker per week (even if they don't
show up, or show up on 6 days), you could then sum the ExtraMinutes to get a
progressive total. Those assumptions are probably unsafe, so you will need
to calculate on the number of weeks in the timeframe you are considering
(week/month/...) times 2220.

For suggestions on displaying the results in hour:min format, see:
Calculating elapsed time
at:
http://allenbrowne.com/casu-13.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"John Ortt" < JohnOrtt@Idontwantsp
amsonoreturnaddress.com> wrote in message
news:42e7b3a8$1_1@gl
kas0286.greenlnk.net...
> Hi There,
>
> My company run a flexi time system whereby you can work extra hours to
> gain
> additionnal days off.
>
> In order to better plan my time I would like to design a database which
> works out the flexitime based on the values you enter and assuming a 7.4
> hour working day (based on a 37 hour week).
>
> The data I would have is the daily start and finish times and a flexi
> balance as a starting point.
>
> I would also like the ability to add further flexi balance records to
> check
> the figures in the future.
>
> I have given an example below (numbers calculated mentally so forgive any
> errors)
>
>
> TABLE - TIMES (Copied from system or enterred speculatively to help plan
> holidays)
> ---------------------
>
> DateVal StartTime EndTime
>
> 01/01/01 07.45 18.00
> 02/01/01 09.00 17.42
> 03/01/01... 08.45 17.45
> etc
>
>
> TABLE - FLEXI (Copied from System as a starting point and as references)
> --------------------
>
> DateVal FlexiBalance
> 01/01/01 -2.00
> 03/01/05 02.09
> etc
>
>
> QUERY - FLEXIBALANCE (works out the flexi balance on a daily basis)
> ------------------------------------
>
> DateVal StartTime EndTime StartFlex EndFlex FlexChk
>
> 01/01/01 07.45 18.00 -02.00
> 00.51 -02.00
> 02/01/01 09.00 17.42 00.51 02.09
> 03/01/01 08.45 17.45 02.09 03.45
> 02.09

Nunya Biznas

2005-07-27, 8:25 pm

Probably not a good idea to have accurate records for 'flexi' time.
Most employees are *required* to be paid for hours worked over 40 per
week.

http://www.dol.gov/elaws/esa/flsa/h...ked/default.asp

A disgruntled employee could call the Dept. of Labor and mess it up for
everyone.

Sponsored Links