Query and macro

[Guest]

Hi,

I have 50 tables (for each state) labled Tbl_XX, where XX is the state
abbreviation. I also have a query that creates another table using each
state table seperately. Is it possible to write a macro that updates each 50
table using the query because right now I have to go and manually update the
table for all 50, this takes a long time. Does anyone know how I could get
started?

Any help would be greatly appreciated!

 

[CompGeek78]

A little bit more information would be helpful. What is in the state
tables? What does the query look like? What is it that you are trying
to change about the tables? Generally, posting more information is
better than not posting enough.

Keven

 

[Guest]

The query takes each state table

State Table columns: Policy Number; Age; Losses
and sums the losses by age so the new table is
Age; Losses

This is done for each state.

Thanks

 

[CompGeek78]

Sounds like your database is set up pretty poorly (non-normalized).

It would be much easier to have one table with fields, State, Policy
Number, Age, Losses.

When you want to get the data you are looking for, you can just run a
query summing up Losses by age and state.

You could create a macro or VBA code to automate fifty different append
queries, but that would be a lot of effort wasted on poor design. If
you have the capability to redesign, I would suggest that first.

Keven

 

[Guest]

Assuming that I have the records in one file (with fields, State, Policy
Number, Age, Losses) how can I automatically create 50 tables from that one
table.
I don't want to create a query and have to manually type in CT, MA, ... etc
for each state because this takes too long.

Thanks

 

[Guest]

You have a misconception about the purpose of tables. They are meant to hold
raw data, not to group it for viewing. Queries and reports are for that. What
you really need is a report that will group the data into 50 sub-reports,
based on the State ID which should be in every record. No need to enter 50
state ID's - just have the report start a new page every time the ID changes.
See the Sorting & Grouping functionality of Reports.

 

[Guest]

I need them in excel, can a report give me an excel file, I also need the
files to be named "XX_tbl.xls" because macros use them.

The purpose for me is to get 50 seperate tables from one large table (in my
case I have 50 seperate tables - but these can be combined into one table).
They have to be in excel, and their name has to be in certain format. The
tables were created somewhere else and imported into acess - they are too
large for excel.

Thanks

 

[Guest]

I mean that I am taking the 50 tables in access and exporting them into excel.

 

[CompGeek78]

You really don't need 50 tables. You can make 50 queries from the one
table, then you can export the queries to excel.

Keven