Timesheet Banked hours

[Billiam]

I am making a timesheet database for our non-profit organization with the
tremendous help of MS Access MVP's-THANK YOU!

Employee's are allowed to bank overtime hours, but they must be used within
a calendar year, and consequently, when banked hours are taken out of the
bank and added to a payperiod, they should use the oldest hours first.

At first i thought this would be easy to do, but the more i thought about
it, the more complex it became.

Does anyone have any experience with Banked hours in a timesheet design, or
any design advice before I make a mess of things?

1. Overtime hours begin after 88 hours. Hours above 88 hours can be banked
at 1.5 times the number of hours overtime. Not everyone will want to bank the
hours though, so i somehow need to generate an event to ask whether or not
the overtime hours OR any PORTION of the overtime hours are to be banked.

2. Currently, my timesheet splitform uses an expression to determine the
daily total hours. I have not as yet built a query based on the payperiod to
total the hours for the payperiod...is this the place to build a form from
with command buttons which allocate overtime hours into payable and banked
portions? Also, I need to keep a running total of banked hours which I
assume i will do with a query and then report for submission to our bookeeper
for payment.

Any advice on table setup for this banked hours scenario or advice would be
greatlty appreciated!!!

Thanks,
Billiam

 

[KARL DEWEY]

Something along these lines ---
EmpID -
PayPeriod - Date
Hours - Number single
Type - Reg, OT, LV, BankEarn, BankUse

Available_BankEarn: Sum(BankEarn) - Sum(BankUse) WHERE [PayPeriod] Between
Date() AND DateAdd("yyyy", -1, Date())

 

[Billiam]

Hi Karl,

Thank you for responding! I have a few questions about your response.
Currently, I have a work form which records the daily hours, and a workcode
(Regular, IT-which is paid at a different rate, Sick, Vacation). I believe
what you are suggesting, is on a daily basis, I would use the WorkCode field
to choose OT, Bank Earn Bank Use on a daily basis vs waiting the whole pay
period, subtracting 88 hours from the total,or having an expression that says
"if total hours> 88.0 then ...Total hours-88.0= xhours, and then the x hours
can be split between two comboboxes (cboPayasOvertimethisPayPeriod
andcboBankThisTime which could split the value of extra hours and allocate
them accordingly: in other words all the extra hours could be banked, all the
extra hours could be paid as overtime, or a percentage could be allocated to
each area?

I guess what I am also trying to say is i am unsure how I allocate those
hours above 88 with the system you are proposing as it seems to me, probably
erroneously ;-] that I cannot allocate multiple types to those hours. I
suspect I do not understand how you intended this to be implemented due to my
inexperience?

The Available_BankEarn expression...where would I place this
expression...would it be bound to my t_TimeSheet as a field or fields to
create a complete record for that payperiod? Would it be a calculated query
which would then simply provide a calculated total available of Banked
Hours...and if so, how would you produce a report of which banked hours are
about to expire based on when they were earned?

This seems really complex and overwhelming...it may be beyond me, Karl, but
if you have any further advice, it would be greatly appreciated!

Billiam

Something along these lines ---
EmpID -
PayPeriod - Date
Hours - Number single
Type - Reg, OT, LV, BankEarn, BankUse

Available_BankEarn: Sum(BankEarn) - Sum(BankUse) WHERE [PayPeriod] Between
Date() AND DateAdd("yyyy", -1, Date())

--
Build a little, test a little.

I am making a timesheet database for our non-profit organization with the
tremendous help of MS Access MVP's-THANK YOU!

Employee's are allowed to bank overtime hours, but they must be used within
a calendar year, and consequently, when banked hours are taken out of the
bank and added to a payperiod, they should use the oldest hours first.

At first i thought this would be easy to do, but the more i thought about
it, the more complex it became.

Does anyone have any experience with Banked hours in a timesheet design, or
any design advice before I make a mess of things?

1. Overtime hours begin after 88 hours. Hours above 88 hours can be banked
at 1.5 times the number of hours overtime. Not everyone will want to bank the
hours though, so i somehow need to generate an event to ask whether or not
the overtime hours OR any PORTION of the overtime hours are to be banked.

2. Currently, my timesheet splitform uses an expression to determine the
daily total hours. I have not as yet built a query based on the payperiod to
total the hours for the payperiod...is this the place to build a form from
with command buttons which allocate overtime hours into payable and banked
portions? Also, I need to keep a running total of banked hours which I
assume i will do with a query and then report for submission to our bookeeper
for payment.

Any advice on table setup for this banked hours scenario or advice would be
greatlty appreciated!!!

Thanks,
Billiam

 

[KARL DEWEY]

Not daily, possibly weekly, or your pay period that seems to be 2 weeks.

Have error checking based on your business rules.

Regular, IT, Sick, and Vacation all add up to be equal to 88 or less. If
greater than 88 then error that must be corrected before submittal to
supervisor for approval.

Supervisor approves time and any OT and/or Bank Earn.
Your business rules apply.


--
Build a little, test a little.

Hi Karl,

Thank you for responding! I have a few questions about your response.
Currently, I have a work form which records the daily hours, and a workcode
(Regular, IT-which is paid at a different rate, Sick, Vacation). I believe
what you are suggesting, is on a daily basis, I would use the WorkCode field
to choose OT, Bank Earn Bank Use on a daily basis vs waiting the whole pay
period, subtracting 88 hours from the total,or having an expression that says
"if total hours> 88.0 then ...Total hours-88.0= xhours, and then the x hours
can be split between two comboboxes (cboPayasOvertimethisPayPeriod
andcboBankThisTime which could split the value of extra hours and allocate
them accordingly: in other words all the extra hours could be banked, all the
extra hours could be paid as overtime, or a percentage could be allocated to
each area?

I guess what I am also trying to say is i am unsure how I allocate those
hours above 88 with the system you are proposing as it seems to me, probably
erroneously ;-] that I cannot allocate multiple types to those hours. I
suspect I do not understand how you intended this to be implemented due to my
inexperience?

The Available_BankEarn expression...where would I place this
expression...would it be bound to my t_TimeSheet as a field or fields to
create a complete record for that payperiod? Would it be a calculated query
which would then simply provide a calculated total available of Banked
Hours...and if so, how would you produce a report of which banked hours are
about to expire based on when they were earned?

This seems really complex and overwhelming...it may be beyond me, Karl, but
if you have any further advice, it would be greatly appreciated!

Billiam

Something along these lines ---
EmpID -
PayPeriod - Date
Hours - Number single
Type - Reg, OT, LV, BankEarn, BankUse

Available_BankEarn: Sum(BankEarn) - Sum(BankUse) WHERE [PayPeriod] Between
Date() AND DateAdd("yyyy", -1, Date())

--
Build a little, test a little.

I am making a timesheet database for our non-profit organization with the
tremendous help of MS Access MVP's-THANK YOU!

Employee's are allowed to bank overtime hours, but they must be used within
a calendar year, and consequently, when banked hours are taken out of the
bank and added to a payperiod, they should use the oldest hours first.

At first i thought this would be easy to do, but the more i thought about
it, the more complex it became.

Does anyone have any experience with Banked hours in a timesheet design, or
any design advice before I make a mess of things?

1. Overtime hours begin after 88 hours. Hours above 88 hours can be banked
at 1.5 times the number of hours overtime. Not everyone will want to bank the
hours though, so i somehow need to generate an event to ask whether or not
the overtime hours OR any PORTION of the overtime hours are to be banked.

2. Currently, my timesheet splitform uses an expression to determine the
daily total hours. I have not as yet built a query based on the payperiod to
total the hours for the payperiod...is this the place to build a form from
with command buttons which allocate overtime hours into payable and banked
portions? Also, I need to keep a running total of banked hours which I
assume i will do with a query and then report for submission to our bookeeper
for payment.

Any advice on table setup for this banked hours scenario or advice would be
greatlty appreciated!!!

Thanks,
Billiam

 

[Billiam]

Hi Karl,

Thank you again for responding. I'm afraid this is beyond me as I do not
understand your advice or method...maybe one day, but definately not today!
Thanks for trying,
warm regards,
Billiam

Not daily, possibly weekly, or your pay period that seems to be 2 weeks.

Have error checking based on your business rules.

Regular, IT, Sick, and Vacation all add up to be equal to 88 or less. If
greater than 88 then error that must be corrected before submittal to
supervisor for approval.

Supervisor approves time and any OT and/or Bank Earn.
Your business rules apply.


--
Build a little, test a little.

Hi Karl,

Thank you for responding! I have a few questions about your response.
Currently, I have a work form which records the daily hours, and a workcode
(Regular, IT-which is paid at a different rate, Sick, Vacation). I believe
what you are suggesting, is on a daily basis, I would use the WorkCode field
to choose OT, Bank Earn Bank Use on a daily basis vs waiting the whole pay
period, subtracting 88 hours from the total,or having an expression that says
"if total hours> 88.0 then ...Total hours-88.0= xhours, and then the x hours
can be split between two comboboxes (cboPayasOvertimethisPayPeriod
andcboBankThisTime which could split the value of extra hours and allocate
them accordingly: in other words all the extra hours could be banked, all the
extra hours could be paid as overtime, or a percentage could be allocated to
each area?

I guess what I am also trying to say is i am unsure how I allocate those
hours above 88 with the system you are proposing as it seems to me, probably
erroneously ;-] that I cannot allocate multiple types to those hours. I
suspect I do not understand how you intended this to be implemented due to my
inexperience?

The Available_BankEarn expression...where would I place this
expression...would it be bound to my t_TimeSheet as a field or fields to
create a complete record for that payperiod? Would it be a calculated query
which would then simply provide a calculated total available of Banked
Hours...and if so, how would you produce a report of which banked hours are
about to expire based on when they were earned?

This seems really complex and overwhelming...it may be beyond me, Karl, but
if you have any further advice, it would be greatly appreciated!

Billiam

Something along these lines ---
EmpID -
PayPeriod - Date
Hours - Number single
Type - Reg, OT, LV, BankEarn, BankUse

Available_BankEarn: Sum(BankEarn) - Sum(BankUse) WHERE [PayPeriod] Between
Date() AND DateAdd("yyyy", -1, Date())

--
Build a little, test a little.


:

I am making a timesheet database for our non-profit organization with the
tremendous help of MS Access MVP's-THANK YOU!

Employee's are allowed to bank overtime hours, but they must be used within
a calendar year, and consequently, when banked hours are taken out of the
bank and added to a payperiod, they should use the oldest hours first.

At first i thought this would be easy to do, but the more i thought about
it, the more complex it became.

Does anyone have any experience with Banked hours in a timesheet design, or
any design advice before I make a mess of things?

1. Overtime hours begin after 88 hours. Hours above 88 hours can be banked
at 1.5 times the number of hours overtime. Not everyone will want to bank the
hours though, so i somehow need to generate an event to ask whether or not
the overtime hours OR any PORTION of the overtime hours are to be banked.

2. Currently, my timesheet splitform uses an expression to determine the
daily total hours. I have not as yet built a query based on the payperiod to
total the hours for the payperiod...is this the place to build a form from
with command buttons which allocate overtime hours into payable and banked
portions? Also, I need to keep a running total of banked hours which I
assume i will do with a query and then report for submission to our bookeeper
for payment.

Any advice on table setup for this banked hours scenario or advice would be
greatlty appreciated!!!

Thanks,
Billiam