Compare Query Data to Column Name

[dmeiser]

Hello:

I need to display historic pay information for employees. I have 3
tables:

1) Job Category & Description,
2) Employee Name, Job Cat, Job Cat Start & End Date (lateral movement),
Date of Hire
3) Job Cat, pay rates at: Start, 6 mo., 1 yr., 2 yr.

So, what I need to do is have the query look like this:

job cat :: name :: mmm yyyy :: mmm yyyy :: ...

Sample information would be like this:

1 :: Joe Jim Bob :: Pay Rate This Month :: Hourly Rate This Month ::
....

Where hourly pay is selected out of table 3 based upon the date
difference between the date of hire and the column name ( e.g. -
Somebody hired in Sept 2003 would be selected out of the "2 yr" column,
and somebody hired yesterday would be selected out of the "Start"
column ).

I'm using a crosstab query and the only thing yet to be accomplished is
getting the date difference between the column name and the start date.


I would not be offended if somebody told me that there is a) a better
way to do this, or b) that I'm stupid for even trying this.

Thank you,
Dave

 

[Jeff Boyce]

If your data's column names hold data themselves (i.e., "Start", "6 Month",
"1 year", "2 year"), you will find yourself working overtime to do simple
things in Access. That type of data structure is about your only option ...
in a spreadsheet! As a relational database, Access offers a rich set of
features and functions, but they only work (well) when your data is
well-normalized.

And by working overtime, I mean, won't you have to go through the database
every so often and "move" people from one column ("Start") to another ("6
Month"), then another ...?

Consider a table that has a hire date field, and a query that returns "folks
hired less than 6 months ago", and another query that returns "folks hired
between 6 month and one year ago", and ... This approach is "dynamic", and
up to date wheneven the query is run.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[dmeiser]

See, what's funny here is that they actually have a spreadsheet that
already does this. But it was poorly designed.

What they want me to do is make something in Access that exactly
mimicks what a well designed spreadsheet would have done.

Since we have multiple job cats with pay rate dependant on date of hire
and people can move laterally to a different job category, I need to
keep track of when they were hired and when they changed job
categories. Again, this is to build a budget history. I guess I
didn't make this clear in the first post.

The end result is that:
1) they don't want to have to enter data whenever somebody's pay rate
changes (what they do now)
2) because they need to see pay progression by employee they won't
accept any end result that doesn't show the data in the first format.
That format, again, is this:

Job Cat :: Name :: mmm yyyy :: mmm yyyy :: mmm yyyy :: ...
1 :: Jim Bob :: $0.30 :: $0.40 :: $1.50 :: ...

-----------------------------------------------------------------------------------------------------------------

 

[dmeiser]

I just thought of an illustration that might help explain why I did the
data design in the way that I did:

We hired Jim Bob as an employee in Jan. 2003 at $2/hr. After 6 months
his pay increases by $.10, and after a year it increases $.20. So, in
Jun 2003, he's making $2.10, and by Jan 2004, he's making $2.30.

He's showing potential and we have an Assistant Manager's position open
and that pay is a $.50 differential from regular employee pay. So Jim
Bob jumps at it. So, in Feb 2004 he's making $2.80 (and also changes
job categories).

Now, in Dec 2005 the company gives a $.60 bump to the Assistant Manager
position and, on top of that, Jim Bob reaches the two year mark which
is also accompanied by a raise. So, Jim Bob's hourly pay jumps by $.90
to $3.70.

But Jim has been really unhappy as an Assistant manager, so he applies
to an open maintenance position, since they make the big bucks. So,
now he's switched job categories again, but he's still been employed
for over 2 years, so he's now making the maintenance two year rate -
and rolling in dough at $5/hr.

Jim Bob loves the job and sticks with it. Dec 2006 rolls around and
the maintenance job cat rate bumps to $5.50, so now he's making $5.50.

----------------------------------------------------------------------------------------------

Here's where my job starts: Now, my manager wants to know what each
person in the the whole company in each job category was making each
month between Sep 2003 and Sep 2006, broken down by person and month,
ie - the initial format that I mentioned - so that s/he can figure out
the percent difference in company payroll during that period of time so
that s/he can budget payroll change in the next year.

So, I needed to incorporate the following into the database design:

1) any job cat pay rate changes, with a date that they took effect, and
also what the pay is at the start, 6 mo, 1 yr, and 2 yr rates
2) when the employee was at each job cat
3) the employee date of hire

I initially thought of having the job cat pay rate database in the
following format:

Job Cat :: Effective Date :: For # Months w/ company :: rate
1 :: 1/1/1900 :: 0 :: $.01
1 :: 1/1/1900 :: 6 :: $.02, etc.

However, this seemed poorly designed since it creates 4 records for
each job cat * ~20 job cats * 4 years = 320 records, as opposed to 1
record per job cat * ~20 job cats * 4 years = 80 records. In addition,
which column to grab the data from isn't the issue.

The real issue is how do I figure Jim Bob's pay in the month of Aug
2003 - 6 months after he's started - in a query, when he hasn't
switched job cats but his pay has increased.

 

[Jeff Boyce]

That's an excellent example of what data you have to work with and what
(kind of) questions you need to answer!

(I'll point out that you can tell your "powers that be" that Access is NOT a
"better spreadsheet".)

While I don't have any magic bullet answers, your explanation makes it much
more likely that someone will have an "I did that!" moment -- you may want
to re-post your question, but include the explanation. This will get more
eyes on your issue.

What did occur to me is that you have some pay raises that are related to
time-at-work and some that are related to time-in-a-specific category. The
other "raises" you mentioned sound more like "look how much more the new job
earns" -- not really a raise in pay, but a new pay level. I'll assume that
taking a "lower" position would result in a new (lower) pay rate.

I'm offering this observation, not because it's a final answer, but because
it may offer a new way to look at the data. (You have to start somewhere,
and in a database, you start with the data!)

If you have payrates for jobs, and payrate increases for length of service,
(?and payrate increases for length of service in a specific job) you'd need
a way to track each of those.

Good luck!

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/