How to loop in a query?

  • Thread starter Thread starter Ben
  • Start date Start date
B

Ben

Hi all,

I would like to create a query that would populate a table with a series of
numbers. For example, let’s say I would like to populate the table with a
series of numbers from 1 to 10, how can I write query that would effectively
act as FOR-loop. Where the series can begin at any (positive) number not
necessarily zero or one. Thanks in advance for sharing your thoughts.

Ben
--
 
Hi all,

I would like to create a query that would populate a table with a series of
numbers. For example, let’s say I would like to populate the table with a
series of numbers from 1 to 10, how can I write query that would effectively
act as FOR-loop. Where the series can begin at any (positive) number not
necessarily zero or one. Thanks in advance for sharing your thoughts.

Ben
Click to expand...

You can't, as written.

A query is not procedural, and does not loop.

You can open a Recordset in VBA code and use a FOR loop to fill the table, or
just use Excel fill-series to create a spreadsheet with 65536 rows with values
0-65535 which you can import into a table. It's fairly handy to have such a
table as you can then use it to create any series you like, e.g.

SELECT 1000001+N FROM Num WHERE N <= 10000

will give you a query with values from 1,000,001 through 1,010,000.
 
You can create a table [tbl09] that contains a numeric field [num] and values
0-9. From this table, you can create a query containing any other number. If
you only want the number 0-9, just include a single copy of the table in the
query. If you want up to 99, add to copies of the table to the query.

SELECT [tbl09].[Num]+([tbl09_1].[Num]*10)
FROM tbl09, tbl09 AS tbl09_1
ORDER BY [tbl09].[Num]+([tbl09_1].[Num]*10);

Keep adding tables for each position left of the decimal. To get all number
0 to 999999 use:
SELECT
[tbl09].[Num]+([tbl09_1].[Num]*10)+([tbl09_2].[Num]*100)+([tbl09_3].[Num]*1000)+([tbl09_4].[Num]*10000)+([tbl09_5].[Num]*100000) AS Numbers
FROM tbl09, tbl09 AS tbl09_1, tbl09 AS tbl09_2, tbl09 AS tbl09_3, tbl09 AS
tbl09_4, tbl09 AS tbl09_5
ORDER BY
[tbl09].[Num]+([tbl09_1].[Num]*10)+([tbl09_2].[Num]*100)+([tbl09_3].[Num]*1000)+([tbl09_4].[Num]*10000)+([tbl09_5].[Num]*100000);

You can filter this with values entered into controls on forms referenced in
the criteria of the column.
 
Back
Top