Combining records

[mac_see]

I have 61 tables. Table names are 20, 21, 22 ....... till 80

Every table has 3 fields Field1, Field2, Field3 (Field names are same for all the 61 tables)

Field1 of all the tables has 65536 records
Field2 of all the tables has 65536 records
Field3 of all the tables has 53684 records

I want to make a master table with only one field and want to import:

All 65536 records from Field1 of 20
Then all 65536 records from Field2 of 20 under Field1 of master
Then all 53684 records from Field3 of 20 under Field1 of master.

After doing this Field1 of master will have 184756 records.

Do the same process for all the remaining 60 table till the time we get 11270116 records in field1 of master.

I can run INSERT INTO SQL statement. But I will have to run it so many times. Can this be done in one go may be using VBA?

Maxi

 

[Norie]

How about this:

Dim I As Integer
Dim J As Integer
Dim strSQL As String
    For I = 20 To 80
        For J = 1 To 3
        
            strSQL = "INSERT INTO Master ( Field1 )" & _
                    " SELECT [" & I & "].Field" & J & _
                    " FROM " & I & ";"
            DoCmd.RunSQL (strSQL)
        Next J
    Next I
End Sub

The only thing is the append warning method will appear 183 times.

Another solution could be the following:

Sub CreateMaster()
Dim db As DAO.Database
Dim rstMaster As DAO.Recordset
Dim rstSource As DAO.Recordset
Dim strSQL As String
Dim I%, J%

    Set db = CurrentDb
    
    Set rstMaster = db.OpenRecordset("Master")
    For I = 20 To 80
        For J = 1 To 3
        
            strSQL = " SELECT [" & I & "].Field" & J & _
                    " FROM " & I & ";"
            
            Set rstSource = db.OpenRecordset(strSQL)
            
            rstSource.MoveFirst
            
            While Not rstSource.EOF
                With rstMaster
                    .AddNew
                    .Fields(1) = rstSource(1)
                    .Update
                End With
            Wend
            
            Set rstSource = Nothing
        Next J
    Next I
    
    Set rstMaster = Nothing
    Set db = Nothing
    
End Sub

 

[mac_see]

I am going to try this when I reach home. This solution will comine all records including duplicates. Can we modify the code to get only unique records or do we have to do this task first and then run another query to eliminate duplicates?

 

[Norie]

Change the SQL statement to only return unique values:

strSQL = " SELECT [" & I & "].Field" & J & _ 
             " FROM " & I & _ 
             " GROUP BY Field" & J & ";"

 

[mac_see]

I tried the second code but I get a compile error on the second line

Dim db As DAO.Database

"User defined type not defined" ???

 

[Norie]

There are two ways round the error.

1 Goto Tools>References... and select the latest version of the Microsoft DAO Object Library.

2 Remove the type part of the variable declaration. eg just have Dim dbs etc

The reason I have them in the code is because of my setup.

If I don't explicitly declare them as DAO objects I get a type mismatch in the code.