Automatic looping of Query stepping through values

[benw]

I wonder if anyone can help...

I want to run a Query (A) automatically that takes values step by step from the result of another Query (B). Each time it runs I want to create a separate file.

i.e. Query B runs & provides a list of product IDs. Then I want Query A to run using the first of these product IDs as it's main selection criteria. The results will then form a file. Next I want to rerun Query A using the next product ID from Query B's results, etc until all the IDs have been queried.

I was going to try to do this using a macro...

I hope this makes sense.

Is this possible?
If so, how do I get Query A to take a value from Query B?
How do I get it to stop looping when it reaches the end of Query B's results?

Cheers
Benw

 

[cmmrfrds]

You should be able to use a sub query to get what you want below is a basic example.

SELECT Y.stor_id, Y.ord_date
FROM sales Y
WHERE Y.stor_id IN
(SELECT X.stor_id
FROM sales X
where X.stor_id = Y.stor_id)
order by Y.stor_id desc