find duplicates??

[Phil]

Hi.
I have a table containing primary address, secondary address,
road,town, postcode, there are otherfields within the table requestid,
round, day, etc.

I want to find all instances of the address if it occurs > 2 in a
given period. I need a report that gives the address and other fields.
I have tried using the find duplicates wizard, i need to run a query
twice, once to pick up the primary address and street and postcode and
one to pick up the secondary address etc. I can then append the
information together with the count(*) to a single table.

At this point it unravels! If I use a query to amalgamate the rest of
the required data, I the get the duplicates back. I have been scouring
the back posts to no avail. so far. I'm sure that there is probably a
perfectly simple solution. but I can't see it. any pointers would be
very much appreciated.
Tia.
Phil

[King Ron]

Ola Phil:

You are making a critical mistake by allowing more than one column of
any particular entity type in your base tables. If you have control
over the database structure you could simplify the problem if you
eliminated the 'Secondary Address' redundancy in the base table. To
normalize this structure you would create an 'aAddress' table linked
back to the address holder on the holder's primary key and containing a
flag to indicate what type of address each aAddress record contains.
This way you can have any number of addresses for an entity without
creating actual duplicates in the holder's base table, and you will
only need a single set of queries to extract information rather than
queries that need to be UNION'ed together.

However, relationally speaking, if you are joining this aAddress table
back to the Holder table, you will return one row in the result set for
each record in the address table, and any selected columns from the
singly-occuring Holder table will be duplicated in the output. That's
the beauty (and curse) of relational algebra.

To work around this limitation, you would normally create your GUI as a
Form(Holder)/SubForm(aAddress) view, by separately binding the aAddress
extract query to a subform displayed as a continuous form on the
Holder's form. This way the master (Holder) record displays singly,
while the user can scroll thru the linked addresses by navigating on
the subform.

If you can't control the database structure, see the online help for
UNION queries.

Kin Ron of Chi

[Tom Warren]

Hi Phil,
Address parsing/matching is a real pain, I know I done 100's of
millions. I don't use a commercial parser so I can't recommend one, but
I'm sure I've seen some in my web travels. You could GOOGLE for
"Address parsing" or "Address matching"and you should turn something
up.

Maybe someone in this group uses one that they would recommend.
Sorry not much help
Tom