Manitoba Health "Conditioner": Difference between revisions

(52 intermediate revisions by 3 users not shown)

Line 1:

~~The Manitoba~~ Health (MB Health~~) "Conditioner" ACCESS Program~~

{{LegacyContent

|explanation=MB Health data stopped

|successor=[[MB Health Tool.mdb]]

|content=

~~See [[Crosschecking data with Manitoba Health]] for more information in regards to the process of sending and receiving data to MB Health.~~

~~==What~~ is ~~it?==~~

The Manitoba Health (MB Health) "Conditioner" is an MS Access Program that facilitates [[Crosschecking data with Manitoba Health]] using the [[Manitoba Health Match Indicator Guideline]].

*ACCESS program

~~==Who Developed it?==~~

*ACCESS Conditioner Program ~~developed by Tina as requested by Julie~~.

*DATE: January, 2005

==What does it do?==

*automates the process of identifying corrections to key patient identifiers (PHIN, LN, FN, DOB & Gender) from an EXCEL file that is sent every few months to MB Health for crosschecking purposes and is returned with matches that require corrections in our Master ICU/Medicine Master Database.

*~~highlights~~ in ~~red, the corrections that we need to make~~ in ~~our database.~~

**items highlighted in RED = errors

**items highlighted in GREEN = no errors

==Where do you find it?==

*X:~~\CCMDB_ADMIN_DATABASE\~~MB_Health_Records_Cleaning~~\'''MB Health Conditioner.mdb~~'''

*{{M:|MB_Health_Records_Cleaning}}'''

* a backup copy of conditioner.mdb is also in this directory - [[User:PTorres|PTorres]] 15:20, 2014 May 29 (CDT)

~~==Where do you find~~ a ~~working BACKUP~~ copy~~?==~~

**X:\CCMDB_ADMIN_DATABASE\MB_Health_Records_Cleaning\Others_backup_folder\'''Pagasa_DONT_TOUCH_MB Health Conditioner.mdb~~'''~~

~~***~~in ~~the "Pagasa DONT TOUCH copy" we fixed columns so the last 3 columns are't as wide and therefore page won't print outside margin limits.~~

***previous backup copy that works is called '''"JAN DON'T TOUCH_MB Health Conditioner.mdb'''"

Pagasa also keeps a backup copy of the above folder and all its contents her C drive

==What files does it read?==

*Excel data files that have been sent to and returned from MB Health by ~~courier~~ which is on a (encrypted/password protected) CD.

*Excel data files that have been sent to and returned from MB Health by [[Courier]] which is on a (encrypted/password protected) CD.

*and ifolder was set up for the purpose of exchanging information, however MB Health would not exchange files in this manner.

==What is in the Excel files that is sent and then returned?==

*Study, Chart, PHIN, LN, FN, DOB & Sex - '''MH_PHIN2, MH_LName, MH_FName, MH_DOB, MH_Sex, matched kind (0 to 2)

'''

===What do the "~~matchind~~" ~~values~~ mean when data is returned from MB Health? ===

===What do the [[Manitoba Health Match Indicator Guideline |"matchkind" indicators]] mean when data is returned from MB Health? ===

Matched Indicators:

*0 = no match made with MHSC data

*1 = matched with registry using PHIN

*2 = matched with registry not using the PHIN (either LN, DOB.)

See [[Manitoba_Health_Match_Indicator_Guideline]] for decision making Guidelines for match and unmatched data recieved from Manitoba Health.

==Where are the Excel Data Files located==

*X:\CCMDB_ADMIN_DATABASE\MB_Health_Records_Cleaning\'''MBHealth'''

*{{S:\MED\MED_CCMED}}CCMDB_ADMIN_DATABASE\MB_Health_Records_Cleaning\'''MBHealth'''

==How are EXCEL files labeled?==

Line 59:

Line 55:

*now you can use MHSC CONDITIONER Program which will import and read the output MHSC file correctly.

*file DISK in MHSC crosschecking binder

~~{{Discussion}}~~

==November,19.08 "Conditioner" ACCESS Program Problems==

Line 68:

Line 60:

*1. No one working on it to further improve it.

*2. No designated person is currently responsible for this program, nor has shown anyone else how to maintain it.

*3.'''Conditioner Program''' is ~~no longer reading~~ the MHSC output EXCEL file correctly, specifically "our" DOB data column.

*3.'''Conditioner Program''' is unable to read the MHSC output EXCEL file correctly, specifically "our" DOB data column & PHIN column. This is because when MHSC sends the data back to use, "our" DOB and PHIN columns are fomatted to text instead of numeric. Not sure why they do that.

*October 10.08 - The EXCEL Batch sent to MHSC for crosschecking: included a few more columns of data: admit and discharge. Purpose was to hopefully allow us to obtain Hosp mortality data if available.

*The EXCEL files returned from MHSC (Med'''X'''out.xls and ICU, '''AV'''out.xls) we noted that in our original DOB column, the format was changed from M/D/YYYY to YYYY-M-D. Because of this, the "'''Conditioner Program'''" could not read the file properly and was reporting error every DOB data.

Line 75:

Line 67:

*In the mean time we reformatted our DOB data column back to the M/D/YYYY format and '''Conditioner Program''' worked.

*hopefully the next batch we send to MHSC will be returned without this problem.

*January 22.09 - MHSC still sending data with these to columns altered.

*Pagasa needs clearly documented instruction to follow on how to reformat columns from TXT to numeric.

=INSTRUCTION for changing the format of a COLUMN of data in EXCEL from NUMERIC to TEXT=

* MHSC- "OUT" -is the name of the file which is '''returned''' from MB Health.

**when we '''send''' data to MB Health, the HSC columns of data for '''PHIN''' and DOB are '''NUMERIC''' (justified to the RIGHT).

**When data file is '''returned''' from MB Health, the HSC column of data for '''PHIN''' is '''retained as NUMERIC''' and the '''DOB''' is '''changed to TEXT''' (justified to LEFT).

**MB Health columns of data for MB_PHIN and MB_DOB columns of data are both TEXT (justified to LEFT)

* '''For MB Conditioner Program to work the following columns of data must be checked''':

#the HSC column for PHIN must be changed to TEXT (justified to left).[[User:TOstryzniuk|TOstryzniuk]] 14:22, 23 June 2009 (CDT)

If conditioner doesn't run with the above fix then check the following:

#the "HSC" column for '''PHIN''' & '''DOB''' should both be TEXT (justified to the LEFT).

#"MHSC" column for MB_PHIN & MB_DOB should both be TEXT (justified LEFT).

#Header Row must be the same. (Pagasa copies header row from previous MBH OUT to current MBH OUT. Save. Exit. Now run MB Conditioner).

*NOTE: Julie's comment:

*if "numbers" in a column are justified to the LEFT, the data in that column is stored as TEXT.

*If numbers in a "column" are justified to the RIGHT, the data in that column is stored as NUMERIC.

*NOTE: that if number field has a small '''green tag''' in it, click on that. It is a comment for that field. It will tell you that the number is stored as a TEXT. [[User:TOstryzniuk|TOstryzniuk]] 18:41, 23 January 2009 (CST)

~~==Instruction: How to Change the DOB format of OUR DOB & PHIN column-~~ for MHSC output.xls==

*for MHSC output.xls

*This instruction starts for batch MHSC output data (ICU_'''AV'''out.xls & Med'''X'''out.xls)& onward.

*Problem: When ~~we send a batch of M~~

*Problem: When MHSC returns crosschecked data back to us, "our" DOB and PHIN column has been altered.

*2 problems:

*how to ~~change~~ "our" ~~sent~~ DOB column ~~format from~~ YYYY-M-D back to M/D/YYYY

# DOB is changed to YYYY-M-D back from the original M/D/YYYY that we sent them.

# DOB and PHIN columns of data are not sent back in consistent format. e.g. HSC PHIN is numeric and MB_PHIN is text format.

*the ACCESS "Conditioner Program" will not run if we don't restore the correct format of these columns.

A. how to change "our" sent DOB column format from YYYY-M-D back to M/D/YYYY

B. how to change "our" PHIN column format from text to numeric.

#Open MHSC_out.xls file

#Select the whole DOB column (this is our DOB column, not the MH_DOB column)

#Go to DATA menu tab at top of EXCEL screen

#Select "~~test~~ to column" from drop down menu

#Select "text to column" from drop down menu

#Press NEXT at bottom of this window

#Press NEXT again

#Top Right of this window you will see '''COLUMN DATE FORMAT'''

**select DATE item on menu

#**select DATE item on menu

**from the drop down list beside DATE, select: '''M/D/Y''' format

#**from the drop down list beside DATE, select: '''M/D/Y''' format

*8. Press FINISH

#Press FINISH

*9. Don't forget to save changes.

*10. Exit Excel

B. how to change "our" PHIN column format from '''numeric''' to text:

*11. MHSC Conditioner Program will now be able to read this Excel file.

#Open MHSC_out.xls file

#Select the whole PHIN column (this is "our" PHIN column, not the MH_PHIN column)

#Go to DATA menu tab at top of EXCEL screen

#Select "text to column" from drop down menu

#Press NEXT at bottom of this window

#Press NEXT again

#Top Right of this window you will see '''General format'''

#Press FINISHED

#Don't forget to save changes.

#Exit Excel

#MHSC Conditioner Program will now be able to read this Excel file.

[[User:TOstryzniuk|TOstryzniuk]] 20:06, 19 November 2008 (CST)

[[User:TOstryzniuk|TOstryzniuk]] 20:49, 22 January 2009 (CST)UPDATED.

==Discussion==

***I see that there is a another page started with [[Crosschecking data with Manitoba Health]]. I didn't realize it was there. Some of this information list here under MB Health CONDITIONER program will need to be moved over there. [[User:TOstryzniuk|TOstryzniuk]] 17:56, 20 November 2008 (CST)

*tina.....feel free to move things.

** Trish, the whole of [[:Category:Manitoba Health]] of could benefit from re-factoring; this will take some time, if you want me to do it please put into [[Change Priorities]]

Legacy info only below here.

==iFolder==

ifolder was set up for the purpose of exchanging information, however MB Health would not exchange files in this manner.

==Who Developed it?==

*ACCESS Conditioner Program developed by Tina as requested by Julie.

*DATE: January, 2005

== Related articles ==

}}

[[Category: ~~Questions]]~~

[[Category:Manitoba Health Legacy Information]]

~~[[Category: Data Processing~~]]

@@ Line 1: / Line 1: @@
-The Manitoba Health (MB Health) "Conditioner" ACCESS Program
+{{LegacyContent
+|explanation=MB Health data stopped
+|successor=[[MB Health Tool.mdb]]
+|content=
-See [[Crosschecking data with Manitoba Health]] for more information in regards to the process of sending and receiving data to MB Health.
-==What is it?==
+The Manitoba Health (MB Health) "Conditioner" is an MS Access Program that facilitates [[Crosschecking data with Manitoba Health]] using the  [[Manitoba Health Match Indicator Guideline]].
-*ACCESS program
-==Who Developed it?==
-*ACCESS Conditioner Program developed by Tina as requested by Julie.
-*DATE: January, 2005
 ==What does it do?==
 *automates the process of identifying corrections to key patient identifiers (PHIN, LN, FN, DOB & Gender) from an EXCEL file that is sent every few months to MB Health for crosschecking purposes and is returned with matches that require corrections in our Master ICU/Medicine Master Database.
-*highlights in red, the corrections that we need to make in our database.
+**items highlighted in RED = errors
+**items highlighted in GREEN = no errors
 ==Where do you find it?==
-*X:\CCMDB_ADMIN_DATABASE\MB_Health_Records_Cleaning\'''MB Health Conditioner.mdb'''
+*{{M:|MB_Health_Records_Cleaning}}'''
+* a backup copy of conditioner.mdb is also in this directory - [[User:PTorres|PTorres]] 15:20, 2014 May 29 (CDT)
-==Where do you find a working BACKUP copy?==
-**X:\CCMDB_ADMIN_DATABASE\MB_Health_Records_Cleaning\Others_backup_folder\'''Pagasa_DONT_TOUCH_MB Health Conditioner.mdb'''
-***in the "Pagasa DONT TOUCH copy" we fixed columns so the last 3 columns are't as wide and therefore page won't print outside margin limits.
-***previous backup copy that works is called '''"JAN DON'T TOUCH_MB Health Conditioner.mdb'''"
+Pagasa also keeps a backup copy of the above folder and all its contents her C drive
 ==What files does it read?==
-*Excel data files that have been sent to and returned from MB Health by courier which is on a (encrypted/password protected) CD.
+*Excel data files that have been sent to and returned from MB Health by [[Courier]] which is on a (encrypted/password protected) CD.
-*and ifolder was set up for the purpose of exchanging information, however MB Health would not exchange files in this manner.
 ==What is in the Excel files that is sent and then returned?==
 *Study, Chart, PHIN, LN, FN, DOB & Sex - '''MH_PHIN2, MH_LName, MH_FName, MH_DOB, MH_Sex, matched kind (0 to 2)
 '''
-===What do the "matchind" values mean when data is returned from MB Health? ===
+===What do the [[Manitoba Health Match Indicator Guideline |"matchkind" indicators]] mean when data is returned from MB Health? ===
 Matched Indicators:
 *0 = no match made with MHSC data
 *1 = matched with registry using PHIN
 *2 = matched with registry not using the PHIN (either LN, DOB.)
+See [[Manitoba_Health_Match_Indicator_Guideline]] for decision making Guidelines for match and unmatched data recieved from Manitoba Health.
 ==Where are the Excel Data Files located==
-*X:\CCMDB_ADMIN_DATABASE\MB_Health_Records_Cleaning\'''MBHealth'''
+*{{S:\MED\MED_CCMED}}CCMDB_ADMIN_DATABASE\MB_Health_Records_Cleaning\'''MBHealth'''
 ==How are EXCEL files labeled?==
@@ Line 59: / Line 55: @@
 *now you can use MHSC CONDITIONER Program which will import and read the output MHSC file correctly.
 *file DISK in MHSC crosschecking binder
-{{Discussion}}
 ==November,19.08 "Conditioner" ACCESS Program Problems==
@@ Line 68: / Line 60: @@
 *1. No one working on it to further improve it.
 *2. No designated person is currently responsible for this program, nor has shown anyone else how to maintain it.
-*3.'''Conditioner Program''' is no longer reading the MHSC output EXCEL file correctly, specifically "our" DOB data column.
+*3.'''Conditioner Program''' is unable to read the MHSC output EXCEL file correctly, specifically "our" DOB data column & PHIN column. This is because when MHSC sends the data back to use, "our" DOB and PHIN columns are fomatted to text instead of numeric.  Not sure why they do that.
 *October 10.08 - The EXCEL Batch sent to MHSC for crosschecking: included a few more columns of data: admit and discharge. Purpose was to hopefully allow us to obtain Hosp mortality data if available.
 *The EXCEL files returned from MHSC (Med'''X'''out.xls and ICU, '''AV'''out.xls) we noted that in our original DOB column, the format was changed from M/D/YYYY to YYYY-M-D.  Because of this, the "'''Conditioner Program'''" could not read the file properly and was reporting error every DOB data.
@@ Line 75: / Line 67: @@
 *In the mean time we reformatted our DOB data column back to the M/D/YYYY format and '''Conditioner Program''' worked.
 *hopefully the next batch we send to MHSC will be returned without this problem.
+*January 22.09 - MHSC still sending data with these to columns altered.
+*Pagasa needs clearly documented instruction to follow on how to reformat columns from TXT to numeric.
+=INSTRUCTION for changing the format of a COLUMN of data in EXCEL from NUMERIC to TEXT=
+* MHSC- "OUT" -is the name of the file which is '''returned''' from MB Health.
+**when we '''send''' data to MB Health, the HSC columns of data for '''PHIN''' and DOB are '''NUMERIC''' (justified to the RIGHT).
+**When data file is '''returned''' from MB Health, the HSC column of data for '''PHIN''' is '''retained as NUMERIC''' and the '''DOB''' is '''changed to TEXT''' (justified to LEFT).
+**MB Health columns of data for MB_PHIN and MB_DOB columns of data are both TEXT (justified to LEFT)
+* '''For MB Conditioner Program to work the following columns of data must be checked''':
+#the HSC column for PHIN must be changed to TEXT (justified to left).[[User:TOstryzniuk|TOstryzniuk]] 14:22, 23 June 2009 (CDT)
+If conditioner doesn't run with the above fix then check the following:
+#the "HSC" column for '''PHIN''' & '''DOB''' should both be TEXT (justified to the LEFT).
+#"MHSC" column for MB_PHIN & MB_DOB  should both be TEXT (justified LEFT).
+#Header Row must be the same.  (Pagasa copies header row from previous MBH OUT to current MBH OUT.   Save. Exit.  Now run MB Conditioner).
+*NOTE:  Julie's comment:
+*if "numbers" in a column are justified to the LEFT, the data in that column is stored as TEXT.
+*If numbers in a "column" are justified to the RIGHT, the data in that column is stored as NUMERIC.
+*NOTE:  that if number field has a small '''green tag''' in it, click on that.  It is a comment for that field.  It will tell you that the number is stored as a TEXT. [[User:TOstryzniuk|TOstryzniuk]] 18:41, 23 January 2009 (CST)
-==Instruction: How to Change the DOB format of OUR DOB & PHIN column- for MHSC output.xls==
+*for MHSC output.xls
 *This instruction starts for batch MHSC output data (ICU_'''AV'''out.xls & Med'''X'''out.xls)& onward.
-*Problem: When we send a batch of M
+*Problem: When MHSC returns crosschecked data back to us, "our" DOB and PHIN column has been altered.
+*2 problems:
-*how to change "our" sent DOB column format from YYYY-M-D back to M/D/YYYY
+# DOB is changed to YYYY-M-D back from the original M/D/YYYY that we sent them.
+# DOB and PHIN columns of data are not sent back in consistent format. e.g. HSC PHIN is numeric and MB_PHIN is text format.
+*the ACCESS "Conditioner Program" will not run if we don't restore the correct format of these columns.
+A. how to change "our" sent DOB column format from YYYY-M-D back to M/D/YYYY
+B. how to change "our" PHIN column format from text to numeric.
 #Open MHSC_out.xls file
 #Select the whole DOB column (this is our DOB column, not the MH_DOB column)
 #Go to DATA menu tab at top of EXCEL screen
-#Select "test to column" from drop down menu
+#Select "text to column" from drop down menu
 #Press NEXT at bottom of this window
 #Press NEXT again
 #Top Right of this window you will see '''COLUMN DATE FORMAT'''
-**select DATE item on menu
+#**select DATE item on menu
-**from the drop down list beside DATE, select: '''M/D/Y''' format
+#**from the drop down list beside DATE, select: '''M/D/Y''' format
-*8. Press FINISH
+#Press FINISH
-*9. Don't forget to save changes.
-*10. Exit Excel
+B. how to change "our" PHIN column format from '''numeric''' to text:
-*11. MHSC Conditioner Program will now be able to read this Excel file.
+#Open MHSC_out.xls file
+#Select the whole PHIN column (this is "our" PHIN column, not the MH_PHIN column)
+#Go to DATA menu tab at top of EXCEL screen
+#Select "text to column" from drop down menu
+#Press NEXT at bottom of this window
+#Press NEXT again
+#Top Right of this window you will see '''General format'''
+#Press FINISHED
+#Don't forget to save changes.
+#Exit Excel
+#MHSC Conditioner Program will now be able to read this Excel file.
 [[User:TOstryzniuk|TOstryzniuk]] 20:06, 19 November 2008 (CST)
+[[User:TOstryzniuk|TOstryzniuk]] 20:49, 22 January 2009 (CST)UPDATED.
 ==Discussion==
 ***I see that there is a another page started with [[Crosschecking data with Manitoba Health]].  I didn't realize it was there.  Some of this information list here under MB Health CONDITIONER program will need to be moved over there. [[User:TOstryzniuk|TOstryzniuk]] 17:56, 20 November 2008 (CST)
 *tina.....feel free to move things.
+** Trish, the whole of [[:Category:Manitoba Health]] of could benefit from re-factoring; this will take some time, if you want me to do it please put into [[Change Priorities]]
+ Legacy info only below here.
+==iFolder==
+ifolder was set up for the purpose of exchanging information, however MB Health would not exchange files in this manner.
+==Who Developed it?==
+*ACCESS Conditioner Program developed by Tina as requested by Julie.
+*DATE: January, 2005
+== Related articles ==
+{{Related Articles}}
+}}
-[[Category: Questions]]
+[[Category:Manitoba Health Legacy Information]]
-[[Category: Data Processing]]