Trying to make a table

R

Robert_DubYa

I have table that is in a traditional chart format (like the results from a
cross tab query). I need to convert this chart to a table in a database
format. The table is an employee skills table someone created in excel. The
table has 300+ employee numbers (rows) and 190 skills (columns).

Example:

Employee# Skill1 Skill2
1 y n
2 y y
3 n n


Desired result

Emplyee# Skill# Trained
1 Skill1 y
1 Skill2 n
2 Skill1 y
2 Skill2 y
3 Skill1 n
3 Skill2 n

Any help would be apperciated.
 
D

Debra Farnham

Robert

I think you need three tables

tblEmployees
EmployeeID (Primary Key)
EmployeeFirstName
EmployeeLastName
Any other fields you need containing details about the employee

tblSkills
SkillID (Primary Key)
Skill
Any other fields you need to describe the skills (if any)

tblSkilledEmployees
EmployeeID (Foreign key - same data type as EmployeeID in tblEmployees)
SkillID (Foreign key - same data type as SkillID in tblSkills)
dtmDateObtained (in case you want to track when the employee obtained the
skill)

You can than create queries to determine who has what skills, when they
obtained them, who doesn't have a given skill etc. etc.

I'm not sure what your level of experience is with Access but if I have
confused you to death, please post back. There are several super resources
out there which will better assist in explaining what I have set out above.

HTH

Debra
 
R

Robert_DubYa

Debra,

Thank you very much for your reply. I have a good deal of exprience with
Access and relational data bases. I don't think I explained my self well in
my prior post. I have a chart that I am trying to extract data from. By
chart I mean it is a two axis Excel grid that another person created. I am
trying to make this into a standard table that would be one dimensional. A
good way to think of this would be the opposite of a cross tab query.

Thanks again and I really apperciate your reply.

Robert
 
D

Debra Farnham

I completely misunderstood your question Robert (I have wayyyy too many
students who call a spreadsheet a "chart" so I just assumed you were
referring to the same thing.

Debra
 
K

Ken Snell [MVP]

Use a UNION query to normalize the data:

SELECT [Employee#], [Skill1], 1 AS SortOrder
FROM TableName AS T
UNION ALL
SELECT [Employee#], [Skill2], 2 AS SortOrder
FROM TableName AS TT
ORDER BY T.[Employee#], T.SortOrder;


I added the extra field to allow you to get the sorting order that you
desire.
--

Ken Snell
<MS ACCESS MVP>
 
K

Ken Snell [MVP]

Right after I sent the earlier answer, I noted that I'd misread your post in
terms of the output you want. Try this query instead:

SELECT T.[Employee#], "Skill1" AS Skill, T.[Skill1]
FROM TableName AS T
UNION ALL
SELECT TT.[Employee#], "Skill2" AS Skill, TT.[Skill2]
FROM TableName AS TT
ORDER BY T.[Employee#], T.Skill;
 
R

Robert_DubYa

Thanks for the info Ken,

I understand your solution fully. Do you know of a way to automate the
Skill*? The names are all diffrent (no key that I could wildcard off of).
Currently in the real table I have 168 columns. The SQL statement would be
huge.

Thanks again for solution.
Robert

Ken Snell said:
Right after I sent the earlier answer, I noted that I'd misread your post in
terms of the output you want. Try this query instead:

SELECT T.[Employee#], "Skill1" AS Skill, T.[Skill1]
FROM TableName AS T
UNION ALL
SELECT TT.[Employee#], "Skill2" AS Skill, TT.[Skill2]
FROM TableName AS TT
ORDER BY T.[Employee#], T.Skill;

--

Ken Snell
<MS ACCESS MVP>


Robert_DubYa said:
Debra,

Thank you very much for your reply. I have a good deal of exprience with
Access and relational data bases. I don't think I explained my self well
in
my prior post. I have a chart that I am trying to extract data from. By
chart I mean it is a two axis Excel grid that another person created. I
am
trying to make this into a standard table that would be one dimensional.
A
good way to think of this would be the opposite of a cross tab query.

Thanks again and I really apperciate your reply.

Robert
Click to expand...
Click to expand...
 
K

Ken Snell [MVP]

Is this what you seek?

SELECT T.[Employee#], "Skill" & T.[Skill1] AS Skill, T.[Skill1]
FROM TableName AS T
UNION ALL
SELECT TT.[Employee#], "Skill" & TT.[Skill2], TT.[Skill2]
FROM TableName AS TT
ORDER BY T.[Employee#], T.Skill;



Or perhaps I'm not understanding what you want in terms of "automating"? Can
you give me an example of what part is to be automated -- the "Skillx" text?
or the field name for [Skillx]?

--

Ken Snell
<MS ACCESS MVP>


Robert_DubYa said:
Thanks for the info Ken,

I understand your solution fully. Do you know of a way to automate the
Skill*? The names are all diffrent (no key that I could wildcard off of).
Currently in the real table I have 168 columns. The SQL statement would
be
huge.

Thanks again for solution.
Robert

Ken Snell said:
Right after I sent the earlier answer, I noted that I'd misread your post
in
terms of the output you want. Try this query instead:

SELECT T.[Employee#], "Skill1" AS Skill, T.[Skill1]
FROM TableName AS T
UNION ALL
SELECT TT.[Employee#], "Skill2" AS Skill, TT.[Skill2]
FROM TableName AS TT
ORDER BY T.[Employee#], T.Skill;

--

Ken Snell
<MS ACCESS MVP>


Robert_DubYa said:
Debra,

Thank you very much for your reply. I have a good deal of exprience
with
Access and relational data bases. I don't think I explained my self
well
in
my prior post. I have a chart that I am trying to extract data from.
By
chart I mean it is a two axis Excel grid that another person created.
I
am
trying to make this into a standard table that would be one
dimensional.
A
good way to think of this would be the opposite of a cross tab query.

Thanks again and I really apperciate your reply.

Robert

:

Robert

I think you need three tables

tblEmployees
EmployeeID (Primary Key)
EmployeeFirstName
EmployeeLastName
Any other fields you need containing details about the employee

tblSkills
SkillID (Primary Key)
Skill
Any other fields you need to describe the skills (if any)

tblSkilledEmployees
EmployeeID (Foreign key - same data type as EmployeeID in
tblEmployees)
SkillID (Foreign key - same data type as SkillID in tblSkills)
dtmDateObtained (in case you want to track when the employee obtained
the
skill)

You can than create queries to determine who has what skills, when
they
obtained them, who doesn't have a given skill etc. etc.

I'm not sure what your level of experience is with Access but if I
have
confused you to death, please post back. There are several super
resources
out there which will better assist in explaining what I have set out
above.

HTH

Debra


message
I have table that is in a traditional chart format (like the results
from
a
cross tab query). I need to convert this chart to a table in a
database
format. The table is an employee skills table someone created in
excel.
The
table has 300+ employee numbers (rows) and 190 skills (columns).

Example:

Employee# Skill1 Skill2
1 y n
2 y y
3 n n


Desired result

Emplyee# Skill# Trained
1 Skill1 y
1 Skill2 n
2 Skill1 y
2 Skill2 y
3 Skill1 n
3 Skill2 n

Any help would be apperciated.
Click to expand...
Click to expand...
Click to expand...
 
Top