I certainly don't feel good about this. But this visual basic script will denormalize your data as you've described. I highly recommend you learn about the relational model. Querying this mess is going to be ugly. I'd prefer to work with your original dataset any day. PS... Always nice to help out a fellow Albertan! Sub Main Set Components = Document.ComponentSet Set query = document.NewQuery("Temp") 'FINDS MAX CMP VALUE query.text = "SELECT MAX([CMP]) as [COUNT] INTO [COUNT] FROM [TABLE]" query.run set component = components.Item("Count") set records = component.recordset set record = records.item(0) Count = record.Data("Count") QUERY.TEXT = "DROP TABLE [COUNT]" QUERY.RUN 'CREATES PIVOT SELECT STATEMENT set component = components.Item("Table") set columns = component.columnset QUERY.TEXT = "SELECT [A].[POLY_ID],[A].[GEOM]" For i = 1 to Columns.Count - 1 set column = columns.Item(i) A = COLUMN.NAME For j = 0 to Count - 1 query.Text = QUERY.TEXT & ",[" & I & "-"& J & "].["&COLUMN.NAME & J+1&"]" next next QUERY.TEXT = QUERY.TEXT & " INTO [RESULTS] FROM(SELECT[1].[POLY_ID],MAX([2].[GEOM (I)]) AS [GEOM]FROM [TABLE] AS [1]INNER JOIN [DRAWING] AS [2] ON [1].[POLY_ID] = [2].[POLY_ID]GROUP BY [1].[POLY_ID]) AS [A]" 'CREATES PIVOT SUBQUERIES For i = 1 to Columns.Count - 1 set column = columns.Item(i) A = COLUMN.NAME For j = 0 to Count - 1 query.Text = QUERY.TEXT & "LEFT JOIN (SELECT [POLY_ID],MAX(["& COLUMN.NAME &"]) AS ["&COLUMN.NAME& J+1&"] FROM [TABLE] WHERE [CMP] = " & J+1 & " GROUP BY [POLY_ID]) AS [" & I & "-"& J & "] ON [A].[POLY_ID] = [" & I & "-"& J & "].[POLY_ID]" next next 'RUNS QUERY AND DELETES QUERY.RUN components.Remove(components.ItemByID(query.ID)) End Sub
|