Creating multiple rows of data from a Quantity value in a table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,

I am looking for a simple way to create multiple unique rows of data based
on a quantity value in a table;

Example

Reference table -

ItemID Desc Quantity
-------- ------ ---------
125 Part X2 5
126 SysXYZ 3

Resulting Dataset -

ItemID Desc UnitNum
-------- ------ ---------
125 Part X2 1
125 Part X2 2
125 Part X2 3
125 Part X2 4
125 Part X2 5
126 SysXYZ 1
126 SysXYZ 2
126 SysXYZ 3

This needs to be accomplished in Access on Office 2003 and I am trying to
avoid using too much VBA, although that would be acceptable also.

Thanks for any help.

Doug
 
The simple method is to create a table of numbers tblNums with a numeric
type field [Num] and add records with values 1 through your maximum
quantity.
Add this table to your query and set the criteria under the [Num] field to
<=[Quantity]
The Num field will be your UnitNum.
 
That is helpful, but is there a way to do it with a SQL statement. Ideally I
would like this to be in the form of a query.

Duane Hookom said:
The simple method is to create a table of numbers tblNums with a numeric
type field [Num] and add records with values 1 through your maximum
quantity.
Add this table to your query and set the criteria under the [Num] field to
<=[Quantity]
The Num field will be your UnitNum.

--
Duane Hookom
MS Access MVP
--

Doug R said:
Hello,

I am looking for a simple way to create multiple unique rows of data based
on a quantity value in a table;

Example

Reference table -

ItemID Desc Quantity
-------- ------ ---------
125 Part X2 5
126 SysXYZ 3

Resulting Dataset -

ItemID Desc UnitNum
-------- ------ ---------
125 Part X2 1
125 Part X2 2
125 Part X2 3
125 Part X2 4
125 Part X2 5
126 SysXYZ 1
126 SysXYZ 2
126 SysXYZ 3

This needs to be accomplished in Access on Office 2003 and I am trying to
avoid using too much VBA, although that would be acceptable also.

Thanks for any help.

Doug
Click to expand...
Click to expand...
 
Sorry about that last post, I misunderstood what you were saying.

So I would just create a table of numbers 1 through X where X is greater
that the largest expect value of QTY. I would then do a join of the two
tables where [Num] <= [QTY] for the particular part number.

That is definately a novel approach to the problem. Thank you.

Could you possibly give me an example of the SQL query to get me going in
the right direction.

-Doug



Duane Hookom said:
The simple method is to create a table of numbers tblNums with a numeric
type field [Num] and add records with values 1 through your maximum
quantity.
Add this table to your query and set the criteria under the [Num] field to
<=[Quantity]
The Num field will be your UnitNum.

--
Duane Hookom
MS Access MVP
--

Doug R said:
Hello,

I am looking for a simple way to create multiple unique rows of data based
on a quantity value in a table;

Example

Reference table -

ItemID Desc Quantity
-------- ------ ---------
125 Part X2 5
126 SysXYZ 3

Resulting Dataset -

ItemID Desc UnitNum
-------- ------ ---------
125 Part X2 1
125 Part X2 2
125 Part X2 3
125 Part X2 4
125 Part X2 5
126 SysXYZ 1
126 SysXYZ 2
126 SysXYZ 3

This needs to be accomplished in Access on Office 2003 and I am trying to
avoid using too much VBA, although that would be acceptable also.

Thanks for any help.

Doug
Click to expand...
Click to expand...
 
SELECT ItemID, Desc, Num As UnitNum
FROM tblReference, tblNums
WHERE Num<=Quantity;

--
Duane Hookom
MS Access MVP


Doug R said:
Sorry about that last post, I misunderstood what you were saying.

So I would just create a table of numbers 1 through X where X is greater
that the largest expect value of QTY. I would then do a join of the two
tables where [Num] <= [QTY] for the particular part number.

That is definately a novel approach to the problem. Thank you.

Could you possibly give me an example of the SQL query to get me going in
the right direction.

-Doug



Duane Hookom said:
The simple method is to create a table of numbers tblNums with a numeric
type field [Num] and add records with values 1 through your maximum
quantity.
Add this table to your query and set the criteria under the [Num] field to
<=[Quantity]
The Num field will be your UnitNum.

--
Duane Hookom
MS Access MVP
--

Doug R said:
Hello,

I am looking for a simple way to create multiple unique rows of data based
on a quantity value in a table;

Example

Reference table -

ItemID Desc Quantity
-------- ------ ---------
125 Part X2 5
126 SysXYZ 3

Resulting Dataset -

ItemID Desc UnitNum
-------- ------ ---------
125 Part X2 1
125 Part X2 2
125 Part X2 3
125 Part X2 4
125 Part X2 5
126 SysXYZ 1
126 SysXYZ 2
126 SysXYZ 3

This needs to be accomplished in Access on Office 2003 and I am trying to
avoid using too much VBA, although that would be acceptable also.

Thanks for any help.

Doug
Click to expand...
Click to expand...
Click to expand...
 
Back
Top