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.
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: