Dividing records

[mac_see]

I have 1000 records in a table with one field and no duplicates. If I create 5 different tables with one field can I export equal records from the main table into all the five tables (i.e. 200 each).

 

[SydneyGeek]

Here's a code-based method with the following assumptions:

1. Source table is called MainData
2. The 5 destination tables are called Table1 thru Table5
3. You want to put the first 200 records in Table1, etc
4. The field name is ValueID in all tables

Adjust the code to suit your setup.
Note: in the code, references to the source table use rst, and to the destination tables use rst2. You will need to use some naming system for the 5 tables like Table1 (no gaps). if necessary, rename the tables before running the code. You can reset their names afterwards.

Function SpreadData()
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim rst2 As DAO.Recordset
    Dim tdf As DAO.TableDef
    Dim tdf2 As DAO.TableDef
    Dim i As Integer, j As Integer, k As Integer
    
    Set dbs = CurrentDb()
    Set tdf = dbs.TableDefs("MainData")
    Set rst = tdf.OpenRecordset
    k = rst.RecordCount
    If k / 5 Mod 5 <> 0 Then
        k = k / 5 + (5 - k / 5 Mod 5) 'round k to the next multiple of 5
    Else
        k = k / 5
    End If
    For i = 1 To 5
        Set tdf2 = dbs.TableDefs("Table" & i)
        Set rst2 = tdf2.OpenRecordset
        For j = 1 To k
            rst2.AddNew
            rst2!valueid = rst!valueid
            rst2.Update
            rst.MoveNext
        Next j
    Next i
End Function

To use, copy this code to a new module in your database. With the curssor anywhere inside the code, press F5

Denis

 

[SydneyGeek]

Updated code -- gets around a calc error that rounded down, not up, and closes the recordsets once finished. If your records are not an exact multiple of 5, the last table will have fewer records than the others.

Function SpreadData()
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim rst2 As DAO.Recordset
    Dim tdf As DAO.TableDef
    Dim tdf2 As DAO.TableDef
    Dim i As Integer, j As Integer, k As Integer, m As Integer
    
    Set dbs = CurrentDb()
    Set tdf = dbs.TableDefs("MainData")
    Set rst = tdf.OpenRecordset
    k = rst.RecordCount
    If k Mod 5 <> 0 Then
        m = k Mod 5
        k = k \ 5 + 1 'round up to cover all records
    Else
        k = k / 5
    End If
    For i = 1 To 5
        Set tdf2 = dbs.TableDefs("Table" & i)
        Set rst2 = tdf2.OpenRecordset
        If i < 5 Then
        For j = 1 To k
            rst2.AddNew
            rst2!valueid = rst!valueid
            rst2.Update
            rst.MoveNext
        Next j
        Else
        For j = 1 To k - (5 - m)
            rst2.AddNew
            rst2!valueid = rst!valueid
            rst2.Update
            rst.MoveNext
        Next j
        End If
    Next i
    
    rst.Close
    rst2.Close
    
End Function

Denis

 

[mac_see]

Thank you so much.