If 0 from subtract in query then goto to previous record

[FLUGO76]

Hi, i need help




I want substract from 9/20/2014 totreg40 - reg = ovt but when reg=0 the remainder subtract from 9/19/2014 (previous record) totreg40 - reg = ovt, etc. The sum of reg = 40.






ID FECHA REG OVT TOTREG40
230 9/15/2014 5.5 0 46.75
231 9/16/2014 8 0 46.75
232 9/17/2014 8 0 46.75
234 9/18/2014 7.75 0 46.75
297 9/19/2014 8 0 46.75
235 9/19/2014 8 0 46.75
233 9/20/2014 1.5 0 46.75




I want the result like this:


ID FECHA REG OVT TOTREG40
230 9/15/2014 5.5 0 46.75
231 9/16/2014 8 0 46.75
232 9/17/2014 8 0 46.75
234 9/18/2014 7.75 0 46.75
297 9/19/2014 8 0 46.75
235 9/19/2014 2.75 5.25 46.75
233 9/20/2014 0 1.5 46.75


Thanks

[azhar2006]

Cannot Ask figures From history, what is 0?
What is the number 40?

[June7]

It is not easy to get value from another record of same table. Review http://allenbrowne.com/subquery-01.html#AnotherRecord

However, what you seem to want is some sort of running deduction. The calculation is not entirely clear to me. I don't understand why the second record for 9/19/2014 is broken up and 1.5 for 9/20/2014 changes fields.

Seems like poor db design.

[FLUGO76]

Thank you

I see the page you indicate me .

The number 40 is the sum of the regular excess of 40 hours must be assigned the OVT, distributing it from the last date to the most recent but there should be no negative number 'm trying to calculate hours.
The totreg40 field is the sum of the reg hours ( 46.75 ).

[June7]

Sorry, still doesn't make sense to me. Saving calculated data is usually a bad idea. You seem to be trying to programmatically accomplish what should be manual data entry of records.

If this is a time and attendance type of db, IMO that is one of the more difficult to build.

[aytee111]

It would make more sense to store the data correctly on the table - regular time and overtime - then queries could read it from the table. This would need to be done as a record is being added to the table, probably only possible using VBA.

[ssanfu]

This looks like a timesheet.
9/15/2014 (Mon) worked 5.5 ST hrs (5.5 ST hrs and 0 OT hrs)
9/16/2014 (Tue) worked 8 ST hrs (13.5 ST hrs and 0 OT hrs)
9/17/2014 (Wed) worked 8 ST hrs (21.5 ST hrs and 0 OT hrs)
9/18/2014 (Thu) worked 7.75 ST hrs (29.25 ST hrs and 0 OT hrs)

Fri worked total of 16 hrs:
9/19/2014 (Fri) worked 8 hrs (first 8 ST hrs and 0 OT hrs)
9/19/2014 (Fri) worked 8 hrs (then 2.75 ST hrs and 5.25 OT hrs)

9/20/2014 (Sat) worked 1.5 hrs (0 ST hrs and 1.5 OT hrs)

If this is correct, I would use VBA to do the calculations.
Since it appears that the data entry person doesn't know/can't calc ST/OT hrs, use the form before update event or the hours control after update event (if there are controls on the form to display the hours) to calculate the ST/OT hours.

As June stated, this is difficult to do correctly.

[June7]

Good point Steve. So OP is trying to calculate hours in a week exceeding 40 and distribute the hours appropriately because the data entry is bonkers?!?

I had to develop a db for my boss so we could identify OT hours. Our pay periods are bi-monthly (15th and last day) but overtime has to be determined Mon-Sun (anything over 60/week requires sign off from higher up). Our timesheets are an Excel file. I developed code that would extract the daily hours data from Excel sheet submitted by each employee. Then the Access db presents the weekly hours and lists employees in excess of the limit on an authorization/justification report. Another report lists all employees and highlights those who exceed the 60 hours.

However, the raw data is already correctly entered on timesheet as Reg and OT hours. I just pull it into Access table. Every day of month for each employee has Reg and OT entries, if no hours then there is a 0 so there are no gaps in date sequence.

Fortunately, I don't have to actually calculate payroll, that's HR's job. Labor and tax laws are too complicated. I would not try to build a db for payroll. I would buy one. I have used QuickBooks (not expensive compared to the effort of building from scratch) and Great Plains (extremely expensive).