how do i create multiple tables from a master table automatically?

[Panos]

i have a table that includes records for multiple deals (i.e., 100 records
for deal ABC, 125 records for deal XYZ). from that master table, i would
like to create separate tables for each deal. creating a query with the
general criteria "[Enter Deal Name]" is simple in order to extract the right
data. however, how do i go about creating new tables for each deal
automatically? it seems that i am not able to put "[Enter Deal Name]" in the
table name. Any ideas?

 

[John Vinson]

i have a table that includes records for multiple deals (i.e., 100 records
for deal ABC, 125 records for deal XYZ). from that master table, i would
like to create separate tables for each deal. creating a query with the
general criteria "[Enter Deal Name]" is simple in order to extract the right
data. however, how do i go about creating new tables for each deal
automatically? it seems that i am not able to put "[Enter Deal Name]" in the
table name. Any ideas?

Well... one idea is DON'T.

You can create a Query based on this table using

[Enter Deal Name]

on the Criteria line.

You can edit the records in this Query; use it as the recordsource for
a Report; use it as the recordsource for a Form; you can export it to
text, to Excel, to Word as a mail merge; you can even base another
query on it.

Creating 100 redundant tables is neither necessary nor beneficial; it
will bloat your database, take a lot of extra time due to the overhead
in creating tables, and subject you to the risk of update anomalies
(you have the same data stored in the master table and a derived
table, and no protection if one table gets changed and the other
doesn't).

Am I missing something?

Oh... you can create MakeTable queries too. It's just not a good idea.

John W. Vinson[MVP]