When working with data that comes from disparate sources, you're bound to get datasets that aren't ideal for use in an Access database. In these cases, you typically go through some gyrations to transform data into something useful. In today's post, I want to share a technique I use to easily transpose a dataset in Access.
In this example, I have a dataset I've imported from Excel.
As in many Excel-based datasets, the table I've imported is a matrix-style table where some of the column headers are actually data dimensions themselves. I need to transpose this dataset so that I have a field for Month and a single Value field.
The Common Manual Method for Transposing a Dataset
The common way to transpose data is to first create a blank table that will hold your transposed data.
In this example, I've created a new table called TabularDataset.
Next an Append query will append a target month to the appropriate fields.
In this query, I'm focusing in on the [Jan] field. I'm appending the string 'Jan' to the Month column, indentifying the month name.
Then I'm appending the values in the [Jan] field to the [Value] field.
This is what the Append query looks like in SQL view.
The idea is to create an Append query for each month field in your original matrix-style table, each one focusing on a separate month.
Here, you see the first append query appends January data, while the second append query appends February data.
Drawbacks to the Manual Method
First, it's a pain to have to create and maintain all these queries. In this example, we 're talking about creating 12 queries – one for each month.
More importantly, we're essentially hard-coding the queries. Sure, we know there are 12 months and what each month is called. But what if the columns aren't months at all. What if the columns in our original matrix-style table actually represent client names? Worse yet, what if the fields in your matrix-style table are variable? That is, what if you're not sure how many column oriented fields you'll have?
This is where some VBA can help.
Automated Method for Transposing a Dataset.
First, you still create the empty container that will hold your transposed data.
In this example, I've created a new table called TabularDataset.
Next we use this code:
-
Function TransposeTable()
-
Dim rsMySet As DAO.Recordset
-
Dim strSQL As String
-
Dim i As Integer
-
-
'Open the original matrix-style dataset
-
Set rsMySet = CurrentDb.OpenRecordset("MatrixDataset")
-
-
'Start the count at the position number of the first column-oriented field
-
'Remember that Recordsets start at 0
-
For i = 2 To rsMySet.Fields.Count - 1
-
-
'Use the recordset field.name property to build out the SQL string for the current field
-
strSQL = "INSERT INTO TabularDataset ([Line Of Business], [Manager], [Month], [Value]) " & _
-
"SELECT [MatrixDataset].[Line Of Business], [MatrixDataset].[Manager], " & _
-
"'" & rsMySet.Fields(i).Name & "'" & " AS Month, " & _
-
"[" & rsMySet.Fields(i).Name & "] " & _
-
"FROM MatrixDataset;"
-
-
'Execute the SQL string
-
CurrentDb.Execute strSQL
-
-
'Move to the next column-oriented field
-
Next i
-
-
End Function
This code essentially builds and runs the same Append query you would normally build by hand, but for each of the column oriented fields.
Here is what the code is doing:
- First it opens the original matrix-style dataset via a recordset
- Next, it starts counting from the position number of the column-oriented field. In this example, since the [Jan] field is the third field our original matrix-style table, the count starts at 2. Why? Because recordsets count the first field as zero(0). So the recordset will consider the third field to be field number 2 .
- The code then builds out the SQL string for the Append query using the current field count.
- Next, it executes the query
- Finally, it moves to the next field and keeps looping until there are no more fields.
Why not go one step further and build the table in the code?
Bob: You're right!
For stuff like that I'd create two queries in access. The first would select all the data for every month using UNION's. I'd create the query first for one month in the query designer then switch to sql mode and do a bunch of copy/pasting. Once I have all the data in the format I'd want I'd create a second one to select from the first and stuff the lot in the table. No need to mess with vba for something as simple as this.
NixNut: Yes but you're still hard-coding queries. Plus you missed the part about the dynamic number of columns. If you don't know how many column-oriented dimensions you have, then you wouldn't be able to figure out how many Union Queries to build. The VBA takes care of that for you.
You're right. But for your example with months you said:
"First, it's a pain to have to create and maintain all these queries. In this example, we 're talking about creating 12 queries – one for each month."
In my opinion it's not a pain. It's very simple to create the two query's needed for this scenario and there's no maintenance once they exist.
If your input sheets are more dynamic than always having 12 columns for months your vba approach is far superior of course. Luckily my data rarely is that volatile in layout.
This is GREAT!! I've been frustrated for years about a dataset that another dept maintains in a matrix-style spreadsheet. It's almost useless unless it's in a tabular layout. And thanks to this code, it now can be!! And simple. It worked the FIRST time. Thank you!
This can be achived by one append query some thing like this:
INSERT INTO ...
Select [F1],[F2], "Jan", [Jan] FROM MatrixDataSet
UNION ALL
Select [F1],[F2],"Feb",[Feb] FROM MatrixDataSet
UNION ALL
Select [F1],[F2],"Mar",[Mar] FROM MatrixDataSet
......
This is excellent! I searched the VBA world far and wide to find this answer. All others were very complicated and didn't work. Thank you!
Wow, this is exactly what I have been looking for. Do I insert this into a module? Once I do this how do I execute the code.
Thanks
It so works!!! OMG! I wish I knew this 10 years ago, shoot 15 years ago. I still remember using Access 2.0 in '95.
THANK YOU!!!