A query to tidy up the presentation of rows of data please!?!

[davidrsutton]

Hi,
I have based a table on a query that displays info as below:

ID Field1 Field2 Field3 Field4 Field 5 ...
1 100 200 300 400
1 100 200 300 400
1 100 200 300 400
1 100 200 300 400
2 100 200
2 100 200
3... and so on, the number of lines for an ID appears depending on how many values the row has. Row (ID) 1 has 4 values so has 4 rows.

Is it possible to write some sneaky query to present the above information in such a way:

ID Field
1 100
1 200
1 300
1 400
2 100
2 200
3...

Any help would be be very greatly appreciated!

Many thanks in advance,

Dave.

 

[PHV]

Are the rows for an ID all the sames ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[davidrsutton]

Hi PHV...

Not quite sure what you mean...

Every instance (row) of an individual ID is identical... if thats what you mean!!!

Thanks.

 

[PHV]

Well, strange DB design ...
Anyway, have a look at Union query:
SELECT ID, Field1 AS Field FROM yourTable WHERE Field1 Is Not Null
UNION SELECT ID, Field2 FROM yourTable WHERE Field2 Is Not Null
UNION SELECT ID, Field3 FROM yourTable WHERE Field3 Is Not Null
...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[davidrsutton]

Thanks PHV... looks like that will work thanks!!! I am trying to migrate data from an old database system into a new one and the original source table holds info like so:

ID Field1 Field2 Field3 Field4 Field5...Field9
1 100 200 300 400
2 100 200
.
.
.
2133 100 200

But the destination tables that will be holding this same information needs to be set out like so:

ID Fields
1 100
1 200
1 300
1 400
2 100
2 100
3
.
.
.
2133 300

My tables and rows look weird because I am playing with all kinds of union queries and make table queries to try and get the info to display in the correct format, and going through manually in a spreadsheet would take an absolute age as there are almost 23000 rows in the destination table.

I'll try out your code on my proper table now and let you know.

Thanks again PHV,

Dave.