Transposing Rows and Columns

[Jackie]

I would like to create a new table by moving columns from 1 table into rows for the new table. The fields to be moved store similar information. I have been experimenting with the cross-tab query, but with little success. I do not want to perform a calculation on the data. The Cross-tab query wizard limits output to 3 rows. I plan to use the wizard to establish the query and then use the query design view to add the additional columns to be used as rows.

Thank you in advance for your help.

Jackie

 

[THoey]

Could you give us an example of what your table currently looks like and what you want the results to look like? Terry M. Hoey

 

[Jackie]

Thank you for responding. My record layout for the table looks like this:
topic 1
avg 1
topic 2
avg 2
topic 3
avg 3
topic 4
avg 4
topic 5
avg 5
topic 6
avg 6

I would like the record layout for the new table to look like this:

topic
avg

Thanks for your help.

 

[THoey]

Well, this is not going to be fun. The only way I can think of to do this would be through VBA code. If I understand you correctly, I gather you have more than one record in the source table with data in all those pairs of fields. First, you will have to add another column to your destination table. Some sort of key to tie the records together.

Then, you will need to open both tables using the OpenRecordset. You will need to loop through each record in the source table, and then append six records (loop) to the destination. The following code is NOT going to work, but it will point you in a direction:

Public Function Transposetables()
Dim db1 As Database
Dim rst1 As Recordset
Dim db2 As Database
Dim rst2 As Recordset

Set db1 = CurrentDb
Set rst1 = db.OpenRecordset("select * from MyTable1&quot
Set db2 = CurrentDb
Set rst1 = db.OpenRecordset("select * from MyTable2&quot

rst1.MoveFirst
rst2.MoveFirst

Do While Not rst1.EOF
FOR I = 1 TO 6
rst2.AddNew
rst2!ID = SOME ID NUMBER????
rst2!topic = rst1.topic# <---- Need to figure
out how to
append the value
of your loop
counter here
rst2!avg = rst1.avg# <---- Need to figure
out how to
append the value
of your loop
counter here
rst2.Update
rst1.MoveNext
NEXT
Loop

rst1.close
rst2.close

End Function

Like I said, this will NOT work, but should kick you in the right direction. Or, maybe someone else will have a better idea.

Hope that helps...
Terry M. Hoey

 

[euskadi]

If this is a one time thing, you can do it with some equations.

topic 1
avg 1
topic 2
avg 2
topic 3

Assuming the data starts in cell A1, add these equations
Cell D1 1
Cell E1 =OFFSET(A1,D1-1,0)
Cell F1 =OFFSET(A1,D1,0)

And then fill down, making sure column D increments 1, 2, 3 etc as you fill. This will put your data side by side and then you can copy/paste it where ever you want it.

If this is an ongoing thing, then you might be able to do some array functions to get this to work also, but if it's a 1-time thing then the offset method works just fine.

 

[THoey]

Ummm... Euskadi, since this is an Access forum, I think (?) this is an Access DB problem. Your solution sounds like an Excell solution. Am I right or losing my mind (again...)? Terry M. Hoey

 

[igk]

Hi Jackie,

If this is one time conversion, I think you can create a table with 2 fields topic and avg; and create an append query to move your records.

 

[euskadi]

Yes, my answer was an Excel solution. Thanks for the wake up call. ;-)

 

[euskadi]

Putting on my Access hat, here’s one way to make it work.

Paste the following code into a module.

The first function is a working autoincrement function. Note, the key here is to pass a value to the function so that it calculates for every row. Also, I added a 2 second check to the function so that if you run it once, and then rerun it a minute later the function will start again at 1.

The second function is a switch function that will tag every other record with 0 or 1 (we’ll later pair up 0 & 1 combos).


Option Compare Database

Global IncrementVariable As Integer
Global lastcall As Date

Function IncrementValues(i) As Integer
If Now > (lastcall + 2 / 60 / 60 / 24) Then
lastcall = Now
IncrementVariable = 0
End If
IncrementVariable = IncrementVariable + 1
IncrementValues = IncrementVariable
End Function

Function switchvalue(i) As Integer
If IncrementVariable Mod 2 = 1 Then
switchvalue = 0
Else
switchvalue = 1
End If
End Function


Now that you have this, create a query that makes a new table adding you current 1 column table as well as an autoincrement field and a switch field. Here’s an SQL sample:

SELECT TableRaw.item, IncrementValues([item]) AS Expr1, switchvalue([item]) AS Expr2 INTO tbl1
FROM TableRaw;

This will create data that looks like this:

Item1 1 0
Avg1 2 1
Item2 3 0
Avg2 4 1
Item3 5 0
Item3 6 1
Et cetera

Then create a query pulling your Items (switch = 0), and your Avgs (switch = 0), where your averages are numbered(item # + 1). Here’s a sample query:

SELECT tbl1.item, tbl1_1.item
FROM tbl1, tbl1 AS tbl1_1
WHERE (((tbl1.Expr1)=[tbl1_1]![expr1]-1) AND ((tbl1.Expr2)=0) AND ((tbl1_1.Expr2)=1));

This took my 6-row sample data and correctly matched the records and returned 3 rows.
---
Note, I seems like you have to do a make table in order to keep the increment value static... when I try to do this using queries alone, I get a weird cartesian product that has oddly matched and mis-matched incremental numbers because the increment function calculates every time the record is touched.

 

[JaneC]

Hi Jackie,

I agree with igk.

A make table query, followed by 5 append queries with topic2-6 / avg2-6 going to topic and avg sounds much simpler.

 

[MichaelRed]

Hmmmmmmmmmmm,

Terry has at least given the framework for a reasonable soloution -IF_ you are using DAO as the default recordset type (A.K.A. ver '97 or ver'95). Otherwise (A.K.A. ver 2K or Vre 2.X or prior) this MAY engender some errors in the setting of the recordsets.

Simce I did NOT build a table to try it there may also be other 'issues' with the code, but the folowing SHOULD be a one pass transform.

Public Function Transposetables()

    Dim Dbs As Database
    Dim RstSrc As Recordset
    Dim RstDest As Recordset
        
    Dim Idx As Integer
    Dim Jdx As Integer
    Dim TopicId As String
    Dim AvgId As String
        
    Set Dbs = CurrentDb
    Set RstSrc = db.OpenRecordset("select * from MyTable1")
    Set RstDest = db.OpenRecordset("select * from MyTable2")

    Jdx = 1

    Do While Not Rst1.EOF
        Rst2.AddNew
        For Idx = 1 To 6
            Rst2!ID = Jdx
            TopicId = "Topic" & Trim(str(Idx))
            AvgId = "Avg" & Trim(str(Idx))
            Rst2!topic = Rst1(TopicId)
            Rst2!avg = Rst1(AvgId)
        Next Idx
        Rst2.Update
        Rst1.MoveNext
    Loop

    Rst1.Close
    Rst2.Close
   
End Function

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[MichaelRed]

Another soloution would be to generate a union query:

Select Item1 as Item, Avg1 as Avg From MyTable
Union All
Select Item2 as Item, Avg2 as Avg From MyTable
Union All
Select Item3 as Item, Avg3 as Avg From MyTable
Union All
Select Item4 as Item, Avg4 as Avg From MyTable
Union All
Select Item5 as Item, Avg5 as Avg From MyTable
Union All
Select Item6 as Item, Avg6 as Avg From MyTable;


Now do either a make table or append query (depending on some details of your needs) using the union query as the source and the &quot;NewTable&quot; as the destination.

Of course, in this soloution you would need to replace &quot;MyTable&quot; with the correct name of your table.

Then -again- I did not construct a test table for this exercise, so there are probably some adjustments to made to overcome my hasty posting.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[THoey]

All these possible solutions and no response from Jackie. Maybe it is their weekend...

IGK, we haven't got a repsonse back as to if there are multiple rows of six topics and six avgs or not, but if they do and they do it your way, there will not be any uniqueness with a table with only two fields. You have to create some sort of key that will keep all the records joined. If there is only one row, then your way would probably work (didn't test it either).

MichaelRed, yes, you are correct, I use Access 97, never felt the need to upgrade.

<tapping fingers on desk> Well, come on Jackie. We're all chomping at the bit here... <just kidding> Terry M. Hoey

 

[Jackie]

I'm here! I'm here! Just a little overwhelmed by all of the responses. Thank you, Thank you!

I agree with the most simplest approach using the append query. I acutally implemented this solution. However, my user may be re-using this database. I would like to make it as easy as possible for it to be reused. This is a case where I &quot;inherited&quot; this project after the database was established and populated.

I like the union query solution. I have not worked with union queries, but am going to try that solution first.

Thank you again.

Jackie