Prev: Grand Total Time
Next: Crosstab Query For Fiscal Year Totals
From: kevin on 26 Feb 2010 12:26
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?
From: Duane Hookom on 26 Feb 2010 12:47
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.

--
Duane Hookom
Microsoft Access MVP


"kevin" wrote:

> 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?
From: John Spencer on 26 Feb 2010 13:06
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

kevin wrote:
> 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?
From: John W. Vinson on 26 Feb 2010 14:37
On Fri, 26 Feb 2010 09:26:04 -0800, kevin <kevin(a)discussions.microsoft.com>
wrote:

>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).
--

John W. Vinson [MVP]
 | 
Pages: 1
Prev: Grand Total Time
Next: Crosstab Query For Fiscal Year Totals