Creating duplicate rows

[kevin]

I need to set up a query that will look at a table and for each row that has
a field with a quantity >1 (eg. 10), will create a row for each quantity
instead (eg. 10 rows). Is this possible?

 

[Duane Hookom]

I would create a table [tblNums] with a single numeric field [Num] and values
from 1 to the maximum quantity.

Then create a query of your current table and tblNums. Set the criteria
under the [Num] field to:
<=[Quantity]
This will create 10 records where the quantity is 10.

 

[John Spencer]

Yes, it is possible. You will need an auxiliary table with a number field
that goes from 1 to largest number of repeating rows.

NumberTable
NumberField (values 1 to 100 or whatever your maximum quantity is)

SELECT YourTable.Field1, YourTable.Field2, NumberTable.NumberField
FROM YourTable, NumberTable
WHERE NumberTable.NumberField <= YourTable.Quantity

That should return n rows for you with the rows numbered from 1 to n for each
group.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[John W. Vinson]

I need to set up a query that will look at a table and for each row that has
a field with a quantity >1 (eg. 10), will create a row for each quantity
instead (eg. 10 rows). Is this possible?

You can do so with the aid of an auxiliary table. I'll routinely include a
table named Num with one long integer field N, filled with values from 0
through 10000 or so (you can use Excel... Insert... Fill Series and copy and
paste to fill it).

A Cartesian join query will then create your duplicates for you:

SELECT yourtable.this, yourtable.that, Num.N+1 AS Seq
FROM yourtable, Num
WHERE Num.N < yourtable.quantity;

You can start N at 1, omit the +1, and use <= instead of <, but it's often
handy to have the zero included (e.g. to get every date of a range of dates
using DateAdd).