Restructure dataset

[Gannet]

I have a program that exports repeating items on one row and I want to rearrange the data to use it in a report.

I need to take data such as(where '*' denotes no data):

ID_____Item1_____Name1_____Item2_____Name2_____Item3_____Name3
1_______A__________Jeff_________B__________Tedd________C__________Jimy
2_______D__________Lisa_________*__________*___________*__________*
3_______E__________Paul_________F__________Greg________*__________*

and make it look like:

ID Item Name
1 A Jeff
1 B Tedd
1 C Jimy
2 D Lisa
3 E Paul
3 F Greg

The tricky part is there may be 1 item/name combo or 50 so my loop needs to check for the existence of a column.


I'm looking to dynamically build a query and I've started with this code:

if i = 1 then
sql = "SELECT Table1.ID, Table1.Item" & i & " AS Item, Table1.Name" & i & " AS TERM FROM Table1 "
else
sql = sql & " UNION SELECT Table1.ID, Table1.Item" & i & " AS Item, Table1.Name" & i & " AS TERM
FROM Table1 "
end if
sql = sql & ";"

 

[pbaldy]

I believe you could open a recordset on the table or use a TableDef and step through the fields. Searching on TableDef should turn up code to loop through them.

 

[gemma-the-husky]

can you not get a query to sort and filter the dataset for you