Table Structure for Pay Grades

[Secret Squirrel]

I need to create a table that will store pay grades for my 6 pay levels but
the min & max of these pay levels increment 2.5% per year. I'm trying to
think of the best way to set this up and one idea I have if possible is to
have 6 new records automatically created in this table and have the min and
max rate calculated from the prior year by 2.5%. Here's what I have for
fields in my tblPayGrades

LevelID - PK
Level
Year
MinGrade
MaxGrade
PercentIncrease

Right now I have my values in there for 2007. Would it be easier to create a
query to calculate this? If so, how would I have it calculate 2.5% for each
year going forward? Any ideas or suggestions would be greatly appreciated.
I'm having a brain freeze on this one!

 

[Secret Squirrel]

Hi George

The paygrades get updated every year. I would include this in the year end
processing so that the new year will be loaded before it begins. They
increase 2.5% per year. So yes we would need records for each year. We
wouldn't need to store the percentage for each year. We could calculate that
if needed for comparisons.

I assume that the "PercentIncreaseforNextYear" control is unbound and just
there for users to enter a percent and then fire the code, correct?

The code you wrote will add new records for all my 6 paygrades, correct? I
assume it will just duplicate them and calculate the min and max paygrades to
increase by the percentage entered on the form, right?

How often do you need to adjust paygrades? Is it necessary to have records
for paygrades as of 2009, 2010, 2020, 2050? Has the adjustment always been
2.5% each year ? WIll it always be 2.5% in the future? If you calculate the
new paygrades at the beginning of a year, do you need to also store the
percent by which they were increased, or could you just calculate that by
comparing current year to prior year?

Answering all of those questions should lead to some ideas about how to
handle this, but here's how I would think about it.

I would imagine that the most flexible way to do this would be to use a form
in which you have a control called something like
"PercentIncreaseforNextYear" In the control, you can enter a value for the
annual increase, as a percent.

Then you'd have a button next to that field labelled something like "Apply
Increase" That button would run a line of code like this:

CurrentDb.Execute "INSERT INTO tblPayGrades ( [Level], PayYear, PayGradeMin,
PayGradeMax ) " & _
" SELECT tblPayGrades.Level, [PayYear]+1 AS NewPayYear,
[PayGradeMin]+([paygradeMin]* " & _
Me.PercentIncreaseforNextYear & ") AS NewPayGradeMin,
[PayGradeMax]+([PayGradeMax]* " & _
Me.PercentIncreaseforNextYear & ") AS NewPayGradeMax FROM tblPayGrades
" & _
" WHERE (((tblPayGrades.PayYear) In (Select Max(PayYear) From
tblPayGrades))) " & _
" GROUP BY tblPayGrades.Level, [PayYear]+1,
[PayGradeMin]+([paygradeMin]*" & Me.PercentIncreaseforNextYear & "),
[PayGradeMax]+ " & _
"([PayGradeMax]* " & Me.PercentIncreaseforNextYear & ");", dbFailOnError

If you want to include the percentage of the increase in a field, I guess
you can, but it's not necessary, as far as I can see.

BTW: Avoid using reserved words like "Year" as names of fields. It's easy to
come up with a more meaningful term thus avoiding the potential for
confusion.

I need to create a table that will store pay grades for my 6 pay levels but
the min & max of these pay levels increment 2.5% per year. I'm trying to
think of the best way to set this up and one idea I have if possible is to
have 6 new records automatically created in this table and have the min
and
max rate calculated from the prior year by 2.5%. Here's what I have for
fields in my tblPayGrades

LevelID - PK
Level
Year
MinGrade
MaxGrade
PercentIncrease

Right now I have my values in there for 2007. Would it be easier to create
a
query to calculate this? If so, how would I have it calculate 2.5% for
each
year going forward? Any ideas or suggestions would be greatly appreciated.
I'm having a brain freeze on this one!

 

[George]

Yes, it's an unbound control. Please try the code, changing the names to
those in your own database, of course. That will tell you if it works the
way you need/want it to work. It worked in my test database, but that's no
gurantee.

I very much doubt that the pay increase will always be 2.5%, but hey, I'm
not running your company. It's a moot point anway. Neither of us can know
that for sure until a few years have passed. And by that time I'll probably
be retired and not too concerned any longer.

Hi George

The paygrades get updated every year. I would include this in the year end
processing so that the new year will be loaded before it begins. They
increase 2.5% per year. So yes we would need records for each year. We
wouldn't need to store the percentage for each year. We could calculate
that
if needed for comparisons.

I assume that the "PercentIncreaseforNextYear" control is unbound and just
there for users to enter a percent and then fire the code, correct?

The code you wrote will add new records for all my 6 paygrades, correct? I
assume it will just duplicate them and calculate the min and max paygrades
to
increase by the percentage entered on the form, right?

How often do you need to adjust paygrades? Is it necessary to have
records
for paygrades as of 2009, 2010, 2020, 2050? Has the adjustment always
been
2.5% each year ? WIll it always be 2.5% in the future? If you calculate
the
new paygrades at the beginning of a year, do you need to also store the
percent by which they were increased, or could you just calculate that by
comparing current year to prior year?

Answering all of those questions should lead to some ideas about how to
handle this, but here's how I would think about it.

I would imagine that the most flexible way to do this would be to use a
form
in which you have a control called something like
"PercentIncreaseforNextYear" In the control, you can enter a value for
the
annual increase, as a percent.

Then you'd have a button next to that field labelled something like
"Apply
Increase" That button would run a line of code like this:

CurrentDb.Execute "INSERT INTO tblPayGrades ( [Level], PayYear,
PayGradeMin,
PayGradeMax ) " & _
" SELECT tblPayGrades.Level, [PayYear]+1 AS NewPayYear,
[PayGradeMin]+([paygradeMin]* " & _
Me.PercentIncreaseforNextYear & ") AS NewPayGradeMin,
[PayGradeMax]+([PayGradeMax]* " & _
Me.PercentIncreaseforNextYear & ") AS NewPayGradeMax FROM
tblPayGrades
" & _
" WHERE (((tblPayGrades.PayYear) In (Select Max(PayYear) From
tblPayGrades))) " & _
" GROUP BY tblPayGrades.Level, [PayYear]+1,
[PayGradeMin]+([paygradeMin]*" & Me.PercentIncreaseforNextYear & "),
[PayGradeMax]+ " & _
"([PayGradeMax]* " & Me.PercentIncreaseforNextYear & ");",
dbFailOnError

If you want to include the percentage of the increase in a field, I guess
you can, but it's not necessary, as far as I can see.

BTW: Avoid using reserved words like "Year" as names of fields. It's easy
to
come up with a more meaningful term thus avoiding the potential for
confusion.

I need to create a table that will store pay grades for my 6 pay levels
but
the min & max of these pay levels increment 2.5% per year. I'm trying
to
think of the best way to set this up and one idea I have if possible is
to
have 6 new records automatically created in this table and have the min
and
max rate calculated from the prior year by 2.5%. Here's what I have for
fields in my tblPayGrades

LevelID - PK
Level
Year
MinGrade
MaxGrade
PercentIncrease

Right now I have my values in there for 2007. Would it be easier to
create
a
query to calculate this? If so, how would I have it calculate 2.5% for
each
year going forward? Any ideas or suggestions would be greatly
appreciated.
I'm having a brain freeze on this one!

 

[Arvin Meyer [MVP]]

You have more to think about than 2.5% increase. Suppose it changes to 3% or
1% or even turns negative in an economic downturn. Typically to cover the
possibilities, you wouldn't store any calculations, but the specific
elements of the calculations. So what you want is a many-side table with:

EmployeeID - FX to tblEmployees
LevelID - FK to tblPayLevels
CalcDate - Date/Time that the change is effective
PayRate - Currency

You may also need to store a field to tell you the RatePeriod (hourly or
salary) per check

 

[Secret Squirrel]

Yes it worked fine. Thank you.

I agree with you about the percentage staying the same. All I know is HR
told me they use 2.5% as the increase amount and they've used that since
2005. These days you never know but I have the flexibility of changing that
from the control on the form I use to fire the code you wrote.

I appreciate your help with this George. You made my day by getting over
this hurdle.
That was my final piece to this database puzzle. Thank you very much!

Yes, it's an unbound control. Please try the code, changing the names to
those in your own database, of course. That will tell you if it works the
way you need/want it to work. It worked in my test database, but that's no
gurantee.

I very much doubt that the pay increase will always be 2.5%, but hey, I'm
not running your company. It's a moot point anway. Neither of us can know
that for sure until a few years have passed. And by that time I'll probably
be retired and not too concerned any longer.

Hi George

The paygrades get updated every year. I would include this in the year end
processing so that the new year will be loaded before it begins. They
increase 2.5% per year. So yes we would need records for each year. We
wouldn't need to store the percentage for each year. We could calculate
that
if needed for comparisons.

I assume that the "PercentIncreaseforNextYear" control is unbound and just
there for users to enter a percent and then fire the code, correct?

The code you wrote will add new records for all my 6 paygrades, correct? I
assume it will just duplicate them and calculate the min and max paygrades
to
increase by the percentage entered on the form, right?

How often do you need to adjust paygrades? Is it necessary to have
records
for paygrades as of 2009, 2010, 2020, 2050? Has the adjustment always
been
2.5% each year ? WIll it always be 2.5% in the future? If you calculate
the
new paygrades at the beginning of a year, do you need to also store the
percent by which they were increased, or could you just calculate that by
comparing current year to prior year?

Answering all of those questions should lead to some ideas about how to
handle this, but here's how I would think about it.

I would imagine that the most flexible way to do this would be to use a
form
in which you have a control called something like
"PercentIncreaseforNextYear" In the control, you can enter a value for
the
annual increase, as a percent.

Then you'd have a button next to that field labelled something like
"Apply
Increase" That button would run a line of code like this:

CurrentDb.Execute "INSERT INTO tblPayGrades ( [Level], PayYear,
PayGradeMin,
PayGradeMax ) " & _
" SELECT tblPayGrades.Level, [PayYear]+1 AS NewPayYear,
[PayGradeMin]+([paygradeMin]* " & _
Me.PercentIncreaseforNextYear & ") AS NewPayGradeMin,
[PayGradeMax]+([PayGradeMax]* " & _
Me.PercentIncreaseforNextYear & ") AS NewPayGradeMax FROM
tblPayGrades
" & _
" WHERE (((tblPayGrades.PayYear) In (Select Max(PayYear) From
tblPayGrades))) " & _
" GROUP BY tblPayGrades.Level, [PayYear]+1,
[PayGradeMin]+([paygradeMin]*" & Me.PercentIncreaseforNextYear & "),
[PayGradeMax]+ " & _
"([PayGradeMax]* " & Me.PercentIncreaseforNextYear & ");",
dbFailOnError

If you want to include the percentage of the increase in a field, I guess
you can, but it's not necessary, as far as I can see.

BTW: Avoid using reserved words like "Year" as names of fields. It's easy
to
come up with a more meaningful term thus avoiding the potential for
confusion.



message I need to create a table that will store pay grades for my 6 pay levels
but
the min & max of these pay levels increment 2.5% per year. I'm trying
to
think of the best way to set this up and one idea I have if possible is
to
have 6 new records automatically created in this table and have the min
and
max rate calculated from the prior year by 2.5%. Here's what I have for
fields in my tblPayGrades

LevelID - PK
Level
Year
MinGrade
MaxGrade
PercentIncrease

Right now I have my values in there for 2007. Would it be easier to
create
a
query to calculate this? If so, how would I have it calculate 2.5% for
each
year going forward? Any ideas or suggestions would be greatly
appreciated.
I'm having a brain freeze on this one!