Query/Report format question from an Access newbie

[Coop]

I have one table with the following fields; date, purchases, sales, bankdata1, bankdata2, etc. I have the date field set as my primary key. The query I have created displays all records with a data > x. The query displays the data with all the field across the columns and displays each date on a row. I would like to switch the row and column so I can create a report that has the date in the columns and the other fields be shown on each row (similiar to a cash flow statement). Any ideas would be greatly appreciated.

 

[Corticus]

Open the Visual Basic editor and insert a module.
Select references, and check Microsoft DAO 3.6

In the module, paste the following code:

Sub Transposer(strSource As String, strTarget As String)

         Dim db As Database
         Dim tdfNewDef As TableDef
         Dim fldNewField As Field
         Dim rstSource As Recordset, rstTarget As Recordset
         Dim i As Integer, j As Integer

         'On Error Resume Next

         DoCmd.DeleteObject acTable, strTarget

 
         Set db = CurrentDb()
         Set rstSource = db.OpenRecordset(strSource)
         rstSource.MoveLast

         ' Create a new table to hold the transposed data.
         ' Create a field for each record in the original table.
         Set tdfNewDef = db.CreateTableDef(strTarget)
         For i = 0 To rstSource.RecordCount - 1
            Set fldNewField = tdfNewDef.CreateField(CStr(i + 1), DB_TEXT)
            tdfNewDef.Fields.Append fldNewField
         Next i
         db.TableDefs.Append tdfNewDef

         ' Open the new table and fill the first field with
         ' field names from the original table.
         Set rstTarget = db.OpenRecordset(strTarget)
         For i = 0 To rstSource.Fields.Count - 1
            rstTarget.AddNew
            rstTarget.Fields(0) = rstSource.Fields(i).Name
            rstTarget.Update
         Next i

         rstSource.MoveFirst
         rstTarget.MoveFirst
         ' Fill each column of the new table
         ' with a record from the original table.
         For j = 0 To rstSource.Fields.Count - 1
            ' Begin with the second field, because the first field
            ' already contains the field names.
            For i = 1 To rstTarget.Fields.Count - 1

               rstTarget.Edit
               rstTarget.Fields(i) = rstSource.Fields(j)
               rstSource.MoveNext
               rstTarget.Update

            Next i
            rstSource.MoveFirst
            rstTarget.MoveNext
         Next j
         rstSource.Close
         rstTarget.Close
         db.Close

End Sub

Now you have a procedure, called Transposer(strSource, strTarget), with two arguments. The first is the name of the source table, the second is what you want to call the transposed table.

Just call the procedure and supply the arguments like:

Sub transp()

    Call Transposer("myTable", "myTableTrans")

End Sub

Replace "myTable" and "myTableTrans" with the source and destination tables, respectively.

For more help, check this out:
http://support.microsoft.com/kb/q182822/

Hope this helps,