filtering and flagging a mailing list for 1 record per household

[Mark V.]

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

[Peter Russell]

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

[Robert Crouser]

"Mark V." <ma**@victorynet.com> wrote in message
news:92*************************@posting.google.co m...

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.com

This is just a general off-the-top-of-my-head approach. You could
create a Yes/No field called UseThisPersonForLabels. 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 UseThisPersonForLabels =
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.openrecordset(strSQL,dbopensnapshot)
rst.movefirst
Do While Not rst.Eof
GetAddressName = GetAddressNames & " & "
rst.MoveNext
Loop
' remove & and space at end of string
GetAddressNames = Left(GetAddressNames,Len(GetAddressNames) - 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.

[Mike Metzger]

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**@victorynet.com> wrote in message
news:92*************************@posting.google.co m...

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