Time and Attendance Recording

[John Ortt]

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]

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" <Jo******@Idontwantspamsonoreturnaddress.com> wrote in message
news:42**********@glkas0286.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]

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" <Jo******@Idontwantspamsonoreturnaddress.com> wrote in message
news:42**********@glkas0286.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]

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/ho...ed/default.asp

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

[John Ortt]

I have set-up a Time and attendance database as outlined in the "Time and
Attendance Recording" thread.

I have now figured out how to get it to work (and successfully done so on a
stripped down test) but when I try to run the proper version I get a Dr
Watson error.

Can anyone see any obvious problems on the following query

(qryMaster) SELECT qryFlexi.DateID, (Select max (Flexi) FROM [tblFlexi] as
[tblFlexi2] WHERE ([tblFlexi2].[DateID] <= [qryFlexi].[DateID]) ;) AS
LastFlex, (Select sum (FlexChangeDeci) FROM [qryFlexi] as [qryFlexi2] WHERE
([qryFlexi2].[DateID] <= [qryFlexi].[DateID]) and ([qryFlexi2].[DateID] >=
[qryFlexi].[PrevFlex]);) AS CFCDeci,
IIf(Left([LastFlex],1)="-",Left([LastFlex],4)-Right([LastFlex],2)/60,Left([L
astFlex],3)+Right([LastFlex],2)/60) AS PrevFlexDeci,
[CFCDeci]+[PrevFlexDeci] AS FlexiDeci
FROM qryFlexi LEFT JOIN tblFlexi ON qryFlexi.DateID = tblFlexi.DateID;

Any help would be greatly appreciated,

Thanks,

John

the Sql for the other queries (incase required) is as follows:

(qryDayOfWeek) SELECT tblTimes.DateID, Weekday([DateID]) AS DayOfWeek,
IIf(Weekday([DateID])=1 Or
Weekday([DateID])=7,"3",IIf(Weekday([DateID])=6,"2","1")) AS DayCode,
tblTimes.ClockOn, tblTimes.ClockOff, tblTimes.Absence, tblTimes.Reason
FROM tblTimes;

(qryTimes) SELECT qryDayOfWeek.DateID, Hour([ClockOn])+Minute([ClockOn])/60
AS ST, Hour([ClockOff])+Minute([ClockOff])/60 AS FT, IIf([Absence] Is Not
Null,0,IIf([DayCode]="1" Or ([DayCode]="2" And [FT]>=13),0.7,0)) AS LT,
IIf(([Absence]="AM" Or
[Absence]="FULLDAY"),0,IIf([DayCode]="3",IIf([ST]<=12,IIf([FT]<12,[FT]-[ST],
12-[ST]),0),IIf([ST]<=7,7.5-[ST],0))) AS AMOT, IIf(([Absence]="PM" Or
[Absence]="FULLDAY"),0,IIf([DayCode]="3",IIf([FT]>=12,IIf([ST]>12,[FT]-[ST],
[FT]-12),0),IIf([FT]>=18.5,[FT]-18,0))) AS PMOT, IIf(([Absence]="AM" Or
[Absence]="FULLDAY") And [reason]="Flexi",-3.7,IIf(([Absence]="AM" Or
[Absence]="FULLDAY"),0,IIf([DayCode]="3",0,IIf([ST]<7.5,IIf([FT]>12,12-7.5,[
FT]-7.5),IIf([FT]>12,12-[ST],[FT]-[ST]))))) AS AMCT, IIf(([Absence]="AM" Or
[Absence]="FULLDAY") And [reason]="Flexi",-3.7,IIf(([Absence]="AM" Or
[Absence]="FULLDAY"),0,IIf([DayCode]="3",0,IIf([FT]>18,IIf([ST]>12,18-[ST],1
8-12),IIf([ST]>12,[FT]-[ST],[FT]-12))))) AS PMCT, [PMCT]+[AMCT]-[LT] AS CT,
[AMOT]+[PMOT] AS OT, IIf([reason] Is Null Or [reason]="",[CT],IIf([reason]
Is Not Null And
[reason]<>"Flexi",IIf([absence]="FULLDAY",7.4,IIf([absence]="AM",[PMCT]+3.7,
[AMCT]+3.7)),IIf([reason]="Flexi",IIf([absence]="FULLDAY",0,IIf([absence]="A
M",[PMCT],[AMCT]))))) AS FA, [FA]-7.4 AS FC, qryDayOfWeek.Absence,
qryDayOfWeek.Reason
FROM qryDayOfWeek
ORDER BY qryDayOfWeek.DateID;

(qryFlexi) SELECT qryTimes.DateID, qryTimes.FC AS FlexChangeDeci, (Select
max (dateID) FROM [tblflexi] as Temp WHERE [temp].[dateid] <=
[qrytimes].[dateid]) AS PrevFlex
FROM qryTimes;

[John Ortt]

I have narrowed it down to the following line...but I still can't see what's
wrong :(

(Select sum (FlexChangeDeci) FROM [qryFlexi] as [qryFlexi2] WHERE
([qryFlexi2].[DateID] <= [qryFlexi].[DateID]) and ([qryFlexi2].[DateID] >=
[qryFlexi].[PrevFlex]);) AS CFCDeci