sorting x amount of rows into columns

[steve riley]

I have a table called cabledrum
It has the following columns
DRUM COND1 COND2 COND3 COND4 COND5
Drum1 NEW DRUM: 14.2392m
Drum10 EP-55306R2-2: 15.9850m
Drum11 REV_B CABLE001: 12.3789m REV_B CABLE002: 12.3789m REV_B CABLE003: 12.3789m REV_B CABLE004: 12.3789m
Drum2 EC-09302: 22.7382m C1: 3.4078m C2: 3.4078m C3: 3.4078m
Drum3 CABLE001: 6.3839m
Drum4 EC-55306: 19.1384m
Drum5 EXTRATEST: 14.2744m
Drum6 EP-55306R2-1: 15.9787m
Drum7 EP-55306R1: 14.2744m
Drum8 ANOTHER: 22.6118m EP-09400: 22.2079m AATAKE2: 14.3743m AFTER: 14.3743m EP-55306R3: 14.2744m
Drum9 EP-55306: 12.0687m

I want to sort this so it is in the format as:

DRUM Cable Tag
Drum1 NEW DRUM
Drum10 EP-55306R2-2
Drum11 REV_B CABLE001
Drum11 REV_B CABLE002
Drum11 REV_B CABLE003
Drum11 REV_B CABLE004
Drum2 EC-09302
Drum2 C1
Drum2 C2
Drum2 C3
Drum3 CABLE001
Drum4 EC-55306
Drum5 EXTRATEST
Drum6 EP-55306R2-1
Drum7 EP-55306R1
Drum8 ANOTHER
Drum8 EP-09400
Drum8 AATAKE2
Drum8 AFTER
Drum8 EP-55306R3
Drum9 EP-55306

I have been able to do this using Left function, SQL Union Select and build table queries
I also have it starting automatically with an AutoExec Macro
The problem I have is the CONDx column is not always 4 columns it can be 100+ columns
This requires me to look at how many CONDx columns there are and write a Left function query for each column
Is there a was where I do this without already knowing the number of condx
Thankyou for you help
i have written this out in txt file incase the columns don't line up

[TheSmileyCoder]

What you should have is a one-to-many relationship between your information. That is one primary record (drum) can have many related secondary records (conditions)

tbl_Drum containing the drum(s) with fields such as:

KEY_Drum    (Primary Key, I'd suggest autonumber)
tx_DrumName (Text field)

tbl_DrumCond:

KEY_Cond (Primary Key, I'd suggest autonumber)
ID_Drum  (Foreign key, with ID of drum)
tx_Cond  (Text field, name of the condition)
int_m    (Integer field, with length in m's)

With this setup, you can make acheive what you want. It is also the Best Practice/standard way of using relational databases. You can create queries, where you now include both tables, joined by the drum ID, sorting by the drum name, which would look like what your asking for.