Grouping duplicate/nonstandardized names and addresses AND Removingthe "First of" label when using t

[Dave K]

Hello:

I may be approaching this the wrong way, so feel free to direct me
otherwise.

I am trying to group a list of duplicative data (names, addresses,
etc) that does not have a unique identifier, other than their full
name (which in some cases can be more than one person). I am trying to
do decent job of reducing the nonstandardized entries to a single
entry.

To do this, I am first concatenating and grouping by a short name
(such as the first four 8 letters of their name, plus their zip code),
using the left function. When I run a totals query, I group by the
short name, and use a "FIRST" option for all other fields.

FYI, I will be using the list to mail out mailer on a regular basis
going forward. I will also be merging the list with two other similar
lists at some point.

So, I guess I have 2 questions:

1) For anyone who has experience with doing this, is there a better
way to clean up data? Am I overlooking something?

2) Is there a way to opt out of having the new labels read "First of"
for each of my labels...because I am needing to run the queries
several times...which makes it a hassle to delete the first of label
every time.

Thanks very much for any suggestions.

 

[Allen Browne]

De-duping is messy, and generally does need scanning with a human eye to see
if you are getting the results you desire. What you are doing makes sense
(though I would have chosen first 4 of surname + first 4 of firstname + zip,
which still doesn't solve father and son with same name at same address.)

Re #2, be careful if you use an alias with the same name as a field in the
underlying table. Access can get confused by that. You don't have to have
the FirstOf prefix, but you do need an alias that's different than the
original field name. For example, you could use Suburb instead of
FirstOfCity.