same query...multiple tables?

[msj120]

Is there a way to run the same query on multiple tables with the same
column headings?

ex. Many similar tables of student's information exist in a school
district database. Each of the tables is setup with uniform
information: FirstName, LastName, DOB, etc.

Can one query apply for each of the school tables in the school
district database without writing separate queries for each school?

Please help...

 

[Jeff Boyce]

Only going from the description you provided, there's a lack of
normalization for the database you are trying to query.

If you have multiple tables, all with "uniform information", you are missing
one of the advantages of using a relational database.

If you are willing to do your querying as SQL statements in a code module,
you can iterate through a set of tablenames, replacing the tablename in the
SQL's "FROM " clause for each ?school's? table. Or you can build a separate
query for each one.

Or, as suggested above, you could modify the table structure to have ONE
table, with the School name/ID field added. Then, a single query could be
created, with a parameter that prompts the user for the School before
running.

Good luck!

Jeff Boyce
<Access MVP>

 

[Michel Walsh]

Hi,


If a different school is a different table, no... well, if you prefer, a
table name cannot be a "parameter". You can either merge all those tables
into just one (which is probably the best solution):



SELECT f1, f2, f3, "school one" As SchoolName FROM SchoolOne
UNION ALL
SELECT f1, f2, f3, "school two" FROM SchoolTwo
UNION ALL
SELECT f1, f2, f3, "school three" FROM SchoolThree


and then, use a parameter in the where clause, about the school name you
want to get some data; either build dynamically the string of the SQL
statement, at run time,


" SELECT whatever FROM " & tableNameYouWant


and do something with that statement (like supplying it as recordsource to a
report in its onLoad event).


Hoping it may help,
Vanderghast, Access MVP

 

[phobos]

either build dynamically the string of the SQL
statement, at run time,


" SELECT whatever FROM " & tableNameYouWant


and do something with that statement (like supplying it as recordsource to a
report in its onLoad event).

I'm trying to do something like this myself: I'm building the SQL
statement in a text box in a form, and then trying to run that
statement when a button on the form is clicked.

The command I'm using is:

DoCmd.RunSQL ([Forms]![SqlGap]![SqlBlurb])

but it doesn't recognise that as a SQL statement. The SQL is fine -
once constructed, it's exactly the same as what was used in the 'one
query for each table' method - so am I referencing it incorrectly?

 

[Michel Walsh]

Hi,


You can only RUN an SQL statement that is an ACTION (like, INSERT INTO ,
for an append, like a DELETE, or even a DDL like an ALTER TABLE ) but not a
query that returns records (where would they go? )

Try to "bind" the RowSource of a listbox, as example, if the SQL text
supply records on execution


Me.MyListBox.RowSource= Forms!FormName!ControlName


(to the contrary of a sub-form, the only modification you have to made is
eventually to get the number of column display right; with a subform you
have to "touch" the controlSource property of each control, individually,
and get their number right, hiding those that would not be used... much more
work).




Hoping it may help,
Vanderghast, Access MVP

"Michel Walsh" <vanderghast@VirusAreFunnierThanSpam> wrote in message

either build dynamically the string of the SQL
statement, at run time,


" SELECT whatever FROM " & tableNameYouWant


and do something with that statement (like supplying it as recordsource to a
report in its onLoad event).

I'm trying to do something like this myself: I'm building the SQL
statement in a text box in a form, and then trying to run that
statement when a button on the form is clicked.

The command I'm using is:

DoCmd.RunSQL ([Forms]![SqlGap]![SqlBlurb])

but it doesn't recognise that as a SQL statement. The SQL is fine -
once constructed, it's exactly the same as what was used in the 'one
query for each table' method - so am I referencing it incorrectly?