A better mousetrap: Matching mailing lists...

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

In need of assistance - 19 Jul 2006 19:21 GMT

The current method I use for matching mailing lists is a join via a created
"matchfield." Such as this

Matchfield : Left([Last Name], 3) & Left([First Name], 3) & Left([Street
Address], 3) & [ZipCode]

I then use this as an ID to find matches in the two lists. My match rate
with this method is sometimes very low, and I was wondering what sort of
methods other people use to match up lists.

Reply to this Message

Allen Browne - 20 Jul 2006 03:02 GMT

Your Client table should have a primary key field - typically an AutoNumber
named ClientID.

You will also have a table containing the various mailing lists you have to
handle, with one field:
MailListID Text Unique name for this mailing list.

Now you need a 3rd table to determine who is on each mailing list. Fields:
ClientID relates to Client.ClientID
MailListID relates to MailList.MailListID
The primary key will be the combination of both fields.

Create the 2 relationships in the Relationsips window (Tools menu.)

The interface will now be a main form bound to the Client table, with a
subform bound to the 3rd table. Display the subform in continuous view. Use
a combo where the user can choose the MailList from a combo box that is fed
from the MailList table (#2 above.) Now you can add the person to as many
mailing lists as you need, one per row in the subform.

That approach is the standard solution to a many-to-many problem, i.e. a
person can be on many different mailing lists, and a mailing list also
contains many different people. For another example of this kind of
structure, see:
Relationships between Tables (School Grades example)
at:
http://allenbrowne.com/casu-06.html

If all of that is really basic and obvious to you, you may be asking some
more advanced questions, such as how you can send a mailing to families,
committees, and organizations, not just individuals. If you are ready to
face that questions, see:
People in households and companies - Modelling human relationships
at:
http://allenbrowne.com/AppHuman.html

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> The current method I use for matching mailing lists is a join via a
> created
[quoted text clipped - 6 lines]
> with this method is sometimes very low, and I was wondering what sort of
> methods other people use to match up lists.

Reply to this Message

In need of assistance - 20 Jul 2006 18:51 GMT

Well it's not so much a problem with native data that I have already.
Althougth the information on modelling human relationships gave me a fresh
prospective on that set of problems.

My situation involves receiving poorly normalized data from outside
sources that I want to match to my current set clients.

For instance I have my native list of clients and then from an outside
source of I receive a list of people that are left handed. I want to flag
all of my clients that are left handed and I need a good way to match them to
the outside list.

What are some of the solutions for dealing with that sort of a problem?

> Your Client table should have a primary key field - typically an AutoNumber
> named ClientID.
[quoted text clipped - 42 lines]
> > with this method is sometimes very low, and I was wondering what sort of
> > methods other people use to match up lists.

Reply to this Message

Allen Browne - 21 Jul 2006 03:16 GMT

Okay, you can't normalize the data, or guarantees uniqueness, so you want to
play percentages.

For imports, I would try:
- the full surname, and
- either full firstname or initial (depending on how the data actually
looked), and
- the first 8 characters of the (address which gets you past the street
number so you are matching on the street name as well), and
- the full zip code (as you have.)

Presumably date-of-birth is not available or not consistent here.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Well it's not so much a problem with native data that I have already.
> Althougth the information on modelling human relationships gave me a fresh
[quoted text clipped - 70 lines]
>> > of
>> > methods other people use to match up lists.

Reply to this Message