2

After importing data from an excel document I ended up with a table that looks like this (quite similar to a pivot table):

EMPLOYEEID | SKILL1 | SKILL2 | SKILL 3
---------------------------------------
   emp1    |    1   |        |    3
   emp2    |    2   |    3   |    
   emp3    |        |        |    1
   emp4    |        |    2   |    3

And in my database I have another table which stores each level of knowledge of each skill for every employee:

EMPLOYEEID |  SKILLID  | LEVEL_OF_KNOWLEDGE
------------------------------------------
   emp1    |  SKILL1  |         1   
   emp1    |  SKILL3  |         3       
   emp2    |  SKILL1  |         2
   emp2    |  SKILL2  |         3
   emp3    |  SKILL3  |         1   
   emp4    |  SKILL2  |         2
   emp4    |  SKILL3  |         3

My question is, how can I retrieve the data from the first table and store it in the second one? Is it possible using only Access queries or have I to deal with vba?

I have found plenty of examples doing the opposite (pivoting the second table to get the first one) but I haven't managed to find the way to do solve this case.

HansUp
  • 95,961
  • 11
  • 77
  • 135
Clickhere
  • 45
  • 7
  • I Answer this in this post http://stackoverflow.com/questions/7255423/how-to-simulate-unpivot-in-access-2010/42726043#42726043 – Vinicius Sousa Mar 10 '17 at 19:15

1 Answers1

4

Sure

SELECT EmployeeID, "SKILL1" AS SkillID, SKILL1 AS Level_OF_Knowledge WHERE SKILL1 IS NOT NULL
UNION ALL SELECT EmployeeID, "SKILL2" AS SkillID, SKILL2 AS Level_OF_Knowledge  WHERE SKILL2 IS NOT NULL
UNION ALL SELECT EmployeeID, "SKILL3" AS SkillID, SKILL3 AS Level_OF_Knowledge  WHERE SKILL3 IS NOT NULL

*repeat last line for each additional column in your first table

APrough
  • 2,671
  • 3
  • 23
  • 31