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

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.
0
Utf
11/5/2007 7:33:00 PM
access 16762 articles. 2 followers. Follow

9 Replies
808 Views

Similar Articles

[PageSpeed] 22

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.

"WildlyHarry" wrote:

> 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.
0
Utf
11/5/2007 8:11:01 PM
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?

"Lance" wrote:

> 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.
> 
> "WildlyHarry" wrote:
> 
> > 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.
0
Utf
11/5/2007 8:26:02 PM
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)







"WildlyHarry" wrote:

> 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?
> 
> "Lance" wrote:
> 
> > 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.
> > 
> > "WildlyHarry" wrote:
> > 
> > > 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.
0
Utf
11/5/2007 8:51:01 PM
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.

"Lance" wrote:

> 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)
> 
> 
> 
> 
> 
> 
> 
> "WildlyHarry" wrote:
> 
> > 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?
> > 
> > "Lance" wrote:
> > 
> > > 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.
> > > 
> > > "WildlyHarry" wrote:
> > > 
> > > > 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.
0
Utf
11/5/2007 9:00:04 PM
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.  


"WildlyHarry" wrote:

> 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.
> 
> "Lance" wrote:
> 
> > 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)
> > 
> > 
> > 
> > 
> > 
> > 
> > 
> > "WildlyHarry" wrote:
> > 
> > > 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?
> > > 
> > > "Lance" wrote:
> > > 
> > > > 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.
> > > > 
> > > > "WildlyHarry" wrote:
> > > > 
> > > > > 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.
0
Utf
11/5/2007 9:10:00 PM
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?

"Lance" wrote:

> 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)
> 
> 
> 
> 
> 
> 
> 
> "WildlyHarry" wrote:
> 
> > 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?
> > 
> > "Lance" wrote:
> > 
> > > 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.
> > > 
> > > "WildlyHarry" wrote:
> > > 
> > > > 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.
0
Utf
11/5/2007 9:16:00 PM
oops.. responded to the wrong question in the wrong window.  Ignore this.

"Lance" wrote:

> 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)
> 
> 
> 
> 
> 
> 
> 
> "WildlyHarry" wrote:
> 
> > 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?
> > 
> > "Lance" wrote:
> > 
> > > 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.
> > > 
> > > "WildlyHarry" wrote:
> > > 
> > > > 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.
0
Utf
11/5/2007 9:21:02 PM
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?

-- 
HTH,
George



"WildlyHarry" <WildlyHarry@discussions.microsoft.com> wrote in message 
news:AE175603-E8DC-49D4-AEB3-BB6D88F1E302@microsoft.com...
> 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.
>
> "Lance" wrote:
>
>> 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)
>>
>>
>>
>>
>>
>>
>>
>> "WildlyHarry" wrote:
>>
>> > 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?
>> >
>> > "Lance" wrote:
>> >
>> > > 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.
>> > >
>> > > "WildlyHarry" wrote:
>> > >
>> > > > 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. 


0
George
11/5/2007 10:26:11 PM
On Mon, 5 Nov 2007 11:33:00 -0800, WildlyHarry
<WildlyHarry@discussions.microsoft.com> wrote:

>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]
0
John
11/6/2007 6:44:05 AM
Reply:

Similar Artilces:

Is it possible
I would like to know is it possible to make million dollar in the internet, if you have any suggestions, write on my forumhttp://isitpossible.truemillion.comMark...

Personal Folders in OWA
Hello, I have just enabled OWA on our Exchange 2003 SP2 through ISA Server 2004. Is there any way to access personal folders in OWA? Thanks in advance. If by personal folders you mean .pst files, the answer is NO! Sorry. C C wrote: > Hello, > > I have just enabled OWA on our Exchange 2003 SP2 > through ISA Server 2004. Is there any way to > access personal folders in OWA? > > Thanks in advance. > > "C C" <someone@atsbcglobal.net> wrote: >I have just enabled OWA on our Exchange 2003 SP2 >through ISA Server 2004. Is there any way...

making a flyer with publisher
I Want to add more than one picture on my flyer, but it does not give me that option. Is it possible to add more than one picture? thank you aj Yes, but you have to do it one at a time. -- JoAnn Paules MVP Microsoft [Publisher] "A.J." <A.J.@discussions.microsoft.com> wrote in message news:5F631F9A-98CE-4915-93D4-56EAF4A34765@microsoft.com... >I Want to add more than one picture on my flyer, but it does not give me >that > option. Is it possible to add more than one picture? > thank you > aj I make flyers with Publisher 2000 and put several pictures...

Aligning an excel table w/ a cluster column chart x-axis columns
I'm trying to create my own data table for the x axis of my cluster col chart. The x axis is the months of the year. I want WSheet col A to align w/ the chart margin, col B to align w/ the Jan chart col, C w/the Feb chart col, thru col M w/ Dec. How do I do this? Every time I change the col widith of the excel columns, the chart stretches or shrinks as the col width does. I can move the graph in the chart to line up the first col, but then adjusting the size of the graph, moves the chart around. Nothing seems to work to get all the chart cols to line up w/ the table cols. I hop...

pivot table macro
I want to create a pivot table using the pivot table wizzard from a worksheet into a worksheet with the name table by running a macro. when I record the macro I create the pivot table using the wizzard and it works fine but when I try to run the macro again it errors "unable to get the pivot tables property of the worksheet class" any help? Hi Post the code you have created -- Regards Roger Govier "skippy51" <skippy51@discussions.microsoft.com> wrote in message news:C0E655DC-B0D8-4FB5-901E-1EA30CF6E089@microsoft.com... > I want to create a pivot table usi...

Downloading Statements Directly Not Possible
I have Money 2002 installed on an Win XP Pro SP1 machine. Something happened and I probably did it, that causes the statement downloads not to go directly to the account. Rather they want to be saved in a file and then opened into Money. I then have to delete the download. I have tried several things in order to fix this: 1. I verified the file associations of OFC, OFX were correct. They were. 2. Lowered all my security settings in IE. Even put banks into Trusted site zone. No go. 3. Made new accounts. Didn't help. 4. My IE cache is large (500MB) and regularly cleaned out. 5. I made s...

How to make odds ratio graph?
How to make odds ratio graph? Would love to help but your cryptic message does not tell me what it is that you want to do. More details, please -- Bernard Liengme http://people.stfx.ca/bliengme Microsoft Excel MVP "How to make odds ratio graph?" <How to make odds ratio graph?@discussions.microsoft.com> wrote in message news:E115BCD1-D2C3-4739-9DFD-244CD8C67F5F@microsoft.com... > How to make odds ratio graph? ...

Pivot Table- Column 2 sort
How can I sort a Pivot Table by the 2nd (or any other) column? I've selected a cell and hit the A-Z Z-A Sort button, but it only works on column 1. Thanks! Right click on the column and select Field Settings | Advanced to access the sort options... -- HTH... Jim Thomlinson "srpd" wrote: > How can I sort a Pivot Table by the 2nd (or any other) column? I've selected > a cell and hit the A-Z Z-A Sort button, but it only works on column 1. > Thanks! Difficult to answer without knowing the structure of your underlying data and what, exactly...

Pivot Table and Protection
I created a macro to update my pivot tables when the worksheet is protected. It works fine as long as I know the password (i.e., I must enter the password to run the macro). This really defeats the purpose of the protection. So my question is how can other users update my pivot tables without knowing my password? I'm using 2003 version. Thank you! -- Catherine ...

how do i make the excel chart axis title boxes bigger?
The text is often truncated as though it were too long for the box. If I reduce the font the titles are often too small to read. How do I "grow" the boxes to accomodate longer strings? Perhaps try Andy Pope's response to a similar past query: http://tinyurl.com/7sz94 -- Rgds Max xl 97 --- GMT+8, 1� 22' N 103� 45' E xdemechanik ---- "Patrick98001" <Patrick98001@discussions.microsoft.com> wrote in message news:608942AC-9AA9-4B10-B81B-1A90588B92AB@microsoft.com... > The text is often truncated as though it were too long for the box. If I > redu...

form calculation not showing in table
Hi i have a table with loan amount and charges which add up to create a balance which is also a field in the table. i have created the form for this table and created the calculation for the balance field to add up the loan amount and charges. The calculation on the form adds up correctly, the problem is that i dont understand why the balance data is not going into the balance table. the loan amount and charges inputted on the form is being entered onto the table, yet the balance is not. Can someone tell me what i am doing wrong, or if i should be doing it a different way. thanks in ...

How can I automatically enter repeating data from one table to ano
Hi I am a novice so if you can help please do. I have set up a database and have a range of tables established for each of my categories and they seem to be working well. How do I get the database to automatically fill in repeating data (ie names) from one table to the next so I don't have to keep retyping? Hope this makes sense. Please help in simple English. Here's hoping!!! Sally In a properly designed relational database system, you shouldn't have the same data in multiple tables. You may want to read up on Database Normalization. Jeff Conrad has some goo...

is this possible? reload a form that is based on a query with new query that is defined in the form.
I have a basic form. The form is based on a query, so when I open the form, a pop comes up where I enter in what I want to query. So instead of 30,0000 records, I get 1-10 records. on the form, I have a text box. I want to be able to enter in what I want to query next and afterupdate have the recordset/form reload with that query instead of my original qeury. I figure this has to be done with VB. I am just starting to learn some VB but I am lost. Any info would be great, thank you. It does. You need to apply the new query or table name, or the sql string to the forms recordsource, then...

unrelated table in a query
I am using a query with several tables, linked with one to many relationships. There is one table in there that is not related to any other tables. I contains a single record and I use a form on opening the database to set parameters on it (start date and end date). I use this table to set conditions on the query, then I use the query for about 10 reports. I haven't had any problems with the query (although the reports run slow). Is there any reason I should not set it up this way? I have to start a new project and would like to repeat the logic if it is not flawed. > I u...

Possible to detect changes to an XmlDocument object?
If I instantiate an XmlDocument object, the load a file through the Load method, is there a way, later on in execution, to easily determine if the XmlDocument object has changed from the original version loaded? I'd like to be able to check a condition to see if the document in memory has been editted (i.e., nodes added, attributes changed, etc.) so that my user can decide whether or not to commit changes to the disk file before moving to another file. Will I have to resort to re-loading the file, and comparing OuterXml properties? Gabe Gabe Covert wrote: > I'd like t...

Export Access Data Project Queries to old Access .mdb file
I want to copy Access Data Project queries from a project into a new .mdb file. What is the best way to do this? In the new .mdb go to the menu bar, click file, Get external data, and Import. Select the orignal .mdb file and then the items. >-----Original Message----- >I want to copy Access Data Project queries from a project into a new .mdb >file. What is the best way to do this? >. > ...

Export report templates
I would like to export the standard and modified reports from Great Plains 6.0 and am not familiar with this application. Is this feature available for both FRx reports and the vanilla standard reports that come with Great Plains. Any assistance in how to obtain this information either in a text, csv format etc would be appreciated. For FRx, you can export to Excel and other formats. Within FRx you would go to the Report Catalog and pick the report you want to print. On the output tab select the type of destination you want. Click the Generate report button. There are no vanilla repo...

Is it possible to compare a string and a field name
I have the following tables: Membership, Subscriptions, OfficeUseOnly. The Office Table contains a Year, Year To and From [financial year] and the other fields are the 5 membership types (regular, student etc) and these 5 fields contain the different amount each type of member has to pay. I want to create a form whereby on entering the financial year and the membership type the correct amount is automatically inserted into the 'Amount' field: FinancialYear - in current form = OfficeUseOnly SubsFinYear and then if MemberType = 'Student' find Student field ...

Advice on Making a TimeLine Control
Hello, I am in the process of making my first custom control, which needs to be a timeline that behaves similar to the timeline in MS Moviemaker (and other apps). So far I have created a CWnd and drawn the 'tick marks' at the top to represent time, and got it to successfully scroll horizontally and vertically. The complication is having to add a label area to the left, to label each entry in the timeline, because these labels must not scroll horizontally with the rest of the view. The tick marks at the top also must not scroll vertically with the rest of the view. I am not sure wha...

SQL help with IV table
Hi, Below is a script and its results: select doctype,docnumbr,lnseqnbr,DTLSEQNM,rcptnmbr, rcptqty,rcptexct from iv30301 4,PRV00053,16384.00000,1,PRV00053,15.00000,487.65000 4,PRV00053,16384.00000,2,PRV00053,1.00000,32.56000 11,PRV00054,16384.00000,1,PRV00053,0.00000,28.05000 11,PRV00054,32768.00000,1,PRV00053,0.00000,1.74000 11,PRV00054,49152.00000,1,PRV00055,0.00000,-6.90000 4,PRV00055,16384.00000,1,PRV00055,10.00000,409.00000 What I am trying to do through a sql script is assign the appropriate Cost adjustment (i.e doctype >9) to the correct receipt line (i.e doctype <9) in POP ...

How do I print my calendar without private appointments?
I'm trying to print a calendar and hide my private appointments. I also don't want the word private appointments to be on my calendar too. I still want to keep my private appointments on my computer system, just not on the printed copy. I saw this question posted in 2006 and Sue Mosher's answer was to filter a view in Outlook so that private appointments wouldn't print. How do you filter a view in Outlook 2003? R-click in the calendar, pick Customize Current View, Filter...... paigehcm wrote: > I'm trying to print a calendar and hide my private ...

pictures in various shapes
Is it possible to insert a picture and make it into a circle or an oval or various other shapes than just squares and rectangles? Thank you ahead of time. with a graphics editor -- Rob Giordano Microsoft MVP - FrontPage "Stephanie" <Stephanie@discussions.microsoft.com> wrote in message news:014D0D06-15F9-4D92-8D93-64FBD3A9CA32@microsoft.com... | Is it possible to insert a picture and make it into a circle or an oval or | various other shapes than just squares and rectangles? Thank you ahead of | time. | | Stephanie wrote: > Is it possible to insert a pict...

Create sequence number for table
Hi, I have a table with a Title field, comment field and a Sequence field. I would like code to sort the table by Title and then fill in the Sequence field based on the order of the records. The first record would have a sequence number of 1, the second record a 2, etc. Can someone help me with code to do this? Thanks in advance. "Phillip" <Phillip@discussions.microsoft.com> wrote in message news:432342F6-AD55-405B-8CDF-43414FBE421D@microsoft.com... > Hi, > I have a table with a Title field, comment field and a Sequence field. I > would like ...

Is this possible
I want to make a list of the files in several different folders. Is it possible to somehow "Copy and paste" just the filenames into a list, in either Word or Excel or Wordperfect. I am just trying to sort the files in several different folders to weed out duplicates. There has got to be an easier way then typing them all or screen printing the lists and manually weeding them out. Any suggestions. Frustrated... Hans, Here is some code that will start in a directory and list all files in that and any sub directories. It uses recursion to go through all the subs. Just ch...

How can I make this happen?
I want a macro or something that will allow me to "show" every comment on a work sheet (there are almost 100) then go to Format Comment and Properties and change it to "move and size with cells" then rehide the comment. Any ideas? -- foxspirit ------------------------------------------------------------------------ foxspirit's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=31264 View this thread: http://www.excelforum.com/showthread.php?threadid=509350 On a worksheet is there a function that unhides all comments in every cell? -- foxsp...