Question: I have the following table

TABLE A
=======

```
ID | QUANTITY
------------
1  | 3
2  | 2

```

What I need is

TABLE B
=======

```
ID | Ref No.
------------
1  | MyRef1
1  | MyRef2
1  | MyRef3
2  | AnotherRef1
2  | AnotherRef2

```

i.e. I need to generate Table B with the same number of rows as the quantity in A with an ascending ref no. on each row.

I can do it with cursors or UDFs but is there a more graceful solution?

Answer: 
I'll assume

1. MyRef etc is a column in TableA
2. You have a [numbers](http://dataeducation.com/you-require-a-numbers-table/) table

Something like:

```
SELECT * INTO #TableA
FROM
 (
    SELECT  1 AS ID, 3 AS QUANTITY, 'MyRef' AS refColumn
    UNION ALL
    SELECT 2, 2, 'AnotherRef'
) T

;WITH Nbrs ( Number ) AS (
    SELECT 1 UNION ALL
    SELECT 1 + Number FROM Nbrs WHERE Number < 99
)
SELECT
   A.ID, A.refColumn + CAST(N.Number AS varchar(10))
FROM
   #TableA A
   JOIN
   Nbrs N ON N.Number <= A.QUANTITY

```
A Numbers table solution will work in MS Access as long as the number of records in tblNumbers exceeds the maximum of Quantity values found in TableA:

`SELECT TableA.ID, TableA.Quantity, tblNumbers.RecNum
 FROM TableA LEFT JOIN tblNumbers ON tblNumbers.RecNum <= TableA.Quantity
 ORDER BY TableA.ID, tblNumbers.RecNum;`

NOTE: tblNumbers.RecNum is a Long Integer starting with row 1 = 1, row 2 = 2, etc.