Transposing data in Access 2007 Query

Joined
Feb 20, 2013
Messages
1
Reaction score
0
I am trying to transpose rows to fields and fields to row from query to a table. The values under company were calculated. example funds provided in 2002 * int rate = 4 under company 1
Deposit Year company1 company5 … company24
1995-2001 1 2 3
2002 4 5 6
2003 7 8 9

2008 10 11 12
I would like to have the data look like this in a Query
Company 1995-2001 2002 2003 … 2008
1
3
5
...
24
I think this will normalize my data and allow me to search for specific company. I tried to use the cross tab query but it limits me to only 3 rows and the output is not in the correct format.

Public Function TransposeData()

Const cstrInputQuery = "DepositAmt"
Const cstrOutputTable As String = "DepositAmtTransposed"

Dim dbs As DAO.Database
Dim rstInput As DAO.Recordset
Dim rstOutput As DAO.Recordset
Dim Company As Integer

Set dbs = CurrentDb
Set rstInput = dbs.OpenRecordset(cstrInputQuery)
Set rstOutput = dbs.OpenRecordset(cstrOutputTable)

If Not rstInput.EOF Then
For Company = 1 To 12
strInputField = Company

rstInput.MoveFirst
rstOutput.AddNew
rstOutput![CompanyName] = Company

Do
rstOutput(rstInput![Deposit Year]) = rstInput(CStr(Company))
rstInput.MoveNext
Loop Until rstInput.EOF

rstOutput.Update
Next Companys
End If
rstInput.Close
rstOutput.Close
dbs.Close

MsgBox "Data Successfully Transformed"
I received a runtime error 3265 pointing to line rstOutput(rstInput![Inv Year]) = rstInput(CStr(Company))
However, I can’t figure out what’s wrong with that statement
I am new to access and sql. So if someone can assist/point me in the right direction on how to transpose this data in a SQL it would be greatly appreciated. Thanks in advance. K.
 
Last edited:
Not sure if you ever got a solution to your problem but I came across this and saw nobody answered. If your still looking for help let me know.
 
Back
Top