wrong query results

[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?

 

[John Vinson]

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?

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

 

[Rudy W.]

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.

 

[Bas Cost Budde]

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?

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