Append and Loop Code

[JJ]

I am trying to create a code that will go through a table of criteria. It
will run a query for a row, append that query to an output table, and then
move down a row and do the same thing over until there are no rows left.

Example of Criteria_Table:

Fields are - AutoID: Name : Level : Location

456 : Joe : Level 1 : East
524 : Sam : Level 3 : West
789 : Bob : Level 10 : North


Example of Criteria_Qry (Criteria_Table linked Position_Table)

Fields are - Name : Level : Location : Position_Num


Example of Ouput_Table

Fields are - Name : Level : Location : Position_Num

Joe : Level 1 : East : 2
Sam : Level 3 : West : 6
Bob : Level 10 : North : 9

Any suggestions would be appreciated. Thanks!

 

[Jeff Boyce]

Not clear what you intend by your expression "append that query to an output
table". Can I assume that you don't wish to have the query itself (i.e.,
the SQL statement), but the results of the query?

So, is this a little like asking Access to "find all the red ones" (and save
them in a new bucket), then "find all the green ones" (and save them in the
same bucket), then "find all ...?

If so, why are you saving them into an output table. Are you intending to
then use the found records in some kind of report? If so, don't limit
yourself! Use a query that pulls together all those separate query results
(this is called a UNION query), and use THAT as your source for your report.

.... or maybe I'm reading too much into what you described!

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[JJ]

Yes Jeff,

Good call. I would like to use a Union Query if possible. The end results
will be used for a report. Basically the criteria table will have a different
set of criteria on each row of the table. I need to keep quering the main
table with each row of the criteria table until all the rows are accounted
for.

 

[Jeff Boyce]

OK, I can see that if you'll be working through a table of various selection
criteria, it might be tough to preserve the results of each row's selected
records without having someplace to stuff them!

A couple ideas come to mind -- first, yours. A temporary table to collect
all those before using the data in that table for your report. A possible
alternative might be to create an Array and write the found data elements
into the array, then use the array as your recordset for the report. A temp
table would be much easier if you haven't worked in the code that much.

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.