I need to retain historical data when a parameter value changes

[Linda]

I have a database for collecting data about employee contributions to
a 401k plan. I use the data to calculate employee contributions and
employer contributions. I have a query to summarize on an annual basis
how much each employee contributes and what the company contribution
was based on a formula (company matches 50 cents on the dollar up to
8% of the employees gross pay unless the employee contributes less
than 8%). The employee table includes name, ss# and % contribution.
Occasionally people change the % they want to contribute. This messes
up my annual report because the underlying query takes the current %
contribution from the employee table and doesn't use the actual % that
was used at the time of the contribution.
Thanks,
Linda

 

[George Nicholson]

company matches 50 cents on the dollar up to

8% of the employees gross pay

Got any openings????

Seriously, i would think the only way to state a meaningful YearEnd or YTD
contribution percentage would be to calculate it. Stored % values would be
pretty useless, for the reason you state:

YTDEmpContrbPct: YTDEmpContributions/YTDGrossPay
YTDCompanyContrbPct: YTDCompanyContributions/YTDGrossPay


HTH,

 

[Guest]

It sounds like you'll need to create another table that includes the dates
whenthe percentage were changed. The employee table isn't the best place to
hold this data. You really have a one-to-many relationship between employees
and contribution percentages.

It might look something like
EmployeeID DateofChange %
124 3/4/07 15
124 6/6/07 22

Then you'd have to write a query that calculates each % against their salary
for the give date range. You also have to consider that their salary might
have changed between reporting periods, so the percentage would have to be
against their salary during the same time frame.

 

[Larry Linson]

I'm afraid you have oversimplified your calculations. In my days in the
corporate world, there were multiple options, and the percentage to be
contributed could be changed. Thus, the contribution to the 401K was
calculated and withheld each pay period, and was, like all other payroll
components, saved.

The relational database mantra is not "never save a calculated value" but
"never save a calculated value that you can recalculate when needed from
factors in the same record (some say 'from factors readily available')". In
your case, because of potential changes in the percentage to be contributed,
you cannot reproduce the earlier calculation from factors in the same
record. Pay can also vary... merit raises, promotions, etc., and this, too,
would be a problem in the calculation method you are using... and, if you
have people paid on commission, their pay rate wouldn't have to change, only
their sales.

This is similar to not recalculating the cost of a part based on the current
list price... you calculate the cost based on the price at the time of sale
and save that value, because list price can vary up or down, but the price
at which the part was originally sold doesn't change along with it.

I guess that you are not "doing the payroll in Access" (though some
certainly do) in this case, but if your calculations/summary data are to be
correct, you'll need to obtain the actual contributions saved from the
payroll data.

Larry Linson
Microsoft Access MVP

 

[Linda]

Hi Larry,
I came to the same conclusion about needing a new table (or tables) to
hold historical data but am not sure how to create the table without
complicating the data entry process. Currently I use a simple form
that includes only contribution date, employee and gross salary (since
I do the data entry I like to keep the fields to a minimum so I did
not include the % in the form but drew from employees table). The
calculations are done in a contributions query. I'd like to store the
results of the query for a given time frame (say each month) in a new
table but drew a blank on how to do that (save query results as a new
table?)

By the way, I have not found any source that really explains in detail
how to make access do more than the little bit they teach in the
classes. I bought the Access for Dummies book but am disappointed. It
has been suggested that people become experts by trial and error
method. Do you agree? Can you recommend some good sources for "how to"
questions?

Thanks,
Linda

 

[Linda]

That's a good idea, I'll play with that. Should be a challenge to
write the query.
Thanks

 

[John W. Vinson]

Do you agree? Can you recommend some good sources for "how to"
questions?

Microsoft Access Inside Out by John Viescas.

Other references:

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

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

A free tutorial written by Crystal (MS Access MVP):
http://allenbrowne.com/casu-22.html

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

John W. Vinson [MVP]