HowTo Create a Sequential Query, in Loop?
Hi,
I've got a problem.
I'm doing an update query on a table.
1 for every month. (...)
Now I would like to use a counter, so I do not have to run 12 queries in a macro, but 1 query with dynamic field names. But I do not get the thing to work.
Current situation:
Table with table names '1' to '12', linked by customer number to a table with values that have a monthnumber in it's row.
Query:
- month number 1: update value in column '1'.
- month number 2: update value in column '2'.
etc.
SQL (repeated 12 times, 1 for every month):
UPDATE [Calculations 2005] INNER JOIN [tbl_BOBCAT_Retails by Dealer by Month 2005] ON [Calculations 2005].[Cust No] = [tbl_BOBCAT_Retails by Dealer by Month 2005].[Dealer No] SET [Calculations 2005].[5] = [tbl_BOBCAT_Retails by Dealer by Month 2005]![Total Retails]
WHERE ((([tbl_BOBCAT_Retails by Dealer by Month 2005].Month)=5));
Now, would it be possible to use a LoopCounter (1-12), instead of 12 queries?
I do have a working Counter from support.microsoft.com/kb/90815/en-us (new here, so I cannot post links yet), I just don't know how to include it into my query.
Thanks in advance for any help,
Timo
Hi,
I've got a problem.
I'm doing an update query on a table.
1 for every month. (...)
Now I would like to use a counter, so I do not have to run 12 queries in a macro, but 1 query with dynamic field names. But I do not get the thing to work.
Current situation:
Table with table names '1' to '12', linked by customer number to a table with values that have a monthnumber in it's row.
Query:
- month number 1: update value in column '1'.
- month number 2: update value in column '2'.
etc.
SQL (repeated 12 times, 1 for every month):
UPDATE [Calculations 2005] INNER JOIN [tbl_BOBCAT_Retails by Dealer by Month 2005] ON [Calculations 2005].[Cust No] = [tbl_BOBCAT_Retails by Dealer by Month 2005].[Dealer No] SET [Calculations 2005].[5] = [tbl_BOBCAT_Retails by Dealer by Month 2005]![Total Retails]
WHERE ((([tbl_BOBCAT_Retails by Dealer by Month 2005].Month)=5));
Now, would it be possible to use a LoopCounter (1-12), instead of 12 queries?
I do have a working Counter from support.microsoft.com/kb/90815/en-us (new here, so I cannot post links yet), I just don't know how to include it into my query.
Thanks in advance for any help,
Timo
Last edited: