Query using selected Table name from Drop down box? Help

[shmoussa]

Hello,

I am fairly new to Access 2007. I would appreciate any help with the
following:

This is what I have: Many tables, each with the same settings, but
different data. A form with a drop down box listing the names of each
of the tables. Also, about 20 queries, right now set up to use only
one of the above tables.

What I want: When I select a table from the drop down box, and click a
button- I want all of the 20 queries to use the table selected from
the drop down box.

For example: If I select table 7-20-2008 from the drop down box and
click Run, all 20 queries will change to use to use the information in
table 7-20-2008. Then, if I select 7-25-2008 from the drop down box
and click run, the 20 queries will use 7-25-2008's information, and so
on and so forth....

If this is at all possible, I would appreciate it if someone can break
it down for me. Thank you!!

 

[KARL DEWEY]

Your best solution is not to use multiple tables but use one with a field for
date. Then in your queries use criteria to select the data you desire.

 

[Klatuu]

Before you get too far lost in the swamp and we have to come pull you out of
the quicksand, why do you have 20 identical tables?

answer goes here:

Response:

Bad Answer. There is never any reason to have 2 identical tables, much less
20. The correct implementation would be one table with an additional field
that identifies the group the record belongs to.

As you are already seeing, multiple indenticaly tables becomes very hard to
deal with and make everything more complex. Before you go any further,
combine all the tables into 1 with the additional field I suggested. To do
this, first create an empty copy of your table schema and add the new field.
Now create an append query based on Table 1 with a calculated field that
contains the value that identifies its data as being in table 1's group and
run it.

Now you only have to do it 19 more times, but here is a trick that will make
it easier. After you have run it the first time, open the query in design
view.

Now add table 2 to the query, but don't remove table 1 yet. Now in the
table row, select table 2 where table 1 was. Now remove table 1, change the
value in the calculated field and run it again.

Sounds like a lot of work, but in the long run, you will be glad you did.

 

[shmoussa]

Thank you for your responses. The reason I had a different table for
each date was because each date has well over 10,000 records. I
thought that would be the best way to handle things. But your way does
sound much better. However, I still do want a form with a drop down
box which lists the date. How can I make it so that the drop down box
does not list the same date 10,000+ times? When I select a date, and
click the VIEW button, I would want queries to run using the date that
I selected from the list. Is this possible? Thanks in advance.

 

[shmoussa]

Thanks again for your help. I figured it out.

 

[Klatuu]

Great.
10,000 records is really nothing. I have some with over half a million.

Just t be sure you got your date thing.

SELECT DISTINCT SomeDateField From SomeTable ORDER BY SomeDateField;

Or, you could just use a text box or a calendar control and let the use pick
a date.