Unduplication headache

[RD]

Hi all,

This really is giving me a headache. Maybe the answer is obvious but I've been
bending my brain around this one for three days.

I need to come up with an unduplicated list (for mailing labels) of clients in
groups CW, FS and MC. The problem is, a client can be in one, two or all three
groups. There is also an internal "Contact" field (looks like XX##). Once
again, a client can have one, two or three different contacts.

Oh! I have to group them by English speaking and Spanish speaking, as well!

Duplication can also occur because of bad data (names spelled two or more
different ways, street address spelled differently, etc.) but I'm not concerning
myself with that. Garbage data is up to the clerical staff to clean up.

Anyway, I get this data from a tilde delimited txt file. I have it as both a
single table and broken into tables by group. I'm getting nowhere using SQL so
maybe there is a VBA solution?

The fields I need for the labels are Name, Address and Contact (so the labels
can be distributed). The fields I need for the associated report are the same
*plus* Group so they can get a count of the population. I've told them that the
count will be wrong because of duplication but they keep bugging me for counts,
anyway.

I have a feeling that this really is impossible, hence the headache.

Any ideas and/or commiserations gratefully accepted.

Thanks,
RD

 

[Guest]

Hi
How about if you put everything in one table with folowing columns: client
name, address, city...., group, language. When everything is in one table
buid a query against it, then in design view go to query properties and play
with UniqueValues and UniqueRecords. I think in your case Unique records
should do a trick. It will clean up your table and you can use it as a base
for your labels. Been there done that

barb

 

[RD]

Thanks for the suggestion, Barb, but that doesn't do it. Everything in the
record could be the same except group (or contact or address or even language).
Access will see that as a unique record.

I made a table that just contains my taget groups and then eliminated the group
field and did a UniqueValues. That dropped my duplications from over 7000 to a
little over 4000 (in an overall recordset of over 28,000).

I think this is going to be a multi-step qury approach. I think I'll break out
all records from one group then run a delete query for those records that are in
both groups. Repeat for next group. I can't see any other way to do it.

Thanks,
RD

 

[Guest]

Hi
I agree.
But it looks like a big mess, I'd go back to my users with this. The impact
is not only in lable printing, but all over this database.

Barb

 

[RD]

In this context, Access is just a reporting tool. The data I get comes from
text file extracts. We don't get access to the actual database. Frankly, this
being a gov't. db, I'm thankful for that. Unfortunately, we also don't have
access to the logic behind the extracts, yet. Anyway, after pouring over the
data and realizing that there was only so much unduplication I could do I came
up with this:

DELETE Q.ID, *
FROM tEITC_All AS Q
WHERE (((Q.ID)>(SELECT MIN(X.ID)
FROM tEITC_All X
WHERE Q.Name = X.Name And Q.Address1 = X.Address1)));

That eliminated only those records where both the name and the address were the
same. All the same, we're printing up 26, 500 labels. Now I have to figure out
the mail merge and convert that to a PDF file (stupid printers).

Regards,
RD