Query NDC Bad Postal Code

Data Integrity Checks
Summary:	Cross check of postal codes against known postal codes
Related:	Postal Code field
Firmness:	hard check
Timing:
App:	Centralized data front end.accdb
Coding:	query NDC_Bad_Postal_Code
Uses L Problem table:	not entered
Status:	implemented
Implementation Date:	2014-09-24
Backlogged:	true

• Cargo

• SMW

• Categories:  

• form:

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

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.

I think this is the process where you said you are having problems with copy/pasting. Copy pasting isn't even mentioned here, so maybe update the process to show how you actually do this, so that someone like Sheila Rusnak would be able to follow the instructions. Ttenbergen 15:45, 2022 March 17 (CDT)

• SMW

• Cargo

• Categories

File Locations

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

Where was it obtained

• FROM Legacy staff: 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

Log

• 2021-01-27 - changed query to only look at the last year of data to make it faster; any older records were already fixed