Make-Table Query - Creating Tables For Separate States With One Query

[Leviathan via AccessMonster.com]

I have a table with a number of fields of data as well as a state field. I
want to be able to automatically run a query off the master table that
creates separate tables for each state. Ideally, I'd also like the table name
for each of those state segregations to be name of the state. Any help is
appreciated!

 

[John W. Vinson]

I have a table with a number of fields of data as well as a state field. I
want to be able to automatically run a query off the master table that
creates separate tables for each state. Ideally, I'd also like the table name
for each of those state segregations to be name of the state. Any help is
appreciated!

Why?

MakeTable queries are VERY rarely necessary.
Storing the same data in 50 tables as you already have in one table is
redundant, will bloat your database, and is certainly not necessary.

What can you do with an Idaho table that you can't do with a query selecting
ID from the State field?

 

[Rob Wills]

New proceedure in VBA


Dim rs as adodb.recordset

set rs = new adodb.recordset

rs.open "SELECT DISTINCT State FROM [TableNameHere]",
CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly

Do Entil rs.EOF

CurrentDB.Execute "SELECT * INTO [tbl_" & rs("State").value & "] " & _
"FROM [TableNameHere] " & _
"WHERE State = '" & rs("State").value & "'"

rs.MoveNext
Loop

 

[Leviathan via AccessMonster.com]

Agreed, but the client has asked for the data in this format. Presumably, to
provide the tables to separate people.

 

[Leviathan via AccessMonster.com]

Thanks Rob.

New proceedure in VBA

Dim rs as adodb.recordset

set rs = new adodb.recordset

rs.open "SELECT DISTINCT State FROM [TableNameHere]",
CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly

Do Entil rs.EOF

CurrentDB.Execute "SELECT * INTO [tbl_" & rs("State").value & "] " & _
"FROM [TableNameHere] " & _
"WHERE State = '" & rs("State").value & "'"

rs.MoveNext
Loop

I have a table with a number of fields of data as well as a state field. I
want to be able to automatically run a query off the master table that
creates separate tables for each state. Ideally, I'd also like the table name
for each of those state segregations to be name of the state. Any help is
appreciated!

 

[John W. Vinson]

Agreed, but the client has asked for the data in this format. Presumably, to
provide the tables to separate people.

<shrug> Fine. Export the Queries.

That said, you'll need to write VBA code to construct the SQL of your
MakeTable queries. If you have a table of states, you could open a Recordset
based on it, step through the states, and construct a SQL string and execute
it.

To get the correct SQL syntax just build one maketable query in the design
window and look at the SQL. Build up such a string concatenating the state
name.