Hi everyone,

 Recently the US Post Office in New Jersey implented a new guideline that states that all Apartments, Suites, Units, etc MUST be either placed as Address Line 1 above the actual street name OR they must be on the same line as the address.                                                                  Example:                                                                                                                                                                                                                 Miss Jane Doe                                                                                                                                                                                               Apartment 4B                                                                                                                                                                                                123 Main Street                                                                                                                                                                                                  Newark, NJ  12345

OR

Miss Jane Doe                                                                                                                                                                                              123 Main Street, Apartment 4B                                                                                                                                                                     Newark, NJ 12345

 Most of our records have the street address as LIne 1 and the Apartment as Line 2.    So we need to change all records to reflect the new guideline.

 My question to you RE users, is what is the best/easiest way to do this?    Can we do a global change?   We have over 70,000 constituents in our system! 

 Please help!

 Lauren Fardella, Manager of Membership Services, The Newark Museum

Those have always (or for a long time now) been the postal regulation guidelines.  It is called CASS certification and it is just now that the PO is going to being enforcing the rules they haven't always enforced.

I highly recommend using CASS certification software.  Blackbaud sells one they call AddressAccelerator which is the one I use and it works great.  Not only can you bulk scan your entire database and have it change the address format for you but you can also turn it on to run any time you add a new address or change an address so no un-certified addresses ever get added to the database.

AddressAccelorator would certainly be the best option. 

If it's not an option for your org, I'd suggest an export and import.  If you have other info that you don't want moved to line 1 of the address, it could be a little harder to clean up but I think it's manageable. 

Create a constituent query for records where address line 2 is not blank.  That will give you the records you may need to fix.  Create an export using the query and including address line 1, address line 2 (3-5 if needed) and the address import ID. Each address a record contains has it's own import ID.  Export the file to excel. 

• If all the records need line 1 combined with line 2, you could merge the data in the two fields into one field.  Merging the fields in excel is quite easy.  Leave line 2 field empty.
• If you want the apt/suite # on line 1 and the address on line two you could swap the fields by copying the data and renaming the columns. 
• If you have other address info on line 2 that you don't want moved/swapped you can sort/hide records or refine your query before you use it for exporting. 

After you cleanup the excel file, do in import to get the corrected addresses into RE.  I don't have an address import set up right now to look at but be sure you replace the address.

I have to this type of clean up and more for annual mailing we do to a huge list from our membership dept that are not in RE at this time.  Excel functions have improved greatly over the past. 

I am not sure I would do this for 70,000 records.  That is a lot of work.

I heard a rumor the post office is implementing some new procedures that will actually have all the mail sorted and ready for the mail carrier by stop on their route - which requires barcoding of the addresses I think for bulk mail in order to get the non-profit discounts. I'm not sure exactly how it works - but i think that was the gist of it.  The regulations have been around as long as I can remember as Melissa said - just haven't been enforced. We use a mail house to process ours, so they run the CASS certification on it for any purchased lists, etc.

I'm assuming that even though the data base is 70,000, the number of records needing the correction would be much smaller.  I don't think I'd mess with trying to correct 70,000 either.

In Excel you have the option to concatenate (merge) fields in to one. Knowledgebase solution BB520814 can walk you through the necessary steps.

Knowledgebase solution BB26213 can help with the required fields and steps needed to update existing address fields via import.

I attended a Postal Customer Council Meeting at the Chattanooga Postal Facility last week.  CASS was the previous requirement.  You must now run your mail lists through NCOA.  You can use Address Service Requested on standard mail envelopes, but the addresses must be updated every 95 days instead of every 185.  They have a system called Merlin that checks the standard mailings after November 23, 2008.  If this shows an undeliverable address that has not been updated in 95 days, they can charge you first class postage on those mailings. (correction - those pieces of mail).

The postal rep stated that they spent $1,8 billion dollars handling undeliverable mail last year, and the purpose is to cut down on the number and expense due to returned and forwarded mail.

Elaine Gipson