Query NDC Bad Postal Code
Jump to navigation
Jump to search
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 |
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.
PWC
|
|
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 :
- check the postal code in EPR, correct if different than ours
- 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 Postal_Code_Master table 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 Postal_Code_Master table
- Open Postal_Code_Master table
- update/add code
- you only need to fill in the fields PCode, rhaname, RHA_Acronym and town, we will eliminate the other fields.
|
- Fixing and adding Postal code to the Master Postal Code
- Create a query need the D_ID, Phin and Postal Code, use Log_PHI Table.
- Open the Postal Code Master if you need to add a Postal Code
- Open the EPR to check the address of the patient
- Check first the Postal Code in EPR - then go to Address
- If not the same get the Province
- Go to Postal Code Master filter by Province then by Town
- Click ctrl C to copy and ctrl V to paste then search to the column Postal code , but erase the last letter and number of PC.
- Sort the Postal Code ,
PTorres 17:06, 2022 March 17 (CDT)
File Locations
old master imports can be found at X:\CCMDB\z_archive\PostalCode
Where was it obtained
integrated with
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