creating duplicate records based on a number in a field

  • Thread starter Thread starter Christine
  • Start date Start date
C

Christine

I want to run a query to print a barcode as many times as
there is quantity in stock. I don't know how to run the
query to duplicate the barcode as many times as there is
quantity.
 
Christine said:
I want to run a query to print a barcode as many times as
there is quantity in stock. I don't know how to run the
query to duplicate the barcode as many times as there is
quantity.
Click to expand...

Hi Christine,

One solution is to make a table
like the following ("tblCopies")
with one field "Copies"

Copies
1
2
2
3
3
3
4
4
4
4
.......

Then include tblCopies in your
query and join on

mytable.quantity = tblCopies.Copies

This table is easy to construct
using Michel's Iota table
(copied from an earlier posting):

Michel's technique:

*** QUOTE ***
Make the table Iotas:

Make a table Ds with field d, 10 records,
values for d = 0 to 9.

In a make table query,
bring Ds table 4 times so you have
Ds
Ds_1
Ds_2
Ds_3

In a free column type:

Iota: Ds.d + 10*Ds_1.d + 100*Ds_2 + 1000*Ds_3

Generate the table Iotas and make Iota a primary key.
*** UNQUOTE ****

SELECT [Ds].[d]+10*[Ds_1].[d]+100*[Ds_2].[d]
+1000*[Ds_3].[d] AS Iota
INTO Iotas
FROM Ds, Ds AS Ds_1, Ds AS Ds_2, Ds AS Ds_3;


The above would give you a table Iotas with
Iota ranging from 0 to 10,000.

I might think hard about the exact max
number I would need here and either
delete records (max+1) to 10,000 from
Iota (or use a limiting query for Iota in the
following query instead of the table Iota).

DELETE * FROM Iotas
WHERE Iota > SomeMax?

Whatever you decide, the following query

SELECT IotaCopy.Iota AS Copies
INTO tblCopies
FROM Iotas, Iotas AS IotaCopy
WHERE (((IotaCopy.Iota)>[Iotas].[Iota]));

will create your "tblCopies"
that you will join in your query.

Copies
1
2
2
3
3
3
4
4
4
4
5
5
5
5
5
6
6
6
6
6
6
7
7
7
7
7
7
7
.........


Please respond back if I misunderstood
or was not clear about something.

Good luck,

Gary Walter
 
I want to run a query to print a barcode as many times as
there is quantity in stock. I don't know how to run the
query to duplicate the barcode as many times as there is
quantity.
Click to expand...

A somewhat simpler suggestion than Gary's also uses an auxiliary
table, one that I find handy so often that I routinely include it in
all my databases: a table named NUM with a single integer field N,
containing values from 1 to 10000 (or the most labels you'll ever need
- be generous).

Include NUM in your query with NO join line.

Instead, put a criterion on N of

<= [quantity]

This "Cartesian join" query will initially create 10000 copies of each
barcode, but the criterion will select only those up to and including
the quantity.
 
Back
Top