Create multiple tables from a single Make-Table Query
| Author |
Message |
|
Gilles Simo
#1 / 8
|
 Create multiple tables from a single Make-Table Query I have a Make-Table Query which outputs to a table called Results.
In the query I have a field called 'Fiscal_month' that concatenate year and
month, say 9903 for March 99.
I would like to run the query automatically using a macro or a procedure
that will add Fiscal_Month to the resulting table, say Results_9903 for this
month and Results_9904 for next month.
Is there a clue or a straight-forward answer to this one ?
Tks,
Gilles.
|
| Sat, 22 Sep 2001 03:00:00 GMT |
|
 |
|
Matthew Mille
#2 / 8
|
Create multiple tables from a single Make-Table Query If you're setting all of the records to have the same value in Fiscal_month, then picking the value out of any line would do the trick. How about something like this: public sub chgqryname()
dim db as database
dim qry as querydef
dim rst as recordset
set db = currentdb
set qry = db.querydefs("yourQuery'sName")
qry.execute 'creates the table'
rst = db.openRecordset("Results",dbopentable)
rst.movefirst
rst.name = rst.name&"_"&rst.fields("Fiscal_Month")
rst.close
set db = nothing
end sub
|
|I have a Make-Table Query which outputs to a table called Results.
|In the query I have a field called 'Fiscal_month' that concatenate year
and
|month, say 9903 for March 99.
|
|I would like to run the query automatically using a macro or a procedure
|that will add Fiscal_Month to the resulting table, say Results_9903 for
this
|month and Results_9904 for next month.
|
|Is there a clue or a straight-forward answer to this one ?
|
|Tks,
|
|Gilles.
|
|
|
|
|
|
| Sat, 22 Sep 2001 03:00:00 GMT |
|
|
|
Gilles Simo
#3 / 8
|
Create multiple tables from a single Make-Table Query Matthew, thks so much, I'll do that, however something I forgot to tell you, is that
I run the query manually every month, so where do I need to include your
procedure ??
in a macro, in the query (where) ???
Gilles.
Quote:
>If you're setting all of the records to have the same value in Fiscal_month, >then picking the value out of any line would do the trick. How about >something like this: >public sub chgqryname()
>dim db as database
>dim qry as querydef
>dim rst as recordset
>set db = currentdb
>set qry = db.querydefs("yourQuery'sName")
> qry.execute 'creates the table'
>rst = db.openRecordset("Results",dbopentable)
> rst.movefirst
> rst.name = rst.name&"_"&rst.fields("Fiscal_Month")
> rst.close
>set db = nothing
>end sub
>|
>|I have a Make-Table Query which outputs to a table called Results.
>|In the query I have a field called 'Fiscal_month' that concatenate year
>and
>|month, say 9903 for March 99.
>|
>|I would like to run the query automatically using a macro or a procedure
>|that will add Fiscal_Month to the resulting table, say Results_9903 for
>this
>|month and Results_9904 for next month.
>|
>|Is there a clue or a straight-forward answer to this one ?
>|
>|Tks,
>|
>|Gilles.
>|
>|
>|
>|
>|
|
| Tue, 25 Sep 2001 03:00:00 GMT |
|
|
|
Matthew Mille
#4 / 8
|
Create multiple tables from a single Make-Table Query It goes into a module - either a regular module or a form class module. Perhaps just create a single form with nothing bound, no controls other than a button to push which will kick off the sub procedure. Quote:
> Matthew, > thks so much, I'll do that, however something I forgot to tell you, is that
> I run the query manually every month, so where do I need to include your
> procedure ??
> in a macro, in the query (where) ???
> Gilles.
> >If you're setting all of the records to have the same value in
> Fiscal_month,
> >then picking the value out of any line would do the trick. How about
> >something like this:
> >public sub chgqryname()
> >dim db as database
> >dim qry as querydef
> >dim rst as recordset
> >set db = currentdb
> >set qry = db.querydefs("yourQuery'sName")
> > qry.execute 'creates the table'
> >rst = db.openRecordset("Results",dbopentable)
> > rst.movefirst
> > rst.name = rst.name&"_"&rst.fields("Fiscal_Month")
> > rst.close
> >set db = nothing
> >end sub
> >|
> >|I have a Make-Table Query which outputs to a table called Results.
> >|In the query I have a field called 'Fiscal_month' that concatenate year
> >and
> >|month, say 9903 for March 99.
> >|
> >|I would like to run the query automatically using a macro or a procedure
> >|that will add Fiscal_Month to the resulting table, say Results_9903 for
> >this
> >|month and Results_9904 for next month.
> >|
> >|Is there a clue or a straight-forward answer to this one ?
> >|
> >|Tks,
> >|
> >|Gilles.
> >|
> >|
> >|
> >|
> >|
-- ----------------------------------- Matthew Miller Ready Net Go, Inc.
|
| Tue, 25 Sep 2001 03:00:00 GMT |
|
|
|
Gilles Simo
#5 / 8
|
Create multiple tables from a single Make-Table Query Matthew, when running your code in a module, it says:
"compile error, invalid use of property"
here is how the module looks like today:
Option Compare Database
Option Explicit
Public Sub chgqryname()
Dim db As Database
Dim qry As QueryDef
Dim rst As Recordset
Set db = CurrentDb
Set qry = db.QueryDefs("Final Results Xls2")
qry.Execute 'creates the table'
rst = db.OpenRecordset("Monthly xls2 in my territory", dbOpenTable)
rst.MoveFirst
rst.name = rst.name&"_"&rst.fields("Fiscal_Month")
rst.Close
Set db = Nothing
End Sub
Quote:
>It goes into a module - either a regular module or a form class module. >Perhaps just create a single form with nothing bound, no controls other than a >button to push which will kick off the sub procedure.
>> Matthew,
>> thks so much, I'll do that, however something I forgot to tell you, is
that
>> I run the query manually every month, so where do I need to include your
>> procedure ??
>> in a macro, in the query (where) ???
>> Gilles.
>> >If you're setting all of the records to have the same value in
>> Fiscal_month,
>> >then picking the value out of any line would do the trick. How about
>> >something like this:
>> >public sub chgqryname()
>> >dim db as database
>> >dim qry as querydef
>> >dim rst as recordset
>> >set db = currentdb
>> >set qry = db.querydefs("yourQuery'sName")
>> > qry.execute 'creates the table'
>> >rst = db.openRecordset("Results",dbopentable)
>> > rst.movefirst
>> > rst.name = rst.name&"_"&rst.fields("Fiscal_Month")
>> > rst.close
>> >set db = nothing
>> >end sub
>> >|
>> >|I have a Make-Table Query which outputs to a table called Results.
>> >|In the query I have a field called 'Fiscal_month' that concatenate
year
>> >and
>> >|month, say 9903 for March 99.
>> >|
>> >|I would like to run the query automatically using a macro or a
procedure
>> >|that will add Fiscal_Month to the resulting table, say Results_9903 for
>> >this
>> >|month and Results_9904 for next month.
>> >|
>> >|Is there a clue or a straight-forward answer to this one ?
>> >|
>> >|Tks,
>> >|
>> >|Gilles.
>> >|
>> >|
>> >|
>> >|
>> >|
>--
>-----------------------------------
>Matthew Miller
>Ready Net Go, Inc.
|
| Mon, 01 Oct 2001 03:00:00 GMT |
|
|
|
Matthew Mille
#6 / 8
|
Create multiple tables from a single Make-Table Query Sorry - I was writing that on the fly - and misused a statement Two quick notes: 1. Add a Set in front of "rst = db.OpenRecordset("Monthly xls2 in my territory", dbOpenTable)" so it reads: Set rst = db.OpenRecordset("Monthly xls2 in my territory",
dbOpenTable)
2. Make sure you put spaces around all concatenation operators (&). Access
needs them to properly identify the boundaries between the objects.
So this line: rst.name = rst.name&"_"&rst.fields("Fiscal_Month")
becomes: rst.name = rst.name & "_" & rst.fields("Fiscal_Month")
That should now run (assuming the names are right everywhere)
Good luck!
Quote:
> Matthew, > when running your code in a module, it says:
> "compile error, invalid use of property"
> here is how the module looks like today:
> Option Compare Database
> Option Explicit
> Public Sub chgqryname()
> Dim db As Database
> Dim qry As QueryDef
> Dim rst As Recordset
> Set db = CurrentDb
> Set qry = db.QueryDefs("Final Results Xls2")
> qry.Execute 'creates the table'
> rst = db.OpenRecordset("Monthly xls2 in my territory", dbOpenTable)
> rst.MoveFirst
> rst.name = rst.name&"_"&rst.fields("Fiscal_Month")
> rst.Close
> Set db = Nothing
> End Sub
-- ----------------------------------- Matthew Miller Ready Net Go, Inc.
|
| Tue, 02 Oct 2001 03:00:00 GMT |
|
|
|
Gilles Simo
#7 / 8
|
Create multiple tables from a single Make-Table Query Matthew, I am making progress given the explanations you provided below, many thanks,
however I have still one remaining error, I bet it should not be difficult
for you:
when running the module, it says:
---> Public Sub chgqryname()
can't assign to read-only property
appreciate your help,
Gilles.
Quote:
>Sorry - I was writing that on the fly - and misused a statement >Two quick notes: >1. Add a Set in front of "rst = db.OpenRecordset("Monthly xls2 in my >territory", dbOpenTable)" >so it reads: Set rst = db.OpenRecordset("Monthly xls2 in my territory",
>dbOpenTable)
>2. Make sure you put spaces around all concatenation operators (&).
Access
>needs them to properly identify the boundaries between the objects.
>So this line: rst.name = rst.name&"_"&rst.fields("Fiscal_Month")
>becomes: rst.name = rst.name & "_" & rst.fields("Fiscal_Month")
>That should now run (assuming the names are right everywhere)
>Good luck!
>> Matthew,
>> when running your code in a module, it says:
>> "compile error, invalid use of property"
>> here is how the module looks like today:
>> Option Compare Database
>> Option Explicit
>> Public Sub chgqryname()
>> Dim db As Database
>> Dim qry As QueryDef
>> Dim rst As Recordset
>> Set db = CurrentDb
>> Set qry = db.QueryDefs("Final Results Xls2")
>> qry.Execute 'creates the table'
>> rst = db.OpenRecordset("Monthly xls2 in my territory", dbOpenTable)
>> rst.MoveFirst
>> rst.name = rst.name&"_"&rst.fields("Fiscal_Month")
>> rst.Close
>> Set db = Nothing
>> End Sub
>--
>-----------------------------------
>Matthew Miller
>Ready Net Go, Inc.
|
| Thu, 04 Oct 2001 03:00:00 GMT |
|
|
|
Matthew Mille
#8 / 8
|
Create multiple tables from a single Make-Table Query Sorry - I was writing that on the fly - and misused a statement Two quick notes: 1. Add a Set in front of "rst = db.OpenRecordset("Monthly xls2 in my territory", dbOpenTable)" so it reads: Set rst = db.OpenRecordset("Monthly xls2 in my territory",
dbOpenTable)
2. Make sure you put spaces around all concatenation operators (&). Access
needs them to properly identify the boundaries between the objects.
So this line: rst.name = rst.name&"_"&rst.fields("Fiscal_Month")
becomes: rst.name = rst.name & "_" & rst.fields("Fiscal_Month")
That should now run (assuming the names are right everywhere)
Good luck!
Quote:
> Matthew, > when running your code in a module, it says:
> "compile error, invalid use of property"
> here is how the module looks like today:
> Option Compare Database
> Option Explicit
> Public Sub chgqryname()
> Dim db As Database
> Dim qry As QueryDef
> Dim rst As Recordset
> Set db = CurrentDb
> Set qry = db.QueryDefs("Final Results Xls2")
> qry.Execute 'creates the table'
> rst = db.OpenRecordset("Monthly xls2 in my territory", dbOpenTable)
> rst.MoveFirst
> rst.name = rst.name&"_"&rst.fields("Fiscal_Month")
> rst.Close
> Set db = Nothing
> End Sub
-- ----------------------------------- Matthew Miller Ready Net Go, Inc.
|
| Mon, 22 Oct 2001 03:00:00 GMT |
|
| |
|
