G
Gurvinder
Hi Folks,
I have created a non normalized table from a linked Excel sheet I
created in the following format and need to update a normalized table
based on values in this linked Excel Sheet.
Most code I have found online for converting columns to rows updates
the values directly but in my case I just need to find the
logicalnames with values of 1 and then update the ServersAppTable with
logicalname and name of the app.
TARGET TABLE
ServersApplicationsTable
logicalname
applications
SOURCE TABLE
ApplicationsServersGrid Linked Excel table
NOTE: Only the servers with value of 1 would be updated in the Target
table with App1 or App2 as second field value,
logicalname App1 App2 App3
SRV1 1 0 1
SRV2 0 1 0
SRV3 0 0 0
I managed to enumerate all columns using the code below but am having
issues INSERTING into the normalized table called ServersAppsTable
In the INSERT statement I can use the SELECT query I formulated in the
code below but how need help with how to update the second field which
is the appname?
< CODE >
Private Sub Toggle6_Click()
' Loop through all of the fields in the table.
' Create a subquery for each field not named logicalname or
comments.
Dim tdf As TableDef
Dim FLD As Field
Dim db As Database
Dim strSql As String
Dim tblName As String
Set db = CurrentDb
tblName = "ApplicationsServersGrid"
Set tdf = db.TableDefs(tblName)
For Each FLD In tdf.Fields
If FLD.Name = "logicalname" Or FLD.Name = "comments" Then
'Nothing
Else
' EXAMPLE of using 3M Coder as appname
'WHERE (((ApplicationsServersGrid.[3M Coder])=1));
strSql = " SELECT [" & tblName & "].[logicalname]
FROM [" & tblName & "] WHERE ([" & tblName & "].[" & FLD.Name & "]=1)
"
Debug.Print strSql
db.Execute strSql, dbFailOnError
End If
Next
MsgBox strSql
Debug.Print strSql
db.Execute strSql, dbFailOnError
Set tdf = Nothing
Set db = Nothing
END SUB
< END CODE>
Any help will be appreciated.
Regards,
Gurvinder Singh
I have created a non normalized table from a linked Excel sheet I
created in the following format and need to update a normalized table
based on values in this linked Excel Sheet.
Most code I have found online for converting columns to rows updates
the values directly but in my case I just need to find the
logicalnames with values of 1 and then update the ServersAppTable with
logicalname and name of the app.
TARGET TABLE
ServersApplicationsTable
logicalname
applications
SOURCE TABLE
ApplicationsServersGrid Linked Excel table
NOTE: Only the servers with value of 1 would be updated in the Target
table with App1 or App2 as second field value,
logicalname App1 App2 App3
SRV1 1 0 1
SRV2 0 1 0
SRV3 0 0 0
I managed to enumerate all columns using the code below but am having
issues INSERTING into the normalized table called ServersAppsTable
In the INSERT statement I can use the SELECT query I formulated in the
code below but how need help with how to update the second field which
is the appname?
< CODE >
Private Sub Toggle6_Click()
' Loop through all of the fields in the table.
' Create a subquery for each field not named logicalname or
comments.
Dim tdf As TableDef
Dim FLD As Field
Dim db As Database
Dim strSql As String
Dim tblName As String
Set db = CurrentDb
tblName = "ApplicationsServersGrid"
Set tdf = db.TableDefs(tblName)
For Each FLD In tdf.Fields
If FLD.Name = "logicalname" Or FLD.Name = "comments" Then
'Nothing
Else
' EXAMPLE of using 3M Coder as appname
'WHERE (((ApplicationsServersGrid.[3M Coder])=1));
strSql = " SELECT [" & tblName & "].[logicalname]
FROM [" & tblName & "] WHERE ([" & tblName & "].[" & FLD.Name & "]=1)
"
Debug.Print strSql
db.Execute strSql, dbFailOnError
End If
Next
MsgBox strSql
Debug.Print strSql
db.Execute strSql, dbFailOnError
Set tdf = Nothing
Set db = Nothing
END SUB
< END CODE>
Any help will be appreciated.
Regards,
Gurvinder Singh