Help with Database Solution

[Stephen]

I volunteer for a non-for profit group and they have alot of names in a
multiple databases. the problem is that some people are in multiple
databases. and if they send out a mailing from multiple databases some
people get 2 and 3 of the same thing. my idea was to either make or find a
program that has everyone is 1 database and then make the people part of
multiple groups. (ex: bill is part of group a and b and im doing a mailing
from both group i only want 1 piece of mail to go to him).

It can be a separate program or with access. that use access now but i cant
figure out how to make my idea work.

any question please ask.
tia
Stephen

[ManningFan]

Try switching to Act! software. It's built for this type of situation.

Short of that, you're going to have to code it from the ground up.
Design it on paper first and think through the way the tables need to
join to get your data to work properly. It may take some time, but
proper database layouts will save you a mountain of headaches in the
future.

Stephen wrote:

I volunteer for a non-for profit group and they have alot of names in a
multiple databases. the problem is that some people are in multiple
databases. and if they send out a mailing from multiple databases some
people get 2 and 3 of the same thing. my idea was to either make or find a
program that has everyone is 1 database and then make the people part of
multiple groups. (ex: bill is part of group a and b and im doing a mailing
from both group i only want 1 piece of mail to go to him).

It can be a separate program or with access. that use access now but i cant
figure out how to make my idea work.

any question please ask.
tia
Stephen

[Jeff L]

Stephen,
I have done something similar where I work. The difficult part is
putting the multiple databases together into one. What I did is I took
one database as my starting point. Then I took a second database and
designed a routine that looped though each record of the second
database to see if that individual was already in my first db. I check
for the person's first name and last name. If it was not found, I then
check to see if there were any records in the first db that had a
variation of the person's first name and matching last name. For
example, Bill and William, John and Jonathan, Sue and Susan, etc. If a
duplicate was found, I saved a record to a table called Duplicates that
contained the data from the record I was checking as well as the
possible duplicate from the database. I then went back and checked the
possible duplicates. If I found a match, I deleted it. If the
possible duplicate was not a match, I then inserted it into the
database.

I hope that gives you a little direction at least.

[Ed Prochak]

Jeff L wrote:

Stephen,
I have done something similar where I work. The difficult part is
putting the multiple databases together into one. What I did is I took
one database as my starting point. Then I took a second database and
designed a routine that looped though each record of the second
database to see if that individual was already in my first db. I check
for the person's first name and last name. If it was not found, I then
check to see if there were any records in the first db that had a
variation of the person's first name and matching last name. For
example, Bill and William, John and Jonathan, Sue and Susan, etc. If a
duplicate was found, I saved a record to a table called Duplicates that
contained the data from the record I was checking as well as the
possible duplicate from the database. I then went back and checked the
possible duplicates. If I found a match, I deleted it. If the
possible duplicate was not a match, I then inserted it into the
database.

I hope that gives you a little direction at least.

I've done something similar for a customer DB on a project a few years
ago. A couple things to consider:
* validating the addresses
* matching name AND address.

There are some software products you can buy (not cheap) that validate
your addresses against the US Postal Service address database. They
used to have a place on their website (usps.com) where you could
manually validate an address against thier DB. Looks like you need
special permission to use that service now. You'll find the link for it
under the Zipcode look up.

When matching names and addresses, it is sometimes easier to match last
first. If you have a potential duplicate based on name, then try
matching on the address. It might be that Bill Jones is the son of
William Jones. And while they live in the same city, they on different
parts of town (different street addresses) Or Bill Jones moved when he
was promoted to VP and now lists his name more formally to go along
with his new job.

For a club you might make a report of potential dups and get some human
input into what is the real situation.

HTH,
Ed

[Ted]

ManningFan wrote:

Try switching to Act! software. It's built for this type of situation.

Act! is an impressive peice of software, but I didn't much like writing
code to interface with it. I encountered a number of bugs in the SDK
for it (but that was long enough ago that the bugs I encountered should
be long fixed).

OTOH, isn't SQL Server Integration Services also designed for this kind
of problem? It was recommended to me, and I have only begun to study
it, but as I recall from what I read about it, I would think it could
be useful here.

Cheers,

Ted