Query NDC Bad Postal Code

We cross-check the postal codes we collect as part of Postal Code field collection against a list of known-good postal codes.

Template:CFE Data Integrity Checks Steps

Comparisons are done as part of vetting via NDC query NDC_Bad_Postal_Code in CFE. Steps will be:

• if the patient has Province = Manitoba or CF :
  1. check the postal code in EPR, correct if different than ours
  2. if same as ours, check the address in EPR against Canada Post Postal Codes;
     • update patient's postal code if it was wrong or
     • make #update to Table Postal_Code_Master if postal code was correct and just not in our list
• if the patient is from any other province:
  • if the Postal Code's first letter is not for the province where the patient lives you need to check EPR or internet to put the correct Postal code
  • If the first letter is appropriate for province but triggers because it's not in the master list, then register the D_ID with Known data errors with query name "NDC_Bad_Postal_Code"
• if the patient has Province = (US or NK or OS) there should be no postal code so query should not trigger at all

update to Table Postal_Code_Master

1. Open Table Postal_Code_Master
2. update/add code
   • you only need to fill in the fields PCode, rhaname, RHA_Acronym and town, we will eliminate the other fields.

File Locations

old master imports can be found at X:\CCMDB\z_archive\PostalCode

Where was it obtained

• From Randy Martens to p:Julie Mojica

integrated with

• http://geocoder.ca/?freedata=1

Wikipedia Postal codes listings across Canada

Wikipedia, Postal codes in Canada

• at the top of the article shows the the first letter that a postal code starts with for a province.

Map showing first letter used in postal codes across Canada

RHA Listings

• see Manitoba RHAs

Start Date

2014-09-29 Cleaning to start end of August 2016