Get Table name for a query from form

[briana.askew]

Ok this is what I'm trying to do:

I have a database with about 30 tables (which are actually linked to Excel
spredsheets). I need to be able to query each table and make a report only
showing certain information for it. I started by doing individual queries for
each table and individual reports for each query. That slowed the program
down ALLOT! and made it crash all the time because it became so large. I
figured out how to get a query for a list of table names using the
MSysObjects table but I need to know how to have a user open a form and
select a table name and have a query run on that table based on the user's
selection. Ex. I have Tables called class A, class B, and class C. In each
table is a list of student names, ages, and...height. I want a user to be
able to open a form and in a drop down menu (or whatever) select class c and
hit a command button that will start a query which will then produce a report
listing only the students in that class over the age of 18 and with there
name and age only appearing. PLEASE PLEASE HELP!!!!!

 

[briana.askew]

Ok what you wrote didn't work exactly how you said ( I probably have it
wrong!) but it did put me on the right track! I can't seem to get the SQL
part of what you wrote working.

I just put in another being a little more specific on exactly what I'm
trying to do and what I've managed so far:
Here's what I have so far:

Every month I pull a bunch of reports from a website. I work for the Army
and it is a list of names of people in different Army branchs. Each branch
has multiple units so I have to pull the list by unit and put them in Excel
spreadsheets so they can be combined for each branch. This leaves me with
about 50 Excel spreedsheets in one workbook each branch made up of about 5
units. I've created a Access Database that links to each Excel spreedsheet in
this workbook and which creates a table for each spreedsheet (I know the
design is strange and not ideal but if you saw the way I have to get this
info you would understand there is no other way). I need the database to
query whatever table I want it to and then give me a report based on that
query (the criteria for the query never changes). I started by creating
queries and reports for every table and that left me with a huge database
that was slow and crashed all the time. I also realized that if I ever leave
this job and the Army comes out with a new branch this program would be
useless cause no one else in my office would know how to create the queries
and the reports to pull that branch. I figured creating a table for a new
branch would be easy and I could teach that to someone here but everything
else would have to be automatically updated. I asked a question here the
other day about how to change the table name in a query based on the users
choice in a form, I got some code that didn't really work (I'm probably doing
something wrong!)but it definitly put me on the right track. I've created one
form that asks the user what branch they want to pull a report on using a
combo box. The recordsource for the combo box is a query on the msysobjects
table so (if understand correctly) all my coworkers would have to do is
create a new table for the new branch and link it to it's Excel spreadsheet
and that would automatically update in the msysobjects table making it an
vailable choice in my combo box. The form then uses the choice made to open a
report. This is workign fine and the report does recognize the table name
from the user's choice; however, it is giving me a report of all the table's
records and only need those that meet the criteria I want. How can I get the
report to look at one field (the field is called "missing") and only display
those records that havea value in them or have a particular value in them? I
can't do a query because I can't figure out how to get the query to change
it's tab;e source based on a user entry in a form... Any suggestions?!!?
Thanks