SQL Query - Filtering Address

[Elmo_loves_you]

Hi Guys

I hope you can help share some light on my problem.

Basically, I have a MS Access table...ID, firstname, surname, Address1, Address2 etc

What I want to do is Select all from table but only show one record where more than one person lives at that address .. Eg only sending one envelope to the one household rather than sending two to the same address ....

I wouldnt have thought it was overly complex, however the combinations that I have been trying from the query builder havent worked ..

I can get a count of the number of records where the same address exists but I can't display a first name aswell.

Your suggestions will be greatly welcomed ... Thanks

Elmo

[LizR]

count items by the address lines or postcode or whatever you decide is the same name, then where records have a count > 2 you join them with a select on name where the address lines = the lines which had more than 2 records.

[dickersonka]

you could also use a group by for your address criteria

http://office.microsoft.com/en-us/access/HA012314821033.aspx