Re-import of TmpV2 data from Julie's Export 2014-09-08

Original import of TmpV2 went wrong. Julie provided a new export 2014-09-04.

Files are in X:\CCMDB\TmpV2_final

process

prep steps

The following steps can be done any time in preparation:

1. copy tmp_to_centralized_importer.mdb from x:\CCMDB\z_archive\2014-09-08_Tmp_reimport to <a folder>
2. open using usual tasty pw
3. confirm table L_TmpV2_1_cleaned_imported is empty and import TmpV2_1.mdb type data
   • statistician provided the data as .xlsx because TmpV2_1.mdb data needs to be limited to valid instance of records
   • use Access 2010 for this next time, problems with 2003 because of size limitation on xls files and commas in comments breaking .csv format... had to put quotes around the comments etc, better to just use 2010 next time...
4. confirm table L_TmpV2_2_IDmaker is empty
5. run query step1_populate_idmaker to populate the D_Tmp_ID field with an autonumber. L_TmpV2 does not use an autonumber for the field but we need one.

import steps

The following steps then have to be done on live data in a "pull" state:

1. copy backup of centralized_data.mdb to where? as centralized_data_pre_tmpv2_import.mdb
2. copy centralized_data.mdb to same <a folder> as in #prep steps
3. link tables L_Log and L_TmpV2 into tmp_to_centralized_importer.mdb
4. run query step2_delete
   • SELECT L_TmpV2.D_ID FROM L_Log INNER JOIN L_TmpV2 ON L_Log.D_ID = L_TmpV2.D_ID WHERE (((L_Log.RecordStatus)<>"incomplete"));
   • and delete all records listed; this will delete tmp entries for all except incomplete L_Log entries
5. delete table L_TmpV2_3_orphans_wo_L_Log_parent if present
6. run query step31_put_orphans_into_separate_table
   • there are a number of orphans in the .mdb that have become disconnected from their parent record in L_Log, likely due to location chagnes; this was flagged during analysis and decided to just segregate the records for now and have Pagasa correct later if needed. The records would already be in the correct format to cut-and-paste them into L_TmpV2 once the D_ID gets corrected.
7. run query step32_append_matches_to_L_TmpV2 to actually append the tmp records to L_TmpV2