Create Multiple Tables based on Field Value

[dancnbr]

I have a table that I need to divide into several smaller tables based
on the region that each record belongs to (the region information for
each record is in the table). I'd like to create a table for each
region. Ultimately each table will be exported into excel for use by
our sales group. The issue is that there are approximately 50 regions
and I don't want to write a make table query for each since there
isn't data for every region and I don't want to send an empty file to
the sales team responsible.

Is it possible to do this by using a query to find the unique regions
in the data set and then creating a table for each region from that?

I'm looking for a VBA solution as this will be distributed to users
that won't have access to the tables.

 

[Smartin]

I have a table that I need to divide into several smaller tables based
on the region that each record belongs to (the region information for
each record is in the table). I'd like to create a table for each
region. Ultimately each table will be exported into excel for use by
our sales group. The issue is that there are approximately 50 regions
and I don't want to write a make table query for each since there
isn't data for every region and I don't want to send an empty file to
the sales team responsible.

Is it possible to do this by using a query to find the unique regions
in the data set and then creating a table for each region from that?

I'm looking for a VBA solution as this will be distributed to users
that won't have access to the tables.

This is air code; there will be some word wrap and maybe syntax
problems. This will not work if your region values have spaces or other
disallowed characters for a table name, but hopefully you get the idea.
In the SQL statements substitute your source table name for BigTable,
and your region field name for Region.

Sub MakeTablesByRegion
Const SQLDistinctRegions as String = _
"SELECT DISTINCT Region FROM BigTable;"
Dim RSRegions as Recordset
Dim CurrentRegion as String
Dim SQLMakeTable as String

Set RSRegions = DBEngine(0)(0).OpenRecorset(SQLDistinctRegions)
RSRegions.MoveFirst
Do While Not RSRegions.EOF
' build up the make table SQL using the
' current value of RSRegions. The new tables
' will be named "tblRegion_<region name>"
' You may need to fix <region name> so it contains
' permissible characters for a table name.
CurrentRegion = NZ(RSRegions.Fields(0).Value,"")
SQLMakeTable = "SELECT * INTO tblRegion_" & CurrentRegion
SQLMakeTable = SQLMakeTable & " FROM BigTable WHERE BigTable.Region = "
SQLMakeTable = SQLMakeTable & CurrentRegion & ";"
DBEngine(0)(0).Execute(SQLMakeTable)
RSRegions.MoveNext
Loop
Set RSRegions = Nothing
End Sub

 

[dancnbr]

Thanks for your response. My regions are free from spaces and
everything seems fine until it tries to execute the make table query.
Then it gives me a "too few parameters" error. Any ideas?

 

[Smartin]

Thanks for your response. My regions are free from spaces and
everything seems fine until it tries to execute the make table query.
Then it gives me a "too few parameters" error. Any ideas?

Ah, there is an error here, assuming your Region field is text:

SQLMakeTable = SQLMakeTable & CurrentRegion & ";"

should be

SQLMakeTable = SQLMakeTable & "'" & CurrentRegion & "';"

 

[dancnbr]

Thanks so much. I spent days looking at this thing. Now I have my
table broken out into excel files and it cleans up after itself. And
it's very user friendly so that when I distribute it to my users they
can't mess it up.

Thanks again

 

[Smartin]

Thanks so much. I spent days looking at this thing. Now I have my
table broken out into excel files and it cleans up after itself. And
it's very user friendly so that when I distribute it to my users they
can't mess it up.

Thanks again

Glad to be of help. You can probably automate exporting the XLS files as
well, but I don't have any experience with that.

 

[dancnbr]

Glad to be of help. You can probably automate exporting the XLS files as
well, but I don't have any experience with that.

I was able to figure that piece out thanks to your help in dividing
the data.

 

[Smartin]

I was able to figure that piece out thanks to your help in dividing
the data.

Nice job!

 

[Peter]

Wish I was smarter in working with Access queries, modules, etc . . . This is
exactly what I am looking for as well. I deal with numerous single files that
contain records for up to 38 dealers each. For the past number of years I
have been manually creating tables by entering each dealer number . . . Now I
just need to figure out where to place this code and how to execute it . . .
..

Now to roll up my brain sleeves and see if I can figure this out . . .

Peter

 

[Peter]

After some trial and error I finally got the code to work for my master
table. This is great! Saves me a lot of work.

A couple of questions:

1) In the line "SQLMakeTable = "SELECT * INTO tblRegion_" & CurrentRegion" I
want to change the ' tblRegion_ ' so that all I get is the Region name as the
new table name. However, if I remove the tblRegion_ I run into errors. Any
ideas?

2) All the tables I have created I need to export as dbf files using the
same name as the tables. I have never created a routine that exports tables
from Access. Would anyone be willing to point me in the right direction?

3) And finally, a stupid question; I want to create a standard macro to run
the code (to eventually add in my tool bar), but cannot find the proper
access to the code via the macro design tool. In the design mode of the
module I see that I can access the Macro "MakeTablesByRegion" under Tools >
Macro. But I can't seem to access that Macro when designing a standard Macro.
Does that make sense?

Thanks

Peter

 

[John W. Vinson]

After some trial and error I finally got the code to work for my master
table. This is great! Saves me a lot of work.

A couple of questions:

1) In the line "SQLMakeTable = "SELECT * INTO tblRegion_" & CurrentRegion" I
want to change the ' tblRegion_ ' so that all I get is the Region name as the
new table name. However, if I remove the tblRegion_ I run into errors. Any
ideas?

Don't Make Tables at all. It's unnecessary. See below.

2) All the tables I have created I need to export as dbf files using the
same name as the tables. I have never created a routine that exports tables
from Access. Would anyone be willing to point me in the right direction?

You can export *FROM A QUERY* just exactly as easily as from a Table. To do it
manually, use File... Export and select dBase as "files of type". To automate
it, see the online help for TransferDatabase.

3) And finally, a stupid question; I want to create a standard macro to run
the code (to eventually add in my tool bar), but cannot find the proper
access to the code via the macro design tool. In the design mode of the
module I see that I can access the Macro "MakeTablesByRegion" under Tools >
Macro. But I can't seem to access that Macro when designing a standard Macro.
Does that make sense?

Do it in VBA instead (which supports error checking and much easier looping).


John W. Vinson [MVP]

 

[GARCIASREMODELING]

g