A better mousetrap: Matching mailing lists...

[Guest]

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.

 

[Allen Browne]

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

in message
news:[email protected]...

 

[Guest]

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.

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

in message

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.

 

[Allen Browne]

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

in message

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.

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

"In need of assistance" <[email protected]>
wrote
in message

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.