How can I select multiple copies of the same row?

Question

I have a table in MS Access with rows which have a column called "repeat"

I want to SELECT all the rows, duplicated by their "repeat" column value.

For example, if repeat is 4, then I should return 4 rows of the same values. If repeat is 1, then I should return only one row.

This is very similar to this answer:

https://stackoverflow.com/a/6608143

Except I need a solution for MS Access.

Answer 1

First create a "Numbers" table and fill it with numbers from 1 to 1000 (or up to whatever value the "Repeat" column can have):

CREATE TABLE Numbers
  ( i INT NOT NULL PRIMARY KEY
  ) ;

INSERT INTO Numbers 
  (i)
VALUES
  (1), (2), ..., (1000) ;

then you can use this:

SELECT t.*
FROM TableX AS t
  JOIN
     Numbers AS n
       ON n.i <= t.repeat ;