473,574 Members | 2,575 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

filtering and flagging a mailing list for 1 record per household

Here's what I have and I'm stumped. I have a table that has several
thousand names and addresses. I only want to send to one address in a
household. So what I would like to do is create a new column that runs
a macro or whatever it takes to flag records so they are housholded.
Some records have a Father and son at the same address, so I would
only want to generate a count in my query that gives me one record for
that household, a head of household flag. Some households have as many
as 4 people at the same address and apt. I have one field with the
combined address in it to query for these duplicates. Any suggestions.

Mark
Nov 12 '05 #1
3 4676
Access has query wizards which are listed when you start a new query.

You can use the Duplicates query wizard to list out all the duplicates and
then visually inspect these to mark the head of household.
If you save this query you can then use it with the Unmatched query wizard
to find all the other records (Or you can negate the criteria in the
duplicates query). If you convert this into an update query you can
automatically set the head of household flag.

However, the whole thing depends on the addresses being exact text
duplicates and not 'equivalents' which, in my experience, is extremely
unlikely.

Good luck.

Regards

Peter Russell

Mark V. previously wrote:
Here's what I have and I'm stumped. I have a table that has several
thousand names and addresses. I only want to send to one address in a
household. So what I would like to do is create a new column that runs
a macro or whatever it takes to flag records so they are housholded.
Some records have a Father and son at the same address, so I would
only want to generate a count in my query that gives me one record for
that household, a head of household flag. Some households have as many
as 4 people at the same address and apt. I have one field with the
combined address in it to query for these duplicates. Any suggestions.

Mark


Nov 12 '05 #2

"Mark V." <ma**@victoryne t.com> wrote in message
news:92******** *************** **@posting.goog le.com...
Here's what I have and I'm stumped. I have a table that has several
thousand names and addresses. I only want to send to one address in a
household. So what I would like to do is create a new column that runs
a macro or whatever it takes to flag records so they are housholded.
Some records have a Father and son at the same address, so I would
only want to generate a count in my query that gives me one record for
that household, a head of household flag. Some households have as many
as 4 people at the same address and apt. I have one field with the
combined address in it to query for these duplicates. Any suggestions.

Mark
This is courtesy of Patrick Finucane:

MklMorgan wrote:
trying to teach myself access. Question for the gurus out there:

I have a table with names and addresses (its a membership listing) that I use to send out letters and whatnot. One of the reports is formatted to address labels. Is there a way to combine married couples to one address label. As set up currently both people can be paid members but when the report runs and
prints I get two labels per couple. One with each name, both with the same
address.

Ideally I would like the report to combine the two names to something like

Joe and Debra Smith

then the address but if thats not possible I would settle for a filter or sort that lists only one of the names to avoid wasting labels.

Thanks in advance
Mike
Mk*******@aol.c om


This is just a general off-the-top-of-my-head approach. You could
create a Yes/No field called UseThisPersonFo rLabels. Then present a
combo box that lists the records of people at that address. If only one
person, it would always be set to True. You'd requery the combo in the
OnCurrent event. If there were only 1 record (the current one), the
Yes/No would be set to True and the combo would be invisible. If more
than one, you would make the the combo visible and let the person set
the combo to the person to use. In the BeforeUpdate event, if more than
one record is at this address and this record is selected to be the
record to used for labels, check the other records and set their flag to
false. At the same time, if the address changed, you could update the
other records addresses....or ask if you want to update the other
addresses with the newaddress (see OldValue property).

Then in your query select all records with UseThisPersonFo rLabels =
True. You could then create a function that would pass the address and
return the names. Ex. In thd field row enter something like
AddressNames : GetAddressNames ([Address],[LastName])
and the function would be something like
Private Function GetAddressNames (strAddress As String, strLastName As
String)
Dim strSQL As STring
Dim rst As Recordset
strSQL = "Select FirstName, LastName From Table Where Address = '" &
strAddress & "'"
set rst = currentdb.openr ecordset(strSQL ,dbopensnapshot )
rst.movefirst
Do While Not rst.Eof
GetAddressName = GetAddressNames & " & "
rst.MoveNext
Loop
' remove & and space at end of string
GetAddressNames = Left(GetAddress Names,Len(GetAd dressNames) - 2)
'add the last name
GetAddressNames = GetAddressNames & strlastName
End Function

You would have to come up with a solution for people living at the same
address with different last names (wife didn't take hubby's name or
boy/girlfriend teams, stepfather/daughter situations.

In actuality, this might be a complicated problem because of variables
that can be introduced. In the long run, it might be cheaper to pay for
the extra label and postage vs the maintenance required to make this
work. Since both are paying for the membership, both should receive a
mailing....in my opinion.

But if you want to save some money on the mailing end, you'll be adding
money in maintenance on the computer end.

Also, if this is not a large list or dup addresses, you may want to
handwrite the labels of those that are dupes.

Either that or redesign the system so that you have a last name and
address field for the main record and make family members a subform in
another table that links to the recorrd ID. That's probably the best
way to do this...but you'll still need to write a function to
concatenate the first name records together similar to the one I
provided.
Nov 12 '05 #3
Mark-

I had a very similar problem to solve last year some time. For me, it was
the requirement that no two people at the same address participate in a
marketing study for a product.

I posted to this newsgroup and got some great suggestions, but the following
worked best. I don't remember exactly how it was originally suggested, but
here's how I ended up implementing it.

Create a table called tblHousehold with the following design:

HouseholdID (key)
HouseholdName
Address
City/ST/ZIP
Phone
etc.

HouseholdName is just for convenience when entering the data or searching
through the table. By default I used the LastName of the person I designated
"head of household". Naturally, this is only the starting point, you'll
certainly end up with variations (Smith001, Smith002, etc.) That's the
reason we don't use the HouseholdName as the relation key.

For the individuals in your database, they should be stored in another table
such as tblMember with the following design:

MemberID (key)
HouseholdID (Foreign Key)
LastName
FirstName
DOB
CellPhoneNo
etc.

Finally, you relate the two tables together in a one-to-many on the
HouseholdID key. That is, for each row in tblHousehold there may be several
corresponding entries in tblMember, related on the HouseholdID key.

The system is pretty simple and works really well. So much for the good
news.

The bad news is there's a huge amount of work doing the initial assignments
of the entries in tblMember to an entry in tblHousehold. I don't know of any
way to automate this, as someone else quite correctly pointed out, the
chances of the addresses being entered exactly the same for two Members is
pretty slim. So there's no easy way to find all the potential members at a
given address. The only thing you can do is sort your tblMembers ascending
on the Address field and read through them manually. If you have 100,000
records, it's not going to be fun. My database had only about 5,000, so it
wasn't too bad.

In practice, then, you require any new entry in tblMember to be assigned
either to an existing household or create a new household just for them. I
created a combo box on the entry form for tblMember that pulls entries from
tblHousehold, sorted in Address order. (I assume you know how to do this -
set combo box data source to a query based on the tblHousehold, sorted on
Address, then display the address column but bind the entry to the
HouseholdID column. The combo box wizard will get a long way on this.)

When you go to print your labels, just print from a query that gets its
address data from tblHousehold.

Once you get through the grunt work of initial assignments, it's just the
usual maintenance of keeping up with address changes either for an entire
household or individual members when they move in or out.

Mike Metzger
"Mark V." <ma**@victoryne t.com> wrote in message
news:92******** *************** **@posting.goog le.com...
Here's what I have and I'm stumped. I have a table that has several
thousand names and addresses. I only want to send to one address in a
household. So what I would like to do is create a new column that runs
a macro or whatever it takes to flag records so they are housholded.
Some records have a Father and son at the same address, so I would
only want to generate a count in my query that gives me one record for
that household, a head of household flag. Some households have as many
as 4 people at the same address and apt. I have one field with the
combined address in it to query for these duplicates. Any suggestions.

Mark

Nov 12 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

8
2095
by: Tim Pollard | last post by:
Hi I am trying to filter a table of users to select only those records whose roleID matches a value in an array. There could be any number of IDs held in the array from one to a few hundred. The array is generated by splitting a comma delimited memo field from a second table in an Access DB. I can split the memo field OK, I can...
0
3628
by: Patrick | last post by:
I'm working on a contact management application, and need a hand with one aspect... Here's what I want to create: ------------------------------------ A form split into two parts. There is a datagrid on the left side that lists names and perhaps a couple of other key fields. The user can click on a record in the datagrid, which should...
2
13496
by: Mikey | last post by:
Sample VB .NET source code to create mailing labels or customized letters using MS Word MailMerge This VB .NET source code will start MS Word and call methods and set properties in MS Word to execute a MailMerge to create mailing labels or customized letters. A label name known to MS Word MailMerge mailing label wizard may be used or a...
4
2534
by: Andy M | last post by:
ALERT There is a person by the name of Mike Cox who's trying to turn this mailing list into a Big-8 newsgroup. Many of you know that this and most of the other postresql mailing lists are already gated to Google Groups and a small number of private news servers. If Mike Cox succeeds, this list will be available as a newsgroup on a great...
7
14791
by: | last post by:
Hello, Does anyone have an idea on how I can filter the data in the gridview control that was returned by an sql query? I have a gridview that works fine when I populate it with data. Now I want to look at that data and filter it based on what is in it. I know that this could have been done with data sets and data views in asp.net 1.1...
2
3085
by: JUAN ERNESTO FLORES BELTRAN | last post by:
Hi you all, I am developping a python application which connects to a database (postresql) and displays the query results on a treeview. In adittion to displaying the info i do need to implement filtering facility for all the columns of the treestore/liststore model in order to allow the user an easy search method to find the desired...
4
4424
by: Dave | last post by:
I am having difficulty filtering a form as the user types in a onchange event here is my code strFilter = cboCriteria.Value & " LIKE '" & Me!txtCriteria.text & "*" & "'" If Len(strFilter ) 0 Then Me.FilterOn = True Me.Filter = strFilter
3
1379
by: Zethex | last post by:
Alright I got asked today by a friend this question, which obviously I couldn't help him with. He needs to get rid of words in a string referring to an already given list then needs to map them using a function he already has. Ill explain this better by giving an example :P Say ur given these lists: un_words =
0
161
by: Zethex | last post by:
Thank you for the help so far. Another quick question, how can I remove the special characters such as ! and ?. I also need to lowercase the words so should i use sentence = sentence.lower() ? Thank you --
0
7832
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
8262
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7843
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
6498
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5643
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
5331
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3768
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
1
1364
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
1094
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.