Creating multiple records in a make table Query OR Create Multiple lables

[Joe]

Hi all,
Can someone please point me in the right direction? I am trying to come up
with a way to print multiple copies of a record, or generate a table with
multiple repeating records so that I can print multiples. But I need a field
on these records to increase with each record. Also, I want the ability to
print the number of copies based upon a case count record in another table.
(for Shipping case labeling) has anyone done this?
So far the onely way I have done this (as a test) is to create a static
table with one record for "1" and two records with "2" and so on and so
forth and then link that number to the case count number in the other table
so that it repeats the record "n" number of times. Then I am able to print a
report that uses incremental page count as my ascending number. I'm sure
there is a better way, since I ship up to 1000 or more cases of product and
I am not about to create a table with one thousand "1000" records!

Have I explained this in enough detail? Help!
Thanks in advance!
-Joe

 

[[MVP] S. Clark]

Just append the number of desired records to the table, then print the
report.


--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

 

[Gary Walter]

Hi all,
Can someone please point me in the right direction? I am trying to come up
with a way to print multiple copies of a record, or generate a table with
multiple repeating records so that I can print multiples. But I need a field
on these records to increase with each record. Also, I want the ability to
print the number of copies based upon a case count record in another table.
(for Shipping case labeling) has anyone done this?
So far the onely way I have done this (as a test) is to create a static
table with one record for "1" and two records with "2" and so on and so
forth and then link that number to the case count number in the other table
so that it repeats the record "n" number of times. Then I am able to print a
report that uses incremental page count as my ascending number. I'm sure
there is a better way, since I ship up to 1000 or more cases of product and
I am not about to create a table with one thousand "1000" records!

Hi Joe,

In addition to Steve's sage advise,
using Michel's Iotas table, it would be
a snap to create the table I believe you
are describing above.

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
FROM Iotas, Iotas AS IotaCopy
WHERE (((IotaCopy.Iota)>[Iotas].[Iota]));

I believe would give you the result
that I think you wanted
(of course you could turn this
into a make table 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
.........


Pretty easy huh?
Ah..the power of SQL!

Please respond back if I have misunderstood.

Good luck,

Gary Walter

 

[Joe]

Man, you guys are WAY over my head, but I will give it a try and post my
results. thanks!
-Joe

Hi all,
Can someone please point me in the right direction? I am trying to come up
with a way to print multiple copies of a record, or generate a table with
multiple repeating records so that I can print multiples. But I need a field
on these records to increase with each record. Also, I want the ability to
print the number of copies based upon a case count record in another table.
(for Shipping case labeling) has anyone done this?
So far the onely way I have done this (as a test) is to create a static
table with one record for "1" and two records with "2" and so on and so
forth and then link that number to the case count number in the other table
so that it repeats the record "n" number of times. Then I am able to print a
report that uses incremental page count as my ascending number. I'm sure
there is a better way, since I ship up to 1000 or more cases of product and
I am not about to create a table with one thousand "1000" records!

Hi Joe,

In addition to Steve's sage advise,
using Michel's Iotas table, it would be
a snap to create the table I believe you
are describing above.

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
FROM Iotas, Iotas AS IotaCopy
WHERE (((IotaCopy.Iota)>[Iotas].[Iota]));

I believe would give you the result
that I think you wanted
(of course you could turn this
into a make table 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
........


Pretty easy huh?
Ah..the power of SQL!

Please respond back if I have misunderstood.

Good luck,

Gary Walter

 

[Joe]

Gary,
You are brilliant! It Worked! I even used it as a select statement and
printed my labels in the correct quantities! Very cool. Thanks!
-Joe

 

[Gary Walter]

Gary,
You are brilliant! It Worked! I even used it as a select statement and
printed my labels in the correct quantities! Very cool. Thanks!
-Joe

No brilliance here *personally*.

Just repeating what I have learned
from Michel and others on this newsgroup.

Glad it worked.

Gary Walter