|
|
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
|
|
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" wrote:
[Quoted Text] > 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
>
|
|
On Nov 24, 3:16 pm, Dale Fye <dale....[ at ]nospam.com> wrote:
[Quoted Text] > 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" wrote:
> > 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
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
|
|
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
"Gurvinder" <gsbains[ at ]gmail.com> wrote in message
news:fa74d5b3-2650-4456-8af2-80f8b4927614[ at ]k8g2000yqn.googlegroups.com...
On Nov 24, 3:16 pm, Dale Fye <dale....[ at ]nospam.com> wrote:
[Quoted Text] > 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" wrote:
> > 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
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
|
|
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
On Nov 24, 6:06 pm, "Dale Fye" <dale....[ at ]nospam.com> wrote:
[Quoted Text] > 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
>
> "Gurvinder" <gsba...[ at ]gmail.com> wrote in message
>
> news:fa74d5b3-2650-4456-8af2-80f8b4927614[ at ]k8g2000yqn.googlegroups.com...
> On Nov 24, 3:16 pm, Dale Fye <dale....[ at ]nospam.com> wrote:
>
>
>
> > 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" wrote:
> > > 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
>
> 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
|
|
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
"Gurvinder" <gsbains[ at ]gmail.com> wrote in message
news:badb9a51-9590-4f4b-afb0-3ece451dde0a[ at ]t2g2000yqm.googlegroups.com...
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
On Nov 24, 6:06 pm, "Dale Fye" <dale....[ at ]nospam.com> wrote:
[Quoted Text] > 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
>
> "Gurvinder" <gsba...[ at ]gmail.com> wrote in message
>
> news:fa74d5b3-2650-4456-8af2-80f8b4927614[ at ]k8g2000yqn.googlegroups.com...
> On Nov 24, 3:16 pm, Dale Fye <dale....[ at ]nospam.com> wrote:
>
>
>
> > 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" wrote:
> > > 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
>
> 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
|
|
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
On Nov 24, 8:49 pm, "Dale Fye" <dale....[ at ]nospam.com> wrote:
[Quoted Text] > 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
>
> "Gurvinder" <gsba...[ at ]gmail.com> wrote in message
>
> news:badb9a51-9590-4f4b-afb0-3ece451dde0a[ at ]t2g2000yqm.googlegroups.com...
> 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
>
> On Nov 24, 6:06 pm, "Dale Fye" <dale....[ at ]nospam.com> wrote:
>
> > 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
>
> > "Gurvinder" <gsba...[ at ]gmail.com> wrote in message
>
> >news:fa74d5b3-2650-4456-8af2-80f8b4927614[ at ]k8g2000yqn.googlegroups.com...
> > On Nov 24, 3:16 pm, Dale Fye <dale....[ at ]nospam.com> wrote:
>
> > > 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" wrote:
> > > > 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
>
> > 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
|
|
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
On Nov 24, 8:49 pm, "Dale Fye" <dale....[ at ]nospam.com> wrote:
[Quoted Text] > 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
>
> "Gurvinder" <gsba...[ at ]gmail.com> wrote in message
>
> news:badb9a51-9590-4f4b-afb0-3ece451dde0a[ at ]t2g2000yqm.googlegroups.com...
> 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
>
> On Nov 24, 6:06 pm, "Dale Fye" <dale....[ at ]nospam.com> wrote:
>
> > 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
>
> > "Gurvinder" <gsba...[ at ]gmail.com> wrote in message
>
> >news:fa74d5b3-2650-4456-8af2-80f8b4927614[ at ]k8g2000yqn.googlegroups.com...
> > On Nov 24, 3:16 pm, Dale Fye <dale....[ at ]nospam.com> wrote:
>
> > > 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" wrote:
> > > > 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
>
> > 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
|
|
|