URGENT:Bringing Forward Previous Balance

[Fozi]

Hi folks

Still struggling to get my form to pull the overtime balance from the previous entry for each staff member as they enter a new record. Deadline for submission looming!!!

I'm working on a timesheet system and wondered if its possible to bring the following functionality to a form.

• Employees enter their times for the week [TotTime].
• Based on a 36 hour week a calculation is performed to determine overtime due for that week.[OverTimeforweek]
• A third control has their current balance [OvertimeDue]

All controls are numeric with two decimal places.

Timesheets are entered weekly. I would like the Form to show [OvertimeDue] accummulated from previous entries which would then alter based upon that weeks entry and feed the next record for that individual.

Database attached. Grateful for any assistance.

Thanks
Fozi

 

[gemma-the-husky]

overtime balance from the previous entry

this sounds like an exercise in normalisation

i assume overtime is not carried forward from week to week, but is calculated each week.
so lets say you have a daily time sheet

so you have

employee 1
time sheet monday 8hrs (or possibly clockin/clock off etc - adding up to 8 hrs)
time sheet tuesday 8hrs etc
time sheet wednesday 8hrs etc

now either - depends on your business
evaluate o/t each day, and sum them to get the total for the week OR
sum the total hours for the week, and then evaluate this to get the o/t

but let access queries do it automatically

but neither of these need to consider a "previous" record - so how are you trying to do it

 

[Fozi]

Staff complete weekly timesheets each monday for the week before. Their time is then totalled for that week. A calculation is then performed to work out their excess hours for that week.

On each record I want a control showing the overtime balance carrying over from the previous week for that staff member. This would then be recalculated in the current record based on their hours that week.


Example:

• John Smith for week 1 has a closing overtime balance of 14 hours due to him. Record one for John shows 14 hours in the balance control.
• When John goes to enter times for week 2 in record 2, I want his balance of 14 to carry forward.
• He works 39 hours that week making his OT due +3. The Balance should then change to 17 and would carry forward for week 3 etc.

Check out the attachment. It will probably make more sense.

Thanks for your help.
Fozi

 

[gemma-the-husky]

you need to do a dlookup then, to read the balance from last weeks payslip

something like

dlookup("overtime","tblPayslips","employee = " & empid & " and payslipdate >=  #" & lastweeksdate & "#"

the last bit is the trickiest, as there may not be a payslip for the previous week - so you may need to do a dmax first to find the latest payslip date

 

[Fozi]

Thanks Gemma but just not getting it to work. My VB skills are pretty limited to say the least.

Is there any chance you (anyone) can check out the attachment and get back to me.

Cheers
Fozi

 

[gemma-the-husky]

try these

query1 - findmaxdate

SELECT Max([TBL_Staff Entries by Week].[Week Commencing]) AS [MaxOfWeek Commencing], [TBL_Staff Entries by Week].[Staff Name]
FROM [TBL_Staff Entries by Week]
GROUP BY [TBL_Staff Entries by Week].[Staff Name];

query2 - ValuesforMaxDate

uses the above query

SELECT FindMaxDate.[Staff Name], FindMaxDate.[MaxOfWeek Commencing], [TBL_Staff Entries by Week].Value, [TBL_Staff Entries by Week].ID
FROM FindMaxDate INNER JOIN [TBL_Staff Entries by Week] ON (FindMaxDate.[Staff Name] = [TBL_Staff Entries by Week].[Staff Name]) AND (FindMaxDate.[MaxOfWeek Commencing] = [TBL_Staff Entries by Week].[Week Commencing]);

is this the sort of thing you mean

 

[Fozi]

Thanks Gemma, thanks for perservering with me! lol

Not really what I need. I'll try to explain here with this example.

[Staff Name] [Week Commencing] [Hours Worked] [PrevOT] [Balance]
John 02/03/09 39 17 20
John 09/03/09 41 20 25
John 16/03/09 34 25 23

So effectively [Balance] is calculated on (([Hours Worked]-36)+[PrevOT]) and then fills [PrevOT] in subsequent records

Obviously needs to incorporate a [Staff Name] check to find their most recent previous entry.

Thanks again.
Fozi