'Un-Crosstab'

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

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" wrote in message
...
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

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