Create Macro to Iterate Query?

[Guest]

Here's my need:

To develop a macro (or equivalent) that will iteratively run an update query
until the number of records to update is zero.

The macro will call the query, then check to see if the number of updated
records is greater than zero. If yes, then the query will run again. If no
then the query will stop.

This wouldn't be too tough except for the fact that this macro will be
called from within an existing macro. The existing macro is going through a
table of queries and running each of them. I want it to be able to pcik up
where it left off when the nested macro stops working. It's a long shot but
worth a try . Thanks for any help. Ruben.

 

[Steve Schapel]

Jose,

Are you able to please give some examples of what these update queries
are doing? I can't quite grasp the concept of an update query running
more than once... I mean, normally I would expect that the query would
update all the records affected, in one hit, and then no need to run it
again.

 

[Guest]

Steve, thanks very much for your reply.

The query in question is being used to "crawl" or "trace" upstream from a
particular point, to see if a certain node exists upstream from the point.
The query is designed to run iteratively because I want it to keep "tracing"
upstream until no nodes exist that meet the criteria.

The query is structured such that there are two references to the same
table, and they are related such that the first table's node ID matches the
second table's upstream node ID (where node ID is not null). If they match,
then the first table's upstream node ID gets the node ID from the second
table.

So the query just keeps updating records that have sequentially longer
"chains" of nodes (E.g., the query will update 1000 records where nodes have
one node upstream from it, then 500 records where there is yet another node
upstream from it--so according to that the table has 500 two-node chains and
500 three-node chains. I hope that makes sense. I don't know if there would
be a way to nest additional queries within this "trace" query so that it
keeps looking upstream for sequentially longer node chains, but that's the
best I've been able to muster thus far. Thanks again for the reply.