wrong query results

  • Thread starter Thread starter Rudy W.
  • Start date Start date
R

Rudy W.

To make complex statistics I fill up tables using queries
(DoCmd.RunSQL strSQL). In a second step I use these
tables to start counting and filling up the result
tables, using queries again.
The amounts I found in my results were always too big
until I ran my program step by step, then I got the
correct amounts.
It looks as if the amounts I get from a query are beeing
added or overwritten by the ones of the next query...
What could have happend?
 
It looks as if the amounts I get from a query are beeing
added or overwritten by the ones of the next query...
What could have happend?
Click to expand...

Well, what could have happened is that you set up the queries
incorrectly; it's very easy to do so!

But without more information, it's more than a bit difficult to guess
how. My only speculation is that you have tables or queries joined so
that what you think is one composite record is actually multiple
copies of the record, caused by a one-to-many join; if the field being
summed is on the "many" side it will be correct, but if it's on the
"one" side it will be repeated as many times as there are related
records, throwing off your sums.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
I test run the program, using F8 step by step. In this
case my results are correct.
When I run them in ones, without stops, I don't get the
right results.
The test step by step proves that my queries are correct.
 
Rudy said:
To make complex statistics I fill up tables using queries
(DoCmd.RunSQL strSQL). In a second step I use these
tables to start counting and filling up the result
tables, using queries again.
The amounts I found in my results were always too big
until I ran my program step by step, then I got the
correct amounts.
It looks as if the amounts I get from a query are beeing
added or overwritten by the ones of the next query...
What could have happend?
Click to expand...

I can imagine, since queries execute in a separate thread, that your
code starts a query that depends on the previous having finished, before
it actually has finished.

Does it help if you use a QueryDef object? Such like

set qd=currentdb.querydefs(strsql)
qd.execute
'do something maybe with qd.recordsaffected
 
Back
Top