Can a Macro create a Query ?

[blackmanofsteel40]

Alright this is the situation. I have about 30 tables with the same
format (columns, rows) but different data and one standard query.
Currently, the standard query links one standard table and one of the
other 29 remaining tables to filter 4 fields. So bascially, I have to
keep manually deleteing the second table and switching it for one of
the other 29 tables until I have ran through them all. Is it possible
to create a macro to switch the tables for me in the query? Or create
29 queries to use the 29 tables? Whichever would be faster.....

Please ask if more clarification is needed. Thanks.

 

[Guest]

So you have 30 tables with the same field names and formats, but just
different records? Or different fields in each table with one common primary
key?

 

[blackmanofsteel40]

So you have 30 tables with the same field names and formats, but just
different records? Or different fields in each table with one common primary
key?






- Show quoted text -

I have 30 tables with the same field names and formats but different
records. I have to create a new table every week for historical
purposes. 30 tables = 30 weeks, so on and so forth. Thanks.

 

[blackmanofsteel40]

I have 30 tables with the same field names and formats but different
records. I have to create a new table every week for historical
purposes. 30 tables = 30 weeks, so on and so forth. Thanks.- Hide quoted text -

- Show quoted text -

Just one more point, Only 1 of the 30 tables has different fields.
That is the table that is linked to the others as I manually insert,
run the query, and then delete each of the other 29 similar tables
until they have all been completed

 

[blackmanofsteel40]

I have 30 tables with the same field names and formats but different
records. I have to create a new table every week for historical
purposes. 30 tables = 30 weeks, so on and so forth. Thanks.- Hide quoted text -

- Show quoted text -

One more point, 1 of the 30 tables has different fields. This is the
table I use in the query to link to the each of the other 29 tables. I
have that table linked to the first of the 29 tables, run the query,
delete it from the query, and then insert the next table until each of
the 29 tables have been run in the query.

 

[BruceM]

No, you do not need to create a new table each week. Use a single table,
and filter as needed. One possibility is to add a calculated query field
based on the date field, something like:
WeekOfYear: DatePart("ww",[YourDateField])
Then group your report on that field. If you want to generate a report for
a given week, set the parameter for the date field to something like:
Between [Start Date] And [End Date]

Or as Steve Schapel suggested in a post once, you could prompt for the week
ending date (let's say it's Friday):
Between [Enter week-ending Friday date]-6 And [Enter week-ending Friday
date]

There are lots of options, but there is no need to generate a bunch of
separate tables. If you must, you can export the query to a spreadsheet so
that you have a separate file for each week, or something like that, but the
data won't be any better organized if you do that.

 

[John W. Vinson]

Alright this is the situation. I have about 30 tables with the same
format (columns, rows) but different data and one standard query.

Then you have an incorrectly designed database, I'd suggest. Why not
ONE table, with one additional field to distinguish what's in the 30
tables!?

Currently, the standard query links one standard table and one of the
other 29 remaining tables to filter 4 fields. So bascially, I have to
keep manually deleteing the second table and switching it for one of
the other 29 tables until I have ran through them all. Is it possible
to create a macro to switch the tables for me in the query? Or create
29 queries to use the 29 tables? Whichever would be faster.....

Please ask if more clarification is needed. Thanks.

A single table would be the cleanest solution; a bad second would be a
UNION query stitching all 30 tables together:

SELECT this, that, theother, whatever
FROM Table1
UNION ALL
SELECT this, that, theother, whatever
FROM Table2
UNION ALL

<etc. through all 30 tables>

in the SQL window of a new query;

a *really* bad third option would be VBA code to run the query thirty
times, changing tablenames in the query's SQL (rather tedious to write
but not hard).


John W. Vinson [MVP]