A long term solution is to set up a separate related table of address records. People with the same address would then link to the same address record. This makes it possible to set up a set of records in the Address table and you'll get one record for each address.
Here's a possible short term solution for working with your data as it currently exists:
It's possible to "mostly" remove your duplicate addresses, by the following method, but it will fail in cases where two people have addresses entered that are really the same address but some minor detail, such as "ave", "suite" or such is entered differently in their individual records.
Define a calculation field that combines all address fields into a single string. Something like:
addressLine1 & " " & AddressLine2 & " " & City & " " & zipcode.
Enter layout mode and double click the "body" part label to change the body part of your labels layout to a Sub Summary part "when sorted by" this new calculation field.
Then sort your records by this field and see what you get when you preview this layout to see if you get the correct set of labels laid out in columns (if you are printing an 'avery' type sheet of address labels). We may need to make a change in layout set up if it does not preview correctly.
Thank you so much for your response. I will employ the long term solution to future projets but the short term solution might just work out for this one. I do need to put a line break or something in between the address and the city, state, etc.. to have the avery label print correctly.
One more question, is there a way to get a count of the records showing? The record count shows the total records found not the total when sorted. Also, when I export this sorted mail list it exports all records (including the duplicates). If I am asking too much of the short term solution then I will go ahead and employ your long term suggestion.
Again, Thanks for the help.
The calculation field I recommeneded is not for printing, just to group records with the same address. It need not be visible on any layout for this to work.
Actually, it is counting your records. It's just not counting your addresses as you have multiple records for some addresses. There is a method to get the count you want but it's kinda hairy. Check out this link and decide if you want to try it: How to count the number of unique occurences in field.
When it comes to exporting the data, you can get one row per summized group if you use the "group by" options when exporting the data.
Export worked perfectly. Thank you. I will work on the other count at a later time.
Your help is much appreciated.