How do i loop a query?

[interim]

I am not experienced in Access, and so i have created a query through the design view.
I have created a query called "EMAIL-QUERY", which uses several other queries when it is run.
One of the other queries it uses, which i called "q1", asks for the user to input a customer ID.

So naturally, when i run EMAIL-QUERY, the user is posed with this question about the customer id.

What i need to do is run EMAIL-QUERY as many times as there are customers, each time entering the customer ID into the query, then putting each result somewhere each time (probably in a table) where i can run a query later to retrieve ALL of the records that this 'looped' procedure produced.

Extra Info: I had been asked to create this query, but assumed that each time, a user would just choose to enter the customer number, to retrive the desired result.
Instead, i have found that i must create a query that produces ALL of the results, so that Microsoft Word's Mail-Merge can automatically traverse through each record.
I tried removing the [customer id] input box that appears each time the query is run, so that Access would automatically run the query for ALL customer IDs, however this does not work, due to the way i have made the many queries that are used to get the final EMAIL-QUERY to work.

Does anyone know how i can loop through such a query and store the result each time?

thankyou.
your help is appreciated.

 

[interim]

I may have scared a few people off by writing too much.
But basically i need the code (using whatever variable names etc you want) for the following pseudocode.
(Assume the main query is called EMAIL-QUERY, and one of the queries it uses, which takes the customerID to retrieve the result, is called QUERY1).

Note: The customerID below has to be passed to QUERY1.

count = min(customers.customerID) //ie. the first customer
Do
Run EMAIL-QUERY, but first giving QUERY1 the value 'count' as the customerID number.
Put the result of the query into a table called TblResult.
Increase count by 1 (or in case the customerIDs are not fully sequential eg. 1,3,4,5....., set 'count' to the customer with the next-highest customerID).
Exit if 'count' is greater than max(customers.customerID) //ie. greater than the last customer.
Loop

ALSO: Do i just put this code in a Module?

Thats it.
thanks for your help.

 

[MichaelRed]

Look into setting up the execution of the process by instantiating the query as a querydef object with the parameter supplied through the code. Basically, a query to retrieve the variable (Query1)?. Use this query as the control mechanisim to obtain the [CustId] to enter into the querydef as the parameter. Use a loop (while not rs.eof ... wend) where the rs is the custid ...


MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[interim]

Correction above, as i discovered i can set EMAIL-QUERY to automatically pass the customerID to QUERY1, so we just have to pass the customerID to EMAIL-QUERY, not QUERY1:

Read the line:
Run EMAIL-QUERY, but first giving QUERY1 the value 'count' as the customerID number.

As:
Run EMAIL-QUERY, and give it the value 'count' as the customerID number.

 

[interim]

Thanks Michael.

I see what you are getting at. But i should make it clear that i have never written any code in Access.

Where do i put this code? in a module?

and would you or someone be able to give me a quick sample of the code i should write? or somewhere i can go to learn which commands to use? thanks.

 

[MichaelRed]

I'm not in a good position to generate code for you. MS Access help does have examples of how to use all of the objects/methods mentioned. Look them up and combine the features as necessary. I believe the outline previously given will 'work', you just need to fill in the procedure along those lines. Get it "close" and re-post when you have specific questions or sample code and errors which can be related to some sample data.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over