[AccessD] Zip+4?

the system I use is Accuzip, a company in California.  It is not cheap but if your volumes are high 
enough it is worthwhile.

Accuzip runs locally on my machine, and performs the CASS on my server.  They provide me with an 
update quarterly which includes the CASS database from the USPS and a program that compares 
addresses against that CASS database.  When the "validation" process (what they call the CASS) 
finishes, I have a new table (DBF) that contains all of my fields plus added fields which explain 
errors found etc.  Included in this database is the zip4 for every address that they think is valid.

They then have an NCOA server which (if you buy that service) a part of the program uploads (FTPs) 
the CASSed data plus person name to the server.  The data is run against the NCOA database and then 
FTPed back down to my server and from there exported to CSV for me to get back into my machine.

As you can see, when I am done I have a list of "valid addresses" WITH zip 4 as well as a list of 
NCOA changes back 18 months.  That includes the old address and the new address if any.

John W. Colby
www.ColbyConsulting.com


William Hindman wrote:
> JC
> 
> ...to my utter chagrin, I already know far too much about CASS and NCOA 
> ...the client uses a mailing house that validates his data against the US 
> post office's data and rejects anything that doesn't match ...there is an 
> additional service which we used once for address correction based upon NCOA 
> data but basically what we got was a list of rejections without any data on 
> WHY they were rejected ...with a paltry few actual updates ...he now does 
> this on a quarterly basis against his entire address list.
> 
> ...but that is only the reason the +4 has become a problem, not a solution 
> to it ...as I noted, the problem is that most zips are still 5 digit but the 
> number of +4 zips is increasing rapidly and the US postal service is now 
> rejecting addresses that it, in its indomitable wisdom, thinks should have 
> the +4 ...but I know of no way to know this before the data is actually 
> rejected by them ...thus I can't force an initial +4 data entry because in 
> most cases it doesn't yet exist ...so I'm dependent upon the data entry 
> people actually entering the +4 data entry, when its available, in a 
> separate field AFTER they've used the Geo cbo to select the right geo record 
> ...and they just are not doing it consistently.
> 
> ...any ideas besides colbyizing them all would be appreciated :)
> 
> William
> 
> --------------------------------------------------
> From: "jwcolby" <jwcolby at colbyconsulting.com>
> Sent: Wednesday, February 11, 2009 12:50 PM
> To: "Access Developers discussion and problem solving" 
> <accessd at databaseadvisors.com>
> Subject: Re: [AccessD] Zip+4?
> 
>> William,
>>
>> Correctly handling the postal services requirements is a non-trivial task. 
>> The process is called
>> CASS and NCOA.  CASS stands for Coding Accuracy Support System.
>>
>> http://www.nextmark.com/glossary/definition.jsp?glossaryTermId=a0800000000EqQrAAK
>>
>> What is essentially does is to verify the addresses against USPS supplied 
>> database (which changes
>> quarterly), and if a given address passes CASS it means that the address 
>> represents an actual
>> deliverable address.
>>
>> NCOA stands for National Change of Address, and it has its own 
>> requirements but in general the USPS
>> wants all addresses CASSed and NCOAed no more than 90 days before use to 
>> qualify for bulk mail
>> discounts and such.
>>
>> NCOA states that a specific person moved (no longer lives at the previous 
>> address) and may / may not
>> supply the current address.  NCOA is a much more nebulous thing than CASS 
>> simply because people may
>> or may not live at an address at all, may have moved and not filed a COA, 
>> may have lived there but
>> moved years ago etc.
>>
>> I am heavily involved in CASS / NCOA, and in fact I am able to provide 
>> such processing if desired.
>>
>> John W. Colby
>> www.ColbyConsulting.com
>>
>>
>> William Hindman wrote:
>>> Group
>>>
>>> I use a separate tblGeo table to manage
>>> city/state/post/country/region/timezone/dst/lat/long data with an fk join 
>>> on
>>> tblOrgAddress.
>>> This is presented as an enforced combo with any new org address entry, 
>>> thus
>>> ensuring that only valid entries are allowed for that data.
>>> When US zip codes were only 5 digits, this worked quite well ...but the
>>> growing number of +4 zips is beginning to grow out of control ...adding 
>>> the
>>> +4 means an additional 10k entries per US post code are possible ...which
>>> would drive the db to its knees.
>>>
>>> So I've separated out the +4 and isolated them in the tblOrgAddress to 
>>> limit
>>> the impact on lookups and just concatenate them when I assemble 
>>> addresses.
>>> This is of course time and code intensive in its own right but limited to
>>> only those times when a full mailing address is required.
>>>
>>> The problem comes in requiring data entry people to enter the +4 separate
>>> from the post code ...it just isn't getting done ...and the client is now
>>> seeing address rejections from the postal service bulk mailing because 
>>> the
>>> +4 is missing.
>>>
>>> Question is, does anyone have a better way of handling the zip+4 issue?
>>>
>>> William
>>>
>>>
>>>
>> -- 
>> AccessD mailing list
>> AccessD at databaseadvisors.com
>> http://databaseadvisors.com/mailman/listinfo/accessd
>> Website: http://www.databaseadvisors.com
>>
>