Postal Code Master table - Revision history

Ttenbergen: /* Maintenance and Validation of Master table */

2025-08-04T17:33:09Z

Maintenance and Validation of Master table

← Older revision		Revision as of 12:33, 4 August 2025
Line 45:		Line 45:

	We have a directory with various postal code lists we have used for comparison over time at		We have a directory with various postal code lists we have used for comparison over time at
			* {{S:\MED\MED CCMED\|CCMDB_Special_Projects\PostalCode}}
			The file '''''!ReadMe Postal Code Data.docx''''' in that directory has additional information.

	~~The file ''!ReadMe Postal Code Data.docx'' in that directory has additional information.~~
	* {{S:\MED\MED CCMED\|CCMDB_Special_Projects\PostalCode}}
	{{DJ \|		{{DJ \|
	* Do we want a regular update process? See comments in that file. [[User:Ttenbergen\|Ttenbergen]] 12:15, 4 August 2025 (CDT)		* Do we want a regular update process? See comments in that file. [[User:Ttenbergen\|Ttenbergen]] 12:15, 4 August 2025 (CDT)

Ttenbergen: /* Data Structure */

2025-08-04T17:24:24Z

Data Structure

← Older revision		Revision as of 12:24, 4 August 2025
Line 12:		Line 12:
	\|\| '''PCODE''' \|\| text(6) \|\| the postal code		\|\| '''PCODE''' \|\| text(6) \|\| the postal code
	\|-		\|-
	\|\| rhaname \|\| text(17) \|\| name of ~~regional health authority~~ that the postal code is in		\|\| rhaname \|\| text(17) \|\| name of [[Manitoba RHAs]] that the postal code is in
	\|-		\|-
	\|\| CA_name \|\| text(22) \|\|		\|\| CA_name \|\| text(22) \|\|

Ttenbergen: /* Data Integrity Checks */

2025-08-04T17:15:45Z

Data Integrity Checks

← Older revision		Revision as of 12:15, 4 August 2025
Line 39:		Line 39:

	== Data Integrity Checks ==		== Data Integrity Checks ==
	* Management of this table has always been driven only by resolving inconsistencies [[query NDC_Bad_Postal_Code]]; our [[Data Processor]] updates Master when she finds that a previously unused code shows up in our data and is found to exist on the Canada Post website. See [[Query NDC_Bad_Postal_Code#CFE Data Integrity Checks Steps]].		Management of this table has always been driven only by resolving inconsistencies [[query NDC_Bad_Postal_Code]]; our [[Data Processor]] updates Master when she finds that a previously unused code shows up in our data and is found to exist on the Canada Post website. See [[Query NDC_Bad_Postal_Code#CFE Data Integrity Checks Steps]].

	== Maintenance and Validation of Master table ==		== Maintenance and Validation of Master table ==

Ttenbergen: /* Maintenance and Validation of Master table */

2025-08-04T17:15:25Z

Maintenance and Validation of Master table

← Older revision		Revision as of 12:15, 4 August 2025
Line 41:		Line 41:
	* Management of this table has always been driven only by resolving inconsistencies [[query NDC_Bad_Postal_Code]]; our [[Data Processor]] updates Master when she finds that a previously unused code shows up in our data and is found to exist on the Canada Post website. See [[Query NDC_Bad_Postal_Code#CFE Data Integrity Checks Steps]].		* Management of this table has always been driven only by resolving inconsistencies [[query NDC_Bad_Postal_Code]]; our [[Data Processor]] updates Master when she finds that a previously unused code shows up in our data and is found to exist on the Canada Post website. See [[Query NDC_Bad_Postal_Code#CFE Data Integrity Checks Steps]].

	=== Maintenance and Validation of Master table ===		== Maintenance and Validation of Master table ==
	We maintain our own [[Postal_Code_Master table]] because access to a canonical record set has always been problematic. We have at times received copies of this data, but not found a source for regular updates.		We maintain our own [[Postal_Code_Master table]] because access to a canonical record set has always been problematic. We have at times received copies of this data, but not found a source for regular updates.

Ttenbergen: /* Data Integrity Checks / moved files from \\ad.wrha.mb.ca\WRHA\HSC\shared\MED\MED_CCMED\QA to consolidate in S:\MED\MED CCMED|CCMDB_Special_Projects\PostalCode; left shortcut there.

2025-08-04T17:15:04Z

Data Integrity Checks: moved files from * \\ad.wrha.mb.ca\WRHA\HSC\shared\MED\MED_CCMED\QA to consolidate in S:\MED\MED CCMED|CCMDB_Special_Projects\PostalCode; left shortcut there.

← Older revision		Revision as of 12:15, 4 August 2025
Line 44:		Line 44:
	We maintain our own [[Postal_Code_Master table]] because access to a canonical record set has always been problematic. We have at times received copies of this data, but not found a source for regular updates.		We maintain our own [[Postal_Code_Master table]] because access to a canonical record set has always been problematic. We have at times received copies of this data, but not found a source for regular updates.

	~~{{Discuss \| JALT~~		We have a directory with various postal code lists we have used for comparison over time at
	We ~~do not~~ have a ~~central process to update~~ postal ~~codes as new ones are added to the system. Do~~ we need a formal process? Is the current ad-hoc process enough. Or do we need a decision on allowing these inconsistencies and just documenting them? [[User:Ttenbergen\|Ttenbergen]] 15:55, 24 June 2025 (CDT)
	}}


	~~Our files~~ for ~~this, with documentation and names, are on~~
	* \\ad.wrha.mb.ca\WRHA\HSC\shared\MED\MED_CCMED\QA
	* S:\MED\MED_CCMED\CCMDB_Special_Projects\PostalCode

	~~{{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)
	*** I have since then found a listing of postal codes through stats can, we may need a process to maintain the master beyond what this page provides. [[User:Ttenbergen\|Ttenbergen]] 15:23, 24 June 2025 (CDT)
	}}

			The file ''!ReadMe Postal Code Data.docx'' in that directory has additional information.
	{{~~Discuss \| who = Tina~~ \| ~~question=~~		* {{S:\MED\MED CCMED\|CCMDB_Special_Projects\PostalCode}}
	* 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)~~		{{DJ \|
	** 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)~~		* Do we want a regular update process? See comments in that file. [[User:Ttenbergen\|Ttenbergen]] 12:15, 4 August 2025 (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)
	}}		}}

	~~=== Initial Import ===~~
	~~old master imports can be found at~~
	~~{{S:\MED\MED_CCMED}}CCMDB\z_archive\PostalCode~~

	== Log ==		== Log ==

Ttenbergen at 16:48, 4 August 2025

2025-08-04T16:48:07Z

← Older revision		Revision as of 11:48, 4 August 2025
Line 70:		Line 70:
	}}		}}

	== ~~File Locations~~ ==		=== Initial Import ===
	old master imports can be found at		old master imports can be found at
	{{S:\MED\MED_CCMED}}CCMDB\z_archive\PostalCode		{{S:\MED\MED_CCMED}}CCMDB\z_archive\PostalCode

Ttenbergen: /* Data Integrity Checks */ just dumping a bunch of stuff here for now, sorting it out shortly.

2025-08-04T16:40:17Z

Data Integrity Checks: just dumping a bunch of stuff here for now, sorting it out shortly.

@@ Line 39: / Line 39: @@
 == Data Integrity Checks ==
 {{Discuss | JALT
-* Management of this table has always been driven only by resolving inconsistencies [[query NDC_Bad_Postal_Code]], and there have not been cross checks for this table itself. We found 2025-06 that there were inconsistencies between postal codes and province entries in this table, and Pagasa found duplicate postal codes. Also, we do not have a central process to update postal codes as new ones are added to the system. Do we need a formal process? Is the current ad-hoc process enough. Or do we need a decision on allowing these inconsistencies and just documenting them? [[User:Ttenbergen|Ttenbergen]] 15:55, 24 June 2025 (CDT)
+We do not have a central process to update postal codes as new ones are added to the system. Do we need a formal process? Is the current ad-hoc process enough. Or do we need a decision on allowing these inconsistencies and just documenting them? [[User:Ttenbergen|Ttenbergen]] 15:55, 24 June 2025 (CDT)
 }}
 == Log ==

Ttenbergen: Text replacement - "[[Centralized data.accdb]]" to "Centralized data.accdb"

2025-07-23T21:38:29Z

Text replacement - "[[Centralized data.accdb]]" to "Centralized data.accdb"

← Older revision		Revision as of 16:38, 23 July 2025
Line 1:		Line 1:
	{{DISPLAYTITLE:Postal_Code_Master table}}Postal_Code_Master table in [[[[Centralized data.accdb]]]] is used by [[query NDC_Bad_Postal_Code]] to detect by exclusion [[Postal Code]] entries that are likely invalid.		{{DISPLAYTITLE:Postal_Code_Master table}}Postal_Code_Master table in [[Centralized data.accdb]] is used by [[query NDC_Bad_Postal_Code]] to detect by exclusion [[Postal Code]] entries that are likely invalid.

	The table on its own would be about 71MB, (30MB with all extra info stripped out) which is why the check was not included in [[CCMDB.accdb]].		The table on its own would be about 71MB, (30MB with all extra info stripped out) which is why the check was not included in [[CCMDB.accdb]].

Ttenbergen: Text replacement - "Centralized_data.mdb" to "Centralized data.accdb"

2025-07-23T20:50:03Z

Text replacement - "Centralized_data.mdb" to "Centralized data.accdb"

← Older revision		Revision as of 15:50, 23 July 2025
Line 1:		Line 1:
	{{DISPLAYTITLE:Postal_Code_Master table}}Postal_Code_Master table in [[~~Centralized_data~~.~~mdb~~]] is used by [[query NDC_Bad_Postal_Code]] to detect by exclusion [[Postal Code]] entries that are likely invalid.		{{DISPLAYTITLE:Postal_Code_Master table}}Postal_Code_Master table in [[[[Centralized data.accdb]]]] is used by [[query NDC_Bad_Postal_Code]] to detect by exclusion [[Postal Code]] entries that are likely invalid.

	The table on its own would be about 71MB, (30MB with all extra info stripped out) which is why the check was not included in [[CCMDB.accdb]].		The table on its own would be about 71MB, (30MB with all extra info stripped out) which is why the check was not included in [[CCMDB.accdb]].

Ttenbergen at 20:55, 24 June 2025

2025-06-24T20:55:48Z

← Older revision		Revision as of 15:55, 24 June 2025
Line 37:		Line 37:
	* https://www.serviceobjects.com/blog/free-zip-code-and-postal-code-database-with-geocoordinates/		* https://www.serviceobjects.com/blog/free-zip-code-and-postal-code-database-with-geocoordinates/
	** This data has some errors... there is no free and regularly updated source of this that we know of.		** This data has some errors... there is no free and regularly updated source of this that we know of.

			== Data Integrity Checks ==
			{{Discuss \| JALT
			* Management of this table has always been driven only by resolving inconsistencies [[query NDC_Bad_Postal_Code]], and there have not been cross checks for this table itself. We found 2025-06 that there were inconsistencies between postal codes and province entries in this table, and Pagasa found duplicate postal codes. Also, we do not have a central process to update postal codes as new ones are added to the system. Do we need a formal process? Is the current ad-hoc process enough. Or do we need a decision on allowing these inconsistencies and just documenting them? [[User:Ttenbergen\|Ttenbergen]] 15:55, 24 June 2025 (CDT)
			}}

	== Log ==		== Log ==

Postal Code Master table - Revision history

Ttenbergen: /* Maintenance and Validation of Master table */

Ttenbergen: /* Data Structure */

Ttenbergen: /* Data Integrity Checks */

Ttenbergen: /* Maintenance and Validation of Master table */

Ttenbergen: /* Data Integrity Checks */ moved files from * \\ad.wrha.mb.ca\WRHA\HSC\shared\MED\MED_CCMED\QA to consolidate in S:\MED\MED CCMED|CCMDB_Special_Projects\PostalCode; left shortcut there.

Ttenbergen at 16:48, 4 August 2025

Ttenbergen: /* Data Integrity Checks */ just dumping a bunch of stuff here for now, sorting it out shortly.

Ttenbergen: Text replacement - "[[Centralized data.accdb]]" to "Centralized data.accdb"

Ttenbergen: Text replacement - "Centralized_data.mdb" to "Centralized data.accdb"

Ttenbergen at 20:55, 24 June 2025

Ttenbergen: /* Data Integrity Checks / moved files from \\ad.wrha.mb.ca\WRHA\HSC\shared\MED\MED_CCMED\QA to consolidate in S:\MED\MED CCMED|CCMDB_Special_Projects\PostalCode; left shortcut there.