0

I am a novice at Access. If there is a simple answer, then please accept my apologies up front.

I am working on a database for my office. Each year salary tables are put out that shows the salary for a GSx step x. I am trying to figure out how to update salary information for each employee at the beginning of each year.

I also have the employee table that has a field that shows what grade and a field that shows what step each employee is as well as a field that shows what their annual salary is.

Is there a way for me to update the employee records each year by telling access to go look at the salary table and finding the intersection of a row and column to pull the salary in based on the grade and step fields in the employee table?

Or, in other words, can I get access to go find the intersection of GS 9 row at the step 8 column? Then take that specific salary and "paste" it into the employee record? It would save a LOT of time and effort to be able to get the software to do it vice having a person do it.

Again. sorry if this is a question that should be easy to answer...I am beating my head against the wall right now.

Andreas Kraft
  • 3,754
  • 1
  • 30
  • 39
Sarah
  • 1

1 Answers1

0

So before answering your question, I think the better question is, why are you duplicating data throughout your DBMS? From what you make it sound like, there are two tables:

SALARIES

SALARY_ID | SALARY_GRADE | SALARY_STEP | SALARY_AMOUNT

EMPLOYEES

EMPLOYEE_ID | GRADE | STEP | SALARY

Database normalization rules state that you should try to reduce redundancy as much as possible in your relation/table structure. Additionally, you should also strive to NOT store calculated fields wherever possible, only retaining the primitives.

Therefore, I would remove the salary field from the EMPLOYEES table and restructure it as follows:

EMPLOYEES

EMPLOYEE_ID | GRADE | STEP

And when you need the SALARY_AMOUNT value, simply JOIN the EMPLOYEE on GRADE and STEP. The only additional data that may be required in the EMPLOYEES table is if there would be any additional salary amount (bonus, raise, etc.) that does not apply to all EMPLOYEEs.

Tony D
  • 184
  • 2
  • 12
  • Guess Tony should have added more information about un-pivoting tables. As Sarah seems to have the salary steps as columns and the grads as rows, the amount being in the intersection. https://stackoverflow.com/questions/7255423/how-to-simulate-unpivot-in-access-2010 – y4cine Jun 22 '17 at 14:51
  • Good call - apologies as was hard to visualize without code/formatted text. If you cannot un-pivot as my astute colleague y4cine mentioned, you may need to resort to a built-in application to be able to dynamically read the employee's step, then use that as the field name in your selection query. – Tony D Jun 22 '17 at 17:23