I have a table called job_lifts which records who carried out a scaffolding job.
Table Field names
charge, rigger1, rigger2 , rigger3, labourer1, labourer2 , labourer3, riggers, labourers, rigger1_fee, rigger2_fee, rigger3_fee, labourer1_fee, labourer2_fee, labourer3_fee.
The table is updated using a form job_lifts_form which has combo boxes to select the riggers and labourers.
The riggers and labourer fields are updated by an update query after selecting the required staff.
My problem is calculating the fee for each rigger/labourer involved in the job due to the breakdown of the charge.
e.g.
if riggers = 1 and labourers = 0 rigger1_fee = charge
if riggers = 1 and labourers = 1 rigger1_fee = 60% of charge and labourer1_fee = 40% of charge
if riggers = 2 and labourers = 0 rigger1_fee = 50% of charge and rigger2_fee = 50% of charge.
if riggers = 2 and labourers = 1 rigger1_fee & rigger2_fee = 35% and labourer1_fee = 30%
etc...
My question is can this be done with IIF statements without getting too complicated and multiple nesting of statements or is it best to try a VB module linked to the form, which I have never attempted before and would appreciate any guidance/help offered if that is the best way forward.
Thank you in anticipation.
Table Field names
charge, rigger1, rigger2 , rigger3, labourer1, labourer2 , labourer3, riggers, labourers, rigger1_fee, rigger2_fee, rigger3_fee, labourer1_fee, labourer2_fee, labourer3_fee.
The table is updated using a form job_lifts_form which has combo boxes to select the riggers and labourers.
The riggers and labourer fields are updated by an update query after selecting the required staff.
My problem is calculating the fee for each rigger/labourer involved in the job due to the breakdown of the charge.
e.g.
if riggers = 1 and labourers = 0 rigger1_fee = charge
if riggers = 1 and labourers = 1 rigger1_fee = 60% of charge and labourer1_fee = 40% of charge
if riggers = 2 and labourers = 0 rigger1_fee = 50% of charge and rigger2_fee = 50% of charge.
if riggers = 2 and labourers = 1 rigger1_fee & rigger2_fee = 35% and labourer1_fee = 30%
etc...
My question is can this be done with IIF statements without getting too complicated and multiple nesting of statements or is it best to try a VB module linked to the form, which I have never attempted before and would appreciate any guidance/help offered if that is the best way forward.
Thank you in anticipation.
