Automate make table query

[Guest]

I have a large table with the columns=
State; PolicyNumber; Age; ClaimAmount

I want to create 50 tables and export these into excel.
I have a make table query that takes the large table and sums the claim
amount by age. So for Texas the table would look like

State; Age; SummedClaimAmount

I need to do this for each state and
I want to automate the process because typing in CT run query, then MA run
query, etc... gets very tedious.

Is it possible to create a macro to automate this.

I am thinking it would look like something like
Sub()
Dim StateName(50) as Array

StateName(1) = "CT", ...

For i = 1 to 50
run query on StateName(i)
Ouput table into excel with filename = StateName(i)&"StateTable.xls"
Next i
End Sub

Thanks for your help.

 

[Douglas J. Steele]

Why? As in why do you think you need to create 50 tables?

You can create a parameter query, pass it the state you want and export the
query.

You really should have a table that contains the information about the 50
states, rather than setting a 50 element array in your code.

 

[Guest]

Use one table instead of 50. The State field is enough identity and one
totals query would group on the State field.

 

[Guest]

I have to create 50 tables, can you just answer the Question

Thanks

 

[Smartin]

I have a large table with the columns=
State; PolicyNumber; Age; ClaimAmount

I want to create 50 tables and export these into excel.
I have a make table query that takes the large table and sums the claim
amount by age. So for Texas the table would look like

State; Age; SummedClaimAmount

I need to do this for each state and
I want to automate the process because typing in CT run query, then MA run
query, etc... gets very tedious.

Is it possible to create a macro to automate this.

I am thinking it would look like something like
Sub()
Dim StateName(50) as Array

StateName(1) = "CT", ...

For i = 1 to 50
run query on StateName(i)
Ouput table into excel with filename = StateName(i)&"StateTable.xls"
Next i
End Sub

Thanks for your help.

It would be simpler to have a table on hand with each of the states
rather than setting up an array in code, but either would work. This
stub might give you some ideas. This will be done in a standard module
using VBA:

Populate a recordset or array with the states
Walk through the recordset/array
Execute make table query using the current state
Export the new table to XLS worksheet
loop