label reports that contain a list in an individual label

label reports that contain a list in an individual label

Post by SmFzb24gRG » Fri, 13 Jan 2006 01:56:03


I'm trying to create labels (AVERY 5163) that include a part # and a sum of
all jobs requiring that part #. I'm using SUM(Part #) to get the aggregate
quantity for all of the jobs. I also want to list on that same label, all
the jobs that are associated to that part #.

When I try to do it now, I add the the field "Job #" and I get one job # per
label. I would like an individual label to appear something like:

Part #
Sum of Required Qty
Required for:
Job 1, Job 2, Job 3, etc...
 
 
 

label reports that contain a list in an individual label

Post by Allen Brow » Fri, 13 Jan 2006 10:28:03

Unusual request. Of course, there is no way to know if all the jobs will fit
on a label, but you could do it with a subreport, or a function.

The subreport would appear on this report, and you set its Link Master
Fields and Link Child Fields properties to [Part #] so it shows the jobs for
the part. The subreport's Can Grow property will default to Yes, but make
sure your Detail section's Can Grow and Can Shrink are set to No so that the
report still matches the fixed-height label.

Alternatively, if you want the job numbers listed horizontally instead of
vertically, use the code from this function in the Control Source of a text
box:
Return a concatenated list of sub-record values
at:
http://www.yqcomputer.com/

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://www.yqcomputer.com/
Reply to group, rather than allenbrowne at mvps dot org.