[AccessD] Payroll

[Jim Dettman]

Bob,

<< tblTimeCard containing.. EmpID, JobID, Payperiod, Monday1Hours,
Tuesday1Hours.......... Monday1Hours15, Tuesday1Hours15,........>>

  You really don't want this.  You've broken a normalization rule and it
really leaves you with an inflexible design if changes are wanted in the
future.   Keep in mind that the table design does not imply the UI. You can
use whatever setup you want for the entry of the time cards and then
generate the records as needed.

Something along these lines would be better:

tblTimeCards - One record per pay period.
TimeCardID - AN - PK
EmpID - Long - FK tblEmployees
JobID - Long
WorkDate - D/T
TimeIn - Decimal(4)
TimeOut - Decimal(4)
ElapsedTime - Decimal(4)
BasePay - Currency
RegHrs - Decimal(4)
OTHTHrs - Decimal(4)
OTDTHrs - Decimal(4)
RegPay - Currency
OTHTHrs - Currency
OTDTHrs - Currency
PayCode - Text(4)
PayCodeComment - Text(32)
EnteredBy
EnteredOn
PostedPR - Date/Time

  Where you have one entry for each clock on/off.    Many systems try to do
one record per time clock "punch", but it becomes a very complex task to
marry up a start time with an end time.   With this setup you can tell
easily if someone missed a clock in or out, and it's much easier to process.

  In regards to the work date and then a start/stop, some will do it as a
date/time in and date/time out.   I've found it easier to handle the times
separately and store them as text as this allows systems where you want
10ths of an hour in one, and actual minutes in another.   Ie.   1030 can be
10th hour 30 minutes, or 10th hour and .3 hours (18 minute).  This is also
why ET (Elapsed Time) is stored in the record above rather than simply end -
start.   If you need on-job time spans > 24 hours, you would need to add a
EndWorkDate.

  You also mentioned a job ID; is this an incentivized system?   If so,
you'll need quite a few more fields (SKU, Machine, Operation, NoPeople, Qty,
Base Rate, STDAllowed,  STDUsed, EarnedHrs, Normalization Factor, Normalized
Earned Hours, Percent of STD, etc.)  and also take into account shift
differentials.

  The other thing you'll notice here is some fields are stored (i.e.
RegPay).  While that flies in the face of what you'd normally do, there are
a few reasons for that:

1. Once calculated, it's easy to sum up those fields to feed to a payroll
system.
2. Doing so allows an employee's base pay to change at any time.
3. The rules for deciding what is Reg vs OT can change at any time.
4. You can always look back and derive how an employees pay was calculated.

  PayCode is so you can tag entries for things like vacation time, sick pay,
training, family leave, etc.

  I can fill in more details if needed.

HTH,
Jim.

-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Bob
Heygood
Sent: Sunday, April 29, 2018 5:25 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Payroll

Thanks for the responses.
I should have been a bit more clear.
I am only tracking the hours worked per day per payperiod.
I will easily via a button dump into Excel for submission to the payroll
processer. Like ADT.

Bout settled on :
tblEmployees
tblJobs
tblTimeCard containing.. EmpID, JobID, Payperiod, Monday1Hours,
Tuesday1Hours.......... Monday1Hours15, Tuesday1Hours15,........
Monday1Hours2, Tuesday 1Hours2, .........
		then for the second week:  Monday2Hours, Tuesday2Hours......
Monday2Hours15, Tuesday2Hours15...... Monday2Hours2, Tuesday2Hours2

this allows for time, time and a half and double time.
Also, makes for easy and familiar entry.

tblEmployees contains pay rate info, so a little multiplication will give me
totals for the resulting sheet/query.

Tia

Bob


-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
James Button via AccessD
Sent: Friday, April 27, 2018 10:36 AM
To: 'Access Developers discussion and problem solving'
Cc: James Button
Subject: Re: [AccessD] Payroll

Deadly assumption(s)

Workers will only work 1 shift in a calendar day Workers shift will be
entirely within a calendar day Pay will be at the standard rate(s) for all
hours in a shift Bonuses will be subject to tax Benefits in kind - car,
fuel, or taxi's, meals , healthcare - various categories and provision
processes Benefits in kind - non taxable such as hydration drinks, uniforms
and safety equipment Newly introduced local, or state taxes 

I would consider the input of hours being a daily thing with all employees
(and contractors?, and agents?, and professional persons?) hours being
entered by rote working down the employee list - That list being ordered by
category, location and maybe grading so 1 person deals with input for
management at a site and 1 person (maybe the same one) deals with the
general pay-scale workers.

Allow for - paid or not, hours
holiday
Sick leave, compassionate/extraordinary
Education/courses
Legally required
Contractual
Pay in lieu of hours
Hours owed/carried forward
Meetings required to attend - performance, disciplinary, associated with the
employment

All should be considered as entries for that person's 'employment' in a
period - day

The input should be appropriate for easy verification that there is no
fraudulent input and submission to courts if required /ordered There maybe a
legal decision that pay is (or is not) required for the time spent in such
activities.

Then - for the actual payment - as in assembly of time for which payment is
due,

That becomes the weekly, or other timing process Not forgetting the
possibility of changes to the qualification and 'cash'
related to any and all the above at any time in the payment period.
Your payroll period is unlikely to match the legislative periods.
 

Also maybe worth having an 'acceptable'  max for each person (or job
classification) for the payment period and for the quarter, and annually

There should also be a very well managed process for implementing changes on
specific dates (and times) with the effects being applied from specific
dates &
times - Local or other 
Consider employees abroad on behalf of the organisation

Payroll is a real pain in the head (and elsewhere) and that is without
getting
retrospective adjustments from insurers,  government departments, and the
courts

As well as evaluations by inspectors who cannot do basic arithmetic, speak
the
language properly. Or even punctuate letters so their orders and
requirements
are clear.

So - no template - but asuggestion that you keep at least 7 separate
structures
- the business, the employee their contractual usability, qualifications
and
benefits, the activities they do, the payments due, the payments made, the
payroll & benefits declarations to the government, and the associated
business
costings.

OK - they will all link into 1 actual composite, but keeping them separate
makes
their management - with modifications, general data input and reporting
easier
to manage as an IT facility.

JimB 


-----Original Message-----
From: AccessD <accessd-bounces at databaseadvisors.com> On Behalf Of Bob
Heygood
Sent: Friday, April 27, 2018 4:21 PM
To: 'Access Developers discussion and problem solving'
<accessd at databaseadvisors.com>
Subject: [AccessD] Payroll

Hello to the list,
I am beginning design of a db to track payroll. The clients biz is such that
even though I suggested a "canned" solution, they need a custom.
But in order not to recreate the wheel I am looking for a template or a
table
relationship to jump start me.
They use a two week pay period. Ideally they would choose an employee
(already
have a table), then a pay period (already have a table listing dates for two
years) and then enter hours in 14 text boxes or maybe a list box ???

Tia
Bob

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com