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.
