Create many single tables from a master table

  • Thread starter Thread starter Stan
  • Start date Start date
S

Stan

I have two tables, the first table "Project"is a list of
100 projects. This table contains a unquie project ID and
the Project Address.

The second is a list of 2000 Tenants. This table contains
the Project Id the Tenant ID and the tenant's address.

I want to create a query that will make a one table per
project with all of that project's tenants listed. And
then continue to create tables until all of the project
have there own table.

I can do a macro to repeat the process but I am not sure
how to create the query to change project numbers and
change the name of the new created tables.
 
I have two tables, the first table "Project"is a list of
100 projects. This table contains a unquie project ID and
the Project Address.

The second is a list of 2000 Tenants. This table contains
the Project Id the Tenant ID and the tenant's address.

I want to create a query that will make a one table per
project with all of that project's tenants listed. And
then continue to create tables until all of the project
have there own table.
Click to expand...

ummm...

WHY ON EARTH would you want 100 tables, containing project data in the
tablename?

Sorry, but this is simply a very bad idea. You can very easily create
a Query which will prompt for a project, or read a projectID from a
combo box on an unbound form, and return a recordset containing all
the tenant's for that project. This Query can be used for Forms,
Reports, for export, as the basis for other queries, for mailmerge
into Word, etc. etc.

Could you explain why you feel it is necessary to create these
redundant tables?

Oh, you can *do* it. You'll need some VBA code to loop through the
Project table recordset, and construct the SQL of a MakeTable query
incorporating the project name as (I'd guess) the new tablename.
Macros won't cut it, you'll need code; and if you decide that you
really need to violate the very basis of relational design in this way
I'll hold my nose and help with the code... but I think you should
reconsider this design!
 
I Completely agree with your comment. When I came to this
company they were using 100 seperate Excel spread sheets
for this process which were complicated and the users
screwed up. I converted the function to Access this year
and everyone loves the process, which is easier to use and
allows a broad range of reporting, which was not available
in prior years. We purchased a Budgeting system from a
large software vendor in my industry and need to load this
info into their program. They are the ones requesting one
spread sheet per project. I have questioned them serveral
times on this point, but they say only one can be
processed at a time. I basicly think this is BULL. But the
project must proceed. In the past I have found that this
company has great 1980 technology. Thanks for the input
 
They are the ones requesting one
spread sheet per project. I have questioned them serveral
times on this point, but they say only one can be
processed at a time.
Click to expand...

Fine; give them one query at a time!

You can export a Query to Excel or to a text file just as easily as
exporting a Table. These users might not need to know whether what
they're exporting is a Table or something else - they type a term to
select the "table" they want to process, they click a button, they get
their results for that one "table". The fact that (internally) there
is no separate table should not affect them.

If they do insist on doing it wrong, please post a bit more detail
(e.g. how should these tables be named, what fields go into them) and
I could cobble up some VBA to do these ugly make-tables.
 
Back
Top