Centralized data Vetting Process

• need to make sure all in Data Appending Process is addressed.

Checklist Tracker Sheet

This is still part of the old system as a cross check until we go totally with the new system.

Print a checklist tracker sheet

• My Documents
• Open workbook2.xls
• Change header to sent date (use Wed date)
  • click Insert
  • Header & Footer
  • Change the date
  • File
  • Print

Check to see that everyone has sent

• RegionalWRHA (wreath icon)
• Output (folder)
• check each folder to see who sent, and write the # on the tracker sheet
  • the data collector sends these files
    • there are only complete files in these xls cvs files
  • when viewing these in the xls file, the header is included as Line 1, so the actual # of files sent will be one less, e.g. if it says 11, then there were 10 complete files sent

To check the incomplete files

• make a copy of PDA Pending.mdb
  • • we have stopped sending to pda pending, what if anything is being used now? Ttenbergen 20:41, 2014 September 29 (CDT) Template:Discussion
  • Regional
  • Output
  • right click PDA_pending.mdb
  • Copy
• Paste the copy on the desktop
• prompts already a copy...
• answer Yes
• open PDA_Pending.mdb
• check each site and write down the # of incomplete on the tracker sheet
  • when viewing these files the header is not included, so the # is how many files pending
    • at the bottom it says Record __ of __
• close each file you have opened
• close PDA_Pending.mdb

Pull data

• "Pull" Centralized data.mdb

prep CFE for use

• Double click to open Centralized_data_front_end.mdb
• click Reconnect

cross-check tables

• Open Patient List
• on the Left side,
• scroll All Access Objects
• Tables
  • L_Log - write down the number
  • L_PHI - write down the number
• click back to top
  • Queries
  • L_Log_PHI - write down the number

**ALL 3 must be the same**

cross-check tracker sheet

Double check these numbers against the tracker sheet, which compares the old to the new combined centralized numbers

The complete numbers come from the xls csv file

The incomplete numbers come from the PDA_Pending.mdb file

• Template:Discussion don't think so, we don't use that any more. Ttenbergen 22:27, 2014 September 29 (CDT)

See who Sent

• Queries
• P_Count_Of_Sent (double click)
• Home
• View
• Pivot Table
• View
• Filter by 'Date Sent'
• Right Click
• Field List
• drag 'sent Dt Time'
• close dialog box
• Home
• Design View
• Change to 'Wed' date
• Home
• View
• Pivot Table View
  • shows the number of incomplete and sent in 2 separate tables
  • use + (plus symbol) to expand
    • when you expand, it shows the serial number
  • use - (minus symbol) to collapse
• Click inside the column and scroll down to do a quick check of the location
• what are we checking for? what if something is wrong? Template:Discussion
• When done
  • click x(in top right) and DO NOT 'save'
  • It will prompt you 'do you want to save'
  • say NO
  • When finished viewing, just close, DO NOT 'save'

Make PseudoPHINs

see Generating PseudoPHINs

Check For Problems

Use Centralized data front end.mdb "NDC" (New Data Check) queries and fix data as needed; if a patient shows up in one of the queries fix if possible; if question to collector is required, set Record Status field to "questioned"

Perform the Queries in this order:

1. "NDC_orphans"
   • should always be zero
2. NDC_Chart_9_Digit
3. NDC_Diff_Phin__SameLN_FN_DOB
   • Home, Advanced, Advanced Filter/Sort, LastName (double click or drag & drop), Sort by ascending, Home, Toggle Filter
4. NDC_SameChart_Site_Diff_Phin
5. NDC_SamePhin_Diff_LN_FN_DOB_Sex
   • Home, Advanced, Advanced Filter/sort, Phin (double click or drag & drop), Sort by ascending, Home, Toggle Filter
6. NDC_SamePHIN_Site_Diff_chart
7. NDC_CLI_AcqDX_but_NoCLI_DateinTMPV2 (see CLI Dx vs Tmp checking)
8. NDC_CLI_No_AcqDX_but_CLI_DateinTMPV2 (see CLI Dx vs Tmp checking)
9. NDC_CLI_unacceptable_date (see CLI Dx vs Tmp checking)
10. NDC_VAP_AcqDX_but_NoVAP_DateinTMPV2 (see VAP Dx vs Tmp checking)
11. NDC_VAP_No_AcqDX_but_VAP_DateinTMPV2 (see VAP Dx vs Tmp checking)
12. NDC_VAP_unacceptable_date (see VAP Dx vs Tmp checking)
13. NDC_unvetted

• • • • • For Queries 8-13
          • we are only monitoring ICU
            • look for 2014 sent and see if it has to be investigated
              • Template:Discussion what is this about? Do queries need tweaks? Ttenbergen 23:20, 2014 September 29 (CDT)

Once every query has been performed, checked, and cleaned perform the first 7 queries once more, to make sure data is all clean and you did not forget any corrections.

• still working on this one (have not done this one yet)
  • NDC_Missing_TransferDate_wPivot
    • Template:Discussion what needs doing? Ttenbergen 23:20, 2014 September 29 (CDT)

Set record as vetted

At this point all records with problems will have had the Record Status field set to "questioned" so the remaining ones that are set to "sent" are clean and can be set to "vetted". Press the "vet all sent" button. This will change all records currently set to "sent" to "vetted".

When all data is cleaned, the last query is NDC_unvetted

• does that query actually do anything for us? Template:Discussion Ttenbergen 23:07, 2014 September 29 (CDT)

On the 'RecStatus' column

Right click the first one

Sort by Z to A which will sort by Sent, Questioned, Incomplete statuses

Scroll down to the last 'sent' record, to see how many records will be vetted

• should that query just change to have only "sent"s? Template:Discussion Ttenbergen 23:07, 2014 September 29 (CDT)

Then click the Vet All Sent button

Prompts 'are you sure'

answer Yes

prompts 'you are about to run ...'

answer Yes

prompts 'you are about to update ___ rows'

answer Yes

The number in the ___ rows, should be the same as the last 'sent' record from your scroll down

e.g. if your last 'sent' record was 25, then the prompt will say 'you are about to update 25 rows'

• has the number ever been anything different? Template:Discussion Ttenbergen 23:07, 2014 September 29 (CDT)

Push up data

When all processing is done, run Push_up_centralized_data.vbs to return the data to where collectors can add to it.

backup and confirm data backups

see Centralized data backup process