CCMDB Wiki

Query NDC Bad Postal Code: Difference between revisions

← Older edit
→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.
 
(20 intermediate revisions by 2 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_timing =
|DIC_coding=query ''NDC_Bad_Postal_Code''
| DIC_app = Centralized data front end.accdb
|DIC_implementation_date=2014-09-24
| DIC_coding = query ''NDC_Bad_Postal_Code''
|DIC_status=implemented
| 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. Also see [[query NDC_Postal_Code_over_time]].


Line 19: 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 Table Postal_Code_Master]] if postal code was correct and just not in our list
*#* 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 25: 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 [[Table Postal_Code_Master]] ===
=== update to [[Postal_Code_Master table]] ===
# Open [[Table Postal_Code_Master]]  
''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.


{{Discuss | who = Pagasa | question=
== Maintenance and Validation of Master table ==
* 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. [[User:Ttenbergen|Ttenbergen]] 15:45, 2022 March 17 (CDT)
see [[Postal_Code_Master table#Maintenance and Validation of Master table]] for other update processes we have for [[Postal_Code_Master table]]''
}}
 
== File Locations ==
old master imports can be found at
X:\CCMDB\z_archive\PostalCode
 
==Where was it obtained==
* FROM [[p:Randy Martens | Legacy staff: Randy Martens]] TO [[p:Julie Mojica]]
integrated with
* http://geocoder.ca/?freedata=1
 
==Wikipedia Postal codes listings across Canada==
[http://en.wikipedia.org/wiki/List_of_postal_codes_in_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.
 
[http://www.canadapost.ca/cpc2/addrm/hh/maps/FSA/CA01.pdf 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==
== 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
* 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 ==
Line 63: Line 43:




[[Category: Data Processing]]
[[Category:Data Processing]]
[[Category: Postal Codes]]
[[Category:Postal Codes]]
[[Category: Registry checks]]
[[Category:Registry checks]]
Retrieved from "https://ccmdb.kuality.ca/index.php?title=Query_NDC_Bad_Postal_Code"