Query NDC Bad Postal Code: Difference between revisions
Jump to navigation
Jump to search
Ttenbergen (talk | contribs) →update to Postal_Code_Master table: removed old question and the incomplete steps that it was about, the instructions here look like they would be sufficient. |
|||
| (27 intermediate revisions by 3 users not shown) | |||
| Line 1: | Line 1: | ||
{{Data Integrity Check | {{Data Integrity Check | ||
| DIC_summary = Cross check of [[Postal Code field|postal codes]] against known postal codes | |DIC_summary=Cross check of [[Postal Code field|postal codes]] against known postal codes | ||
| DIC_related_concepts = Postal Code field | |DIC_related_concepts=Postal Code field | ||
| DIC_firmness = hard check | |DIC_firmness=hard check | ||
|DIC_coding=query ''NDC_Bad_Postal_Code'' | |||
|DIC_implementation_date=2014-09-24 | |||
| DIC_coding = query ''NDC_Bad_Postal_Code'' | |DIC_status=implemented | ||
|DIC_LProblem=No | |||
| DIC_implementation_date = 2014-09-24 | |DIC_app=Centralized data front end.accdb | ||
|DIC_backlogged=No | |||
}} | }} | ||
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]]. | |||
We cross-check the postal codes we collect as part of [[Postal Code field]] collection against a list of known-good postal codes. | |||
== CFE Data Integrity Checks Steps == | == CFE Data Integrity Checks Steps == | ||
| Line 21: | Line 19: | ||
*# if same as ours, check the address in EPR against [https://www.canadapost.ca/cpo/mc/personal/postalcode/fpc.jsf Canada Post Postal Codes]; | *# if same as ours, check the address in EPR against [https://www.canadapost.ca/cpo/mc/personal/postalcode/fpc.jsf Canada Post Postal Codes]; | ||
*#* update patient's postal code if it was wrong '''or''' | *#* update patient's postal code if it was wrong '''or''' | ||
*#* make [[#update to | *#* 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 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 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 | ||
| Line 27: | Line 25: | ||
* if the patient has '''Province = (US or NK or OS)''' there should be no postal code so query should not trigger at all | * 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 [[ | === update to [[Postal_Code_Master table]] === | ||
# Open [[ | ''see [[Postal_Code_Master table#Maintenance and Validation of Master table]] for other update processes we have for [[Postal_Code_Master table]]'' | ||
# Open [[Postal_Code_Master table]] | |||
# update/add code | # update/add code | ||
#* you only need to fill in the fields '''PCode, rhaname, RHA_Acronym and town''', we will eliminate the other fields. | #* you only need to fill in the fields '''PCode, rhaname, RHA_Acronym and town''', we will eliminate the other fields. | ||
== | == Maintenance and Validation of Master table == | ||
see [[Postal_Code_Master table#Maintenance and Validation of Master table]] for other update processes we have for [[Postal_Code_Master table]]'' | |||
== | == 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 | |||
* 2016-08-end - cleaning started | |||
* 2014-09-24 - implemented | |||
== Related articles == | == Related articles == | ||
{{Related Articles}} | {{Related Articles}} | ||
[[Category: Data Processing]] | [[Category:Data Processing]] | ||
[[Category: Postal Codes]] | [[Category:Postal Codes]] | ||
[[Category: Registry checks]] | [[Category:Registry checks]] | ||
Latest revision as of 10:44, 4 August 2025
| Data Integrity Checks | |
| Summary: | Cross check of postal codes against known postal codes |
| Related: | Postal Code field |
| Firmness: | hard check |
| Timing: | always |
| App: | Centralized data front end.accdb |
| Coding: | query NDC_Bad_Postal_Code |
| Uses L Problem table: | No |
| Status: | implemented |
| Implementation Date: | 2014-09-24 |
| Backlogged: | No |
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 :
- 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
see Postal_Code_Master table#Maintenance and Validation of Master table for other update processes we have for 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.
Maintenance and Validation of Master table
see Postal_Code_Master table#Maintenance and Validation of Master table for other update processes we have for Postal_Code_Master table
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
- 2016-08-end - cleaning started
- 2014-09-24 - implemented
Related articles
| Related articles: |