Append and Loop Code - microsoft.public.access.modulesdaovba

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!

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" <JJ@discussions.microsoft.com> wrote in message news:61582C6E-2914-42B6-9FAF-2CF051BAF1DB@microsoft.com... >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! >

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" wrote: > 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" <JJ@discussions.microsoft.com> wrote in message > news:61582C6E-2914-42B6-9FAF-2CF051BAF1DB@microsoft.com... > >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! > > > > > . >

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. "JJ" <JJ@discussions.microsoft.com> wrote in message news:F73F782B-99F4-416D-9994-72B5F46CF5D5@microsoft.com... > 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" wrote: > >> 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" <JJ@discussions.microsoft.com> wrote in message >> news:61582C6E-2914-42B6-9FAF-2CF051BAF1DB@microsoft.com... >> >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! >> > >> >> >> . >>

Similiar Articles:

Append and Loop Code - microsoft.public.access.modulesdaovba ...
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...

Form Code to append results from query to table - microsoft.public ...
Append and Loop Code - microsoft.public.access.modulesdaovba ..... to create a code that will go through a table of criteria. It will run a query for a row, append ...

Clear form after append query - microsoft.public.access ...
Append and Loop Code - microsoft.public.access.modulesdaovba ... Clear form after append query - microsoft.public.access ... Append and Loop Code - microsoft.public.access ...

Append if Not Exist - microsoft.public.access.queries
Append and Loop Code - microsoft.public.access.modulesdaovba ... Append if Not Exist - microsoft.public.access.queries VBA code to loop append query fails I suspect that ...

Append to Table with Auto Number - microsoft.public.access.queries ...
Hi All, I need to paste or use an append query to append a number of rows to a table that contains an autonumber field. Attempts so far have failed ...

Convertto-HTML appending more information - microsoft.public ...
Append and Loop Code - microsoft.public.access.modulesdaovba ... Append and Loop Code - microsoft.public.access.modulesdaovba ... Convertto-HTML appending more information ...

Access 2003: Loop Through Records - microsoft.public.access ...
Append and Loop Code - microsoft.public.access.modulesdaovba ... Access 2003: Loop Through Records - microsoft.public.access ... Append and Loop Code - microsoft.public ...

VBA - concatenate with a loop - microsoft.public.excel.programming ...
Append and Loop Code - microsoft.public.access.modulesdaovba ... Append if Not Exist - microsoft.public.access.queries VBA code to loop append ...

using vba to loop an insert query until all records in query are d ...
Append and Loop Code - microsoft.public.access.modulesdaovba ... using vba to loop an insert query until all records in query are d ... Append and Loop Code - microsoft ...

TransferText - Append to existing text file - microsoft.public ...
Alternatively, you could use your own code to open a recordset on the table/query, open the target file for append, and loop through the recordset, formatting and ...

VBA code to loop append query fails
I suspect that the problem is that the NEW record has not been saved. Since it has not been saved it does not exist and if you have relational

VBA code to loop append query fails DataBase
Microsoft Server 2008 DuckRace database Main table is Contestant with PK ContestantID Child table is Purchases with PK PurchaseID and FK is ContestantID I have an ...

Append to nothing « Code and Culture
For a long time, I would write one set of code for the first instance, then a second set for the append loop. This is a pain in the ass as it makes the code ...

Using Loops to Repeat Code
Worksheets.Add ActiveSheet.Name = "NewSheet" End Sub The statement For Each ... As mentioned earlier, selecting cells in a code loop is an inefficient way of working ...

7/26/2012 5:27:39 PM