Create tables based on field value

[fpmsi]

I have a mailing table that has a country field. I need to seperate these into seperate tables based on the country field.

What would be the easiest way to do this as I do this daily. Right now i create a make table query with all fields and then type my criteria(country), and create a table one by one... With up too 60 countries now this is taking way too much time.

Any ideas?




Thanks
Ryan.

[jzwp11]

Setting up separate tables each based on a country would violate a key principal in relational databases: similar data in 1 table.

You can use a (1) query to isolate the records for a particular country when you need them. Can you provide a little more information as to what you are trying to do/business process you are modeling with your database?

Can you also provide some sample data (with anything sensitive removed, of course)?

[fpmsi]

Basically I run a small direct mail shop and we mail based on country, and the data alwasy comes over together. Lookign for a very simple way to seperate the table by country for import into our postal software.

Not really being used as a database so really no principals lol...

Data: (Basic Mail File)
Name, Address, City, State, Zip, Country

Need to seperate by Country for import into usps software

[jzwp11]

A general approach would be to create a form where you can select the appropriate country and feed that to the criteria clause of a query. Run the query and then transfer the query results to a form that your postal software can handle. But, of course, to do this your database must be structured properly. The database structure has to follow the rules of normalization. This site has an overview. Also, this site has some tutorial for someone just starting out with Access.

[fpmsi]

Thanks but I was able to do what i wanted in excel using vba. Bascially using the country field to create a worksheet for each country value.

Country Value being in field "A":

Sub PagesByDescription()
Dim rRange As Range, rCell As Range
Dim wSheet As Worksheet
Dim wSheetStart As Worksheet
Dim strText As String
    Set wSheetStart = ActiveSheet
    wSheetStart.AutoFilterMode = False
    'Set a range variable to the correct item column
    Set rRange = Range("A1", Range("A65536").End(xlUp))
    
        'Delete any sheet called "UniqueList"
        'Turn off run time errors & delete alert
        On Error Resume Next
        Application.DisplayAlerts = False
        Worksheets("UniqueList").Delete
        
        'Add a sheet called "UniqueList"
        Worksheets.Add().Name = "UniqueList"
        
           'Filter the Set range so only a unique list is created
            With Worksheets("UniqueList")
                rRange.AdvancedFilter xlFilterCopy, , _
                 Worksheets("UniqueList").Range("A1"), True
                 
                 'Set a range variable to the unique list, less the heading.
                 Set rRange = .Range("A2", .Range("A65536").End(xlUp))
            End With
            
            On Error Resume Next
            With wSheetStart
                For Each rCell In rRange
                  strText = rCell
                 .Range("A1").AutoFilter 1, strText
                    Worksheets(strText).Delete
                    'Add a sheet named as content of rCell
                    Worksheets.Add().Name = strText
                    'Copy the visible filtered range _
                    (default of Copy Method) and leave hidden rows
                    .UsedRange.Copy Destination:=ActiveSheet.Range("A1")
                    ActiveSheet.Cells.Columns.AutoFit
                Next rCell
            End With
            
        With wSheetStart
            .AutoFilterMode = False
            .Activate
        End With
        
        On Error GoTo 0
        Application.DisplayAlerts = True
End Sub

[jzwp11]

I'm glad to hear that you found a solution. I know Access has a steeper learning curve compared to Excel, but Access can be much more powerful when you are dealing with large sets of data. Good luck with your project.