Printing mailing labels with multiple recipient name records associated with a single address

[Skylark]

I have been browsing this, and other Access groups, as well as some of
the Microsoft KB articles, and while I find posts decribing similar
objectives to mine, can't seem to find anything that specifically
shows me how to solve my problem. I posted this topic, by mistake
actually to the Access general group, when I meant to post it here.

I have a property management database which contains many related
tables, including a table "Units" and a table "Tenants". Tenant
records have a corresponding foreign key for the UnitID relating them
to the Units table. A tenant can only be associated with one Unit
record, but a Unit may be associated with many Tenant records. I need
to be able to print mailing labels, listing all of the tenant names,
with their related Unit address. For example:


Tenants.Name (1)
..
..
..
Tenants.Name (n)
Units.AddLine1
Units.AddLine2
Units.AddLine3


The Mailing Labels wizard won't help me, because that prints a
separate label for each tenant record.


I have tried various things, for example, a multi-column report
grouped by UnitID, with the Tenants.Name field in the detail area,
and
the Unit Address fields in the group footer. I get the correct data
this way, but I can't get the data to format correctly for labels.
Since the Tenants.Name detail will vary depending on the Unit I can't
figure out how to fix the report areas.


I am using Avery 5163 4"X2" mailing labels (2 columns, 5 rows on an
8.5" X 11" sheet) with our company logo/return address pre-printed in
the upper left hand corner, which occupies 1.25" X 1" of space on the
label, so my recipient address needs to be positioned on the label
space accordingly.

I have also tried setting up my report with the group footer with
Unit Address information, and Select Distinct Unit as a criteria in
the query that is the record source for the report, and I have a
subreport that simply has Tenant name in the details field. What I am
ending up with is the data displaying as follows:

Tenant (1)
..
..
..
Tenant (n)


Tenant (1)
..
..
..
Tenant (n)
UnitAdd1
UnitAdd2
UnitAdd3


Which is closer to what I want, but I am obviously still missing
something very basic.

I found this existing topic:

http://groups.google.com/group/micr...3172cb?lnk=gst&q=mailing+labels&rnum=20&hl=en

which describes a task essentially like I am wanting to do, but I am
not understanding the proposed solution. When I create a subreport in
the detail section of the main report for Tenants.Name, I am getting
the same results as above.

Any help would be appreciated!

 

[Duane Hookom]

There are at least two methods. The one mentioned in the link you provided
would require a main report based on only each distinct Unit. The main report
would not have any tenant information. The subreport would have the tenant
information. Use the Link Master/Child properties to join based on the Unit
ID.

You could also use the generic concatenate function found at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane

 

[Skylark]

There are at least two methods. The one mentioned in the link you provided
would require a main report based on only each distinct Unit. The main report
would not have any tenant information. The subreport would have the tenant
information. Use the Link Master/Child properties to join based on the Unit
ID.

You could also use the generic concatenate function found athttp://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane
--
Duane Hookom
Microsoft Access MVP
















- Show quoted text -

Duane,

Thank you for the reply. I am not experienced with Access reports, and
I am still having trouble. Perhaps more information would help. The
purpose of the mailing labels is for offering lease renewals to
tenants whose leases are about to expire. I have a query based on the
Units table, and Tenants table. The query has user prompted parameters
for a date range for lease expiration. This Lease Expirations query is
used as the record source for a report the administrative staff prints
out each month to prepare lease renewal documents. It works great, but
they have been hand typing each mailing label with all the tenant
names, which is extremely time consuming.

For the latest attempt at creating the mailing labels, I created a new
report with just Unit address info, and a subreport based on the Lease
Expirations query with Tenant names, and the main report is linked to
the subreport by UnitID. What is happening now, is I am getting
prompted over and over for the date parameters from the subreport's
query.

 

[Skylark]

Duane,

Thank you for the reply. I am not experienced with Access reports, and
I am still having trouble. Perhaps more information would help. The
purpose of the mailing labels is for offering lease renewals to
tenants whose leases are about to expire. I have a query based on the
Units table, and Tenants table. The query has user prompted parameters
for a date range for lease expiration. This Lease Expirations query is
used as the record source for a report the administrative staff prints
out each month to prepare lease renewal documents. It works great, but
they have been hand typing each mailing label with all the tenant
names, which is extremely time consuming.

For the latest attempt at creating the mailing labels, I created a new
report with just Unit address info, and a subreport based on the Lease
Expirations query with Tenant names, and the main report is linked to
the subreport by UnitID. What is happening now, is I am getting
prompted over and over for the date parameters from the subreport's
query.- Hide quoted text -

- Show quoted text -

P.S. I did look at the concatenate function you linked to yesterday,
but it appears to group the names on a single line, and I need each
tenant name record to be forced to a new line on the mailing label. Is
there a way to accomplish such a thing with concatenate?

 

[Duane Hookom]

Do all tenants have the same lease expiration per unit? If so, add the
expiration date field to your main report's record source so that you know
which Units expire at which date. This might have to be a totals query so
that you have only one record per unit.

Then link the subreport based on UnitID and remove the parameter prompts.
IMHO, all parameter prompts should be trashed in favor of criteria entered
into controls on forms.

Also, the concatenate function allows you to use Chr(13) & Chr(10) as the
delimiter. This places a carriage return between each tenant.

 

[Skylark]

Do all tenants have the same lease expiration per unit? If so, add the
expiration date field to your main report's record source so that you know
which Units expire at which date. This might have to be a totals query so
that you have only one record per unit.

Then link the subreport based on UnitID and remove the parameter prompts.
IMHO, all parameter prompts should be trashed in favor of criteria entered
into controls on forms.

Also, the concatenate function allows you to use Chr(13) & Chr(10) as the
delimiter. This places a carriage return between each tenant.
--
Duane Hookom
Microsoft Access MVP






- Show quoted text -

The lease expiration date is a field in the Tenant record. Every
tenant on a particular lease for the same unit SHOULD have the same
lease expiration date recorded unless someone has made a data entry
error. Thank you for the info about the special char for carriage
return as delimeter in the Concatenate function.

I have experimented trying to do query by form to eliminate parameter
queries, but have not been able to get anything to work correctly. I
am operating with very limited knowledge.