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

[Doug R]

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

 

[Tom Wickerath]

Hi Doug,

Basically, you are looking for a method to convert a crosstab type data set into its source data.
As far as I know, this can only be done using VBA code. I have a sample that I would be happy to
send to you, if you send me a personal e-mail request with a valid return address. My e-mail
address is pretty easy to figure out. Whatever you do, please do not post your real e-mail
address to the newsgroup.

Tom
___________________________________________


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

 

[John Vinson]

On Fri, 15 Oct 2004 11:53:03 -0700, Doug R <Doug

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

One way is to use an auxiliary table. I'll usually add a table named
Num, with a single Integer field N, filled with values from 1 to 10000
(it's cheap; and you can fill it using Excel fill-down and importing).

You can create a non-equijoin query between Reference and N:

SELECT Reference.ItemID, Reference.Desc, Num.N
FROM Reference INNER JOIN Num
ON Num.N <= Reference.Quantity;


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps