Link Pay Period Ending NUMBER to PPE DATE

[Ann Scharpf]

I'm running Access 2003.

I am trying to establish a database that can be used year after year. In
it, I need to calculate estimated employee hours for each contract, spread
evenly over the course of the year. Our pay period ending dates are every
two weeks and change every year.

I don't think you want all the information about the tables/queries in the
database. Here are the pertinent fields in the tables/queries in question:

Employee Timesheet Allocation
Employee Name
Contract Number
PPE1 (Hours expected to work against contract in first pay period)

 

[John Vinson]

I'm running Access 2003.

I am trying to establish a database that can be used year after year. In
it, I need to calculate estimated employee hours for each contract, spread
evenly over the course of the year. Our pay period ending dates are every
two weeks and change every year.

I don't think you want all the information about the tables/queries in the
database. Here are the pertinent fields in the tables/queries in question:

Employee Timesheet Allocation
Employee Name
Contract Number
PPE1 (Hours expected to work against contract in first pay period)
.
.
.
PPE26 (Hours expected to work against contract in last pay period)

This isn't a table. This is a SPREADSHEET.

"Fields are expensive, records are cheap". A much better design for
this would be to have *one* allocation per record:

EmployeeTimesheetAllocation <it's best to avoid blanks in names>
EmployeeID <DON'T use names as keys, they aren't unique and can
change>
ContractNumber
PPEDate
ExpectedHours

Hours Recorded
Employee Name
Contract Number
PPE Date
Actual hours worked during pay period associated with PPE Date.

PPE Dates - one record with 26 fields
PPE1 (Picks up first PPE date from another table)
PPE2 (Adds 14 days to PPE1)
PPE3 (Adds 28 days to PPE1)

Again - this is incorrectly normalized. Just a table with one field,
one date per record, named PPEDate.

.
.
PPE26 (Adds 350 days to PPE1)

My end goal is to be able to total up all the hours recorded in the time
dump (by contract). Then add all FUTURE estimates to the expended time. I
cannot see how to get a query to substitute/link the PPE DATE for the PPE#
that is in my timesheet allocation table. Or how to say if the date is
greater than the last date in the time dump, take all associated PPE#s from
the PPE Dates query and use those PPE#s in the estimates table and add up the
associated times.

Easy using a Totals query if you design your table correctly.
Nightmarishly difficult with your wide-flat design.

I feel like I'm rambling but I can't think of a better way to explain what
I'm trying to do. Am I completely out in left field with the way I've set up
my database? Is there a way to do this calculation/substitution?

Yes, and yes. See above.

Thanks in advance for any help you can give me.

Check out the links at

Jeff Conrad's resources page:
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

especially the "Database Design 101" link on Jeff's page.

John W. Vinson[MVP]

 

[Ann Scharpf]

Thanks for taking the time to answer my question.

I didn't state all my rationale for the design. Throughout the year, the
business office will reallocate individuals to different contracts, as the
burn rate is never what is originally planned. I need to have the ability to
change the expected work hours for future pay periods.

I'd thought that I should have separate records for the PPE dates instead of
one record with 26 fields ... But I went with 26 pay periods in one record so
I could automatically calculate out all the PPE dates by having the user
enter just the first one. Can I automate this if each PP is a separate
record or would the user have to figure out and enter all 26 dates? (I
couldn't see how to make records increment by 14 days from the previous
record.)

If I implement the type of design you recommend, how will I tell the
database how many pay periods are in the future? (E.g. this is 12/21, so
multiply the estimated hours by 21 ... This is January 5, so multiply the
estimated hours by 18...)

I will check out the links you listed. Especially the design 101. I've
only done a few databases and I'm still struggling with the design aspect.

 

[Ann Scharpf]

Took a quick look at these links before going to bed. They are really great.
Will bookmark at both work & home. Thanks.

 

[Ann Scharpf]

I figured out that I could add a table PPEdates with the following fields:

PPEID (autonum key)
PPEnum PPE1 - PPE26 (might end up deleting this field but leaving for now)
PPEdate (must be manually entered. I used transpose rows in Excel to flip
data from old table)
RemainingPPEs (decrements by 1 for each record)

Then my query asks the user for the last reported PPE date. I multiply the
SINGLE value HrsPerPPE * RemainingPPEs and get the estimate for the year.
This does not accommodate the fact that the last pay period of the year is
often less than a full two weeks but I think it will be ok.

Thanks very much for your help. This WILL be far easier to maintain than
the "flat" design I had before.

 

[John Vinson]

Thanks very much for your help. This WILL be far easier to maintain than
the "flat" design I had before.

Glad to have been of assistance. It should be easy enough to maintain
- you'll need to populate this new table each year but that could be
done programmatically or with a simple fill-down in Excel, followed by
an import or copy & paste.

John W. Vinson[MVP]