Creating one query that creates multiple output table

[jlmr]

I have an access database with ONE table. In this database contains a
field name GROUP wich contains a total of 20 different groups; i.e.
group1, group2, ...group20. I need to seperate the data associated with
each of these groups into their own table. So for example, if group1
has 100 records associated with it, then there will be an access table
called group1 with 100 records in it, and so on for the other 19
groups. I am familair with writing a 'MAKE TABLE QUERY' that will
output the data associated with each group INDIVIDUALLY into seperate
table, ONE GROUP AT A TIME..

Does anyone know if there is a way to create ONE query (possibly from
using each of the 20 group Make Table Queries) that would seperate all
the data into 20 different tables with one key stroke rather than have
to run 20 different Make Table Queries?

Thanks,

Jim

 

[Klatuu]

Your table is not currently normalized. What you are contimplating will only
make it worse. Not knowing exactly what you are trying to accomplish, I
can't suggest a correct design. You don't give much information about the
other data items in the table.

Now, if all you want to do is be able to show only one group at a time, then
rather than use multiple tables, use a parameter query to return only the
records in the the table for the selected group.

 

[jlmr]

Your table is not currently normalized. What you are contimplating will only
make it worse. Not knowing exactly what you are trying to accomplish, I
can't suggest a correct design. You don't give much information about the
other data items in the table.

Now, if all you want to do is be able to show only one group at a time, then
rather than use multiple tables, use a parameter query to return only the
records in the the table for the selected group.

Klatuu,

Here is the Access query that will create and output data to one table.

SELECT Tbl1.XYZ INTO e
FROM Tbl1
WHERE (((Tbl1.XYZ)="e"));

Very simple!

Now suppose we want to add on to this query to extract another group
called 'a' from this same table 'Tbl1' and then output it to another
table called 'a'. So that it looks something like this:


SELECT Tbl1.XYZ INTO e
FROM Tbl1
WHERE (((Tbl1.XYZ)="e"));

SELECT Tbl1.XYZ INTO a
FROM Tbl1
WHERE (((Tbl1.XYZ)="a"));


The problem with the above query is syntax... Can someone make a simple
recommendation to change the above syntax so that it will run and hence
two tables will be created, one with data from group 'e' and another
table with data from group 'a'.

 

[Klatuu]

Not gonna happen.
You now have 1 query doing the wrong thing. You will have to either have 20
queries doing the wrong thing or create a parameter for the group field and
run the one query 20 times doing the wrong thing.

You really should reconsider your database design before you proceed. You
are creating a mess for yourself. Having 20 identical tables each storing
data for a different group is one of the most common mistakes in database
design.

So what are you going to do when you want to manipulate data with a form?
Create 20 identical forms each with a different recordsource, one form that
you have to change recordsources on?

I can see the post in a few weeks:
How do I change the table my form is using?

 

[jlmr]

Not gonna happen.
You now have 1 query doing the wrong thing. You will have to either have 20
queries doing the wrong thing or create a parameter for the group field and
run the one query 20 times doing the wrong thing.

You really should reconsider your database design before you proceed. You
are creating a mess for yourself. Having 20 identical tables each storing
data for a different group is one of the most common mistakes in database
design.

So what are you going to do when you want to manipulate data with a form?
Create 20 identical forms each with a different recordsource, one form that
you have to change recordsources on?

I can see the post in a few weeks:
How do I change the table my form is using?

Thanks Klatuu for your recommendations and agree! Unfortunately we have
no other choice but to use this setup and 1 table arrangement. I'm not
sure I'm following the comment about '1 query doing the wrong
thing...'. I'm farily certain there must be a way to run a batch job
with multiple INTO and SELECT statements that will output 20 different
tables with one keystroke of the run button. Jim

The form creation will not be an issue..

 

[Klatuu]

By doing the wrong thing, I mean you database design in not good and this
will make it worse.

If you want to do this by pushing a button, it will take some moderate to
advanced VBA coding or you can create one query for each group and put all
the query calls in a macro, then just run the macro.

 

[jlmr]

Thank you again!

By doing the wrong thing, I mean you database design in not good and this
will make it worse.

If you want to do this by pushing a button, it will take some moderate to
advanced VBA coding or you can create one query for each group and put all
the query calls in a macro, then just run the macro.