Help with update balance

[Guest]

Can anyone help me?
I have one parent table StaffID primary key that holds a Balance Hours.
I need to apply this balance apportioning it to a child table StaffID/Date
as the primary key with a field of PLanned hours and Used Hours.

for example StaffID 01 Balance Hours 19
need to apportion until Balance is used as such
StaffID 01 Date 1/10 Planned Hours 7 then update Used Hours to 7
StaffID 01 Date 2/10 Planned Hours 7 then update Used Hours to 7
StaffID 01 Date 3/10 Planned Hours 7 then update Used Hours to 5

Hope this makes sense.
Cheers

 

[Michel Walsh]

SELECT a.staffID, a.dateStamp,
iif(SUM(b.plan))<= LAST(c.stock), LAST(a.plan),
iif(LAST(c.stock)-(SUM(b.plan) - LAST(a.plan) ) < 0, 0,
LAST(c.stock)-(SUM(b.plan) - LAST(a.plan) )))

FROM (balances AS c INNER JOIN plans As a ON c.staffID=a.staffID)
INNER JOIN plans AS b ON a.staffID-b.staffID AND a.dateStamp >=
b.dateStamp

GROUP BY a.staffID, a.dateStamp


should do.

Table balances, here aliased AS c, is assumed to have at least two fields,
<staffID, stock> where stock is the number of 'balance hours' , I think,
or, if you prefer, has the value stock=19 associated to staffID=staff_01.

Table plans, aliased AS a and also aliased AS b, have at least three fields,
<staffID, dateStamp, plan> where, under plan, you have the maximum hour
associable to that staff, that date.

The magic occurs through the first iif. Indeed, if, for a given staff and a
given date (referred to the GROUP BY), the sum of the plan-ed hours for all
the days previous to this one, and including this one, are less than the
maximum allocated:

iif( SUM(b.plan) <= LAST( c.plan) , ... , ... )

then, use all the planned hours, LAST(a.plan).

ELSE, if the allocated hours, excluding for this date, is already greater
than the maximum of what is available, we cannot use anymore:

iif( LAST(c.stock) + LAST(a.plan) < SUM(b.plan) , 0 , ... )

but if there is still some bit left, LAST(c.stock)-(SUM(b.plan) -
LAST(a.plan) ) , use it.



Hoping it may help,
Vanderghast, Access MVP

 

[Michel Walsh]

Note that I understood that your problem is like having 19 apples and 23
oranges ( 19 = number of hours for staff_01 and 23=number of hours for
staff_02), and you were looking to satisfy invoice orders. On date 1/10, you
need 7 apples, on date 2/10, you need 7 apples, on date 3/10, you need 7
apples, ... and you look at how you can fill the invoices orders by a rule
of first ordered, first to be filled.


Vanderghast, Access MVP