SQL advanced -> Create tally table in MS-Access

In this example we will create tally table ("Numbers" table) of 100 sequential numbers using two table of 10 records each and only one query. First create tables and insert initial data:

CREATE TABLE num_tb
(num Integer,
CONSTRAINT pk_num PRIMARY KEY (num));

CREATE TABLE k_tb
(k Integer,
CONSTRAINT pk_k PRIMARY KEY (k));

num_tb will store values:
num
0
1
2
3
4
5
6
7
8
9
10

k_tb will store values:
k
2.18
3.33
3.69
3.89
4.17
4.52
4.58
4.63
4.71
4.83

Following query creates tally table of 100 sequential numbers from 1 to 100:

SELECT t1.n1 INTO num_seq
FROM
(

SELECT c.no AS n1
FROM
(
  SELECT (a.num + b.num) AS no
  FROM num_tb AS a, num_tb AS b
   UNION ALL
  SELECT (a.num * b.num) AS no
  FROM num_tb AS a, num_tb AS b
   UNION ALL
  SELECT (a.num + b.num) AS no
  FROM num_tb AS a, num_tb AS b
   UNION ALL
  SELECT (a.num * b.num) AS no
  FROM num_tb AS a, num_tb AS b
) AS c
GROUP BY c.no

  UNION ALL

SELECT (c.no * d.num) AS n1
FROM
(
  SELECT (a.num + b.num) AS no
  FROM num_tb AS a, num_tb AS b
) AS c, num_tb AS d

  UNION ALL

SELECT Round(c.no * d.k) AS n1
FROM
(
  SELECT (a.num + b.num) AS no
  FROM num_tb AS a, num_tb AS b
) AS c, k_tb AS d

) AS t1
WHERE (((t1.n1) BETWEEN 1 AND 100))
GROUP BY t1.n1
ORDER BY t1.n1;



sqlexamples.info