Convert Columns to Rows and insert into another table based on valuesin columns

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
 
D

Dale Fye

Why not create a normalization (union) query to get your data into the proper
"vertical" format, then join that to the table you want to update. It might
look like:

qryNormalize:

SELECT LogicalName, "Application1" as AppName
FROM ApplicationsServersGrid
WHERE [App1] = 1
UNION ALL
SELECT LogicalName, "Application2" as AppName
FROM ApplicationsServersGrid
WHERE [App2] = 1
UNION ALL
SELECT LogicalName, "Application3" as AppName
FROM ApplicationsServersGrid
WHERE [App3] = 1

Once you have this query, you should be able to Append, Update, and Delete
records from the ServersApplicationsTable with a couple of relatively simple
queries.

Queries will generally run signifcantly faster than procedural code that
loops through recordset.

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.



Gurvinder said:
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
Click to expand...
 
G

Gurvinder

Why not create a normalization (union) query to get your data into the proper
"vertical" format, then join that to the table you want to update.  It might
look like:

qryNormalize:

SELECT LogicalName, "Application1" as AppName
FROM ApplicationsServersGrid
WHERE [App1] = 1
UNION ALL
SELECT LogicalName, "Application2" as AppName
FROM ApplicationsServersGrid
WHERE [App2] = 1
UNION ALL
SELECT LogicalName, "Application3" as AppName
FROM ApplicationsServersGrid
WHERE [App3] = 1

Once you have this query, you should be able to Append, Update, and Delete
records from the ServersApplicationsTable with a couple of relatively simple
queries.

Queries will generally run signifcantly faster than procedural code that
loops through recordset.

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

Gurvinder said:
Hi Folks,
Click to expand...
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.
Click to expand...
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.
Click to expand...
TARGET TABLE
ServersApplicationsTable
logicalname
applications
Click to expand...
SOURCE TABLE
ApplicationsServersGrid Linked Excel table
Click to expand...
NOTE: Only the servers with value of 1 would be updated in the Target
table with App1 or App2 as second field value,
Click to expand...
logicalname      App1     App2    App3
SRV1                 1            0       1
SRV2                 0            1       0
SRV3                  0           0       0
Click to expand...
I managed to enumerate all columns using the code below but am having
issues INSERTING into the normalized table called ServersAppsTable
Click to expand...
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?
Click to expand...
< 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.
Click to expand...
 Dim tdf As TableDef
 Dim FLD As Field
 Dim db As Database
 Dim strSql As String
 Dim tblName As String
Click to expand...
 Set db = CurrentDb
 tblName = "ApplicationsServersGrid"
Click to expand...
 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));
Click to expand...
                     strSql = " SELECT [" & tblName & "].[logicalname]
FROM [" & tblName & "] WHERE ([" & tblName & "].[" & FLD.Name & "]=1)
"
           Debug.Print strSql
             db.Execute strSql, dbFailOnError
       End If
 Next
Click to expand...
    MsgBox strSql
    Debug.Print strSql
    db.Execute strSql, dbFailOnError
    Set tdf = Nothing
    Set db = Nothing
END SUB
Click to expand...
< END CODE>
Click to expand...
Any help will be appreciated.
Click to expand...
Regards,
Gurvinder Singh
Click to expand...
Click to expand...

Thanks for your reply Dale,

Speed is not an issue as this only needs to be done infrequently to
update the database with new app names.

Having said that the UNION query would create an intermediate table
before it ends up in the final table or am I reading this incorrectly.

Result of the UNION Query

logicalname App1
Srv1 1
Srv2 1
Srv3 1


This way there is no way to distinguish between app names.

Example output of strsql query after I ran in on 5 columns.

SELECT [ApplicationsServersGrid].[logicalname], [3M Coder] FROM
[ApplicationsServersGrid] WHERE ([ApplicationsServersGrid].[3M Coder]
=1) UNION ALL SELECT [ApplicationsServersGrid].[logicalname],
[Active Directory] FROM [ApplicationsServersGrid] WHERE
([ApplicationsServersGrid].[Active Directory]=1) UNION ALL SELECT
[ApplicationsServersGrid].[logicalname], [Ansos] FROM
[ApplicationsServersGrid] WHERE ([ApplicationsServersGrid].[Ansos]=1)
UNION ALL SELECT [ApplicationsServersGrid].[logicalname],
[BlackBerry] FROM [ApplicationsServersGrid] WHERE
([ApplicationsServersGrid].[BlackBerry]=1) UNION ALL SELECT
[ApplicationsServersGrid].[logicalname], [Bluecoat Internet Proxy]
FROM [ApplicationsServersGrid] WHERE ([ApplicationsServersGrid].
[Bluecoat Internet Proxy]=1)

So if there is a way instead of value 1 I can substitute the name of
the app like 3M Coder from the FLD.Name that would solve the issue.

Thanks,
Gurvinder
 
D

Dale Fye

If done correctly, the union query would give you 1 record for each 1 in
your ServersGrid. And each record would contain the servers LogicalName,
and the AppName (assuming that your column headers App1, App2, App3 actually
represent application names). The results, given the data you gave us would
be (lets call this qryNormal):

LogicalName AppName
SRV1 App1
SRV2 App2
SRV1 App3

From here, you would join it to your ServersApplicationsTable and insert
those that are in this query but not in your ServersApplicationTable, or
delete those in your ServersApplicationsTable that are not in qryNormal.

Dale

Why not create a normalization (union) query to get your data into the
proper
"vertical" format, then join that to the table you want to update. It
might
look like:

qryNormalize:

SELECT LogicalName, "Application1" as AppName
FROM ApplicationsServersGrid
WHERE [App1] = 1
UNION ALL
SELECT LogicalName, "Application2" as AppName
FROM ApplicationsServersGrid
WHERE [App2] = 1
UNION ALL
SELECT LogicalName, "Application3" as AppName
FROM ApplicationsServersGrid
WHERE [App3] = 1

Once you have this query, you should be able to Append, Update, and Delete
records from the ServersApplicationsTable with a couple of relatively
simple
queries.

Queries will generally run signifcantly faster than procedural code that
loops through recordset.

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

Gurvinder said:
Hi Folks,
Click to expand...
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.
Click to expand...
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.
Click to expand...
TARGET TABLE
ServersApplicationsTable
logicalname
applications
Click to expand...
SOURCE TABLE
ApplicationsServersGrid Linked Excel table
Click to expand...
NOTE: Only the servers with value of 1 would be updated in the Target
table with App1 or App2 as second field value,
Click to expand...
logicalname App1 App2 App3
SRV1 1 0 1
SRV2 0 1 0
SRV3 0 0 0
Click to expand...
I managed to enumerate all columns using the code below but am having
issues INSERTING into the normalized table called ServersAppsTable
Click to expand...
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?
Click to expand...
< 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.
Click to expand...
Dim tdf As TableDef
Dim FLD As Field
Dim db As Database
Dim strSql As String
Dim tblName As String
Click to expand...
Set db = CurrentDb
tblName = "ApplicationsServersGrid"
Click to expand...
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));
Click to expand...
strSql = " SELECT [" & tblName & "].[logicalname]
FROM [" & tblName & "] WHERE ([" & tblName & "].[" & FLD.Name & "]=1)
"
Debug.Print strSql
db.Execute strSql, dbFailOnError
End If
Next
Click to expand...
MsgBox strSql
Debug.Print strSql
db.Execute strSql, dbFailOnError
Set tdf = Nothing
Set db = Nothing
END SUB
Click to expand...
< END CODE>
Click to expand...
Any help will be appreciated.
Click to expand...
Regards,
Gurvinder Singh
Click to expand...
Click to expand...

Thanks for your reply Dale,

Speed is not an issue as this only needs to be done infrequently to
update the database with new app names.

Having said that the UNION query would create an intermediate table
before it ends up in the final table or am I reading this incorrectly.

Result of the UNION Query

logicalname App1
Srv1 1
Srv2 1
Srv3 1


This way there is no way to distinguish between app names.

Example output of strsql query after I ran in on 5 columns.

SELECT [ApplicationsServersGrid].[logicalname], [3M Coder] FROM
[ApplicationsServersGrid] WHERE ([ApplicationsServersGrid].[3M Coder]
=1) UNION ALL SELECT [ApplicationsServersGrid].[logicalname],
[Active Directory] FROM [ApplicationsServersGrid] WHERE
([ApplicationsServersGrid].[Active Directory]=1) UNION ALL SELECT
[ApplicationsServersGrid].[logicalname], [Ansos] FROM
[ApplicationsServersGrid] WHERE ([ApplicationsServersGrid].[Ansos]=1)
UNION ALL SELECT [ApplicationsServersGrid].[logicalname],
[BlackBerry] FROM [ApplicationsServersGrid] WHERE
([ApplicationsServersGrid].[BlackBerry]=1) UNION ALL SELECT
[ApplicationsServersGrid].[logicalname], [Bluecoat Internet Proxy]
FROM [ApplicationsServersGrid] WHERE ([ApplicationsServersGrid].
[Bluecoat Internet Proxy]=1)

So if there is a way instead of value 1 I can substitute the name of
the app like 3M Coder from the FLD.Name that would solve the issue.

Thanks,
Gurvinder
 
G

Gurvinder

Thanks again Dale,

When I tried using the Union Query the last time using just 2 columns
I still get the 1s in the second column, not the app names and app
names are the columns in the Grid as seen in the query below which I
ran in the Query Window for testing puroses, Also the Server may
contain more than one Apps.

SELECT [ApplicationsServersGrid].[logicalname], [3M Coder] FROM
[ApplicationsServersGrid] WHERE ([ApplicationsServersGrid].[3M Coder]
=1) UNION ALL SELECT [ApplicationsServersGrid].[logicalname],
[Active Directory] FROM [ApplicationsServersGrid] WHERE
([ApplicationsServersGrid].[Active Directory]=1)

What may be a factor is that I am not sure how to display the output
of Select Query in VBA code and instead am using the Query window to
test it.
When It reaches the dbexecute it gives an error message.

strSql = " SELECT [" & tblName & "].[logicalname]
FROM [" & tblName & "] WHERE ([" & tblName & "].[" & FLD.Name & "]=1)
"
Debug.Print strSql
db.Execute strSql, dbFailOnError


Regards,
Gurvinder

If done correctly, the union query would give you 1 record for each 1 in
your ServersGrid.  And each record would contain the servers LogicalName,
and the AppName (assuming that your column headers App1, App2, App3 actually
represent application names).  The results, given the data you gave us would
be (lets call this qryNormal):

LogicalName  AppName
SRV1             App1
SRV2             App2
SRV1             App3

From here, you would join it to your ServersApplicationsTable and insert
those that are in this query but not in your ServersApplicationTable, or
delete those in your ServersApplicationsTable that are not in qryNormal.

Dale


Why not create a normalization (union) query to get your data into the
proper
"vertical" format, then join that to the table you want to update. It
might
look like:
qryNormalize:
Click to expand...

SELECT LogicalName, "Application1" as AppName
FROM ApplicationsServersGrid
WHERE [App1] = 1
UNION ALL
SELECT LogicalName, "Application2" as AppName
FROM ApplicationsServersGrid
WHERE [App2] = 1
UNION ALL
SELECT LogicalName, "Application3" as AppName
FROM ApplicationsServersGrid
WHERE [App3] = 1
Click to expand...
Once you have this query, you should be able to Append, Update, and Delete
records from the ServersApplicationsTable with a couple of relatively
simple
queries.
Click to expand...
Queries will generally run signifcantly faster than procedural code that
loops through recordset.
email address is invalid
Please reply to newsgroup only.
Click to expand...
Gurvinder said:
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
Click to expand...
Click to expand...

Thanks for your reply Dale,

Speed is not an issue as this only needs to be done infrequently to
update the database with new app names.

Having said that the UNION query would create an intermediate table
before it ends up in the final table or am I reading this incorrectly.

Result of the UNION Query

logicalname      App1
Srv1                  1
Srv2                  1
Srv3                  1

This way there is no way to distinguish between app names.

Example output of strsql query after I ran in on 5 columns.

 SELECT [ApplicationsServersGrid].[logicalname], [3M Coder] FROM
[ApplicationsServersGrid] WHERE ([ApplicationsServersGrid].[3M Coder]
=1) UNION ALL   SELECT [ApplicationsServersGrid].[logicalname],
[Active Directory] FROM [ApplicationsServersGrid] WHERE
([ApplicationsServersGrid].[Active Directory]=1) UNION ALL   SELECT
[ApplicationsServersGrid].[logicalname], [Ansos] FROM
[ApplicationsServersGrid] WHERE ([ApplicationsServersGrid].[Ansos]=1)
UNION ALL   SELECT [ApplicationsServersGrid].[logicalname],
[BlackBerry] FROM [ApplicationsServersGrid] WHERE
([ApplicationsServersGrid].[BlackBerry]=1) UNION ALL   SELECT
[ApplicationsServersGrid].[logicalname], [Bluecoat Internet Proxy]
FROM [ApplicationsServersGrid] WHERE ([ApplicationsServersGrid].
[Bluecoat Internet Proxy]=1)

So if there is a way instead of value 1 I can substitute the name of
the app like   3M Coder from the FLD.Name that would solve the issue.

Thanks,
Gurvinder
Click to expand...
 
D

Dale Fye

The query should read (using your 2 columns)

SELECT [ApplicationsServersGrid].[logicalname],
"3M Coder" as AppName
FROM [ApplicationsServersGrid]
WHERE [ApplicationsServersGrid].[3M Coder] =1
UNION ALL
SELECT [ApplicationsServersGrid].[logicalname],
"Active Directory" as AppName
FROM [ApplicationsServersGrid]
WHERE [ApplicationsServersGrid].[Active Directory]=1

If you look closely, you will see that instead of refering to the column as
[3M Coder], I wrapped the application name in quotes.
The reason you were getting the value 1 is that you were using the column
[3M Coder] rather than making the column name into a string value.

Ignore the VBA code for now. When we get this query working properly,
you'll only need 1 or 2 lines of code.

Dale

Thanks again Dale,

When I tried using the Union Query the last time using just 2 columns
I still get the 1s in the second column, not the app names and app
names are the columns in the Grid as seen in the query below which I
ran in the Query Window for testing puroses, Also the Server may
contain more than one Apps.

SELECT [ApplicationsServersGrid].[logicalname], [3M Coder] FROM
[ApplicationsServersGrid] WHERE ([ApplicationsServersGrid].[3M Coder]
=1) UNION ALL SELECT [ApplicationsServersGrid].[logicalname],
[Active Directory] FROM [ApplicationsServersGrid] WHERE
([ApplicationsServersGrid].[Active Directory]=1)

What may be a factor is that I am not sure how to display the output
of Select Query in VBA code and instead am using the Query window to
test it.
When It reaches the dbexecute it gives an error message.

strSql = " SELECT [" & tblName & "].[logicalname]
FROM [" & tblName & "] WHERE ([" & tblName & "].[" & FLD.Name & "]=1)
"
Debug.Print strSql
db.Execute strSql, dbFailOnError


Regards,
Gurvinder

If done correctly, the union query would give you 1 record for each 1 in
your ServersGrid. And each record would contain the servers LogicalName,
and the AppName (assuming that your column headers App1, App2, App3
actually
represent application names). The results, given the data you gave us
would
be (lets call this qryNormal):

LogicalName AppName
SRV1 App1
SRV2 App2
SRV1 App3

From here, you would join it to your ServersApplicationsTable and insert
those that are in this query but not in your ServersApplicationTable, or
delete those in your ServersApplicationsTable that are not in qryNormal.

Dale


Why not create a normalization (union) query to get your data into the
proper
"vertical" format, then join that to the table you want to update. It
might
look like:
qryNormalize:
Click to expand...

SELECT LogicalName, "Application1" as AppName
FROM ApplicationsServersGrid
WHERE [App1] = 1
UNION ALL
SELECT LogicalName, "Application2" as AppName
FROM ApplicationsServersGrid
WHERE [App2] = 1
UNION ALL
SELECT LogicalName, "Application3" as AppName
FROM ApplicationsServersGrid
WHERE [App3] = 1
Click to expand...
Once you have this query, you should be able to Append, Update, and
Delete
records from the ServersApplicationsTable with a couple of relatively
simple
queries.
Click to expand...
Queries will generally run signifcantly faster than procedural code that
loops through recordset.
email address is invalid
Please reply to newsgroup only.
Click to expand...
Gurvinder said:
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
Click to expand...
Click to expand...

Thanks for your reply Dale,

Speed is not an issue as this only needs to be done infrequently to
update the database with new app names.

Having said that the UNION query would create an intermediate table
before it ends up in the final table or am I reading this incorrectly.

Result of the UNION Query

logicalname App1
Srv1 1
Srv2 1
Srv3 1

This way there is no way to distinguish between app names.

Example output of strsql query after I ran in on 5 columns.

SELECT [ApplicationsServersGrid].[logicalname], [3M Coder] FROM
[ApplicationsServersGrid] WHERE ([ApplicationsServersGrid].[3M Coder]
=1) UNION ALL SELECT [ApplicationsServersGrid].[logicalname],
[Active Directory] FROM [ApplicationsServersGrid] WHERE
([ApplicationsServersGrid].[Active Directory]=1) UNION ALL SELECT
[ApplicationsServersGrid].[logicalname], [Ansos] FROM
[ApplicationsServersGrid] WHERE ([ApplicationsServersGrid].[Ansos]=1)
UNION ALL SELECT [ApplicationsServersGrid].[logicalname],
[BlackBerry] FROM [ApplicationsServersGrid] WHERE
([ApplicationsServersGrid].[BlackBerry]=1) UNION ALL SELECT
[ApplicationsServersGrid].[logicalname], [Bluecoat Internet Proxy]
FROM [ApplicationsServersGrid] WHERE ([ApplicationsServersGrid].
[Bluecoat Internet Proxy]=1)

So if there is a way instead of value 1 I can substitute the name of
the app like 3M Coder from the FLD.Name that would solve the issue.

Thanks,
Gurvinder
Click to expand...
 
G

Gurvinder

Thanks a lot Dale.

The UNION query worked.

I ran into some sort of limitation as the complete UNION query with 40
app fields could not be executed so I split it into 2 queries and it
worked.

Thanks and have a happy Thanksgiving :)

Thankfully,
Gurvinder


The query should read (using your 2 columns)

SELECT [ApplicationsServersGrid].[logicalname],
                "3M Coder" as AppName
FROM [ApplicationsServersGrid]
WHERE [ApplicationsServersGrid].[3M Coder] =1
UNION ALL
SELECT [ApplicationsServersGrid].[logicalname],
               "Active Directory" as AppName
FROM [ApplicationsServersGrid]
WHERE [ApplicationsServersGrid].[Active Directory]=1

If you look closely, you will see that instead of refering to the column as
[3M Coder], I wrapped the application name in quotes.
The reason you were getting the value 1 is that you were using the column
[3M Coder] rather than making the column name into a string value.

Ignore the VBA code for now.  When we get this query working properly,
you'll only need 1 or 2 lines of code.

Dale


Thanks again Dale,

When I tried using the Union Query the last time using just 2 columns
I still get the 1s in the second column, not the app names and app
names are the columns in the Grid as seen in the query below which I
ran in the Query Window for testing puroses, Also the Server may
contain more than one Apps.

 SELECT [ApplicationsServersGrid].[logicalname], [3M Coder] FROM
[ApplicationsServersGrid] WHERE ([ApplicationsServersGrid].[3M Coder]
=1) UNION ALL   SELECT [ApplicationsServersGrid].[logicalname],
[Active Directory] FROM [ApplicationsServersGrid] WHERE
([ApplicationsServersGrid].[Active Directory]=1)

What may be a factor is that I am not sure how to display the output
of Select Query in VBA code and instead am using the Query window to
test it.
When It reaches the dbexecute it gives an error message.

                   strSql = " SELECT [" & tblName &"].[logicalname]
FROM [" & tblName & "] WHERE ([" & tblName & "].[" & FLD.Name & "]=1)
"
           Debug.Print strSql
             db.Execute strSql, dbFailOnError

Regards,
Gurvinder

If done correctly, the union query would give you 1 record for each 1 in
your ServersGrid. And each record would contain the servers LogicalName,
and the AppName (assuming that your column headers App1, App2, App3
actually
represent application names). The results, given the data you gave us
would
be (lets call this qryNormal):
Click to expand...
LogicalName AppName
SRV1 App1
SRV2 App2
SRV1 App3
Click to expand...
From here, you would join it to your ServersApplicationsTable and insert
those that are in this query but not in your ServersApplicationTable, or
delete those in your ServersApplicationsTable that are not in qryNormal..

"Gurvinder" <[email protected]> wrote in message
Click to expand...
Why not create a normalization (union) query to get your data into the
proper
"vertical" format, then join that to the table you want to update. It
might
look like:
qryNormalize:
SELECT LogicalName, "Application1" as AppName
FROM ApplicationsServersGrid
WHERE [App1] = 1
UNION ALL
SELECT LogicalName, "Application2" as AppName
FROM ApplicationsServersGrid
WHERE [App2] = 1
UNION ALL
SELECT LogicalName, "Application3" as AppName
FROM ApplicationsServersGrid
WHERE [App3] = 1
Once you have this query, you should be able to Append, Update, and
Delete
records from the ServersApplicationsTable with a couple of relatively
simple
queries.
Queries will generally run signifcantly faster than procedural code that
loops through recordset.
--
HTH
Dale
email address is invalid
Please reply to newsgroup only.
:
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
Click to expand...
Click to expand...
Thanks for your reply Dale,
Click to expand...
Speed is not an issue as this only needs to be done infrequently to
update the database with new app names.
Click to expand...
Having said that the UNION query would create an intermediate table
before it ends up in the final table or am I reading this incorrectly.
Click to expand...
Result of the UNION Query
Click to expand...
logicalname App1
Srv1 1
Srv2 1
Srv3 1
Click to expand...
This way there is no way to distinguish between app names.
Click to expand...
Example output of strsql query after I ran in on 5 columns.
Click to expand...
SELECT [ApplicationsServersGrid].[logicalname], [3M Coder] FROM
[ApplicationsServersGrid] WHERE ([ApplicationsServersGrid].[3M Coder]
=1) UNION ALL SELECT [ApplicationsServersGrid].[logicalname],
[Active Directory] FROM [ApplicationsServersGrid] WHERE
([ApplicationsServersGrid].[Active Directory]=1) UNION ALL SELECT
[ApplicationsServersGrid].[logicalname], [Ansos] FROM
[ApplicationsServersGrid] WHERE ([ApplicationsServersGrid].[Ansos]=1)
UNION ALL SELECT [ApplicationsServersGrid].[logicalname],
[BlackBerry] FROM [ApplicationsServersGrid] WHERE
([ApplicationsServersGrid].[BlackBerry]=1) UNION ALL SELECT
[ApplicationsServersGrid].[logicalname], [Bluecoat Internet Proxy]
FROM [ApplicationsServersGrid] WHERE ([ApplicationsServersGrid].
[Bluecoat Internet Proxy]=1)
Click to expand...
So if there is a way instead of value 1 I can substitute the name of
the app like 3M Coder from the FLD.Name that would solve the issue.
Click to expand...
Thanks,
Gurvinder
Click to expand...
Click to expand...
 
G

Gurvinder

Thanks a lot Dale :)

The query worked with using quotes for field names. I ran into some
limits as the entire query with 40 fields just was not executing so I
split it and it worked like a charm.

Have a happy Thanksgiving.

Thankfully,
Gurvinder

The query should read (using your 2 columns)

SELECT [ApplicationsServersGrid].[logicalname],
                "3M Coder" as AppName
FROM [ApplicationsServersGrid]
WHERE [ApplicationsServersGrid].[3M Coder] =1
UNION ALL
SELECT [ApplicationsServersGrid].[logicalname],
               "Active Directory" as AppName
FROM [ApplicationsServersGrid]
WHERE [ApplicationsServersGrid].[Active Directory]=1

If you look closely, you will see that instead of refering to the column as
[3M Coder], I wrapped the application name in quotes.
The reason you were getting the value 1 is that you were using the column
[3M Coder] rather than making the column name into a string value.

Ignore the VBA code for now.  When we get this query working properly,
you'll only need 1 or 2 lines of code.

Dale


Thanks again Dale,

When I tried using the Union Query the last time using just 2 columns
I still get the 1s in the second column, not the app names and app
names are the columns in the Grid as seen in the query below which I
ran in the Query Window for testing puroses, Also the Server may
contain more than one Apps.

 SELECT [ApplicationsServersGrid].[logicalname], [3M Coder] FROM
[ApplicationsServersGrid] WHERE ([ApplicationsServersGrid].[3M Coder]
=1) UNION ALL   SELECT [ApplicationsServersGrid].[logicalname],
[Active Directory] FROM [ApplicationsServersGrid] WHERE
([ApplicationsServersGrid].[Active Directory]=1)

What may be a factor is that I am not sure how to display the output
of Select Query in VBA code and instead am using the Query window to
test it.
When It reaches the dbexecute it gives an error message.

                   strSql = " SELECT [" & tblName &"].[logicalname]
FROM [" & tblName & "] WHERE ([" & tblName & "].[" & FLD.Name & "]=1)
"
           Debug.Print strSql
             db.Execute strSql, dbFailOnError

Regards,
Gurvinder

If done correctly, the union query would give you 1 record for each 1 in
your ServersGrid. And each record would contain the servers LogicalName,
and the AppName (assuming that your column headers App1, App2, App3
actually
represent application names). The results, given the data you gave us
would
be (lets call this qryNormal):
Click to expand...
LogicalName AppName
SRV1 App1
SRV2 App2
SRV1 App3
Click to expand...
From here, you would join it to your ServersApplicationsTable and insert
those that are in this query but not in your ServersApplicationTable, or
delete those in your ServersApplicationsTable that are not in qryNormal..

"Gurvinder" <[email protected]> wrote in message
Click to expand...
Why not create a normalization (union) query to get your data into the
proper
"vertical" format, then join that to the table you want to update. It
might
look like:
qryNormalize:
SELECT LogicalName, "Application1" as AppName
FROM ApplicationsServersGrid
WHERE [App1] = 1
UNION ALL
SELECT LogicalName, "Application2" as AppName
FROM ApplicationsServersGrid
WHERE [App2] = 1
UNION ALL
SELECT LogicalName, "Application3" as AppName
FROM ApplicationsServersGrid
WHERE [App3] = 1
Once you have this query, you should be able to Append, Update, and
Delete
records from the ServersApplicationsTable with a couple of relatively
simple
queries.
Queries will generally run signifcantly faster than procedural code that
loops through recordset.
--
HTH
Dale
email address is invalid
Please reply to newsgroup only.
:
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
Click to expand...
Click to expand...
Thanks for your reply Dale,
Click to expand...
Speed is not an issue as this only needs to be done infrequently to
update the database with new app names.
Click to expand...
Having said that the UNION query would create an intermediate table
before it ends up in the final table or am I reading this incorrectly.
Click to expand...
Result of the UNION Query
Click to expand...
logicalname App1
Srv1 1
Srv2 1
Srv3 1
Click to expand...
This way there is no way to distinguish between app names.
Click to expand...
Example output of strsql query after I ran in on 5 columns.
Click to expand...
SELECT [ApplicationsServersGrid].[logicalname], [3M Coder] FROM
[ApplicationsServersGrid] WHERE ([ApplicationsServersGrid].[3M Coder]
=1) UNION ALL SELECT [ApplicationsServersGrid].[logicalname],
[Active Directory] FROM [ApplicationsServersGrid] WHERE
([ApplicationsServersGrid].[Active Directory]=1) UNION ALL SELECT
[ApplicationsServersGrid].[logicalname], [Ansos] FROM
[ApplicationsServersGrid] WHERE ([ApplicationsServersGrid].[Ansos]=1)
UNION ALL SELECT [ApplicationsServersGrid].[logicalname],
[BlackBerry] FROM [ApplicationsServersGrid] WHERE
([ApplicationsServersGrid].[BlackBerry]=1) UNION ALL SELECT
[ApplicationsServersGrid].[logicalname], [Bluecoat Internet Proxy]
FROM [ApplicationsServersGrid] WHERE ([ApplicationsServersGrid].
[Bluecoat Internet Proxy]=1)
Click to expand...
So if there is a way instead of value 1 I can substitute the name of
the app like 3M Coder from the FLD.Name that would solve the issue.
Click to expand...
Thanks,
Gurvinder
Click to expand...
Click to expand...
 

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

insert values into new table 3
Adding table into existing access database via ADO 0
populate listbox form database table 0
Insert into table not working 4
copy data to table 0
Insert Into problem 1
Create a SubForm on a Tab Control 0
Code will work on one table and form but not on another? 19

Top