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. 3 followers. Follow

9 Replies
1459 Views

Similar Articles

[PageSpeed] 26

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:

Query date range
I have a query asking for records whose field (CombinedDate) is not null. I'd like be able to pull out only records whose CombinedDate occurs within the next 2 weeks. I have trouble creating expressions for dates & don't really catch when to use DateDiff, DateAdd, or something on the order of Date()+. Appreciate your help. Field: CombinedDate Criteria: Between Date() and DateAdd("d",13,Date()) Use DateAdd when you want to create a date. Use DateDiff when you want to determine the difference in two dates Use Date() + when you want to add a specific number of days o...

Return Value in Column 2 Q
I have a table which contains 2 columns and 30 rows of Data Col1 = Sales Col2 = ManHours I wish to "lookup" a value that is input into A1 (which will be a sales value) and Return the appropriate ManHours associated with these sales. My only problem is that my value in A1 will probably not match exactly what is in Col1. For example Col1 increments in 100's, so it will show sales of 9,000, 9,100, 9,200 etc Opposite these Sales values will be Manhours, say, 50, 55, 58 etc Cell A1 might contain 9,040, thus the value I want returned is 55. Likewise if A1 = 9,025 this should al...

Spelling in new Security Tables in GP v10
There are a significant number of spelling mistakes in the Dynamics SY09000 and SY09100 tables in GP 10.0. These are the new Security Role and Task Master tables. Please correct these for future releases. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www...

Query-based Distribution Groups Attribute Question
A student asks: When a new attribute is added to a user class object in the schema, does that attribute automatically appear in the choices one has when configuring a query in the definition of a Query-based Distribution Group? If not, how would one get it to appear? Many thanks in advance! JB Fields jbfields@msn.com ...

Make more profit with 12 Daily Pro!
Hi, all! On to 12 Daily Pro. http://www.12dailypro.com/?ref=104399 12 Daily Pro is an investment opportunity that will make you 150% profit on your investment in less than a month. Get paid 12% daily to look at websites. An example cashout history: http://www.centsi.fatcow.com/cashout.jpg My personal cashout history http://img.photobucket.com/albums/v181/IceSaint/payout1.jpg Check out my site for full details! EVERY BIT OF INFORMATION YOU NEED TO KNOW, I WILL PERSONALLY HOLD YOUR HAND IF NEEDED. I talk to many of my members on the phone almost every day, believe it or not! http://A...

Querying the word "automatic"
I have a table with approximately 80,000 records. I've created a full text index on the table with the following statement: CREATE FULLTEXT INDEX ON AdsAuto ( StockNumber, YearText, Make, Model, Trim, Transmission, ExteriorColor, Description, Equipment, FullTextKeywords, VIN, AdCode ) KEY INDEX PK_AdsAuto ON TraderCatalog WITH CHANGE_TRACKING AUTO This query takes 13 seconds to run: SELECT ID, Year, Make, Model, Description FROM AdsAuto WHERE CONTAINS(AdsAuto.*, '"automatic*"') This query takes 2 seconds to run: SELECT ID, Year, Make, Mo...

HOW CAN i PRINT A PICTURE TO THE EDGE OF A PAPER WITHOUT A MARGIN
How can I print pictures to the edge of the paper without the margin showing. ? Luie <Luie@discussions.microsoft.com> was very recently heard to utter: > How can I print pictures to the edge of the paper without the margin > showing. ? You need a printer that supports full bleed printing. -- Ed Bennett - MVP Microsoft Publisher ...

How do I make animations even faster?
I want fast animations and 'very fast' doesn't quite cut it, plus I want them to animate sooner, and 'after previous' doesn't quite cut it ('with previous' doesn't fit the bill either) Any ideas on how to speed them up and make them happen sooner? (FYI typing '-0.5' in the 'timing' field didn't work). Thanks, AF What version of PPT are you working with? Animations can be set to 0.1 seconds (either in the timing dialog box or by shortening animation bar in advanced timeline. Anything faster is at the same time as previo...

How can I open my Calendar without defaulting to Calendar in Arch
I don't know why is my Calendar defaults to Calendar in Archive when opening it? I need to right click "Calendar" to open it in new window to show my recent meeitng schedules. Thanks "rrg1965" <rrg1965@discussions.microsoft.com> wrote in message news:C9DE3C3F-6430-49D0-8C68-C019A6536D4C@microsoft.com... >I don't know why is my Calendar defaults to Calendar in Archive when opening > it? > I need to right click "Calendar" to open it in new window to show my recent > meeitng schedules. In Control Panel, start the Mail ap...

Help pulling data from pivot table for calculation
I wanted to find out if there was anybody out there who could help. I attached a copy of a pivot table. This is just one of many files and the actuals column (highlighted) is no a constant. I wanted to find out if there was any way to do a search for any actuals columns and pull and total the data for other calculations. I know this can be done but still a newbie to VB and can not figure it out on my own. +-------------------------------------------------------------------+ |Filename: Actuals.doc | |Download: http://www.excelforum.com/attachment....

Make Excel add
I need a formula where excel would recognize an entry in say column "AO" (any entry) and add 1 to the number already in column AN. Something like that, I hope somebody understands what I am trying to say. Thanks In cell AO1, put this formula and copy and paste it as far down column AO as you need.......... =AN1+1 Vaya con Dios, Chuck, CABGx3 "Bman" wrote: > I need a formula where excel would recognize an entry in say column "AO" > (any entry) and add 1 to the number already in column AN. Something like > that, I hope somebody understands w...

Need Help with Making Address Labels with Data Merge
Hi Anyone, I have done this before, but I downloaded the latest update to Office 2004. I have created my excel spreadsheets and cannot make data merge help me with address labels. I would be better off hand writing over 250 addresses on mailers as I have put in over 10 hours of time and am able to make address labels but 24 pages of labels only produces 8 addresses over and over again. Anyone with tips out there? Feel free to email me at melanie.m.hahn@gmail.com. The entire process has aggrevated my back and so I will now go lie down and perhaps some miracle will take place. Thank you f...

Parameters for Query
I have an query from Access database which depends on 3 parameters (user selects from dropdown box). I need the parameters to be dependent on the others i.e. When I select Team A if should list All TeamA records and when 2nd parameter BrandA is input it should show records dependent on those two criteria and hence forth. Is this possible? Any help would be appreciated. Team Brand Area Sales Vallue A BrandA Area1 100 B BrandA Area1 105 C BrandB Area1 150 A ...

Suppress msg from update query
I would like to run this update query automatically when the database opens. At present I have it running from a macro when the form is opened however it prompts the user twice before it runs. I would like to eliminate the user input. UPDATE Transactions SET Transactions.Status = "Cleared" WHERE (((Transactions.date)<Date()-10) And ((Transactions.Status)="Pending")); TIA Dim strSQL As String strSQL = "UPDATE Transactions " & _ "SET Transactions.Status = 'Cleared' " & _ "WHERE Transactions.[Date]<Date()-10 " ...

Can I use the old 2003 pivot tables in excel 2007?
I'm currently using excel 2007 but prefer the interface/feel/look of 2003 pivot tables? is there a way i can still use the 2003 pivot tables in 2007 excel? thanks! ...

How do I make a pie chart with 42 answers that shows % each out of
I want to graph survey responses where peolple could pick more than one answer. So there would be some overlap in the pie chart, is this possible? Please give us more details, -- Thanks, Shane Devenshire "brmg" wrote: > I want to graph survey responses where peolple could pick more than one > answer. So there would be some overlap in the pie chart, is this possible? ...

Writing to a CStdioFile without a path?
Hello, I recently have encountered a problem with some code I have written involving writing to a file. I am writing to a text file which i open without specifying a path CStdioFile LogFile; LogFile.Open("log.xml", CFile::modeCreate | CFile::modeWrite | CFile::shareDenyNone | CFile::typeText); I then write to the file CString aString; aString.Format("<LOG>\n"); LogFile.WriteString(aString); This all seems to work fine. Of course the location of the fi...

Access 2003 does not display pivot table view
When I am viewing a query in Access 2003 and I try to change the view to Pivot Table the system just hangs, I do not get any error message. I have tried opening the mdb file on another computer and was able to switch to Pivot table view fine, so I do not think my file is corrupt. Any ideas? Thank you I get the same problem in Access 2007 "seamonik@gmail.com" wrote: > When I am viewing a query in Access 2003 and I try to change the view > to Pivot Table the system just hangs, I do not get any error message. > I have tried opening the mdb file on another computer and was a...

Query that feeds the report
Im using Windows XP and Office 2003 I would like to put more than 255 fields in my report. I have one query that feeds my report. That query is full [255 fields]. If i take the query that is full and and make a relationship between it and another query that has a lot of unused field, will this allow me to put more than 255 fields in my report? If yes, how can i do this? Or how can i get more than 255 fields in my report? Thanks for any help ed -- thanks You can't get more than 255 fields in a report. You can get 255 in a report and another 255 in a sub-report. You will have to fig...

Multiple lines from different tables in a report detail
I'm not sure if I can easily describe what I'm trying to do here, but here goes... Let's say I'm trying to make a report that describes the vehicles each person in my database owns. So I have a table for each person, a table for cars, and a table for pickup trucks. There's a one to many relationship from each person to the car table, and from person to trucks. The car and truck tables don't have the same fields, so I need them to be different tables. I want my report to look like this: Person Car Name Car Mileage Truck Name Cargo Cap...

how to copy sheet with charts without link to original data
I have a worksheet with ten charts on it. I need to copy the worksheet with all its data and related charts to new sheets or files so I have a page for every day, with different data entered every day. But when I copy the worksheet with embedded charts and paste it into a new file, the charts pull their source data from the original worksheet, not the new one. The source data Value box is written as such, ='03-09-10'!$D$12 any ideas? Hi Esty, Probably wont help you, but I had similar problem, I posted few days ago. Didn't resolved it yet, but Jon suggested ...

Very simple update query running super slow!
I have two tables: Rugman and PDrive The table look like this: Rugman -- ID (Auto/key) / Number (text!) / location (text) / test (text) PDrive -- ID (Auto/key) / Field1 (text!) The reason I had to use text for Number and Field1, is because there are a few lines what have a number like: 1234-2 I just want to check the Number in table Rugman and see if it does exist in the table PDrive... If it does: field test must be changed to "done" for that record where the numer exists in the other table... Don't get why it's running so slow. I have created also other update queries ...

Making a query that shows a value based on another value.
Hi I have a Query that extracts two fields of data from a table 1. CustomerName (Contains customer name) 2. BuySell (Has either the Word "Buy" or "Sell") However I wish this query to display CustomerName (As above) but I want it to display a word based on whether field 2 is the word 'Buy' or the word 'Sell'. So for example if the word 'Buy' is in field 2 then I want the word 'RHS' to be displayed and if the word 'Sell' is in field 2 I want the word 'LHS' to be displyed. I do not need this to save into a table jus...

Is it possible to add further contacts to the quick campaign after
Hy, Why is it not possible to add more contacts to the quick campaign after it started. Do I make something wrong? Antonio Yes. 1. Go under: WorkPlace->My Work-> Activities 2. Create a new activity that matches the one from your Quick Campaign, Lets Say Phone Call. 3. Enter the same subject in your new phone call as the ones used in the Quick Campaign, as well as another other data that should match the existing Phone calls. 4. the Recipient should be the contact you wanted to add. 5. On the Regarding field, launch the lookup, change "look for" to "Quick Campaign&q...

Can I have in Excel SQL query computed constants like "? as Col1"
Trying to have a query that can set a constant in the select statement, for example: SELECT Author, '11/15/2005' as Updated, PublishDate FROM Author WHERE Author like 'A*' Can I prompt the user for this value as an input, for example: SELECT Author, ? as Updated, PublishDate FROM Author WHERE Author like 'A*' Maybe with a prompt "Please enter Updated date". Before resorting to VBA or Access, just want to see if it can be done in Excel. ...