'Un-Crosstab'

[colin ashley]

I have a table that shows the jobs that people carry out. The column
headings are person ID's, and the row headings are job ID's. An 'x' at an
intersection shows that a person can perform a job. All other intersections
are blank.
I would like to transform this data into a linear relationship
table . To 'un-crosstab' it, if you like!

This is how the existing table looks:

Person ID's
Job ID's 1001 1002 1003
1001 x x
1002 x
1003 x

I need to derive a table that looks like this:
Job ID Person ID
1001 1001
1001 1003
1002 1002
1003 1001

Any ideas or help would be much appreciated!

colin

 

[Michel Walsh]

Hi,


SELECT JobID, 1001 AS PersonID FROM myTable WHERE NOT [1001] Is Null
UNION ALL
SELECT JobID, 1002 FROM myTable WHERE NOT [1002] Is Null
UNION ALL
SELECT JobID, 1003 FROM myTable WHERE NOT [1003] Is Null




Hoping it may help,
Vanderghast, Access MVP

 

[colin ashley]

Thank you very much Michel.Your solution worked perfectly.

The actual table contains about 40 columns, so I've got a spot of query
writing to do!

thanks again!

Colin