Query NDC Bad Postal Code: Difference between revisions
Jump to navigation
Jump to search
Ttenbergen (talk | contribs) m (→updating the codes: u) |
Ttenbergen (talk | contribs) mNo edit summary |
||
(41 intermediate revisions by 3 users not shown) | |||
Line 1: | Line 1: | ||
{{Data Integrity Check | |||
| DIC_summary = Cross check of [[Postal Code field|postal codes]] against known postal codes | |||
| DIC_related_concepts = Postal Code field | |||
| DIC_firmness = hard check | |||
| DIC_timing = | |||
| DIC_app = Centralized data front end.accdb | |||
| DIC_coding = query ''NDC_Bad_Postal_Code'' | |||
| DIC_status = implemented | |||
| DIC_implementation_date = 2014-09-24 | |||
}} | |||
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]]. | |||
=== | {{Todo | ||
| who = Tina | |||
| todo_added = 2022-03-24 | |||
| todo_action = 2022-07-15 | |||
| question = PWC | |||
* emailed Chastity to see if we could get Postal Code data from them rather than maintaining our own. | |||
** Never got a response. Emailed Chastity again. [[User:Ttenbergen|Ttenbergen]] 14:41, 2022 June 30 (CDT) | |||
}} | |||
== CFE Data Integrity Checks Steps == | |||
Comparisons are done as part of [[vetting]] via NDC query ''NDC_Bad_Postal_Code'' in [[CFE]]. | Comparisons are done as part of [[vetting]] via NDC query ''NDC_Bad_Postal_Code'' in [[CFE]]. | ||
Steps will be: | 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 [https://www.canadapost.ca/cpo/mc/personal/postalcode/fpc.jsf 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. | |||
{{Discuss | who = Tina | question= | |||
* 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) | |||
** Do you create that query each time? Would we be able to update the NDC query that finds these in the first place to include the info you need? We can discuss at our next meeting. [[User:Ttenbergen|Ttenbergen]] 16:23, 2022 March 17 (CDT) | |||
*** Taking this off Pagasa's list for now, since if we can get this data from DSS we won't need to do this any more. [[User:Ttenbergen|Ttenbergen]] 15:36, 2022 March 24 (CDT) | |||
}} | |||
* 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 , | |||
[[User:PTorres|PTorres]] 17:06, 2022 March 17 (CDT) | |||
== File Locations == | == File Locations == | ||
Line 15: | Line 60: | ||
==Where was it obtained== | ==Where was it obtained== | ||
* | * FROM [[p:Randy Martens | Legacy staff: Randy Martens]] TO [[p:Julie Mojica]] | ||
integrated with | integrated with | ||
* http://geocoder.ca/?freedata=1 | * 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== | ==Start Date== | ||
2014-09-29 | 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 | |||
== Related articles == | |||
{{Related Articles}} | |||
[[Category: Data Processing]] | [[Category: Data Processing]] | ||
[[Category: Postal Codes]] | |||
[[Category: Registry checks]] |
Latest revision as of 12:03, 2023 May 4
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