Select with partial group

J

John

Hi

I have a clients table on which I want to run a select query on to extract
all fields. But I also want the result-set to be grouped by Company and
Postcode to remove any duplicates. The reason I don't want to include other
fields Address line 1 etc as there could be minor defences in spellings,
punctuations etc.

How do I go about such a query?

Thanks

Regards
 
J

John W. Vinson

Hi

I have a clients table on which I want to run a select query on to extract
all fields. But I also want the result-set to be grouped by Company and
Postcode to remove any duplicates. The reason I don't want to include other
fields Address line 1 etc as there could be minor defences in spellings,
punctuations etc.

How do I go about such a query?

Thanks

Regards
Click to expand...

Tom's suggestion to do it directly on the report should be fine; if you want
to create a query for display (but not editing, see below) on a form or for
export, use a Totals query. Group By the Company and Postcode, and use the
"First" operator on the address fields to select the first record in disk
storage order (essentially an arbitrary, uncontrolled selection of one of the
addresses).

This query, like all totals queries, will not be updateable. If you want to
update this data you will probably want to normalize it into a table with one
record per company, related one-to-many to a table containing distinct
information. This would be a step on the way to cleaning up the near-duplicate
addresses (though that will require a USB - Using Someone's Brain - interface
to finish).

John W. Vinson [MVP]
 
Top