Combing multiple columns.

[Toby Gallier]

I have a table i use to store scores for employees.

EMPID Q1 Q2 Q3
1234 3 2 4

Q1,Q2 & Q3 being the separate questions and the numbers (in this case
3,2 & 4) being the actual scores for that question. I am trying to
make a query or report that will display this data combining the
separate questions columns into one column and also display the
Question name (Q1,Q2 & Q3) in its own column as shown bellow

EMPID Question Score
1234 Q1 3
1234 Q2 2
1234 Q3 4
Any help would be appreciated!

[Pachydermitis]

On Nov 14, 2:14 pm, Toby Gallier <[e-mail]:

I have a table i use to store scores for employees.

EMPID Q1 Q2 Q3
1234 3 2 4

Q1,Q2 & Q3 being the separate questions and the numbers (in this case
3,2 & 4) being the actual scores for that question. I am trying to
make a query or report that will display this data combining the
separate questions columns into one column and also display the
Question name (Q1,Q2 & Q3) in its own column as shown bellow

EMPID Question Score
1234 Q1 3
1234 Q2 2
1234 Q3 4

Any help would be appreciated!

Yuck, It may be slow depending on how many records you are looking
at. Assuming your table name is tQuestion
SELECT EmpID, 'Q1' as Question, Q1 as Score FROM tQuestion
UNION
SELECT EmpID, 'Q2' as Question, Q2 as Score FROM tQuestion
UNION
SELECT EmpID, 'Q3' as Question, Q3 as Score FROM tQuestion

Have fun
P

[tina]

suggest you normalize your tables design, then you won't have any problems
getting the result you described. two tables, as

tblEmployees
EmpID (primary key)
FirstName
LastName

tblEmployeeScores
ScoreID (primary key)
EmpID (foreign key from tblEmployees)
Question
Score

tblEmployees
EmpID FirstName Last Name
1 John Doe
2 Jane Smith

tblEmployeeScores
ScoreID EmpID Question Score
1 1 1 10
2 1 2 12
3 2 1 9
4 2 2 15
5 2 3 11

recommend you read up on relational design principles. for more information,
see http://home.att.net/~california.db/tips.html#aTip1.

hth
"Toby Gallier" <az****@gmail.comwrote in message
news:11**********************@q5g2000prf.googlegro ups.com...

I have a table i use to store scores for employees.

EMPID Q1 Q2 Q3
1234 3 2 4

Q1,Q2 & Q3 being the separate questions and the numbers (in this case
3,2 & 4) being the actual scores for that question. I am trying to
make a query or report that will display this data combining the
separate questions columns into one column and also display the
Question name (Q1,Q2 & Q3) in its own column as shown bellow

EMPID Question Score
1234 Q1 3
1234 Q2 2
1234 Q3 4
Any help would be appreciated!