Access 2000 (9.0.2720)

Topic

New Reply

I am fairly new to Access and need some help about the best way to set up the information for a table.

We are working on a case where we think the workers may have been under paid. We have a number of workers, some have worked all of 2002 and up through January of 2003. Others have worked part of 2002 and into 2003, but maybe only a couple of months.

I need to record information per pay period for each worker in order to calculate pay per hour for each worker.

My first question has to do with the how to work with the pay periods. Each pay period is the same for every worker that worked that particular pay period. I don’t want to enter that information for each worker, since the dates will be the same. But some workers will have worked more months than other. I’m assuming I should have a separate table made up of the pay period dates, but how do I set it up to get that information to show correctly for each worker in order to show only the pay period worked for that particular person?

The other question involves, I think, using a calculated field. After I put in the info for pay period and gross pay, I need to enter the total hours worked per the check stub, then have a field in the query that returns the hourly wage based on the calculation of gross pay + bonus – deductions. The second part is similar, only I enter the number of hours that the worker has documented in his records, and get an hourly wage based on that to compare with the hourly wage based on the pay stub hours listed. This I think I know how to do, but would be happy to hear any ideas or thoughts on ways to do it, or problems I might encounter.

Thanks very much for any help you can give. We are a non-profit law firm (I know, I know, an oxymoron if there ever was one) working with poverty level clients. My work has paid for Access classes for me, but I still feel like I just haven’t used it enough to know the subtitles of stuff like this. I humbly ask for help from the experts.

Reply | Quote

Replies

Hi Tia,

I would create a separate table that contains a date and further relevant information for each pay period. How to store pay period information for the individual workers, depends on your situation.

If workers always work for a contiguous period of time, you can store the start and end date of their employment in the worker record itself. To get a list of all pay periods for a worker, create a query and add the Workers and PayPeriods table. Add the date field from the PayPeriods table to the query and set as criterion Between [StartDate] And [EndDate] where StartDate and EndDate are the field names from the Workers table.

If a worker can work several periods, say May 2002 through August 2002 and December 2002 through April 2003, you need an intermediate table. Each record in this table represents a contiguous work period of a single worker, so it would need at the least three fields: the worker ID and the start and end dates, perhaps other fields that contain pay information.

Perhaps you can use these ideas to set up the tables. Others will probably contribute tips and ideas too. Don’t hesitate to come back and ask more questions.

Reply | Quote

Hi Tia,

I do both Access development and HR consulting (mostly around compensation issues), so this is right up my alley. The major assumption I’m going to make is that you’re in the US and dealing with US laws — if not, much of what I say may not apply. A few questions first. Are you in the US? Are you just concerned with people getting paid less than their base hourly wage? Or are you concerned with people not getting paid overtime properly? Or both? What sorts of bonuses are people receiving? What do you mean by deductions? Just FICA, Medicare, Social Security, voluntary pre/post-tax medical deductions, etc.? Or are there other deductions?

If you’re looking at overtime issues, you’ll need to break everything down by the week, even if a pay period is two weeks, half a month or monthly. Hopefully the pay stubs show that breakdown.

I would have a table of workers, a table of pay periods, a table of pay weeks (which is just the weeks in the year), a table of worker pay periods, and a table of worker pay weeks. If you have the start and end dates for each of the workers, you can run an append query (selecting only the periods within those dates) to add the periods to each worker pay period/week table, and then fill in the data from there.

This is still skipping over quite a few details, but that’s how I would start. Feel free to contact me directly if you’d like some more help.

Brent

Reply | Quote

Thank you to both Brent & HansV.

This case deals with pay issues regarding migrant workers, so a lot of the regular employment law doesn’t fit them. They fall under agricultural, and even there there are some things peculiar to the wages of agricultural workers who are alien workers. To complicate this, the employer just switched at the first of the year from a 5th & 20th payday every month to an “every two weeks schedule.” Anyway, your suggestion to put the pay periods into one table is what I needed, I think.

The “deductions” are part of what we find dubious, they deduct for “uniforms” every pay period. Those amounts need to be subtracted from the gross salary before calculating the hourly wage. I need to calculate a wage per hour field based on total hours in a pay period.

Thanks again for responding so quickly and so thoroughly!

Reply | Quote