Is it Possible to make a table from a query without a Make Table Q

[Guest]

Is it possible to make a table with a query as the record source, without
using a Make Table Query. The reason that I as is that I have a rather
large database that I use to create tables for each day of the month based on
activity recieved from a larger database. I use one Pass-Through query as
the source for 31 Make Table queries. The reason I have done this is because
I need to segregate the activity by date so that I can produce daily averages
over the entire month. The reason that I have had to break each one per day
is the Pass Through query is huge (returning 7,000,000 + records) and that it
takes entirely too long run one all encompassing Make Table query. Is there
a way to write one query, with variable date ranges, that would produce a
seperate named table based on the date from a From? For example the same
query would produce table "Day 1" on the first day of the month and "Day 2"
on the second and so on for the entire month. That way I do not need to keep
31 different make table queries that do the same thing just creating tables
with different names. Thanks in advance for your help.

 

[Guest]

Have you considered filtering your data by date with your pass-through query,
then using a single make query to move the results into your database? You
should be able to make your pass-through pull data based on the current day,
and accomplish all you need ( even faster than you were ) with two queries
instead of 32.

 

[Guest]

Thanks for the reply. I do use a date range on the Pass Through and then a
make table query based on the results. The problem being the table names.
These are all processes that I have automated using code behind a form. The
user enters the file date that they want to run. The date is passed as a
range to the Pass Through query. And then the appropriate Make Table query
is run based on the day. My problem is the naming of the tables that the
Make Table queries create. I have to have a seperate query for each
different table, in this case 31 queries for the 31 days of the month. It
seems like there should be a better way I am just not sure what that way is.
I would love to use some code on the form that would run one make table query
and name it based on the users date input. Does anyone know if this is
possible?

 

[Guest]

Aaah, I see now. All you need to do is create a SQL statement based on
whatever input control your users are entering the date in, then feed that to
a docmd.runsql or a currentdb.execute statement.

So if your users are putting the date into a textbox and your tables are
named DAY1, DAY2, DAY3, etc..

you'd do something like:

SQL_STRING = "INSERT INTO DAY" & me.text1.text & " ( Field1, FIELD2 )
SELECT MY_PASSTHROUGH.Field1, MY_PASSTHROUGH.FIELD2
FROM MY_PASSTHROUGH"

Then a currentdb.execute(sql_string) or docmd.runsql(sql_string)

 

[Guest]

How would that create my tables? I have the database running now. I have
the code that creates the SQL based on user input. I have 31 queries the
tables are creating my tables. I am just tyring to get down to one query
additional to my Pass Through to make my life easier. I really do not want
to have to maintain 31 essentially identical queries if there is an issue, a
user requirement, further development or a storage space concern.

 

[Guest]

Sorry, the SQL example I used was only an append. You'd have to write ( or
copy and paste from one of your existing queries ) the SQL for a make table
query. But the concept is the same..

If you want, just copy and paste the SQL from one of your existing queries
and I'll help you with the changes you need to make to it. I'd also need the
name of whatever controls your users are entering dates into, unless you just
want to go with the current day.

This approach would get you to 1 query ( your pass through ), and the
SQL_statement you're executing.

 

[Guest]

So your table looks like:

Customer Address
Bob Bob's Address #1
Bob Bob's Address #2
Bob Bob's Address #3

????

Are there any other fields in the table?

 

[Guest]

oops.. responded to the wrong question in the wrong window. Ignore this.

 

[George Nicholson]

At its most basic, the SQL for your current MakeTable might look something
like this (drastically oversimplified):

SELECT MyInfoField1, MyInfoField2 WHERE MyDateField = #11/01/2007# INTO
TableDay01
FROM TableSource

So the idea is to write code that will loop through the appropriate number
of days and replace the date criteria and table name with new values for
each day of the month within the SQL and then run the SQL. (Simplified
aircode follows

Public Sub MakeDailyTables()
Dim strSQL as String
Dim dtmDate as Date
Dim strTableName
Dim i as Integer

'Note: Day(DateSerial(Year(today), Month(today)+1,0)) = End of current
month = day zero of next month

For i = 1 to Day(DateSerial(Year(today), Month(today) +1,0))
dtmDate = DateSerial(Year(today), Month(today), i)
strTableName = "TableDay" & Format(i, "00")
strSQL = "SELECT MyInfoField1, MyInfoField2 WHERE MyDateField = #" &
dtmDate & "# INTO " & strTableName & "FROM TableSource"
CurrentDB.Execute strSQL, dbFailOnError
Next i
MsgBox i & " tables created."
End Sub

Unaddressed issues:
- what to do about preexisting tbl31, etc. when month is shorter than 31
days? Try to delete all 31 possible tables before running MakeTable?

 

[John W. Vinson]

Is it possible to make a table with a query as the record source, without
using a Make Table Query. The reason that I as is that I have a rather
large database that I use to create tables for each day of the month based on
activity recieved from a larger database. I use one Pass-Through query as
the source for 31 Make Table queries. The reason I have done this is because
I need to segregate the activity by date so that I can produce daily averages
over the entire month. The reason that I have had to break each one per day
is the Pass Through query is huge (returning 7,000,000 + records) and that it
takes entirely too long run one all encompassing Make Table query. Is there
a way to write one query, with variable date ranges, that would produce a
seperate named table based on the date from a From? For example the same
query would produce table "Day 1" on the first day of the month and "Day 2"
on the second and so on for the entire month. That way I do not need to keep
31 different make table queries that do the same thing just creating tables
with different names. Thanks in advance for your help.

If you're assuming that you must create 31 tables in order to produce daily
averages, your assumption is *wrong*. I don't think a maketable query of *any*
sort is needed here!

With a proper index on the date field, you should be able to use a Select
Query - perhaps a passthrough query, if your big data table is in SQL or
another server database - to extract and average the data for one day. Have
you tried and rejected this option because you have *demonstrably* inadequate
performance? (It's *got* to be better than even a single make-table!)

John W. Vinson [MVP]