turning column to row

[BrOkEn_iCe]

hi all i want to know how to turning column in table to row
as example i need to turn this


date f1 f2
jan 100 200
feb 300 400


to

name jan feb
f1 100 300
f2 200 400


is there any easy way to do that
the only thing i know that i creat new table and insert the data on it but i want to do that i query that i don't need to run vba code every time i want to see data

is there any thing that i can do

 

[Xproterg]

The absolute easiest way that I can think of doing this is through VBA, and you're going to need to have systematic naming of your columns in order for it to work.

First, you are going to need to name all of your fields the same name, with a number on the end, in ascending order (eg. field1, field2, field3, field4, etc...)

Next...

Create an invisible listbox. (eg. mylist)


If you create a button, have an onclick event that does the following:

' Sets variables
Dim wutwut as String
Dim myrow as String
Dim myfield as String
dim myfielddone as String
 
wutwut = ""
myfield = ""
 
' Populates the listbox with the column that you want to change into a row.
mylist.rowsource = "Select Column_Name from Table_Name;"

' Loops through the listbox and takes all of the values and stores them in one variable.
For i = 0 to mylist.listcount - 1
wutwut = mylist.column(0, i) & ", " & wutwut
myfield = myfield & i & ", " myfield & i 
Next i
 
myrow = wutwut
myfielddone = myfield
 
' Creates the query after the loop
STRcolTOrow = "INSERT INTO Table_Name (" & myfielddone & ") VALUES (" & myrow & ");"
 
' Runs the query
DoCmd.RunSQL STRcolTOrow

This is simple a point in the direction. I'm absolutely sure there is a better way to do this with a recordset, but I'm not sure how to do that.

 

[Beetle]

the only thing i know that i creat new table and insert the data on it

I would advise you not to do that. This can be done by using a function in a global module that concatenates the data from the multiple records into a single string that can be displayed in a query field simply by calling the function in the query. For a downloadable sample application, see this link;