Manitoba Health "Conditioner"

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

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?

• 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.
  • 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

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 worked is called "JAN DON'T TOUCH_MB Health Conditioner.mdb"

• • as of January 22.09 it has been discovered that this program is not working correctly. Needs investigation.TOstryzniuk 20:35, 22 January 2009 (CST)

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.
• 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?

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.)

Where are the Excel Data Files located

• X:\CCMDB_ADMIN_DATABASE\MB_Health_Records_Cleaning\MBHealth

How are EXCEL files labeled?

• sent files are labeled as follows:
  • ICU EXAMPLE: AU.xls (AU.zip)
  • MED EXAMPLE: MedX.xls
• returned files are labeled as follows:
  • ICU EXAMPLE: AUout.xls
  • MED EXAMPLE: Med_Xout.xls
• paper log file is kept of sent/recieved batch numbers for ICU and Medicine

Procedure when DATA CD is returned from MHSC

• copy password protected zip file into file folder on X that contains original sent file
• unzip MHSC out.xls file into same folder
• Open out.xls file
• NOTE: AS of OCT 10.08 batch AVout.xls ICU and med_Xout.xls: changed of format of "our" DOB column of data from YYYY-M-D to M/D/YYYY format. (see instruction how to do this below). This is a work around for now until Julie sorts out with MHSC if they can revert to previous output format for our DOB column.
• also you must change the header row label MH_PHIN2. Remove the 2, change to MH_PHIN. If this change is not made the "Conditioner" Program will not read file correctly.
• Save change
• Exit
• now you can use MHSC CONDITIONER Program which will import and read the output MHSC file correctly.
• file DISK in MHSC crosschecking binder

November,19.08 "Conditioner" ACCESS Program Problems

PROBLEM:

• 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.
• 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 (MedXout.xls and ICU, AVout.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.

REPAIR SOLUTION:

• Julie will advise the contact person at MHSC about format change on the output file. We want to retain same DOB format for our "original" DOB column of data which is M/D/YYYY. (MH_DOB column is fine as is).
• 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.

Instruction: (for MHSC output.xls). How to Change the "Our" DOB & PHIN column format from text to numeric formatt

• This instruction starts for batch MHSC output data (ICU_AVout.xls & MedXout.xls)& onward.

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

1. DOB is changed to YYYY-M-D back from the original M/D/YYYY that we sent them.
2. DOB and PHIN columns are formatted as text instead of numeric.

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

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

1. Open MHSC_out.xls file
2. Select the whole DOB column (this is our DOB column, not the MH_DOB column)
3. Go to DATA menu tab at top of EXCEL screen
4. Select "test to column" from drop down menu
5. Press NEXT at bottom of this window
6. Press NEXT again
7. Top Right of this window you will see COLUMN DATE FORMAT

• • select DATE item on menu
  • from the drop down list beside DATE, select: M/D/Y format
• 8. Press FINISH
• 9. Don't forget to save changes.
• 10. Exit Excel
• 11. MHSC Conditioner Program will now be able to read this Excel file.

TOstryzniuk 20:06, 19 November 2008 (CST) TOstryzniuk 20:49, 22 January 2009 (CST)UPDATED

• Pagasa need to include steps to change PHIN column from TXT to numeric. I really need some input from Julie or Pagasa here if they feel there are changes that can be made here to help others who might have to run this program. Thanks!!

• note: Julie's observation: if "numbers" in a column are justified to the left, the data in that column is text. If numbers in a "column" are justified to the right, the data in that column is numeric. ___Pagasa can you double check this with Julie please.___TOstryzniuk 20:55, 22 January 2009 (CST)

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. TOstryzniuk 17:56, 20 November 2008 (CST)
• tina.....feel free to move things.