Multiple Numbers on ONE Label

  • Thread starter Thread starter NEWER USER
  • Start date Start date
N

NEWER USER

I have several thousand labels I need to print. I would like to put up to 4
part numbers on each label (Avery 5164). These labels will placed on boxes
that hold product. Some labels may only get 2 or 3 part numbers per label
depending on size of the part. My part numbers are classified as S,M,L. Can
this be done? Example...

ABC123 - small
ABC234 - large (max 2 numbers per label)
ABC345 - small
ABC456 - med (max 3 numbers per label)
BCD123 - small
BCD234
 
I have used your method in the past with great success. This time something
a little different. Instead of printing 100 labels, I want to print 25
labels with 4 Different numbers on each label. I am going to put one label
 
The data has to come from somewhere. If you want to specify which parts go
into which boxes, you probably need a related table that holds that
information. You can then draw the data from the tables (via a query), and
print it.

If it's not just a specific range (which could be generated by the Cartesian
product), it has to be specified.
 
I have been unclear in what I want to do. I have a table that stores my Part
Numbers in ONE field [Part]. How do I put one field on a label and get 4
numbers to print on the SAME label? Do I need a subreport which will list
all the numbers and if so, how do I limit the 4 max numbers per label?
 
So you have *multiple* part numbers in *one* field?
And you need to print a separate label for each one?

So, if there's a record where you have this in the part number field:
Part53; partXYZ; part999
then you need to print 3 labels for this record, each one one of the part
numbers
Part53
partXYZ
part999

If that's the idea, the real problem is the non-normalized design (i.e. the
fields are not atomic -- multiple items in one field.)

To work around it, you could create a query that counts the number of items
in the field by identifying the separator you used between items. The
example above assumes the semicolon, so you would use:
Len([PN]) - Len(Replace([PN], ";", ""))
Replace PN with your field number. Result is zero-based (since that's what
you'll need for the array.)

Now you have the count, you can use a Cartesian Product query to generate a
record for each one (as per the link), where you counting table has a zero
record. You will then need a VBA function using Split() to return the nth
item from the array.

A better solution would be to normalize the design , so one part can have
multiple entries in a related table.

Hope I've understood what you're trying to do. Based on your original post,
the count does not seem to be consistent for Small/Medium/Large, and your
follow up post indicates *different* numbers on each label, and your next
post indicates all these numbers are in the one field so I assume there is
some kind of consistent separator between them.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.


NEWER USER said:
I have been unclear in what I want to do. I have a table that stores my
Part
Numbers in ONE field [Part]. How do I put one field on a label and get 4
numbers to print on the SAME label? Do I need a subreport which will list
all the numbers and if so, how do I limit the 4 max numbers per label?

Allen Browne said:
The data has to come from somewhere. If you want to specify which parts
go
into which boxes, you probably need a related table that holds that
information. You can then draw the data from the tables (via a query),
and
print it.

If it's not just a specific range (which could be generated by the
Cartesian
product), it has to be specified.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.



.
Click to expand...
Click to expand...
 
NEWER USER,
How about providing some actual records with real field names, the values
that determine how these are "sized", how they can be sorted to make sure the
appropriate records are printed on the appropriate label, and the desired
layout from those actual records.

You can make your output look formatted with something like:

+-----------------------
| Part A (medium)
| Part B (medium)
|
+------------------------
|
| Part C (Large)
|
+--------------------
--

Duane Hookom
Microsoft Access MVP


NEWER USER said:
I have been unclear in what I want to do. I have a table that stores my Part
Numbers in ONE field [Part]. How do I put one field on a label and get 4
numbers to print on the SAME label? Do I need a subreport which will list
all the numbers and if so, how do I limit the 4 max numbers per label?

Allen Browne said:
The data has to come from somewhere. If you want to specify which parts go
into which boxes, you probably need a related table that holds that
information. You can then draw the data from the tables (via a query), and
print it.

If it's not just a specific range (which could be generated by the Cartesian
product), it has to be specified.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.



.
Click to expand...
Click to expand...
 
Field names are Part and Size - text fields; sorted by Part in ascending
order. I am going place labels on outside of stock boxes and place up to
four parts max in a box.

A1234 S
A2345 S
A5465 S
A8765 S These 4 can occupy 1 label (1st label)

A7654 M
A8791 S Only two labels (M,S) because "L" in group (2nd label)
B6532 L
B7653 M Only two on this label (L & M) 3rd label

C4352 XL Only 1 on this label (XL) 4th label

D6543 S
D7654 M
E7654 M 3 on this label

As the records are grouped in multiples of 4, all 4 "S" would fit on one
label. If a "M" is in the group of 4, then max of 3 per label. If a "L" is
present, then max of 2 per label, and "XL" would print only one number on
label. I am using a 4" x 3" label (Avery 5164).




Duane Hookom said:
NEWER USER,
How about providing some actual records with real field names, the values
that determine how these are "sized", how they can be sorted to make sure the
appropriate records are printed on the appropriate label, and the desired
layout from those actual records.

You can make your output look formatted with something like:

+-----------------------
| Part A (medium)
| Part B (medium)
|
+------------------------
|
| Part C (Large)
|
+--------------------
--

Duane Hookom
Microsoft Access MVP


NEWER USER said:
I have been unclear in what I want to do. I have a table that stores my Part
Numbers in ONE field [Part]. How do I put one field on a label and get 4
numbers to print on the SAME label? Do I need a subreport which will list
all the numbers and if so, how do I limit the 4 max numbers per label?

Allen Browne said:
The data has to come from somewhere. If you want to specify which parts go
into which boxes, you probably need a related table that holds that
information. You can then draw the data from the tables (via a query), and
print it.

If it's not just a specific range (which could be generated by the Cartesian
product), it has to be specified.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.


I have used your method in the past with great success. This time
something
a little different. Instead of printing 100 labels, I want to print 25
labels with 4 Different numbers on each label. I am going to put one
label
on a box and then put those 4 parts in that box. Can this be done?

:

See:
Print a Quantity of a Label
at:
http://allenbrowne.com/ser-39.html

The article assumes you have a Quantity field that tells you how many
labels
you need for each record.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.


I have several thousand labels I need to print. I would like to put up
to
4
part numbers on each label (Avery 5164). These labels will placed on
boxes
that hold product. Some labels may only get 2 or 3 part numbers per
label
depending on size of the part. My part numbers are classified as
S,M,L.
Can
this be done? Example...

ABC123 - small
ABC234 - large (max 2 numbers per label)
ABC345 - small
ABC456 - med (max 3 numbers per label)
BCD123 - small
BCD234

.

.
Click to expand...
Click to expand...
Click to expand...
 
I think you'll need to add a field to your OrderDetail table to store the
BoxNum for each row. This gives you a complete trail of exactly what the
database is going, and also will be useful to know if a box ever goes
astray.

Populating this field will require some VBA code, working with recordsets.

Create a table of the sizes and the max items per box:
BoxSize MaxItemsInBox
S 4
M 3
L 2

Create a query that selects the items for order. Include the table above,
and sort the query by MaxItemsInBox. Use this SQL statement in your code
with OpenRecordset. You can now walk the recordset, Edit, assign the BoxNum
and Update. When a box is full, you increment your box number variable and
move on to the next one.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.


NEWER USER said:
Field names are Part and Size - text fields; sorted by Part in ascending
order. I am going place labels on outside of stock boxes and place up to
four parts max in a box.

A1234 S
A2345 S
A5465 S
A8765 S These 4 can occupy 1 label (1st label)

A7654 M
A8791 S Only two labels (M,S) because "L" in group (2nd label)
B6532 L
B7653 M Only two on this label (L & M) 3rd label

C4352 XL Only 1 on this label (XL) 4th label

D6543 S
D7654 M
E7654 M 3 on this label

As the records are grouped in multiples of 4, all 4 "S" would fit on one
label. If a "M" is in the group of 4, then max of 3 per label. If a "L"
is
present, then max of 2 per label, and "XL" would print only one number on
label. I am using a 4" x 3" label (Avery 5164).




Duane Hookom said:
NEWER USER,
How about providing some actual records with real field names, the values
that determine how these are "sized", how they can be sorted to make sure
the
appropriate records are printed on the appropriate label, and the desired
layout from those actual records.

You can make your output look formatted with something like:

+-----------------------
| Part A (medium)
| Part B (medium)
|
+------------------------
|
| Part C (Large)
|
+--------------------
--

Duane Hookom
Microsoft Access MVP


NEWER USER said:
I have been unclear in what I want to do. I have a table that stores
my Part
Numbers in ONE field [Part]. How do I put one field on a label and get
4
numbers to print on the SAME label? Do I need a subreport which will
list
all the numbers and if so, how do I limit the 4 max numbers per label?

:

The data has to come from somewhere. If you want to specify which
parts go
into which boxes, you probably need a related table that holds that
information. You can then draw the data from the tables (via a
query), and
print it.

If it's not just a specific range (which could be generated by the
Cartesian
product), it has to be specified.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.


I have used your method in the past with great success. This time
something
a little different. Instead of printing 100 labels, I want to
print 25
labels with 4 Different numbers on each label. I am going to put
one
label
on a box and then put those 4 parts in that box. Can this be done?

:

See:
Print a Quantity of a Label
at:
http://allenbrowne.com/ser-39.html

The article assumes you have a Quantity field that tells you how
many
labels
you need for each record.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.


message
I have several thousand labels I need to print. I would like to
put up
to
4
part numbers on each label (Avery 5164). These labels will
placed on
boxes
that hold product. Some labels may only get 2 or 3 part numbers
per
label
depending on size of the part. My part numbers are classified
as
S,M,L.
Can
this be done? Example...

ABC123 - small
ABC234 - large (max 2 numbers per label)
ABC345 - small
ABC456 - med (max 3 numbers per label)
BCD123 - small
BCD234

.

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