Labels not duplicated

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




0
Utf
12/4/2007 2:51:01 AM
access.queries 6343 articles. 1 followers. Follow

3 Replies
849 Views

Similar Articles

[PageSpeed] 11

On Mon, 3 Dec 2007 18:51:01 -0800, Chuck <Chuck@discussions.microsoft.com>
wrote:

>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]
0
John
12/4/2007 4:26:15 AM
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.

"John W. Vinson" wrote:

> On Mon, 3 Dec 2007 18:51:01 -0800, Chuck <Chuck@discussions.microsoft.com>
> wrote:
> 
> >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]
> 
0
Utf
12/5/2007 3:31:01 AM
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



"Chuck" wrote:

> 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.
> 
> "John W. Vinson" wrote:
> 
> > On Mon, 3 Dec 2007 18:51:01 -0800, Chuck <Chuck@discussions.microsoft.com>
> > wrote:
> > 
> > >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]
> > 
0
Utf
12/5/2007 3:52:01 AM
Reply:

Similar Artilces:

Filter to change a Label
I have a command button that performs a filter. What I'd like it to ALSO do is be able to change the text in a label that sits in the Header. My code is: With Me .Filter = "[Source] = 'SE Meeting' AND [Status] = 'Open'" .FilterOn = True .OrderBy = "[Group_Name], [IssueID]" .OrderByOn = True End With So, when I click the command button it'll filter but I want "Label32" to change from what it is now to "Systems Engineering" Is that possible if so, what can I do or is there another approach?...

No duplications
I have a query that shows many records with the same "Part Number". I want to create a query that shows only the Part Numbers without duplications. Any ideas anyone? You don't give enough information for us to help. You could want a "distinct" query or a "group by" clause. It all depends what other data you want to show. Post the SQL by going into design view and selecting SQL View and then post it here along with a description of what you expect to see and links between tables that are used. Cheers, Jason Lepack On Mar 28, 9:13 am, "Jeff Klei...

duplicate wage entries
I am DEFINITELY not a guru at this. 1. Somehow I ended up with two entries in my budget for my paycheck. However, MS Money only allows me to delete the category, and not the duplicate. If I delete the category and reinitialize it, how will that affect the existing entries in my Accounts? 2. My home page shows bill that are overdue. However, the are entered into my Accounts. Is there a way to cleanly associate those entries with the corresponding bill that is due? ...

Browse
This is probably a simple question and handle. I've got a Browse list that contains duplicate data in one of its columns. Is there a way, through the Browse control template's default behavior dialog settings to remove or filter out records that contain duplicate data in one of its fields? I've played with it for about an hour and thought best to ask the professionals. Thanks in advance. -Ray Please ignore this post. "ray" <judy@yahoo.com> wrote in message news:u2cONjEDLHA.4744@TK2MSFTNGP06.phx.gbl... > This is probably a simp...

Area that duplicate showing what I type in a cell is missing.
I am missing the blank area that duplicates what I type in a cell - it duplicates it near the top of the Excel Page. All at once it has disappeared. If I correct anything in a cell I have to go to the cell --- I prefer going up to the top sometimes and correcting it in that area. But that area is GONE. From the View Menu select Formula Bar. Regards Rowan bojoco wrote: > I am missing the blank area that duplicates what I type in a cell - it > duplicates it near the top of the Excel Page. All at once it has disappeared. > If I correct anything in a cell I have to go to the cell...

When I make a duplicate page the first page is now gone. Why?
Can someone help? I've been making brochures and newsletter with my Publisher 2003 for a while now. I went back into one of my brochures to change some information but the little tabs on the bottom of the document are gone. Ya know those little tabs that you click on to go to the next page in your documents. So I opened up one of my old newsletters and the same thing, the tabs are gone and I can't see anything but the first page of the newsletter. So I tried to make a new newsletter and when I tried to add a new page, I got a new blank page, but my first page was gone and t...

Create a duplicate record
I use a form to input the data into the table. From time to time, I need to create a duplicate record from previous records or just completed new record. I saw the previous articles here but most of them to copy the last record to a new record. However, I need to highlight the desired record and copy to a new record. Any guidance and example are appreciated. Thanks, Scott Here's one that lets you duplicate the record in a form, as well as any related records in the subform: http://allenbrowne.com/ser-57.html Just ignore the part about the subform, and use the rest. -- ...

Label printing #6
What is the recommended label printer for RMS or wil lanyone do? Pretty much anyone will do, there may be a list of printers recommended or "RMS certified" that may be in the MS web site, anyway I've seen zebra LP2824 and 2844 work well, as well as cognitive and datamax printers - "Faiz Amir" wrote: > What is the recommended label printer for RMS or wil lanyone do? If you do any UPS shipping ask your UPS rep for a new label printer. Then go to barcodewarehouse.biz and get item number 3351210 - comes in a case of 8 rolls. These labels work the best for our ...

Duplicate record checking should be included with CRM
Duplicate record checking/auditing should be included with CRM. That seems to be one of the fundamental basics for keeping CRM data clean and at this time it's a 3rd party purchased add on. dph ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft...

Need Help with Deleting Duplicates
I have a client that uses excel to manage their email addresses. When they send out their newsletters they have an opt out option for people that decide that they don't want the newsletter anymore. This opt out option sends my client an email with the address to be removed. Is there a way to copy the removal list into the list of all the addreses and then completely delete the duplicate entries so the email addresses are removed from the list? Thank You! Chip Pearson has instructions for tagging duplicate entries in a list: http://www.cpearson.com/excel/duplicat.htm#TaggingDuplicat...

wine bottle labels
How do I use Publisher to set up labels, 3 to a page? I am going to print on gummed stock. jemilo <jemilo@discussions.microsoft.com> was very recently heard to utter: > How do I use Publisher to set up labels, 3 to a page? I am going to > print on gummed stock. This depends on your version of Publisher. Instructions here are for Publisher 2003. a) Use File > Page Setup > Printer and Paper to set the paper size to the size of the sheet on which the gummed labels are. b) Use File > Page Setup > Layout to set the page size in Publisher to the size of one label. c...

Divorcing labels and their controls
Using the Access 2007 Form Wizard to create a form from a table would be a great convenience, except for one thing. The label-control pairs it generates appear to be inseparably welded together. For example, if I want to replace a text box control with a combo box, I can't just delete the text box and leave the label--the label gets deleted too. Similarly, I can't move the label and leave the corresponding control here it is, label and control move together. To make it worse, when I try to reposition the generated label-control pairs they move as a block with the other labels-contro...

Font Size in Labels on Forms
How can I change the font size on a label in a form. There is no obvious option (in 97'). I have tried font.size = 12 etc to no avail... Cheers.... -- The Zero ST This worked in xl2003: Me.Label1.Font.Size = 12 Craig Coope wrote: > > How can I change the font size on a label in a form. There is no > obvious option (in 97'). > > I have tried font.size = 12 etc to no avail... > > Cheers.... > > -- > The Zero ST -- Dave Peterson On Fri, 16 Mar 2007 18:53:50 -0500, Dave Peterson <petersod@verizonXSPAM.net> wrote: >This worked in xl200...

Dynamic data label placement for graphs
I've read the article KB179199 on how to create custom data labels for charts and it's good, however doesn't meet all my requirements. Is there a way to have the data labels automatically move to a desired location relative to the graph? For instance, I'm using 50 bar graphs per work book and would like each data label to be just above their respective bar. TIA Hafeez I looked up article 179199 and got this: BUG: Grid Based on .QPR File Appears Blank in Visual FoxPro 5.0 which probably isn't the one you meant. By above the bar, are they horizontal bars and you ...

get report to display custom label rather than numeric data in tex
i've searched for ages trying to find a similar query already answered but have had no luck - apologies if i'm using poor terminology, i'm not that good on access in a report, one of the the text box fields records 'site' which is coded as a number 1-8. is it possible to use some sort of code (i'm thinking perhaps some kind of IIf statement from the looks of other replies) to get the report to display "Bolton" instead of 1, Burnley instead of 2 and so on? thanks in advance for help. "Emelina Bumsquash" <EmelinaBumsquash@discussions.microso...

how duplicate detection works on access levels
Hello, I would like to know if duplicate detection is able to detect the duplicate record while importing/saving if the user does not have access for an existing record. It might be hard to tell why I am asking this . But it is generally because if a user in a department tracks its activities on Company A which the user has access to, other department user also has activities with the company A but that user is unaware that the other company has been already recorded in CRM db because he/she has no access to it. Doesn't this bring a conflict in reports when the CEO will see two re...

Duplicate detection rule when converting a Lead
Hi, I've created a duplicate detection rule where I'm supposed to get notify when a new account is created with the same account name. It works fine but when I convert a lead into an account and the account alreay exists, it let me convert the lead and does not give me any notification. We create 2 exact same accounts. Any idea? Valery Perhaps the standard dup detect only works in the parent pipeline? - Chris ...

Checking for duplication on rows
Hello, In a particular spreadsheet, I would like to verify if the same name is listed on multiple rows. The name is entered in multiple columns on the same row, that is acceptable, but I would like to know if the name is entered on multiple rows regardless of the column. How can I check this? THANKS, karmen -- Karmen ------------------------------------------------------------------------ Karmen's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=30972 View this thread: http://www.excelforum.com/showthread.php?threadid=509495 Presume you're checking some...

duplicate e-mail in Exchange 2003
I attempted to added an e-mail address to a user and the message "This E-mail address already exists in this organization" is prompted. However, I check all users and groups, that e-mail address is not assigned to anyone or anywhere. However, I am able to send e-mails to that address and message tracking states that messages are being delivered to that e-mail address. I even try sending e-mail the checking to fine if the any of the mailbox increase in size and items. I read in a Microsoft KB article something about Exchange not recognizing that mailboxes have been deleted. Can&#...

Button Label is invisable
I added a "SAVE RECORD" Button to a Form and the label is not visable on the button. The Property sheet says it is there. I have looked at all the properties and cannot find anything that might cause this. I would greatly appreciate some suggestions. -- Mel "Mel" <Mel@discussions.microsoft.com> wrote in message news:ADF6552F-8E60-450A-8708-E621D8270633@microsoft.com... >I added a "SAVE RECORD" Button to a Form and the label is not visable on >the > button. The Property sheet says it is there. I have looked at all the > prop...

radar chart & labels
Is it possible to label *grid* in a radar chart...?The labels of Y-axis makes the chart unreadable... Hi, Here an explanation of what I think you are wanting. http://www.andypope.info/ngs/ng37.htm Post back if I mis understood. Cheers Andy uriel78 wrote: > Is it possible to label *grid* in a radar chart...?The labels of Y-axis > makes the chart unreadable... > > -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info ...

Labels 08-09-04
How can I use the SDK to retrieve the label of a field or can I do this by accessing the db directly ...

Outlook Contacts Duplicate
When entering new info for contacts and saving it in outlook, sometimes a box comes up saying that I have made changes and what do I want to do with those changes. Other times, it just duplicates the contacts. My question is why is that box not coming up all the time? Thanks for help! From the Outlook help file (search term: "duplicate contacts"): "If you save a contact with the same name or e-mail name as one that already exists in your Contacts folder, Microsoft Office Outlook displays a dialog box with options to either add the duplicate contact as a new c...

duplicate emails.
When my outlook is open I am receiving dup emails. I have a handheld so in my outlook I have checked the leave copy of message on server so the web client can retrieve its copy for the handheld. But it seems when outlook is open and running it is taking both copies of the message leaving nothing for the web client. When the desktop is off and outlook not running the web client retrieves its copy fine. Please help as my boss is getting crazy over this!! ...

Column Labels #7
Columns in Excel are A,B,C, etc. Is there any way to change these letters to words to describe the columns? Not the way you might expect, but In a fashion, yes.... Put the labels you want to display in Row_1 Click on cell A2 Window>Freeze Panes Tools>Options>View ---uncheck: Row & Column Headers Caveat: That will also hide the row numbers that appear on the left of the worksheet window. Does that help? *********** Regards, Ron "Sug" wrote: > Columns in Excel are A,B,C, etc. Is there any way to change these letters to > words to describe the columns? So ...