I have a table [Table1] and only ten field [Field1],[Field2]... so on with 12 records in each field.
I have a make table query after running which, I get 66 records into a new table. This query basically makes all possible combinations of all 12 records in Field1. I want to do this task for all the 10 fields so I thought of writing a code but it gives me compile errors and too many space continuation.
Dim I As Integer
Dim strSQL As String
For I = 1 To 10
strSQL = “HERE IS THE PROBLEM"
Next...
End
Here is the SQL:
SELECT Table1.Field1 & "," & Table1_1.Field1 & "," & Table1_2.Field1 & "," & Table1_3.Field1 & "," & Table1_4.Field1 & "," & Table1_5.Field1 & "," & Table1_6.Field1 & "," & Table1_7.Field1 & "," & Table1_8.Field1 & "," & Table1_9.Field1 AS Expr1, Table1.Field1+Table1_1.Field1+Table1_2.Field1+Table1_3.Field1+Table1_4.Field1+Table1_5.Field1+Table1_6.Field1+Table1_7.Field1+Table1_8.Field1+Table1_9.Field1 AS Expr2 INTO NewTable
FROM Table1, Table1 AS Table1_1, Table1 AS Table1_2, Table1 AS Table1_3, Table1 AS Table1_4, Table1 AS Table1_5, Table1 AS Table1_6, Table1 AS Table1_7, Table1 AS Table1_8, Table1 AS Table1_9
WHERE (((Table1_1.Field1)>[Table1].[Field1]) AND ((Table1_2.Field1)>[Table1].[Field1] And (Table1_2.Field1)>[Table1_1].[Field1]) AND ((Table1_3.Field1)>[Table1].[Field1] And (Table1_3.Field1)>[Table1_1].[Field1] And (Table1_3.Field1)>[Table1_2].[Field1]) AND ((Table1_4.Field1)>[Table1].[Field1] And (Table1_4.Field1)>[Table1_1].[Field1] And (Table1_4.Field1)>[Table1_2].[Field1] And (Table1_4.Field1)>[Table1_3].[Field1]) AND ((Table1_5.Field1)>[Table1].[Field1] And (Table1_5.Field1)>[Table1_1].[Field1] And (Table1_5.Field1)>[Table1_2].[Field1] And (Table1_5.Field1)>[Table1_3].[Field1] And (Table1_5.Field1)>[Table1_4].[Field1]) AND ((Table1_6.Field1)>[Table1].[Field1] And (Table1_6.Field1)>[Table1_1].[Field1] And (Table1_6.Field1)>[Table1_2].[Field1] And (Table1_6.Field1)>[Table1_3].[Field1] And (Table1_6.Field1)>[Table1_4].[Field1] And (Table1_6.Field1)>[Table1_5].[Field1]) AND ((Table1_7.Field1)>[Table1].[Field1] And (Table1_7.Field1)>[Table1_1].[Field1] And (Table1_7.Field1)>[Table1_2].[Field1] And (Table1_7.Field1)>[Table1_3].[Field1] And (Table1_7.Field1)>[Table1_4].[Field1] And (Table1_7.Field1)>[Table1_5].[Field1] And (Table1_7.Field1)>[Table1_6].[Field1]) AND ((Table1_8.Field1)>[Table1].[Field1] And (Table1_8.Field1)>[Table1_1].[Field1] And (Table1_8.Field1)>[Table1_2].[Field1] And (Table1_8.Field1)>[Table1_3].[Field1] And (Table1_8.Field1)>[Table1_4].[Field1] And (Table1_8.Field1)>[Table1_5].[Field1] And (Table1_8.Field1)>[Table1_6].[Field1] And (Table1_8.Field1)>[Table1_7].[Field1]) AND ((Table1_9.Field1)>[Table1].[Field1] And (Table1_9.Field1)>[Table1_1].[Field1] And (Table1_9.Field1)>[Table1_2].[Field1] And (Table1_9.Field1)>[Table1_3].[Field1] And (Table1_9.Field1)>[Table1_4].[Field1] And (Table1_9.Field1)>[Table1_5].[Field1] And (Table1_9.Field1)>[Table1_6].[Field1] And (Table1_9.Field1)>[Table1_7].[Field1] And (Table1_9.Field1)>[Table1_8].[Field1]));
Maxi
I have a make table query after running which, I get 66 records into a new table. This query basically makes all possible combinations of all 12 records in Field1. I want to do this task for all the 10 fields so I thought of writing a code but it gives me compile errors and too many space continuation.
Dim I As Integer
Dim strSQL As String
For I = 1 To 10
strSQL = “HERE IS THE PROBLEM"
Next...
End
Here is the SQL:
SELECT Table1.Field1 & "," & Table1_1.Field1 & "," & Table1_2.Field1 & "," & Table1_3.Field1 & "," & Table1_4.Field1 & "," & Table1_5.Field1 & "," & Table1_6.Field1 & "," & Table1_7.Field1 & "," & Table1_8.Field1 & "," & Table1_9.Field1 AS Expr1, Table1.Field1+Table1_1.Field1+Table1_2.Field1+Table1_3.Field1+Table1_4.Field1+Table1_5.Field1+Table1_6.Field1+Table1_7.Field1+Table1_8.Field1+Table1_9.Field1 AS Expr2 INTO NewTable
FROM Table1, Table1 AS Table1_1, Table1 AS Table1_2, Table1 AS Table1_3, Table1 AS Table1_4, Table1 AS Table1_5, Table1 AS Table1_6, Table1 AS Table1_7, Table1 AS Table1_8, Table1 AS Table1_9
WHERE (((Table1_1.Field1)>[Table1].[Field1]) AND ((Table1_2.Field1)>[Table1].[Field1] And (Table1_2.Field1)>[Table1_1].[Field1]) AND ((Table1_3.Field1)>[Table1].[Field1] And (Table1_3.Field1)>[Table1_1].[Field1] And (Table1_3.Field1)>[Table1_2].[Field1]) AND ((Table1_4.Field1)>[Table1].[Field1] And (Table1_4.Field1)>[Table1_1].[Field1] And (Table1_4.Field1)>[Table1_2].[Field1] And (Table1_4.Field1)>[Table1_3].[Field1]) AND ((Table1_5.Field1)>[Table1].[Field1] And (Table1_5.Field1)>[Table1_1].[Field1] And (Table1_5.Field1)>[Table1_2].[Field1] And (Table1_5.Field1)>[Table1_3].[Field1] And (Table1_5.Field1)>[Table1_4].[Field1]) AND ((Table1_6.Field1)>[Table1].[Field1] And (Table1_6.Field1)>[Table1_1].[Field1] And (Table1_6.Field1)>[Table1_2].[Field1] And (Table1_6.Field1)>[Table1_3].[Field1] And (Table1_6.Field1)>[Table1_4].[Field1] And (Table1_6.Field1)>[Table1_5].[Field1]) AND ((Table1_7.Field1)>[Table1].[Field1] And (Table1_7.Field1)>[Table1_1].[Field1] And (Table1_7.Field1)>[Table1_2].[Field1] And (Table1_7.Field1)>[Table1_3].[Field1] And (Table1_7.Field1)>[Table1_4].[Field1] And (Table1_7.Field1)>[Table1_5].[Field1] And (Table1_7.Field1)>[Table1_6].[Field1]) AND ((Table1_8.Field1)>[Table1].[Field1] And (Table1_8.Field1)>[Table1_1].[Field1] And (Table1_8.Field1)>[Table1_2].[Field1] And (Table1_8.Field1)>[Table1_3].[Field1] And (Table1_8.Field1)>[Table1_4].[Field1] And (Table1_8.Field1)>[Table1_5].[Field1] And (Table1_8.Field1)>[Table1_6].[Field1] And (Table1_8.Field1)>[Table1_7].[Field1]) AND ((Table1_9.Field1)>[Table1].[Field1] And (Table1_9.Field1)>[Table1_1].[Field1] And (Table1_9.Field1)>[Table1_2].[Field1] And (Table1_9.Field1)>[Table1_3].[Field1] And (Table1_9.Field1)>[Table1_4].[Field1] And (Table1_9.Field1)>[Table1_5].[Field1] And (Table1_9.Field1)>[Table1_6].[Field1] And (Table1_9.Field1)>[Table1_7].[Field1] And (Table1_9.Field1)>[Table1_8].[Field1]));
Maxi
