Selecting and copying records sets from query to table using event

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I would to be able to write a event procedure that once a button is clicked
it would run a query select and copy all the results of that query and paste
them into a predetermined table which would need all of its current records
deleted before it paste the new records. I would then use this table for
another query to run through and copy and paste to another table and on and
on...

This may seem silly but I have what is apparently is a very complex set of
queries that I've had to divide into smaller steps so it will run on my
computer with MS Access. I understand the only other way is to purchase SQL
or other high end database program -this would be not for me at this time.
 
David,

Maybe your whole approach is sub-optimal, based on the assumption that
Access can't do what you want in a more efficient way. If you cared to
explain *what* you are trying to do - I mean the big picture, not just a
small step of the process you are currently working on, perhaps you could be
pointed to a more efficient direction... because what you describe sounds
indeed strange.

Nikos
 
Well I'll try to explain in a nutshell.

The "Big picture" is rating a team based on performance against opponents
and their performance against opponents, opponents , and their performance
against opponents, opponents, opponents, and their performance against
opponents, opponents, opponents, opponents.

To do this I take the average margin a victory against direct opponents
times a factor (determined elsewhere) + the average margin of victory that
their opponents have against their own opponents times another factor and on
and on. Each winning margin is adjusted for by being a home or road game.
The results of this process will give a ranking of each team - say this is
the “Intitial Power Rankingâ€.

The Intitial Power Ranking is then used to compare the actual game results
to the difference in the intitial power ranking of the two teams in a
particular game and for every other game played by every other team in the
season (adjusting for home and away teams). The average residual of the
predicted results against the actual results will then be added to the Power
Ranking to get a new power ranking for each team.
This step is run in multi-iterations until the results merge (about 10
cycles). There are about 10,000 games played each season so it does become
complex and difficult for my computer to run in a single step. (Access Query
Response: Query is to Complex)

thanks for any help, (maybe I should just let them play the game on the
field).
 
David,

I regret having made you write all this, it looks really scary! To be honest
it's so complicated it's hard to picture the process, so I'll just try to
answer your initial question, without questioning its reason any further.

Assuming you have a saved query called qrySource, and want to put the
records returned in table tblDestination, the code would be something like:

strSQL = "DELETE * FROM tblDestination"
CurrentDb.Execute strSQL, dbFailOnError
strSQL = "INSERT INTO tblDestination SELECT qrySource.* FROM qrySource"
CurrentDb.Execute strSQL, dbFailOnError

This assumes the table already exists.

HTH,
Nikos
 
Thanks for help. But I'm not quite there yet.
My Event procedure now is wrtten as:

Private Sub Run_Query_Click()
On Error GoTo Err_Run_Query_Click


DoCmd.OpenQuery "111 Avg Adjusted Margin", acNormal, acEdit
DoCmd.OpenTable "111 Avg Adjusted Margin DB", acNormal, acEdit

strSQL = "DELETE * FROM 111 Avg Adjusted Margin DB"
CurrentDb.Execute strSQL, dbFailOnError

strSQL = "INSERT INTO 111 Avg Adjusted Margin DB SELECT 111 Avg Adjusted
Margin.* FROM 111 Avg Adjusted Margin"
CurrentDb.Execute strSQL, dbFailOnError


Exit_Run_Query_Click:
Exit Sub

Err_Run_Query_Click:
MsgBox Err.Description
Resume Exit_Run_Query_Click

End Sub
Err_Run_Query_Click:
MsgBox Err.Description
Resume Exit_Run_Query_Click

End Sub


But this ends with a error message : syntac error in FROM clause

111 Avg Adjusted Margin DB = tblDestination
111 Avg Adjusted Margin = qrySource
I'm not sure if I need the Docmd. statements or not.
 
David said:
Thanks for help. But I'm not quite there yet.
My Event procedure now is wrtten as:

Private Sub Run_Query_Click()
On Error GoTo Err_Run_Query_Click


DoCmd.OpenQuery "111 Avg Adjusted Margin", acNormal, acEdit
DoCmd.OpenTable "111 Avg Adjusted Margin DB", acNormal, acEdit
Click to expand...

Aw, object names with spaces. Put them inside [], especially within SQL
statements. Like
strSQL = "DELETE * FROM [111 Avg Adjusted Margin DB]"
CurrentDb.Execute strSQL, dbFailOnError
Click to expand...

etc

Or consider renaming the objects (as I often do)
 
Well, you were only half a step away!

The spaces in the object names are definitely an issue here, and Bas.s
suggestuion is exactly what tyou need to do. Also, it's better to not open
the table/query before you run your action queries.

Nikos

Bas Cost Budde said:
David said:
Thanks for help. But I'm not quite there yet.
My Event procedure now is wrtten as:

Private Sub Run_Query_Click()
On Error GoTo Err_Run_Query_Click


DoCmd.OpenQuery "111 Avg Adjusted Margin", acNormal, acEdit
DoCmd.OpenTable "111 Avg Adjusted Margin DB", acNormal, acEdit
Click to expand...

Aw, object names with spaces. Put them inside [], especially within SQL
statements. Like
strSQL = "DELETE * FROM [111 Avg Adjusted Margin DB]"
CurrentDb.Execute strSQL, dbFailOnError
Click to expand...

etc

Or consider renaming the objects (as I often do)

--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html
I prefer human mail above automated so in my address
replace the queue with a tea
Click to expand...
 
Nikos said:
Well, you were only half a step away!

The spaces in the object names are definitely an issue here, and Bas.s
suggestuion is exactly what tyou need to do. Also, it's better to not open
the table/query before you run your action queries.
Click to expand...

I don't think it is necessary at all. If you just want to execute a
couple of SQL statements, there is no need for the table to be open in a
window.
 
Thanks, works like a charm.

Nikos Yannacopoulos said:
Well, you were only half a step away!

The spaces in the object names are definitely an issue here, and Bas.s
suggestuion is exactly what tyou need to do. Also, it's better to not open
the table/query before you run your action queries.

Nikos

Bas Cost Budde said:
David said:
Thanks for help. But I'm not quite there yet.
My Event procedure now is wrtten as:

Private Sub Run_Query_Click()
On Error GoTo Err_Run_Query_Click


DoCmd.OpenQuery "111 Avg Adjusted Margin", acNormal, acEdit
DoCmd.OpenTable "111 Avg Adjusted Margin DB", acNormal, acEdit
Click to expand...

Aw, object names with spaces. Put them inside [], especially within SQL
statements. Like
strSQL = "DELETE * FROM [111 Avg Adjusted Margin DB]"
CurrentDb.Execute strSQL, dbFailOnError
Click to expand...

etc

Or consider renaming the objects (as I often do)

--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html
I prefer human mail above automated so in my address
replace the queue with a tea
Click to expand...
Click to expand...
 
Back
Top