Query / Code to Normalize Columns

G

Guest

I have a linked table to an Excel sheet that includes columns for the past 12
months. I need a query (or vba code) that will normalize the data into one
column for month values, and multiple rows for each month.

Starting with a structure like:

State Dec2004 Jan2005 Feb2004 Mar2004
OH 2 3 4
5
MI 20 30 40
50
NY 200 300 400
500

I want to create a structure like:

State Month Widgets
OH Dec2004 2
OH Jan2005 3
OH Feb2005 4
OH Mar2005 5
MI Dec2004 20
MI Jan2005 30
MI Feb2005 40
MI Mar2005 50
NY Dec2004 200
NY Jan2005 300
....

It is easy enough to write a UNION query to do this for a known list of
months, but since the months included in the linked spreadsheet columns
change each month I need a way to identify the months that are in the linked
sheet and to run a union query that creates rows that identify all the date
values that are found in the sheet.

I am a bit rusty with ADO, but I suspect that the answer lies in that
direction. Can someone please guide me towards the best way to do this?

Thanks,

TK
 
D

Duane Hookom

SELECT State, #12/1/2004# as Mth, Dec2004 as Widgets
FROM tblExcel
UNION ALL
SELECT State, #1/1/2005#, Jan2005
FROM tblExcel
UNION ALL
SELECT State, #2/1/2005#, Feb2005
FROM tblExcel
UNION ALL
--etc--;
 
G

Guest

Thanks for the reply.

Your suggestion will work for months Dec2004, Jan2005, Feb2005 and Mar2005,
but I need a query (or process) that will work for any month, based on
whatever columns are found in the excel sheet at any time in the future.

I need to be able to determine the date column names (not values), as the
process runs, and I don't think this can be done with SQL alone. I have done
similar stuff by manipulating SQL strings with vba code, using string
variables to insert the desired names in the SQL code as needed, but what I
need help with is how to determine what the column names in the source table
are so I can use them while piecing together the SQL code with vba.

I am coming to realize that I should have probably posted this on a vba
newsgroup, but if there is a way to do this in SQL that would be my
preference since my vba is rusty at best.

Thanks for your help,

TK
 
D

Duane Hookom

When I have resolved something like this in the past, I have used code to
loop through the fields/columns to create my SQL. It sounds like you have
done this before. I guess if you have some specific coding/looping
questions, they might be more appropriate in the Modules news group but many
of us read through several Access NGs.
 
G

Gary Walter

Actually, you probably want something like:

(tested in Debug window with

fImportToThinTablePreserveField "tblT","tblThin","State"

"tblT" was a linked table to an Excel file as you described.)

'***start code****
Public Sub fImportToThinTablePreserveField(pFromTable As Variant, pToTable
As Variant, Optional pPreserveField As Variant)
On Error GoTo Err_fImportToThinTablePreserveField
Dim rsFrom As DAO.Recordset
Dim rsTo As DAO.Recordset
Dim Response, strMsg As String, varReturn
Dim strSQL As String
Dim strPreserve As String
Dim lngVal As Long
Dim lngRecNum As Long, i As Long


'check that pFromTable is not null nor ZLS
If Len(Trim(pFromTable & "")) > 0 Then
'check that pToTable is not null nor ZLS
If Len(Trim(pToTable & "")) > 0 Then
'continue processing
Else
MsgBox "Please provide name of thin table " _
& "you wish to fill with number data."
GoTo Exit_fImportToThinTablePreserveField
End If
Else
MsgBox "Please provide name of wide table " _
& "with many number fields."
GoTo Exit_fImportToThinTablePreserveField
End If


strMsg = "Will be importing number data from the following table:" _
& vbCrLf & vbCrLf & pFromTable & vbCrLf & vbCrLf _
& "into the following thin table:" _
& vbCrLf & vbCrLf & pToTable
Response = MsgBox(strMsg, vbOKCancel)
If Response = vbCancel Then ' User chose to Cancel
GoTo Exit_fImportToThinTablePreserveField
End If


DoCmd.Hourglass True


'delete pToTable if it exists
If TableExists(CStr(pToTable)) Then
'if it exists, delete it
CurrentDb.Execute "DROP TABLE " & pToTable, dbFailOnError
End If

'recreate pToTable
'do we have a pPreserveField?
If Len(Trim(pPreserveField & "")) > 0 Then
strSQL = "CREATE TABLE " & pToTable & " (ID AUTOINCREMENT, " _
& "FldPreserve TEXT, FldName TEXT, FldValue LONG, " _
& "CONSTRAINT PK_ID PRIMARY KEY (ID ));"
'Debug.Print strSQL
CurrentDb.Execute strSQL, dbFailOnError
Else
'no Preserve field
strSQL = "CREATE TABLE " & pToTable & " (ID AUTOINCREMENT, " _
& "FldName TEXT, FldValue LONG, " _
& "CONSTRAINT PK_ID PRIMARY KEY (ID ));"
'Debug.Print strSQL
CurrentDb.Execute strSQL, dbFailOnError
End If


Set rsFrom = CurrentDb.OpenRecordset(pFromTable, dbOpenDynaset)
'quit if empty table
If rsFrom.EOF = True Then
rsFrom.Close
MsgBox pFromTable & " does not contain any records.", vbCritical
GoTo Exit_fImportToThinTablePreserveField
Else
'continue
End If


Set rsTo = CurrentDb.OpenRecordset(pToTable, dbOpenDynaset)


rsFrom.MoveFirst
lngRecNum = 0
Do While Not rsFrom.EOF
lngRecNum = lngRecNum + 1


'****** update progress display in status bar *****************
varReturn = SysCmd(acSysCmdSetStatus, "Processing Rec # " &
lngRecNum)

'did we have a pPreserveField?
If Len(Trim(pPreserveField & "")) > 0 Then
'get value of preserve field
For i = 0 To rsFrom.Fields.Count - 1
With rsTo
If rsFrom.Fields(i).Name = pPreserveField Then
strPreserve = rsFrom.Fields(i) & ""
Exit For
Else

End If
End With
Next i
'save record in thin table
For i = 0 To rsFrom.Fields.Count - 1
With rsTo
If rsFrom.Fields(i).Name <> pPreserveField Then
.AddNew
!FldPreserve = strPreserve
!FldName = rsFrom.Fields(i).Name
!FldValue = rsFrom.Fields(i)
.Update
Else

End If
End With
Next i

Else
'no Preserve field
For i = 0 To rsFrom.Fields.Count - 1
With rsTo
.AddNew
!FldName = rsFrom.Fields(i).Name
!FldValue = rsFrom.Fields(i)
.Update
End With
Next i
End If

rsFrom.MoveNext
Loop


'clear display in status bar
varReturn = SysCmd(acSysCmdClearStatus)


rsFrom.Close
rsTo.Close


MsgBox "Have successfully imported number data from " & vbCrLf _
& pFromTable & vbCrLf & " into table " & vbCrLf & pToTable & "."


Exit_fImportToThinTablePreserveField:
DoCmd.Hourglass False
Set rsFrom = Nothing
Set rsTo = Nothing
Exit Sub


Err_fImportToThinTablePreserveField:
MsgBox Err.Description
Resume Exit_fImportToThinTablePreserveField
End Sub


Public Function TableExists(strTableName As String) As Boolean
'from Joe Fallon
On Error Resume Next
TableExists = IsObject(CurrentDb.TableDefs(strTableName))
End Function
 
G

Gary Walter

previous would not work if left out preserve field
in sub call.....

Public Sub fImportToThinTablePreserveField(pFromTable As Variant, _
pToTable
As Variant, _
Optional
pPreserveField As Variant)
On Error GoTo Err_fImportToThinTablePreserveField
Dim rsFrom As DAO.Recordset
Dim rsTo As DAO.Recordset
Dim Response, strMsg As String, varReturn
Dim strSQL As String
Dim strPreserve As String
Dim lngVal As Long
Dim lngRecNum As Long, i As Long


'check that pFromTable is not null nor ZLS
If Len(Trim(pFromTable & "")) > 0 Then
'check that pToTable is not null nor ZLS
If Len(Trim(pToTable & "")) > 0 Then
'continue processing
Else
MsgBox "Please provide name of thin table " _
& "you wish to fill with number data."
GoTo Exit_fImportToThinTablePreserveField
End If
Else
MsgBox "Please provide name of wide table " _
& "with many number fields."
GoTo Exit_fImportToThinTablePreserveField
End If


strMsg = "Will be importing number data from the following table:" _
& vbCrLf & vbCrLf & pFromTable & vbCrLf & vbCrLf _
& "into the following thin table:" _
& vbCrLf & vbCrLf & pToTable
Response = MsgBox(strMsg, vbOKCancel)
If Response = vbCancel Then ' User chose to Cancel
GoTo Exit_fImportToThinTablePreserveField
End If


DoCmd.Hourglass True


'delete pToTable if it exists
If TableExists(CStr(pToTable)) Then
'if it exists, delete it
CurrentDb.Execute "DROP TABLE " & pToTable, dbFailOnError
End If

'recreate pToTable
'do we have a pPreserveField?
If Not IsMissing(pPreserveField) Then
strSQL = "CREATE TABLE " & pToTable & " (ID AUTOINCREMENT, " _
& "FldPreserve TEXT, FldName TEXT, FldValue LONG, " _
& "CONSTRAINT PK_ID PRIMARY KEY (ID ));"
'Debug.Print strSQL
CurrentDb.Execute strSQL, dbFailOnError
Else
'no Preserve field
strSQL = "CREATE TABLE " & pToTable & " (ID AUTOINCREMENT, " _
& "FldName TEXT, FldValue TEXT, " _
& "CONSTRAINT PK_ID PRIMARY KEY (ID ));"
'Debug.Print strSQL
CurrentDb.Execute strSQL, dbFailOnError
End If


Set rsFrom = CurrentDb.OpenRecordset(pFromTable, dbOpenDynaset)
'quit if empty table
If rsFrom.EOF = True Then
rsFrom.Close
MsgBox pFromTable & " does not contain any records.", vbCritical
GoTo Exit_fImportToThinTablePreserveField
Else
'continue
End If


Set rsTo = CurrentDb.OpenRecordset(pToTable, dbOpenDynaset)


rsFrom.MoveFirst
lngRecNum = 0
Do While Not rsFrom.EOF
lngRecNum = lngRecNum + 1


'****** update progress display in status bar *****************
varReturn = SysCmd(acSysCmdSetStatus, "Processing Rec # " &
lngRecNum)

'did we have a pPreserveField?
If Not IsMissing(pPreserveField) Then
'get value of preserve field
For i = 0 To rsFrom.Fields.Count - 1
With rsTo
If rsFrom.Fields(i).Name = pPreserveField Then
strPreserve = rsFrom.Fields(i) & ""
Exit For
Else

End If
End With
Next i
'save record in thin table
For i = 0 To rsFrom.Fields.Count - 1
With rsTo
If rsFrom.Fields(i).Name <> pPreserveField Then
.AddNew
!FldPreserve = strPreserve
!FldName = rsFrom.Fields(i).Name
!FldValue = rsFrom.Fields(i)
.Update
Else

End If
End With
Next i

Else
'no Preserve field
For i = 0 To rsFrom.Fields.Count - 1
With rsTo
.AddNew
!FldName = rsFrom.Fields(i).Name
!FldValue = CStr(rsFrom.Fields(i) & "")
.Update
End With
Next i
End If

rsFrom.MoveNext
Loop


'clear display in status bar
varReturn = SysCmd(acSysCmdClearStatus)

'close recordsets
rsFrom.Close
rsTo.Close


MsgBox "Have successfully imported number data from " & vbCrLf _
& pFromTable & vbCrLf & " into table " & vbCrLf & pToTable & "."


Exit_fImportToThinTablePreserveField:
DoCmd.Hourglass False
Set rsFrom = Nothing
Set rsTo = Nothing
Exit Sub


Err_fImportToThinTablePreserveField:
MsgBox Err.Description
Resume Exit_fImportToThinTablePreserveField
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

dynamically choose columns in query 1
Year-to-Date Query 1
convert columns to rows 2
Adding running total to union query 2
Need to transform Access columns to rows 5
Tough PIVOT problem 1
Showig differences in Month to Month data from data in the same ta 8
How To Name Columns With Code 6

Top