Centralized import from Ed.mdb

The Access file Creating Centralized import from Ed.mdb contains the code needed to import Ed's old data into centralized_data.mdb.

It is stored on x:\ccmdb\Centralized import from Ed

Use

NOTE:For now, you need two folder on your desktop. a. Centralized front end for practice and 2. Centralized import from Eds.

• get newest version from x:\ (\\Hsc1msfp0001\med_ccmed\CCMDB\Centralized import from Ed\copy contents of this folder to one folder on your desktop)
• From output folder on Regional server: copy pharmacy.mdb into: mdbs_to_import
  • Julie must provide a copy of latest TASKS.xls to put in mdbs_to_import folder (not the TASKS_1.mdb on server).
• copy From S:\MED\CCMED\​S:\MED\CCMED\Pagasa\CCData\rawdata: cc_rawdata and med_rawdata content
• open centralized import from ed.mdb
• open Modules, go to module "Import", run Public Sub Import().
• EXIT out when done (large X to right screen)
• Final Step:FROM the Ed import folder on your own desktop, copy centralized.mdb, PHI.mdb TO the centralized Front end practice folder on your desktop.
• you can also copy from S:\MED\CCMED\​S:\MED\CCMED\TISS28\Database\TISS28_data.mdb to the centralized Front end practice folder on your own desktop
• when you open front end, there is a link table button, press it to link in TISS28.accdb.

resolutions for differences between (ed's and legacy) and centralized

Legacy Labs values from TMSX

We are going to import the ones that we have slots for, and leave the rest in the final output from Ed in case anyone ever wants to access them in the future.

serials for early CC data

Early CC records don't have serial numbers. I assigned negative numbers arbitrarily to these to be able to generate D_IDs.

duplicates

turned out there were a few duplicates in ed's where a serial was used twice for the same hospital/location. These were resolved in Ed's data and should not be a problem for the final import.

Importing Pharmacy.mdb

uses queries

• pharmacymdb_to_L_Pharm_flowsheet
  • imports variables from pharmacy.mdb into L_Pharm_flow for the following fields, regardless of date
    • PPI, [H2-Blockers], [Heparin SQ], LMWH, [benzodiazepines cont inf], [opioids cont inf], [propofol cont inf], [Insulin cont inf], [Furosemide cont inf], azoles, fungins, [antipseudomonal PCNs], vancomycin, fluoroquinolones, carbapenems, ertapenem, [vanco alternatives], [3rd generation cephalosporins], ceftazidime, tigecycline (ignore square brackets, this was taken directly from SQL)
• pharmacymdb_to_L_Log
  • will set checkboxes for pharmacy fields in L_Log to "true" if value in pharmacy.mdb is > 0

Change Requests

See:Change Priorities- issue with missing data in L_task and also missing from L_DX after import done with this version.Trish Ostryzniuk 11:58, 2014 May 6 (CDT)

• import tmp_v2 data after L TmpV2 multi-entry clean-up

Change Log

2014-05-13

copied to x:\ Ttenbergen 12:19, 2014 May 14 (CDT)

• fixed queries tmp_importer_* to handle date and time properly. Ttenbergen 14:03, 2014 May 13 (CDT)

2014-05-09

copied to x:\ Ttenbergen 16:27, 2014 May 9 (CDT)

• changed query "orig_med_to_L_Log" from

  • AP_AdmitType: IIf(IsNull([surgical]) Or IsNull([es]),Null,IIf([r_AdmDate]<#2007/Jan/01#,"Medical",IIf([ES]="Y","Elect.Surg",IIf([surgical]=False,"Medical","Surg.Emerg"))))

  • To

    AP_AdmitType: IIf(IsNull([surgical]) Or IsNull([es]),Null,IIf([r_AdmDate]>#2007/Jan/01#,"Medical",IIf([ES]="Y","Elect.Surg",IIf([surgical]=False,"Medical","Surg.Emerg"))))

2014-05-08

copied to x:\ Ttenbergen 15:56, 2014 May 8 (CDT)

• made s_admitDischarge same as elsewhere, ie incl. setting FP and FR to "surgical" and making sure that the Grace Nursing Home Ward and HSC Nursing Home Ward entry is present. Ttenbergen 15:56, 2014 May 8 (CDT)

ver 2014-05-05

copied to x:\ Ttenbergen 10:59, 2014 May 5 (CDT)

• updated import for CC apache to set all Renal/ARF nulls to false as per Trish. Ttenbergen 10:59, 2014 May 5 (CDT)

ver 2014-05-01

copied to x: Ttenbergen 20:31, 2014 May 1 (CDT)

• changed the import for medicine and cc L_Log to import as null apache elements that are null in Ed's
  • ap_chronic
  • ap_admitType
  • ap_ARF
• fixed query "orig_med_tasks_from_Juliexls_peritoneal", was entering wrong dx code. Ttenbergen 19:38, 2014 May 1 (CDT)
• likely included the fix to Admit Type for APACHE II elective surgery (ES) Tina to confirm.Trish Ostryzniuk 16:00, 2014 May 2 (CDT)

ver 2014-03-24

not rolled yet but used for 03-28 import Ttenbergen 15:58, 2014 March 24 (CDT)

• reinstated line that cleans out L_PHI, it had got omitted when I changed the clean-up to just copy a clean centralized rather than slowly delete content. Ttenbergen 15:58, 2014 March 24 (CDT)
• fixed queries "orig_med_to_L_Log" and "orig_cc_to_L_Log" , were mixing up emerg and elect surgery ( see also Admit Type for APACHE II and ES) Ttenbergen 15:03, 2014 March 27 (CDT)
• tasks are not being imported

ver 2014-03-10

used for import of 2013-03-21 data, and rolled to x:\ - Ttenbergen 10:39, 2014 March 24 (CDT) ( not rolled out yet Ttenbergen 15:19, 2014 March 10 (CDT) but used for import already...)

• added (and then removed since Julie already has this combined in tasks.xls below) queries to import iTISS items into L_Dxs (b see Tasks vs iTISS:
  • query "orig_med_itiss19_to_L_Dxs"
  • query "orig_med_itiss23_to_L_Dxs"
  • query "orig_med_itiss24_to_L_Dxs"
  • query "orig_med_itiss36_to_L_Dxs"
  • query "orig_med_itiss37_to_L_Dxs"
  • query "orig_med_itiss38_to_L_Dxs"
  • query "orig_med_itiss65_to_L_Dxs"
• removed linking to tasks.mdb since we will import from Julie's tasks.xls instead Ttenbergen 15:21, 2014 March 11 (CDT)
• automated import from tasks.xls into table orig_m_tasks_from_Julies_xls Ttenbergen 15:21, 2014 March 11 (CDT)
• added queries to import from Julie's tasks.xls into L_DXs still need to figure d_diag_ids Ttenbergen 15:21, 2014 March 11 (CDT)
  • orig_med_tasks_from_Juliexls_bipap
  • orig_med_tasks_from_Juliexls_hemo
  • orig_med_tasks_from_Juliexls_isolation
  • orig_med_tasks_from_Juliexls_peritoneal
  • orig_med_tasks_from_Juliexls_trach
• removed the original tasks queries Ttenbergen 12:04, 2014 March 12 (CDT)
• fixed sub "get_cc_dxs" which was excluding imports for early records that had no serial/pat_id - Ttenbergen 13:55, 2014 March 14 (CDT)
• updated queries orig_cc_to_L_Log and orig_med_to_L_Log to fix importing for "type" field - Ttenbergen 14:32, 2014 March 14 (CDT)
• fix tmp import setting up now: Import all TmpV2 that have the latest available tmp_sent_date) for a given patient-ward-stay.
• deleted table orig_m_tasks and orig_m_saps since we are not using them now Ttenbergen 16:02, 2014 March 17 (CDT)
• updated sub Clean_origs() to also include intermediate tables for tasks and tmp Ttenbergen 16:03, 2014 March 17 (CDT)
• changed module re-jigger subs to get dxs and comos to only pull for the program currently being processed, they were importing each dx for both the study supposed to and the same study number in the other program. Ttenbergen 12:43, 2014 March 21 (CDT)
• changed queries "orig_med_to_L_Log" and "orig_cc_to_L_Log" to set imported records to "vetted" to respond to new definition in RecordStatus field. Ttenbergen 13:29, 2014 March 21 (CDT)

ver 2014-03-06

rolled out Ttenbergen 18:31, 2014 March 6 (CST)

• fixed import for dxs, it wasn't importing dxs for patients without serials in ed's - Ttenbergen 16:12, 2014 March 6 (CST)
• corrected query pharmacymdb_to_L_Log
  • heparin cont inf was pulling from heparin sq, corrected
  • bosentan wasn't pulling any data, corrected
  • confirmed that the import for viagra and anti-TB drugs is correct; some of the values in pharmacy.mdb are 1, some are -1

ver 2014-02-13

copied up to x:\ Ttenbergen 18:24, 2014 February 13 (CST)

• confirmed that Comos are getting imported. Ttenbergen 13:32, 2014 February 13 (CST)
• confirmed that ER wait date and time get imported, need to see why not visible Ttenbergen 13:47, 2014 February 13 (CST)
• added query pharmacymdb_to_L_Log to fill in drug checkboxes in L_Log Ttenbergen 14:06, 2014 February 13 (CST)
• fixed orig_med_to_L_Log and orig_cc_to_L_Log to:
  • import R_DischargedTo properly. Ttenbergen 13:37, 2014 February 13 (CST)
  • import sex as full words rather than initial letters to make same as CCMDB.accdb Ttenbergen 16:14, 2014 February 13 (CST)
  • fix import for R_type and R dc treat

ver 2014-02-09

• updated query TmpV2_to_L_TmpV2 to import dates properly Ttenbergen 21:16, 2014 February 9 (CST)
• updated and completed code to import automatically Ttenbergen 21:16, 2014 February 9 (CST)

ver 2014-01-19

• replaced orig_med_to_L_Log with mod version of orig_cc_to_L_Log Ttenbergen 19:20, 2014 January 19 (CST)
• changed orig_cc_to_L_Log to exclude ADLs Ttenbergen 19:21, 2014 January 19 (CST)
• fixed serial 99999 deletion for Med, had previously only fixed for CC Ttenbergen 21:46, 2014 January 19 (CST)
• s_AdmitDischarge, imported locations that are in orig_* records but no longer active. Ttenbergen 21:46, 2014 January 19 (CST)
  • added field "active" , imported locations that are in orig_* records but no longer active. Ttenbergen 21:46, 2014 January 19 (CST)
  • created column "old_code" to accomodate 3-letter code "oth", which imports as "ot" due to field length restrictions. Ttenbergen 21:46, 2014 January 19 (CST)
• added queries 1_check_no_tasks and 1_check_tasks_orphans and 1_check_task_dupes for pre-task import checks Ttenbergen 00:42, 2014 January 31 (CST)
• added queries tasks_to_dx, tasks_to_dx_u, tasks_resolver and TASKS_d_id to process and import tasks Ttenbergen 00:42, 2014 January 31 (CST)

ver 2014-01-09

• AP_Chronic to ap_chronic: IIf([orig_cc_apa].[CH]="N","9No CH","0HasChronic") Ttenbergen 10:36, 2014 January 10 (CST)
  • orig_med_to_L_Log
  • orig_cc_to_L_Log
• AP_AdmitType: IIf([surgical]=False,"Medical",IIf([ES]="N","Elect.Surg","Surg.Emerg")) Ttenbergen 10:36, 2014 January 10 (CST)
  • orig_med_to_L_Log
  • orig_cc_to_L_Log
• added Pat_ID to orig_med_to_L_Log Ttenbergen 17:21, 2014 January 14 (CST)

ver 2013-12-23

• automated running of query "x_Infection_pathogen_fix" upon import. Ttenbergen 14:20, 2013 December 23 (CST)
• Added MedTMS exported ADL import, table was not being processed before Ttenbergen 17:13, 2013 December 23 (CST)
• ADLs (ADL General Collection Information)
  • imported table s_ADLOptions to resolve entries needed for l_log.adl_* fields Ttenbergen (2013 December 23)
  • updated query orig_cc_to_L_Log to include ADLs Ttenbergen (2013 December 23)
• link to s_GCS table in centralized.mdb Ttenbergen 12:44, 2013 December 30 (CST)
• updated query orig_cc_to_L_Log to use s_GCS table to populate with values used in CCMDB rather than scores from TMSX Ttenbergen 12:44, 2013 December 30 (CST)
• changed study 99999 fix to only delete if ((orig_cc_reg.STUDY)=99999 and (orig_cc_reg.CHART)=999999999);" Ttenbergen 12:49, 2013 December 30 (CST)
• confirmed that L_Labs_Flowsheet has values, ready for validation Ttenbergen 12:55, 2013 December 30 (CST)
• linked in L_Pharm_Flowsheet Ttenbergen 12:56, 2013 December 30 (CST)
• added code to import pharmacy from pharmacy.mdb Ttenbergen 14:47, 2013 December 30 (CST)

ver 2013-11-18

This is not the date it was made, but the date documenting started. after I lost and had to find the file. :-)

• fixed lab importing
• moved L_aux into centralized db and including in import and linking.
• set the record_Status to complete for all imported
• added query "x_Infection_pathogen_fix" to put dx code and pathogen number into their respective places.
• fixed PHI import query for medicine, it was excluding names and DOB for some reason.