Labels not duplicated

[Chuck]

Hi there,
i have a db with tblclients and tbladdress. I have a one-to-many
relationship between then two as i can have 2 clients (typcally Wife and
husbnad) at the same address. I would like to create a mailing list using a
query but dont want duplicated addresses on my labels. More specifically,
Right now the simple query is producing dup addressess. i would like to
create a new field [WifeHusb] in the query that somehow knows IF there is a
dup address then concat FirstName (wife&husband) so i can use that field in
the label report. I have FirstName and LastName in my table.

Instead of having two labels one with;
Joe Smith
123 somewhere place
thiscity etc

and;

Brenda Smith
123 somewhere place
thiscity etc

I would like one label with;

Joe and Brenda Smith
123 somewhere place
thiscity etc

How do i accomplish this? thanks

 

[John W. Vinson]

Hi there,
i have a db with tblclients and tbladdress. I have a one-to-many
relationship between then two as i can have 2 clients (typcally Wife and
husbnad) at the same address. I would like to create a mailing list using a
query but dont want duplicated addresses on my labels. More specifically,
Right now the simple query is producing dup addressess. i would like to
create a new field [WifeHusb] in the query that somehow knows IF there is a
dup address then concat FirstName (wife&husband) so i can use that field in
the label report. I have FirstName and LastName in my table.

Instead of having two labels one with;
Joe Smith
123 somewhere place
thiscity etc

and;

Brenda Smith
123 somewhere place
thiscity etc

I would like one label with;

Joe and Brenda Smith
123 somewhere place
thiscity etc

How do i accomplish this? thanks

Well... it's not that simple.

My name's John Vinson. My wife's name is Karen Strickler. We don't use the
same last name (usually) so your algorithm will fail.

What if you're sending to three clients, mother, son, and daughter... and the
daughter has a different last name from a previous marriage?

You're in the 21st century and you can not count on nuclear two-person
families as the norm any more!!

What I've done in this situation is to have a Families table (with a field for
the preferred mode of address, and the mailing address) related one to many to
a People table. Base the mailing on the Families table as appropriate.

John W. Vinson [MVP]

 

[Chuck]

John, got you. I agree it wont work. I do have two other fields in tblclients
(spouse_name and family_status) that might help down the road. I'll leave
that for another day.

I've modified the requirement and now i ask the user to manually select
records in a subform (using yes/No field) and then i have vba to filter and
sort that list. Now i want to print the labels using the filtered records in
the subfrmProspectPromotion. How do i go about doing that.

Hi there,
i have a db with tblclients and tbladdress. I have a one-to-many
relationship between then two as i can have 2 clients (typcally Wife and
husbnad) at the same address. I would like to create a mailing list using a
query but dont want duplicated addresses on my labels. More specifically,
Right now the simple query is producing dup addressess. i would like to
create a new field [WifeHusb] in the query that somehow knows IF there is a
dup address then concat FirstName (wife&husband) so i can use that field in
the label report. I have FirstName and LastName in my table.

Instead of having two labels one with;
Joe Smith
123 somewhere place
thiscity etc

and;

Brenda Smith
123 somewhere place
thiscity etc

I would like one label with;

Joe and Brenda Smith
123 somewhere place
thiscity etc

How do i accomplish this? thanks

Well... it's not that simple.

My name's John Vinson. My wife's name is Karen Strickler. We don't use the
same last name (usually) so your algorithm will fail.

What if you're sending to three clients, mother, son, and daughter... and the
daughter has a different last name from a previous marriage?

You're in the 21st century and you can not count on nuclear two-person
families as the norm any more!!

What I've done in this situation is to have a Families table (with a field for
the preferred mode of address, and the mailing address) related one to many to
a People table. Base the mailing on the Families table as appropriate.

John W. Vinson [MVP]

 

[Chuck]

Sorry John, i hould add some additional info for you. The subform does not
have the address information of the prospect but it is in the query
(qryProspectInv) that the subform is based on. I need to pull the name from
the subform and the address from the query to create the labels. thanks again

John, got you. I agree it wont work. I do have two other fields in tblclients
(spouse_name and family_status) that might help down the road. I'll leave
that for another day.

I've modified the requirement and now i ask the user to manually select
records in a subform (using yes/No field) and then i have vba to filter and
sort that list. Now i want to print the labels using the filtered records in
the subfrmProspectPromotion. How do i go about doing that.

Hi there,
i have a db with tblclients and tbladdress. I have a one-to-many
relationship between then two as i can have 2 clients (typcally Wife and
husbnad) at the same address. I would like to create a mailing list using a
query but dont want duplicated addresses on my labels. More specifically,
Right now the simple query is producing dup addressess. i would like to
create a new field [WifeHusb] in the query that somehow knows IF there is a
dup address then concat FirstName (wife&husband) so i can use that field in
the label report. I have FirstName and LastName in my table.

Instead of having two labels one with;
Joe Smith
123 somewhere place
thiscity etc

and;

Brenda Smith
123 somewhere place
thiscity etc

I would like one label with;

Joe and Brenda Smith
123 somewhere place
thiscity etc

How do i accomplish this? thanks

Well... it's not that simple.

My name's John Vinson. My wife's name is Karen Strickler. We don't use the
same last name (usually) so your algorithm will fail.

What if you're sending to three clients, mother, son, and daughter... and the
daughter has a different last name from a previous marriage?

You're in the 21st century and you can not count on nuclear two-person
families as the norm any more!!

What I've done in this situation is to have a Families table (with a field for
the preferred mode of address, and the mailing address) related one to many to
a People table. Base the mailing on the Families table as appropriate.

John W. Vinson [MVP]