How to create duplicate rows in Access or Excel ? 1

[NadCan]

Hi, I have a table with the following fields:

ID Item Name Item Code Print Quantity Label Quantity
1 Global Equity 01-03-060 677 30
2 Select Growth 01-05-026 823 40

I need to generate 30 duplicate lines for ID:1 and 40 duplicate lines for ID:2, automatically.


Please advise how can i do it in MS Access or Excel ?

Many thanks,
nad.

 

[Golom]

Step 1: Create a table called integers with one field called Num (integer). Load it with the integers from 0 to 9.

Step 2: Build a query called qryInt2

Select (I1.Num * 10 + I2.Num) As myInt
From Integers As I1, Integers As I2

Step 3: Then

Select A.ID, A.[Item Name], A.[Print Quality], A.[Label Quantity]

From myTable As A, qryInt2 As I2

Where A.ID = 1 AND I2.myInt <= 30

UNION

Select A.ID, A.[Item Name], A.[Print Quality], A.[Label Quantity]

From myTable As A, qryInt2 As I2

Where A.ID = 2 AND I2.myInt <= 40

Order By 1

 

[Golom]

Sorry ... that should be

Select A.ID, A.[Item Name], A.[Print Quality], A.[Label Quantity]

From myTable As A, qryInt2 As I2

Where A.ID = 1 AND I2.myInt [red]BETWEEN 1 AND 30[/red]

UNION [red]ALL[/red]

Select A.ID, A.[Item Name], A.[Print Quality], A.[Label Quantity]

From myTable As A, qryInt2 As I2

Where A.ID = 2 AND I2.myInt [red]BETWEEN 1 AND 40[/red]

Order By 1

 

[NadCan]

Hi Golom,

It worked GREAT ... !!! ... Many thanks !!!

One last thing, since i'd be getting a same data (mytable) every week with different [Label Quantity] figures.
For instance i'll receive a revised table next week, like below:

ID Item Name Item Code Print Quantity Label Quantity
1 Global Equity 01-03-060 677 30
2 Select Growth 01-05-026 823 40
3 Global Equity 01-03-032 1000 50
4 Select Growth 01-03-027 9238 60

How can i automate in Access / or maybe VBA to read it from [Label Quantity] field and execute the union query ? Or will i have to update the BETWEEN clause, manually (everytime i get a revised table with new [Label Quantity] figures. ?

Thanks again !
nad.

 

[PWise]

I would Create a table called integers with one field called Num (integer). Load it with the integers from 1 to ....

run this query

Select ID,[Item Name],[Item Code],[Print Quantity],   [Label Quantity]
from items
 INNER JOIN integers ON  integers .num<=items.[Label Quantity]

 

[NadCan]

Great pwise, it worked .. !
Many Thanks,
nad.

 

[Golom]

I think you want (with apologies to pwise)

Select ID,[Item Name],[Item Code],[Print Quantity],   [Label Quantity]
from items
 INNER JOIN [red]qryInt2[/red] ON  [red]qryInt2.myInt < [/red]items.[Label Quantity]

<= will actually give you one more label than you want because values from 0 to items.[Label Quantity] will be included.

You want to use qryInt2 rather than integers because the Integers table has only 10 values in it (0-9).

 

[NadCan]

yep Golom, that's excatly what i needed ... Thanks guys !

 

[PWise]

with apologies to pwise Golom

I would Create a table called integers with one field called Num (integer). Load it with the integers from 1 to ....

run this query

[CODE JetSql]
Select ID,[Item Name],[Item Code],[Print Quantity], [Label Quantity]
from items
INNER JOIN integers ON integers .num<=items.[Label Quantity][/code]

I think running this query with my integers table will return the desired results

 

[Golom]

pwise

Sorry. You're right.

I was just refering back to my original post where the integers table was 0-9 and the query then built a temporary table from 0-99. Saves me the trouble of having to preload the integers table with enough numbers to make sure I have enough to satisfy the maximum value in the user's [Label Quantity] field.

 

[PHV]

Saves me the trouble of having to preload the integers table
It's so easy with Excel ...
What happens with your suggestion if [Label Quantity] > 99 ?

 

[Golom]

PHV

A qryInt3?

Select (I1.Num * 100 + I2.Num * 10 + I3.Num) As myInt
From Integers As I1, Integers As I2, Integers As I3

 

[PWise]

This is the code iuse to create my numbers tables
The Database that i am working on @ this moment has a numbers table with 999,999 records

Sub CreateDigitTable(MaxNumber)
Dim Mydb As Database
Dim Num As Long
Set Mydb = CurrentDb
Mydb.Execute "Create Table Digits1 (Digitid INTEGER CONSTRAINT Digitid PRIMARY KEY)"
For Num = 1 To MaxNumber
    Mydb.Execute "Insert Into Digits (Digitid) Values (" & Num & ")"
Next
End Sub