create new tables from query on another table.

[Richard U]

I have a table that contains multiple entries for each country in the world.

I need to break this up to one table for each country. I'm very rusty at this. I know what I have to do, just can't remember how.

I know that I need to have a query to return the country codes...

Select distinct Country_Code 
from Country_Table

which will give me the list of countries I need.

I know I need to wrap a statement around it...

Select into NewTable....

But that's where I get lost. I know I need to iterate down the results of the country codes query, but I've forgotten how to do that.

 

[Norie]

I just used the following code to export the records for each country in a table called 'country' to separate Excel files.

Sub ExportCountries()
Dim db As DAO.Database
Dim rstCountry As DAO.Recordset
Dim qdfExport As DAO.QueryDef
Dim strSQL As String

    Set db = CurrentDb

    Set rstCountry = db.OpenRecordset("SELECT DISTINCT CountryName FROM Country ORDER BY CountryName")

    rstCountry.MoveFirst

    While Not rstCountry.EOF
        strSQL = "SELECT * FROM Country WHERE CountryName = '" & rstCountry.Fields(0).Value & "'"

        Set qdfExport = db.CreateQueryDef("qryExport", strSQL)

        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qryExport", "C:\Test\" & rstCountry.Fields(0).Value, True
        db.QueryDefs.Delete "qryExport"
        rstCountry.MoveNext
    Wend
    
    rstCountry.Close
    
    Set rstCountry = Nothing
    
    Set db = Nothing
    
End Sub

 

[Joe4]

I was on my way out the door earlier, so didn't get a chance to give more detail at the time, but it looks like Norie came to the rescue, so hopefully you have what you need now.