By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,327 Members | 2,627 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,327 IT Pros & Developers. It's quick & easy.

save table records from select tempquery

P: n/a
Hello group,
hope to anybodys help on my temporary blackout.
(Using Access 2003 on XP Win.)
I know how to create and edit temporary query recordset, then I can set
this data source as my form recordsource.
Until there all this work for me at a glance.

Problem case now!
For another reason I need to store my selected datas from this
datasource in another new table, can we name it as tempTable.
There I stucked right now.
I can recall, how to input records by records by using addNew method.
But absolute blackout for me - how to save whole temporary data set in
new table!
Is it possible? Hope, yes.

By using paste append?

Another question.
My form is bind to tempTable.
I also need test this table for old records each time I open my form.
Then on forms load event I can trigger my combobox routine to select
time period to use for routine selected data according selected period.
How to select and easy delete?
Should I use recordset.count and then some docmd.deleteqry methods?

I know - answers is next doors, might be anybody can help me with
direction?
Thank's in advance
Ainars.

Aug 8 '06 #1
Share this Question
Share on Google+
6 Replies


P: n/a
Turn the rowsource for form into a maketable query?

Aug 9 '06 #2

P: n/a
Thank You, excellent idea, now I feel much better! :)
Maketable query adds for select query operator INTO where I can point
to my TmpTable.
I will try this right now, but does this run correct in case when I
need add datas in small portions, more than only one time? Paste append
in manual mode can do this, but in VB I never used such functionality.

Ainars.

pi********@hotmail.com wrote:
Turn the rowsource for form into a maketable query?
Aug 9 '06 #3

P: n/a
Me again,
just have tried make workaround from this, but stuck again, because
never done functionality like this.
Can You help with more?
My code:

Dim a As String, n As String

Select Case CmbPeriod.Column(0) 'this combo selects month or quarters
or half year
Case 1
n = "QSP01" '<<< this is select query name where i can aggregate column
data
....
End select

a = "SELECT 6199 as Konts, Sum([" & n & ".b50]) AS b50, Sum([" & n &
".b55]) AS b55, Sum([" & n & ".b62]) AS b62, Sum([" & n & ".b63]) AS
b63, Sum([" & n & ".b70]) AS b70, Sum([" & n & ".b71]) AS b71, Sum([" &
n & ".b72]) AS b72, Sum([" & n & ".b99]) AS b99, -1 as Fons"
a = a & " INTO TmpTable FROM " ' <<< I added INTO there
a = a & " (SELECT " & n & ".Konts, " & n & ".b50, " & n & ".b55, " & n
& ".b62, " & n & ".b63, " & n & ".b70, " & n & ".b71, " & n & ".b72, "
& n & ".b99, 0 as Fons"
a = a & " FROM " & n
a = a & " WHERE ((" & n & ".Konts) Like " & "'61*'" & " And (" & n &
".Konts) Not Like " & "6199))"
a = a & " UNION"
a = a & " SELECT " & n & ".Konts, " & n & ".b50, " & n & ".b55, " & n &
".b62, " & n & ".b63, " & n & ".b70, " & n & ".b71, " & n & ".b72, " &
n & ".b99, 0 as Fons"
a = a & " FROM " & n
a = a & " WHERE ((" & n & ".Konts) Like " & "'61*'" & " And (" & n &
".Konts) Not Like " & "6199);"

This query string actually is much much longer because repeated 20
times for each another ledger total sum!
(Why I need turn this into smaller portions?)
Does for me the following - top query part makes sum from "where"
subquery datas then with "union" I select same subquery data again and
collect this all together with sum and reflect on my form.

Now, how can I turn my string data to maketable?
docmd have not such functionalities, or I missed something?
then I found under run comands 'DoCmd.RunCommand
acCmdQueryTypeMakeTable
but no help for me available for this.
Ideas?
Ainars.
pi********@hotmail.com wrote:
Turn the rowsource for form into a maketable query?
Aug 9 '06 #4

P: n/a
"A_M_IS" <ai***@iname.comwrote
I will try this right now, but does this
run correct in case when I need add
datas in small portions, more than
only one time?
No, "MakeTable" Queries, just as the name suggests, make an entirely new
Table.

To _add_ data to an existing Table, check Help on Append Queries.

To _change_ data in existing records of an existing Table, check Help on
Update Queries.

Larry Linson
Microsoft Access MVP
Larry Linson
Microsoft Access MVP

Aug 9 '06 #5

P: n/a
Thank You for Your input!
Append query help is right choice for my case. This can help.
No, I don't need edit data right in this table, but I keep in my mind
this point to next designs.

What is making most of trouble for me right on current moment?
I looking virtually to results I wished to achieve (ordered to me from
my accountant's room) and trying understand best design solution
points for this.
Picture, I trying achieving, is more alike crosstab, just more complex
on this.
So, deeper I digging, this looks more and more complex for me.

Might be experienced Access gurus can help me with pointing me on
design?

Initially I have on hand nine department tables organized on same
manner as others are.
A rows there is ledger numbers, columns is months.
A result in tables is departments planned amounts to corresponding
months.

Now I need rearrange to call those datas like crosstab:
rows stay the same, columns now should be department codes, but
filtered selection of datas should be made by time periods which can be
those same from month's columns or even complex like aggregated
quarters or half years amounts.

More or less I managed this picture via my select query (see sample
code on my earlier post), sum total aggregations and union queries.
But what if our finance analyst desired more?
- to calculate intermediate ledger group totals and input those results
next to ledger number groups as well?

There I filed :(
My screen form recordsource select query is ~10000 characters long and
I see I can double this character total count if I try achieving
desired screen result on same design manner!
Is this normal length of Query? I looked in Access specifications list
than not, but I am not sure for another - because I let this utility to
bookkeeper's room for later adjustments if they desired. I believe
they need buy then some "nutcracker" for selected edit task.
This is why I looking for another design idea, but out any for today.
Might be selecting and writing calculated inputs to TmpTable in small
portions can let me chances to call again and easy calculate running
sum totals or recall selected aggregated totals as necessary for later
grand total formulas?
Might be gurus seen the same and can point directions for me from
experiences?
Highly appreciate inputs in advance!
Rgrds -
Ainars.

Larry Linson wrote:
"A_M_IS" <ai***@iname.comwrote
I will try this right now, but does this run correct in case when I need add
datas in small portions, more than only one time?

No, "MakeTable" Queries, just as the name suggests, make an entirely new
Table.

To _add_ data to an existing Table, check Help on Append Queries.

To _change_ data in existing records of an existing Table, check Help on
Update Queries.

Larry Linson
Microsoft Access MVP
Aug 10 '06 #6

P: n/a
Found part of solution for me by myself!
I asked there, how to convert VB recordsource string to executable
query.

After hours of n.g. browsing found that "currentdb.execute
MyStringName", I think this is what I need and browsed for there in
last days.

Never used this method before!
At least I can move ahead with my design now.
Thanks to Pietlinden and Larry Linson for spending time in assistance
to my problem!
Regards to all -
Ainars.
A_M_IS wrote:
Me again,
just have tried make workaround from this, but stuck again,
....
.... how can I turn my string data to maketable?
docmd have not such functionalities, or I missed something?
then I found under run comands 'DoCmd.RunCommand
acCmdQueryTypeMakeTable
but no help for me available for this.
Ideas?
Ainars.
pi********@hotmail.com wrote:
Turn the rowsource for form into a maketable query?
Aug 11 '06 #7

This discussion thread is closed

Replies have been disabled for this discussion.