labels using same record per row

[Barry]

I am using an Access database to try and create labels. The labels
are 2 rows with 7 labels per row. The database contains several
hundred records. I need each row of labels to use the information
from the 1st databse record, then the next row uses the information
from the next database record, the 3rd row uses the information from
the next database record, etc etc. How do I keep the same record for
the 1st row of seven labels? (For example, I want the same name to
appear on the 1st line of each of the 7 labels in a row). Right now
each of the 14 labels on the page uses a different database record and
its corresponding information. I want 2 records to be used per page,
the 1st record on the 7 labels of row 1 and the 2nd record on the 7
labels of row 2 and on and on.

Using Word 2000 on WIN2K.

Barry

 

[Doug Robbins - Word MVP - DELETE UPPERCASE CHARACT]

Hi Barry,

In the mailmerge main document, remove the <<Next Record>> field from the
2nd through the seventh labels in each row, so that the only label that
includes it is the first label on the second row. (It is not needed in the
first label on the first row.)

Please post any further questions or followup to the newsgroups for the
benefit of others who may be interested. Unsolicited questions forwarded
directly to me will only be answered on a paid consulting basis.

Hope this helps
Doug Robbins - Word MVP

 

[Barry]

Hi Barry,

In the mailmerge main document, remove the <<Next Record>> field from the
2nd through the seventh labels in each row, so that the only label that
includes it is the first label on the second row. (It is not needed in the
first label on the first row.)

Please post any further questions or followup to the newsgroups for the
benefit of others who may be interested. Unsolicited questions forwarded
directly to me will only be answered on a paid consulting basis.

Hope this helps
Doug Robbins - Word MVP

Gosh, my explanation was not quite right. I want the 7 labels in the
first COLUMN to be from the same record, then the next COLUMN from the
next record.
I took out the <<next record>> from COLUMN 1 rows 1 to 7. I put a
<<new record>> field on the first label of COLUMN 2. However the mail
merge is going as follows: 1st column 1st row, 2nd column 1st row,
1st column 2nd row, 2nd column 2nd row and so on. How do I change the
mailmerge main document so that it goes COLUMN 1 row 1, COLUMN 1 row
2, COLUMN 1 row 3, COLUMN 1 row 4, COLUMN 1 row 5, COLUMN 1 row 6,
COLUMN 1 row 7, then the next record goes to COLUMN 2 row 1, COLUMN 2
row 2 and so on?

Barry

 

[Doug Robbins - Word MVP - DELETE UPPERCASE CHARACT]

Hi Barry,

About all I can think of in that case is use an append query in Access to
create a table containing seven sets of the records, sort it so that there
are seven copies of the first record, then seven of the second etc. and then
probably use a catalog or directory mailmerge in Word to create and
intermediate datasource upon which you could use the following macro:

' Macro to assign numbers to data source so that it can be sorted to cause
labels to print down columns
Dim Message, Title, Default, labelrows, labelcolumns, i As Integer, j As
Integer, k As Integer
Message = "Enter the number of labels in a row" ' Set prompt.
Title = "Labels per Row" ' Set title.
Default = "3" ' Set default.
' Display message, title, and default value.
labelcolumns = InputBox(Message, Title, Default)
Message = "Enter the number of labels in a column" ' Set prompt.
Title = "Labels per column" ' Set title.
Default = "5" ' Set default.
labelrows = InputBox(Message, Title, Default)
ActiveDocument.Tables(1).Columns.Add
BeforeColumn:=ActiveDocument.Tables(1).Columns(1)
ActiveDocument.Tables(1).Rows(1).Range.Cut
k = 1
For i = 1 To ActiveDocument.Tables(1).Rows.Count - labelcolumns
For j = 1 To labelrows
ActiveDocument.Tables(1).Cell(i, 1).Range.InsertBefore k + (j - 1) *
labelcolumns
i = i + 1
Next j
k = k + 1
i = i - 1
If k Mod labelcolumns = 1 Then k = k - labelcolumns + labelcolumns *
labelrows
Next i
ActiveDocument.Tables(1).Sort FieldNumber:="Column 1"
ActiveDocument.Tables(1).Rows(1).Select
Selection.Paste
ActiveDocument.Tables(1).Columns(1).Delete

Please post any further questions or followup to the newsgroups for the
benefit of others who may be interested. Unsolicited questions forwarded
directly to me will only be answered on a paid consulting basis.

Hope this helps
Doug Robbins - Word MVP

 

[Barry]

Gosh, my explanation was not quite right. I want the 7 labels in the
first COLUMN to be from the same record, then the next COLUMN from the
next record.
I took out the <<next record>> from COLUMN 1 rows 1 to 7. I put a
<<new record>> field on the first label of COLUMN 2. However the mail
merge is going as follows: 1st column 1st row, 2nd column 1st row,
1st column 2nd row, 2nd column 2nd row and so on. How do I change the
mailmerge main document so that it goes COLUMN 1 row 1, COLUMN 1 row
2, COLUMN 1 row 3, COLUMN 1 row 4, COLUMN 1 row 5, COLUMN 1 row 6,
COLUMN 1 row 7, then the next record goes to COLUMN 2 row 1, COLUMN 2
row 2 and so on?

Barry

Okay, I am now able to get the printing of the labels to go down, then
across using a method (see below) by Bob S. Now my question is, I
have 2 fields on the same line of the label, let's say field called
'name' and field called 'number'. These fields have a variable number
of characters in the source data. So, on the label line that these
fields print I'd like the 'name' field to be left aligned on the label
and the 'number' field to be right aligned on the label. Is there any
way to do this?

Barry


Bob S.'s method:
"The easy way is to use the snaking columns feature. Start the mail
merge and let Word generate the label table. Write down the width of
the label cells, and the width of the small cells that Word uses to
separate the columns of labels. Count the number of label columns,
select all the rows, and use Table | Insert Rows once for each column
of labels after the first. For example, Avery 5260 has ten rows and
three columns, so you would select all ten rows and use Insert Rows
twice, giving 30 rows of three columns.

The next step is to select all columns (including the little spacer
columns) except the first label column, and use Table | Delete
Columns. For the example we would now have 30 rows in one column.

Now invoke Format | Columns. Set the number of columns to be the
number of columns on the label sheet, set the column width equal to
the table cell width, and set the space between columns equal to the
width of the original spacer columns. For the example, we would have
three columns, the column width would be 2.625" and the space between
columns would be 0.125".

You now have a label setup that will order records down then across.
You can now continue with the merge."

 

[Doug Robbins - Word MVP - DELETE UPPERCASE CHARACT]

Hi Barry,

Place a right aligned tab stop in the label cell and use Ctrl+Tab to move
the number field to that stop.

Please post any further questions or followup to the newsgroups for the
benefit of others who may be interested. Unsolicited questions forwarded
directly to me will only be answered on a paid consulting basis.

Hope this helps
Doug Robbins - Word MVP

 

[Barry]

Hi Barry,

Place a right aligned tab stop in the label cell and use Ctrl+Tab to move
the number field to that stop.

Please post any further questions or followup to the newsgroups for the
benefit of others who may be interested. Unsolicited questions forwarded
directly to me will only be answered on a paid consulting basis.

Hope this helps
Doug Robbins - Word MVP

Doug,

That did it! Thanks.

Barry