Returning one record per address from a contacts database

[David Rothbauer]

I need to filter out returns so I'm only getting one record where the fields
address1,address2,city,province,postal code and country match those in other
records.

I'm printing mailing labels and I only want one label per household.

I'm stumped.

Thanks

 

[John Spencer]

The easiest way if you don't care which name of several is on the
mailing label is to use an aggregate query where you group on the common
items of the address and return the FIRST of the items that are not in
common. SOMETHING like

SELECT First(FirstName) as FName, First(LastName) as LName
, address1,address2,city,province,[postal code], country
FROM SomeTable
GROUP BY address1,address2,city,province,[postal code], country
FROM SomeTable

In Query Design view
-- Add the table
-- Add the fields you want to see
-- Select VIEW: Totals from the menu
-- Change GROUP BY to FIRST only under the fields you don't want
consolidated. Leave Group By where you want the repeating fields rolled
up into one.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================