By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,255 Members | 2,640 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,255 IT Pros & Developers. It's quick & easy.

Data Transform from columns to rows

P: n/a
I have a table that I need to import into access. The table is monthly
data where the month is the column header. I need to transform that
data so that location code, month and number are the only columns.

EX:

Current format

Location
CODE Jan FEB March ...
1 10 12 13
2 7 9 8
-----------------------------------------------------------
Needs to look like

Location
CODE MON #
1 JAN 10
1 FEB 12
1 MAR 13
2 JAN 7
etc.

Jul 25 '06 #1
Share this Question
Share on Google+
3 Replies


P: n/a
ga*******@gmail.com wrote:
I have a table that I need to import into access. The table is monthly
data where the month is the column header. I need to transform that
data so that location code, month and number are the only columns.

EX:

Current format

Location
CODE Jan FEB March ...
1 10 12 13
2 7 9 8
-----------------------------------------------------------
Needs to look like

Location
CODE MON #
1 JAN 10
1 FEB 12
1 MAR 13
2 JAN 7
etc.
I expect the proponents of and experts in pivot tables will have a much
more efficient answer. I can offer only code:

Dim d As DAO.Database
Dim z As Long
Dim s(0 To 1) As String
Set d = CurrentDb()
s(0) = "SELECT Code, " _
& "'MonthName' AS Mon, " _
& "0 AS Occurrences " _
& "INTO NewTable " _
& "FROM Table3 WHERE 1 = 2"
d.Execute s(0), dbFailOnError
s(0) = "INSERT INTO NewTable SELECT Code, " _
& "'MonthName' AS Mon, " _
& "MonthName AS Occurrences " _
& "FROM Table3"
For z = 1 To 12
s(1) = Replace(s(0), "MonthName", MonthName(z, True))
d.Execute s(1), dbFailOnError
Next z
Set d = Nothing

This is based on the notion that there is only one code 1, code 2 etc.
If there are multiple codes then the sql must be revised to include
sums and groups. It also requires Access 2000 or newer.

Jul 25 '06 #2

P: n/a
Why would you have code lying around to perform such a pointless task?

Look into using arrays to solve this problem, unless A2K3 or better has
a function that does it.

Lyle Fairfield wrote:
ga*******@gmail.com wrote:
I have a table that I need to import into access. The table is monthly
data where the month is the column header. I need to transform that
data so that location code, month and number are the only columns.

EX:

Current format

Location
CODE Jan FEB March ...
1 10 12 13
2 7 9 8
-----------------------------------------------------------
Needs to look like

Location
CODE MON #
1 JAN 10
1 FEB 12
1 MAR 13
2 JAN 7
etc.

I expect the proponents of and experts in pivot tables will have a much
more efficient answer. I can offer only code:

Dim d As DAO.Database
Dim z As Long
Dim s(0 To 1) As String
Set d = CurrentDb()
s(0) = "SELECT Code, " _
& "'MonthName' AS Mon, " _
& "0 AS Occurrences " _
& "INTO NewTable " _
& "FROM Table3 WHERE 1 = 2"
d.Execute s(0), dbFailOnError
s(0) = "INSERT INTO NewTable SELECT Code, " _
& "'MonthName' AS Mon, " _
& "MonthName AS Occurrences " _
& "FROM Table3"
For z = 1 To 12
s(1) = Replace(s(0), "MonthName", MonthName(z, True))
d.Execute s(1), dbFailOnError
Next z
Set d = Nothing

This is based on the notion that there is only one code 1, code 2 etc.
If there are multiple codes then the sql must be revised to include
sums and groups. It also requires Access 2000 or newer.
Jul 25 '06 #3

P: n/a
ManningFan wrote:
Why would you have code lying around to perform such a pointless task?
I didn't. Why would you think I had?

Jul 25 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.