[AccessD] Payroll

James Button jamesbutton at blueyonder.co.uk
Mon Apr 30 14:44:02 CDT 2018


>From (painful) experience -
Yes - you can do what the client (boss?) declares is to be done. 
Or 
You can do what will allow flexibility in the process 

Either way you should detail what the process is required to do, and what the
design will not allow to be addressed.

That document should be accepted by, and signed off by those responsible for the
implementation of the process, those who will have to enter the data into the
system, and those who will have to use the data from the system.
Your job will then be to design a data structure and process to meet the
requirements of all groups.

So - considering what had been detailed 
No person is allowed to leave work and then return during the same day 
No person is allowed to continue working through midnight

So - that is a total inability coded within the system for someone to go to the
doctors, to hospital, to the police station, on an unpaid basis,  and then
return to complete their shift  

If you only have the 1 period enterable, then there is no possibility of
enhanced payment for an extended day 

Definitely - you are going to need to have gotten (and secured) a set of
signatures for the design of the user interface!

The documentation will certainly be needed for UAT and may later maintenance
processing (after you move on to a new environment). 

Normalisation is a good idea and should be applied within the data store
Views are a means of presenting composite data to a facility/app/user report 
And while Normalisation need not be applied at all times, the data should ALWAYS
be easy to present in a normalised manner for other processing.

As in - yes UI should present payroll number, Earning period (which is not the
payroll period) and the period worked 
It is also prudent to include other data such as the employees name, work
location, supervisor/manager time recorder id, from other tables 

You will need to consider the process that will be needed to take the data from
the presented single start and end time for each of 14 days  into a normalised
form for the payment calculator 
That follow on process will (I would expect) be processing a payment at a set
rate for a single specification of the number of hours worked.

JimB


-----Original Message-----
From: AccessD <accessd-bounces at databaseadvisors.com> On Behalf Of Rocky Smolin
Sent: Monday, April 30, 2018 7:57 PM
To: 'Access Developers discussion and problem solving'
<accessd at databaseadvisors.com>
Subject: Re: [AccessD] Payroll

You make the data entry form bound to the hours table and let them enter a
date range to display.  If the date range exceeds that last date in the
hours table for that employee, create the empty records and then display. 

They can then tab from one entry to the next.  Instead of tabbing across
they would be tabbing down. But same approach to tabbing. 

If you want to discuss further call me.


Rocky Smolin
Beach Access Software
760-683-5777
www.bchacc.com
www.e-z-mrp.com
Skype: rocky.smolin
 


-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Bob
Heygood
Sent: Monday, April 30, 2018 11:31 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Payroll

Rocky & Jim,
Yes I knew intuitively that I was breaking some normalization rules.
Just started to let the UI interface drive the schema.

For data entry the user insists on being able to "tab" through a whole pay
period (2 weeks) for each employee and job.

So, how do I use a table with only one record per day?
UNBOUND, I guess. And then via code stuff into a "proper" table........
I have done so before but a lot work.

Really don't need any other fields.

Thanks for responding,

Bob




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

Bob:

I think your timecard capture table should have one record for each day with
FK of Empl ID:

fldTimeCardID	Autonumber
fldEmplID	Number (FK)
fldDateWorked	Date
fldHours	Number(Double)
fldMultiplier	Number (for straight time(1), time and 1/2(1.5), double
time(2)

Obviously an Employee table.
And then a work period Start Date, End Date But for rate of pay since the
rate changes you should have a RateofPay
table:

fldRateOfPayID	Autonumber
EmplID			Number (FK)
fldRateStarDate	Date
fldRateEndDate	Date
fldBaseRate		Number (Double)

It's a little more complicated that way but that way you could reconstruct
their pay for any period in the past. 

But if all you want is hours without anything else then you don't need the
pay rate data.  But then why not just dump the timecards into a spreadsheet?
What is Access doing for you?

If you want to discuss, call me.  Or come over and in  short order we could
design the back end to just what you want.

R
760-683-5777


-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Bob
Heygood
Sent: Sunday, April 29, 2018 2: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



More information about the AccessD mailing list