Create duplicate records using a query

[Alquimia]

I would like to create new rows based on a column field. For example,
I have a table like with two columns like this:

ID Value
1 3
2 2

and I'd like to transform it into:

ID Value
1 1
1 1
1 1
2 1
2 1

I know I could do this using VBA but can't find an answer to do it
using a query. Any ideas?

 

[vanderghast]

Can be done with a driver table: have a table Iotas, one field, its primary
key, Iota, with values from, say, 1 to 999 (generate the values in Excel
and import the sequence into the table)



SELECT id, 1
FROM tableName INNER JOIN iotas
ON tableName.value >= iotas.iota



would do. Can also give a try to:


SELECT id, iota
FROM tableName INNER JOIN iotas
ON tableName.value >= iotas.iota



where the new value will be a sequence rather than always the constant 1.


Vanderghast, Access MVP

 

[Krzysztof Naworyta]

Juzer vanderghast <vanderghast@com> napisa³
| Can be done with a driver table: have a table Iotas, one field, its
| primary key, Iota, with values from, say, 1 to 999 (generate the
| values in Excel and import the sequence into the table)
|
|
|
| SELECT id, 1
| FROM tableName INNER JOIN iotas
| ON tableName.value >= iotas.iota

There's no need of using Inner Join with not equal comparison (as this has
no
representation in QBE)

We can just use simple "cross join":

SELECT id, 1
FROM
tableName, iotas
WHERE
tableName.value >= iotas.iota


KN

 

[Alquimia]

Juzer vanderghast <vanderghast@com> napisa³
| Can be done with a driver table: have a table Iotas, one field, its
| primary key, Iota, with values from, say, 1 to 999  (generate the
| values in Excel and import the sequence into the table)
|
|
|
| SELECT id, 1
| FROM tableName INNER JOIN iotas
|    ON tableName.value >= iotas.iota

There's no need of using Inner Join with not equal comparison (as this has
no
representation in QBE)

We can just use simple "cross join":

SELECT id, 1
FROM
   tableName, iotas
WHERE
   tableName.value >= iotas.iota

KN

It worked perfectly. Thank you both!! Just to raise the bar, Is
there any way to do it without an additional table?

 

[Krzysztof Naworyta]

Juzer Alquimia <[email protected]> napisal


(...)
| It worked perfectly. Thank you both!! Just to raise the bar, Is
| there any way to do it without an additional table?

"Additional" table that has e.g. 1000 records is the cheapest way to do
such thing