full page of (10) of each label??

[Jeff C]

Using the Label Wizard I set up a business card size label and I am
using no record source for this report. The page setup has two columns
3.5 inches wide and 2 inches high. I then created a second report with
the page setup two columns 1.5 inches wide and 2 inches high. I have
it set up with departments in the group header with new page before
section, and the detail nicely gived me two columns of phone numbers.
This report is four pages, one page per department. I placed this 2nd
report as an unbound subreport in the label report. The result is one
label in the top left position of each full page of 10 labels.

How can I get a full page of ten (10) labels for each department?
Thank you

 

[Jeff C]

Thank you Allen:

I have a tbl of Depts related 1 to many on the dept_ID to a table of
phone stations and numbers. I created a report using the deptname as a
group header so that each of the depts phones and numbers is printed
out in two columns in the detail.

I placed this report into the label report.

I built your table and a new query(Q6) with it and the dept table so I
got 10 records of each department returned.

I placed this query and the phone station tbl into a new query(Q7) with
a left sided join calling for all the records from (Q6).

My result is all 10 station1, then 10 station 2, then 10 station 3 etc.

I am probably confusing with this description but I am trying to get a
result that looks something like this:

Dept A

station 1 ##### Station 2 #####
station 3 ##### Station 4 #####

10 of each dept which will fill one sheet of labels, most depts have
between 12 and 18 numbers.

Can you or anyone give me a bit more insight into this or point me to a
good resource? Thanks again.

Happy New Year

 

[Allen Browne]

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

The article explains how to create a table with 10 records in it, and add
that to your query so you get 10 of each record. (It also mentions a
Quantity field, but you won't need that part, since you always want 10 of
each.)

 

[Jeff C]

I have built new queries and using the process in your article I have
ten labels per page with the name of the department on each label.
Underneath the txt_Name control I placed the subreport of the related
phone numbers and they look great . . Except

I get two across the top, skip a row, two across row three, skip row 4
and 5, etc etc, the skipping rows are not consistent, I do get 10
labels from each department but not all on one page.

Any thoughts, anyone?? Thank you

 

[Jeff C]

I have built new queries and using the process in your article I have
ten labels per page with the name of the department on each label.
Underneath the txt_Name control I placed the subreport of the related
phone numbers and they look great . . Except

I get two across the top, skip a row, two across row three, skip row 4
and 5, etc etc, the skipping rows are not consistent, I do get 10
labels from each department but not all on one page.

Any thoughts, anyone?? Thank you

 

[Jeff C]

I have built new queries and using the process in your article I have
ten labels per page with the name of the department on each label.
Underneath the txt_Name control I placed the subreport of the related
phone numbers and they look great . . Except

I get two across the top, skip a row, two across row three, skip row 4
and 5, etc etc, the skipping rows are not consistent, I do get 10
labels from each department but not all on one page.

Any thoughts, anyone?? Thank you

 

[Jeff C]

1st, sorry about the multiple posts, I had server timeout warnings
after I hit the submit button and well, anyway

I have come to the conclusion that since there is no way to shorten the
page length in an access report, inserting a subreport, while giving me
the data in two columns will always mess up the columns in the label
formatted report.

At any rate I don't seem to be able to get the thing to work, thanks
for your help.

 

[Jeff C]

I am sorry I am not very clear. Each department has numerous stations each
with a phone#.

Normailized one-to-many relationship between tbl_Depts and tbl_Phone on the
Dept_ID. Each dept has one label with a list of all stations and #s. One
full sheet of 10 lables for each department. To get all the #s for each
Dept to fit on it's label it requires two columns (thus the subreport)

 

[Allen Browne]

So you don't want 10 identical copies of each label at all? You want the
different labels (each one showing a different phone number), and you want
the whole thing repeated for each department?

The solution will depend how your tables are setup. If you have a relational
structure, you have a Phone table with fields like this:
PhoneID primary key
DeptID foreign key to the department
Station a number between 1 and 10 (based on your example)
Ph the phone number for that station of that dept.

Then you need a combination of an outer join (to generate 10 numbers even if
the dept has fewer) and a cartesian product (to repeat everything by the
number of departments.) The outer join will be between the tblCount table
you created (with the numbers 1 to 10) and Phone table. Double click the
line joining tblCount.CountID to Phone.StationID, and choose the option for:
All records from tblCount, and any matches from Phone.
That's an outer join. More info on that:
http://allenbrowne.com/casu-02.html

Then delete the line from your Department table to Phone.DeptID. That's a
Cartesian product.

 

[Jeff C]

My labels are 3.5 by 2 , 2 across and 5 down. Record source is Q1 with
tblCount and tblDept and includes cntID, deptName, deptID resulting in
10 records for each dept numbered 1-10. Resulting report is 10 labels
per sheet numbered 1 thru 10 across and down with the deptName in the
top center of each label.

Sub report uses Q2 as a record source which has Q1 above and tblPhone
joined on deptID, and includes cntID, deptID, station, number, and
deptName which results in 10 records of each phone station for each
dept.

The subreport is linked to the label rpt on the cntID and deptID. The
resulting label report gives me perfect labels but,

Page 1 row 1 = dept1cnt1, dept1cnt2
Page 1 row 2 = blank
Page 1 row 3 = dept1cnt3, dept1cnt4
Page 1 row 4 = blank
Page 1 row 5 = blank

Each page continues like this in dept order and cnt order 4 labels per
page

I am baffled
Thanks again for any ideas

 

[Jeff C]

Well, I got it to work and thank you for your time spent reading
through my questions. Somehow, and I believe it was a default setting
but am not sure, the CanGrow setting on the detail section of the Label
report was set to yes. No matter how small I made the typeface and row
height in the subreport I kept getting a partial sheet. CanGrow = No,
CanShrink = yes gives me exactly what I needed, ten copies (1 sheet) of
labels with the deptname on top and two columns of stations and numbers
underneath which is normalized and can be changed and updated easily.

Thanks Again

Happy New Year

(and I actually questioned whether is was beyond Access, never again)

 

[Allen Browne]

Glad to know you got there, Jeff.

And yes, there's still more to tap into in Access in 2007.