Query NDC Bad Postal Code
|Data Integrity Checks|
|Summary:||Cross check of postal codes against known postal codes|
|Related:||Postal Code field|
|App:||Centralized data front end.accdb|
|Uses L Problem table:||not relevant for this app|
You mentioned that you occasionally got patients with letters rather than numbers; if that happens again, pls let me know.
CFE Data Integrity Checks Steps
- if the patient has Province = Manitoba or CF :
- 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
- Open Table Postal_Code_Master
- update/add code
- you only need to fill in the fields PCode, rhaname, RHA_Acronym and town, we will eliminate the other fields.
old master imports can be found at X:\CCMDB\z_archive\PostalCode
Where was it obtained
Wikipedia Postal codes listings across Canada
- at the top of the article shows the the first letter that a postal code starts with for a province.
- see Manitoba RHAs
2014-09-29 Cleaning to start end of August 2016
- 2021-01-27 - changed query to only look at the last year of data to make it faster; any older records were already fixed