Data Entry Guidelines for the Logistics Indicators Assessment Tool (LIAT)

Publication date: 2011

Data Entry Guidelines for the Logistics Indicators Assessment Tool (LIAT) MARCH 2011 This publication was produced for review by the U.S. Agency for International Development. It was prepared by the USAID | DELIVER PROJECT, Task Order 1. Data Entry Guidelines for the Logistics Indicators Assessment Tool (LIAT) The authors' views expressed in this publication do not necessarily reflect the views of the U.S. Agency for International Development or the United States Government. USAID | DELIVER PROJECT, Task Order 1 The USAID | DELIVER PROJECT, Task Order 1, is funded by the U.S. Agency for International Development under contract no. GPO-I-01-06-00007-00, beginning September 29, 2006. Task Order 1 is implemented by John Snow, Inc., in collaboration with PATH; Crown Agents Consultancy, Inc.; Abt Associates; Fuel Logistics Group (Pty) Ltd.; UPS Supply Chain Solutions; The Manoff Group; and 3i Infotech. The project improves essential health commodity supply chains by strengthening logistics management information systems, streamlining distribution systems, identifying financial resources for procurement and supply chain operations, and enhancing forecasting and procurement planning. The project also encourages policymakers and donors to support logistics as a critical factor in the overall success of their health care mandates. Recommended Citation USAID | DELIVER PROJECT, Task Order 1. 2011. Data Entry Guidelines for the Logistics Indicators Assessment Tool (LIAT). Arlington, Va.: USAID | DELIVER PROJECT, Task Order 1. USAID | DELIVER PROJECT John Snow, Inc. 1616 Fort Myer Drive, 11th Floor Arlington, VA 22209 USA Phone: 703-528-7474 Fax: 703-528-7480 Email: askdeliver@jsi.com Internet: deliver.jsi.com Contents Database Preparation . 1 Country-Specific Needs . 1 Modifying your Microsoft Access Database . 2 Guidelines for Training Data Entry Personnel. 7 Overview of the LIAT Questionnaire .7 Using Microsoft Access for LIAT Data Entry . 7 Checking Double Data Entry . 13 Getting Started . 13 Cross-Checking Data. 16 Correcting Errors . 18 Troubleshooting. 18 Other Useful Information. 21 Data Entry Quick Start Guide . 23 Tables 1. Country-Specific Adaptations That Should Be Considered When Using the LIAT .1 2. LIAT Questionnaire Sections . 7 iii iv Database Preparation Country-Specific Needs To facilitate the collection of quality data in a facility-based supply chain assessment, it is essential to adapt the Logistics Indicators Assessment Tool (LIAT) so that it addresses the specific parameters of a given country’s supply chain. Among other elements, this includes referring to the exact products being assessed and corresponding logistics information (e.g., order interval), as well as to the logistics management forms that are in use. Table 1 summarizes key adaptations that should be considered. Table 1. Country-Specific Adaptations That Should Be Considered When Using the LIAT Elements Location Instructions Country name and year Form 1: LIAT Background Data Enter the name of the country and the year in which the assessment is being conducted. Products and units Form 1: LIAT Background Data Enter the names and units of count for products to be surveyed for Table 1: Stock Status. If a subset of products will be used for tables 3‒5, check the sample box for those products. Enter the maximum stock level, minimum stock level, and order interval for each product at each level of the supply chain. Form labels for question 102, responses A and B Form B: Questions 101–126 Rename the labels for A and B so that they correspond with the country-specific form names. Additional variables Forms A–G See page 4, Adding and Deleting a Skip, for an overview of the steps to add additional variables. 1 Modifying your Microsoft Access Database Most modifications made to your Access database will require you to open tables or forms in Design View. To do this, select the form or table so that it is highlighted on the screen, and then click on the Design button. Limit to List The Limit to List function is activated for many fields in the database to ensure that data entry personnel do not accidentally enter an incorrect response as they are tabbing through the forms. If an incorrect response is entered, a prompt comes up and stops the user from moving forward. If Limit to List blocks a user from entering a response the way it is recorded on the questionnaire, it is possible to disable the function: 1. Go to Design View. 2. Click on the field you wish to edit. 3. Right-click on the field to go to the Properties menu. a. Under the Data tab, find Limit to list. b. Change Yes to No. 2 Look-Up Fields Some fields in these forms are linked to drop-down menus that draw from look-up tables. You may need to make edits and additions to these tables during data collection. Most look-up tables are numbered either in the order they appear in the database or to correspond to a specific question in the LIAT. Some tables are used multiple times, such as those that contain the responses Yes, No, and Missing. These tables are not numbered. To add a response to a Look-up table— 1. Close the form. 2. Go to the Objects menu and choose Tables. 3 3. Open the appropriate Look-up table. 4. At the bottom of the list, write the response as a new record on this list. You must enter both a number in the ID field and a text description in the Name field. Enter the wording exactly as it is recorded on the survey (unless it is only a minor spelling mistake). 5. Save the table and close it to go back to the form. Adding and Deleting a Skip Data entry forms can be programmed so that the cursor moves to the next appropriate field based on skip instructions in the questionnaire. When new fields are added, the steps below should be taken before data entry begins to maintain the skip pattern. If there are questions in the paper-based forms that do not appear in the database, contact your survey administrator for help including them in the database. 1. Add a variable for the question in the appropriate table. Make sure that the variable is named in such a way as to make it clear where the question falls in the survey. Renumber the variables following your new variable, in order, in the table. 2. Include the variable in any queries that populate the database data entry forms (for standard LIAT databases, forms B–G are populated by queries). 3. Add the variable to the data entry form in Design View. 4. Renumber the other variables in the form to match the new order. 5. Add Skip patterns as Macros to the new variable if needed. Sometimes this programming stops working and interferes with data entry. The Data Analyst should decide when a skip needs to be deleted. To delete a skip— 1. Go to Design View. 2. Go to the Properties menu for the field in question. This is done by selecting the field by left- clicking on it, and then right-clicking to bring up the pop-up menu where you can choose Properties (it is the last entry on the menu). 4 3. Under the Event tab, find the row with an entry (there is normally only one entry). On the far right side of that row, click on the three dots (…) that appear next to the row. A new window will then open. 4. Delete the commands in the new window, then click the “save” icon on the top left toolbar. Close the new window, and then close the properties menu. Save the form in design view and go back to form view to continue entering data. 5 6 Guidelines for Training Data Entry Personnel Training is centered on hands-on practice in entering data collected with the LIAT. If possible, use data from the pretest facilities that the data collectors visited during data collection training. It is best to supervise data entry personnel during the practice data entry session, providing immediate feedback (instead of a review at the end of the session) to help resolve misunderstandings and errors. Similarly, personnel should be urged to ask questions before entering data. All efforts should be made to ensure that decisions on how to enter data for particular questions are clear to every member of the data entry team so that uniform databases are achieved. Overview of the LIAT Questionnaire Using table 2 below, explain to participants how the LIAT questionnaire is divided into seven sections for entry into the database: form A–G. Table 2. LIAT Questionnaire Sections Paper-based LIAT Section Corresponding Database Form Optional? Facility Services and Infrastructure, Questions 01–05 A: Facility Information No Questions 101–126, including optional cold-chain questions 117–122 B: Questions 101–126 No (except Q117‒122) Table 1, Stock Status C: Table 1, Stock Status No Table 2, Storage Conditions D: Table 2, Storage Conditions No Table 3, LMIS Data Quality E: Table 3, LMIS Data Quality Yes Table 4, Percentage Difference Between Quantity Ordered and Quantity Received F: Table 4, Difference Between Ordered and Received Yes Table 5, Order Fill Rate to Be Calculated at Issuing Warehouses G: Table 5, Order Fill Rate Yes Remind trainees that it is important to check the paper copy of the survey completely to be sure they have entered all data before starting a new record. Using Microsoft Access for LIAT Data Entry Review the database structure and data entry forms, and go through several examples of how to enter sample data. 7 Begin by downloading a pre-prepared Microsoft Access database to your computer for display (preferably using a projector) to the trainees. The file will have a name like LIATdatabase_2008.CoteIvoire.mdb. Edit the year and country name as needed. A security warning will pop up when you open the file. Select Open in response to the warning. Database Structure Use the table below to explain to the participants that the data from the questionnaires will be entered into the MS Access database using seven different forms. There is one form for each section of the survey. Double-click on the form to open it, and then enter data from the paper survey on the form. The data they enter will be stored in seven different tables, one for each section of the questionnaire. Data entered in Form: …is stored in table: A: Facility Information Data Table 0 Facility Information B: Questions 101–126 Data Table 0 Facility Information C: Table 1, Stock Status Data Table 1, Stock Status D: Table 2, Storage Conditions Data Table 2, Storage Conditions E: Table 3, LMIS Data Quality Data Table 3, LMIS Data Quality F: Table 4, Difference Between Ordered and Received Data Table 4, Ordered v. Received G: Table 5, Order Fill Rate Data Table 5, Order Fill Rate Note that two additional tables, Country_and_Year and Products, will be populated by the data analyst before you begin entering data. Participants do not need to open or edit these tables unless the data analyst instructs them to do so. Display the tables in Design View to view the labels for each field and to show how to toggle between the database and design views. However, do not alter data. If necessary, the data analyst should do this. Entering Data Participants should keep a copy of the Data Entry Quick Start Guide (see page 21 of this document) nearby to help remind them of the steps for entering survey data from the LIAT. For practice, each participant will enter data for one facility at a time. Do not split up the sections for one facility between participants. When each trainee has finished entering all of the 8 information for a facility into his or her database, the trainee should write his or her initials in the top left corner of the first page of the survey form. This will signify that the form is ready to be passed to another participant. Subsequent trainees should also initial at the top left corner after finishing data entry. Data will be entered into the database forms using a data field for each question. Many fields have drop-down menus from which you can select a response that matches the response written on the questionnaire. In most cases, the drop-down menus have two columns: one on the left with numbers and one on the right with words. Participants should select the response that matches what is marked on the questionnaire. It may be easiest to type the marked numeric response from the questionnaire directly into each field. Although the drop-down lists include both numbers and descriptions, only the numbers will be displayed in the field and stored in the data tables. Advise trainees that— 1. The numbers they enter in a field remain posted if they use the Tab key to toggle to the next field. It is always best to use the Tab key to move to the next field and next record, not the mouse. This is the fastest way to enter data and will allow the database to move the cursor automatically to the next appropriate field. If you use a mouse, it will take longer, and there is a greater chance for data entry errors. Furthermore, it is also advisable to avoid using a mouse with a rolling wheel in between the buttons because the wheel can sometimes scroll a user to the next blank record, increasing the possibility that data will be entered in the wrong field. 2. To go back to a previous field, use the arrow keys or press the Shift and Tab keys together. 3. There are automatic skips programmed into the database. After entering a response to a question with a skip and hitting the Tab button, the cursor will automatically go to the correct field and will gray out any unneeded fields. When participants have completed data entry in one section of the database, they should use the Go to… button at the top of each form to open the next form. If you need to go back and edit data from a previously entered survey, finish the survey you are working on first, then go back to the appropriate form and edit the data from the previously entered survey. Open the form and click on the Find Record button to bring up the survey you wish to edit. Other rules for trainees to keep in mind when entering data:  Enter data exactly as they appear on the questionnaire.  Dates should be entered using numbers. For example, write in 15, 2, 2007 for February 15, 2007. 9  If you notice cases where other (specify) fits in one of the precoded categories, check with the data analyst.  If a skip/filter is not followed in the questionnaire, record responses exactly as they appear on the questionnaire and make a note of this issue at the bottom of the form in the field called Data Entry Comments. If possible, go back to the data collectors to ask why the responses were recorded this way. In general, the Data Entry Comments field should be used to enter any explanations of problems encountered with the database, questions about the data themselves from the questionnaires, or actions taken that do not follow established guidelines. This information will be stored in the database and will be very helpful in the analysis phase.  If you cannot understand a response written on the questionnaire, consult the data analyst. If necessary, circle the question in pencil and go back to the data collectors to ask what the response is.  Finish entering all of the data for a particular form before taking a break.  In the paper survey and the database, go all the way through tables 3, 4, and 5 (forms E, F and G) to ensure that any data that exist for those tables are entered. Creating Unique ID Numbers The data collection team leader should already have written a unique ID number on each survey packet and questionnaire. To explain how unique ID numbers are formulated, tell the participants— 1. Characters #1‒2 of the unique ID should be ID. Each ID must begin with these two letters. 2. Characters #3‒5 of the unique ID are the region code. This code is three digits long. Examples of region codes include 100, 003, and 025. Always enter the code as three digits, using zeroes where needed. 3. Characters #6‒9 are the three-digit district code. This code is also three digits long. Always enter the code as three digits, using zeroes where needed. 4. Characters #10‒12 of the unique ID are the three-digit facility code. Always enter the code as three digits, using zeroes where needed. Summarize by saying that each completed survey should have a unique ID with the following structure: “ID” + Region Code + District Code + Facility Code, such as in the following examples:  ID006001025 (ID + Region 006 + District 001 + Facility 025)  ID055148003 (ID + Region 055 + District 148 + Facility 003)  ID290014889 (ID + Region 290 + District 014 + Facility 889). Types of Questions Provide the examples below of all question formats found in the LIAT questionnaire and review the related guidelines on how to enter responses. For single response questions, like #117 and #118 below, enter the number that corresponds to the response (e.g., 1 for Yes in #117). 10 __________________ 107 117 Did your last supervision visit include drug management (e.g., stockcards checked, reports checked, expired stock removed, storage conditions checked)? Yes No Don’t know 1 0 9 118 When was the last time you sent an order/report for products at this facility? Never Within the last month 2 months ago 3 months ago More than 3 months ago 0 1 2 3 4 For multiple response questions, like #109 below, there is a field for each of the letter options. None of these fields should be left blank: enter a 1 to indicate yes for every item that is circled and a 0 to indicate no for items not circled. Enter 9 in all fields for missing when the entire question appears to have been overlooked. In the particular example of #109 below, W is offered for responses not otherwise listed. If a respondent circled W, data entry personnel would enter a 1 (yes) for W and record the written response from the questionnaire in the text field called other (specify). If W is chosen, but nothing is written, nothing written should be typed into the other (specify) field. During a logistics A How did you learn to complete the forms/records used at workshop B 109 this facility? (Circle all that apply.) On-the-job training Never been trained C W Other (specify) For numeric response questions, such as #107, write the number that is recorded on the questionnaire. If nothing is recorded, make a note under Data entry comments and request instructions from the data analyst. Do not write 00 if the question is blank on the questionnaire. How many facilities are supposed to send LMIS reports to this facility? For open-ended response questions, type the response exactly as it appears on the completed questionnaire. Saving Data Advise the participants that the Microsoft Access file will automatically save most changes as they are made (however, when prompted, users must choose whether to save). At the end of each day, close all the forms you have been working in and back up the database file onto a separate external flash drive. To back up the file, go to File menu and select Backup Database , or, if you are using a version of Windows without the Backup command, use Save as, making sure to back up to an external drive and not the computer’s hard drive. 11 When you are finished backing up the file on the external drive, remember to remove it immediately afterward and store it in a safe place. Be sure to work on the hard drive version every day when you start again. 12 Checking Double Data Entry To reduce the likelihood of errors, information from completed surveys is entered into two separate Access databases. The databases are then cross-checked to identify discrepancies, and to facilitate the creation of a single, final version of the database. The survey administrator is responsible for the double data entry checking and reconciling process. When organizing an assessment in which data will be collected from more than 100 facilities, it is recommended that four data entry personnel be hired to work in teams of two. For smaller assessments, it is usually sufficient to have two data entry personnel working separately. In both cases, a double data entry data approach should be used. Sufficient office space should be secured to enable members of the same data team to sit together and work from the same set of completed LIAT forms, and a proper place should always be reserved to store all forms after they have been entered. Cross-checking is performed using Excel; tasks can be automated by using macros. Commands for macros are written in a programming language called Visual Basic for Applications (VBA). Macro for Data Cleaning.xls is a macro that has been programmed in VBA to check whether the information in a cell on one database sheet (called First Try) is the same as in a second database sheet of the same type (called Second Try).1 Discrepancies are automatically listed in a third sheet (called Errors Explained). It is therefore essential that double entry databases be structured exactly the same and contain all information in the identical order. Getting Started Step 1. Exporting Tables from Access to Excel The following steps must be repeated for every table. 1. Open the table you wish to export by clicking on Tables in the Object start-up screen, and then double-clicking on the table. 2. Right-click on the Survey ID column and left-click on Sort Ascending. (It will be very important for comparison that the two tables are sorted alphabetically either A‒Z or Z‒A but not both.) 1 At this time, we are unable to identify the origin of the macro. 13 3. Go to: File > Export. 4. In Save as type, select Microsoft Excel 97‒2003. The file name will automatically be the current table name. It is essential to recognize which table is from which database. It may be useful want to add dataclerk1 or dataclerk2 as a suffix to the file name. 5. Select Export All. 6. Repeat steps for all tables that you are checking so that you now will just be working in Excel. Step 2. Preparing Excel 1. Make sure Excel is set to Medium Security so that the macro can run (this only needs to be done once). Do this by opening any Excel workbook and going to Tools>Macro>Security. 14 2. On “Security Level” Tab select Medium and click OK. 3. Close Excel by going to File>Exit. Step 3. Ensuring that the Macro File Is Clean Warning Viruses can be transmitted through macros. Changing your settings to Medium will mean that unsigned macros are not automatically disabled. Although Macro for Data Cleaning.xls is unsigned, its source is known and is safe. If you do not change your settings back to High or Very High following this exercise, you should be very careful opening up any files you receive, especially those with macros. 1. Open the “Macro for Data Cleaning.xls” file. 2. Select Enable Macros when security warning appears. 3. Check in the lower left-hand portion of the screen to ensure that the file has three sheets: First Try, Second Try, and Errors Explained. 15 4. Make sure that all three sheets are clean (empty) before you paste in the data. Click on cell A1 and, while holding down the left button, drag your mouse, highlighting all cells, until the last column and row with information inside. Do not click the cell in far upper left-hand corner of the spreadsheet to delete information because this will also delete the programming in the macro. Cross Checking Data Step 1. Adding Data to a Macro File Open the first exported table (e.g., TableA-dataclerk1.xls) and paste into the First Try sheet of the Macro file. Copy data from the second table and paste it into the Second Try sheet. If data were not sorted previously in the Access database based on “unique ID,” this must be done before copying and pasting data into the Excel macro. Step 2. Running Macro At this point, complete tables should be in the First Try and Second Try sheets. 1. Click on Tools>Macro>Macros… (or Alt+F8). 16 2. Select Search for Errors and click on Run. 3. Enter the number of columns and rows into the Hildah’s Error Detector dialog box and click Okay. The workbook will automatically be directed to the Error Explain, where discrepancies between tables 1 and 2 will be shown. If the two tables are identical, the sheet will be empty and no message will be displayed. An empty sheet could also mean that the macro was deleted from the workbook (possibly during step 3 of Getting Started). If you think this might have happened, close the Excel sheet without saving and start over. Step 3: Understanding the Error Explain Sheet  Column A identifies the cells in which there is a discrepancy between the First Try and Second Try sheets.  Column B provides the “UniqueID” of records with discrepancies (or the data listed in column A in the First Try sheet).  Column C shows the question numbers containing discrepancies (or the data listed in Row 1 of the First Try sheet).  Column D lists corresponding data from the First Try sheet.  Column E lists corresponding data from the Second Try sheet. 17 Consult the “Troubleshooting” section of this document (beginning on the next page) to resolve common problems with the sheet that may be preventing you from easily understanding discrepancies that were identified. Correcting Errors 1. Determine whether the First Try sheet or Second Try sheet has fewer errors by looking at the discrepancies and determining the correct information that should appear (this often requires consulting the hand-written surveys from which data was entered). It is best to make corrections to the sheet that contains fewer initial errors, and then save that version as the final database that will be used for analysis. Make sure that all corrections in all sections are made to the same database sheet. 2. Click inside the uniqueID box as if you are about to enter a new facility. 3. Using your mouse, click on the Find Record button in the Form view (located on the right-hand side of screen). 4. Enter the survey ID you wish to edit in a given space. 5. Locate the question and make corrections as needed. Troubleshooting Common Problem 1: Numbers Appearing as Dates If a date (e.g., 1/21/2010) appears in column D or E when it should be a number: 1. Left-click on the column heading, and then right-click, selecting Format Cells in the pop-up menu. 18 2. In the Format Cells dialog box, go to the General tab and select Number. Then click OK. Common Problem 2: Discrepancies Caused by Typographical Errors (and the like) It is important to remember that the macro has been programmed to check whether the information in a cell on the first sheet is the exactly the same as that on the second sheet. Therefore, it will still report a discrepancy for identical responses that have been entered differently. For example, line 23 in the figure below shows the same response for both sheets, but a discrepancy is shown because the words No and Comment are capitalized in the second response, but only the word No is capitalized in the first. Similarly, discrepancy reports will occur as the result of spelling mistakes, missing words, or extra spaces between words. 19 Common Problem 3: Hidden text When text cannot be seen because it extends beyond the end of a cell, such as in cells D11 and E11 in the image below, can be adjusted to “wrap” inside the cell. To wrap text inside a cell— 1. Highlight column(s) in which the cells appear by left-clicking on the column heading. 2. Go to Format > Cells in the main menu. 3. In the Format Cells dialog box, go to the Alignment tab and select Wrap Text. Then click OK. Adjust the row width if it does not change automatically at this point. 20 Common Problem 4: An Unusually Large Number of Reported Errors In surveys where data are collected from numerous facilities, it is common to find a large number of discrepancies in double-entered data. However, in particularly long lists of reported errors, you may notice certain patterns, such as repeating records (see column B in the figure below) or repeating question numbers (see column C in the figure below). In this case, you should check whether the same number of records is included on each sheet. If not, identify the missing records. You should also check that sorting by UniqueID was done before pasting data into the Excel macro. If not, go back to each sheet and sort by UniqueID. (See the next page for directions on sorting in Excel.) Other Useful Information Sorting in Excel 1. Click on cell A1. 2. Go to Data >Sort in the menu. 21 3. In the Sort dialog box, select Header row and Ascending. Then click OK. If sorting does not occur, or if only certain rows are sorted, check whether all row numbers are highlighted in blue at the left edge of the sheet. Data Analysis SPSS is recommended for data analysis. After data entry is complete— 1. Transfer data to SPSS using Stat/transfer. 2. Run frequencies of each variable using the SPSS syntax files that were provided to you. 3. Review the SPSS outputs to identify any areas of the data that need cleaning and proceed to clean them. Backup Access and SPSS files in a secure place. 22 Data Entry Quick Start Guide 1. Start in the Forms section of the database. 2. Open the first form, 1: LIAT Background Data and ensure that data have been entered. If not, contact your survey administrator before proceeding. The survey administrator should populate this table before the survey begins. 3. Each form in the database corresponds to a specific section of the paper-based LIAT. Begin entering data on form A: Facility_Information. Each survey that you enter must have a unique identification number. This always begins with the letters ID, and is followed by three sets of numbers. These numbers correspond to the region code, district code, and facility code, each of which is three digits long. 4. After entering the survey ID, fill in any other fields being used at the top of the form. These fields are optional, and may also be left blank. a. If using the Longitude and Latitude fields, the standard used is WGS84 (World Geodetic System 1984, updated in 2004). Data should be entered as Positive or Negative (POS = Positive, NEG = Negative), followed by two digits and a period, then followed by five digits (e.g., POS_12.15555 or NEG_12.15555). b. If you need to make changes to a survey ID, do so in form A: Facility Identification. The changes will automatically update to all other tables. Similarly, after you create the survey ID 23 on form A, the products used for tables 1, 3, 4, and 5 will automatically be entered for you into forms C, E, F, and G. 5. After you have completed data entry on form A: Facility_Identification, you will find a button on the last tab that will automatically take you to the next form in the survey, Form B. At the end of form B, you will find a button that will take you to form C, and so on through the end of the forms. 6. Note that forms A and B have multiple tabs. Ensure that you have entered data on all the tabs before proceeding to the next form. 7. Once you have entered all data in the survey, look for the button on form F (the last form) that will begin a new record for you in form A. 8. SPSS is recommended for data analysis. After data entry is complete— a. Transfer data to SPSS using Stat/transfer. b. Run frequencies of each variable using the SPSS syntax files that were provided to you. c. Review the SPSS outputs to identify any areas of the data that need cleaning and proceed to clean them. d. Backup Access and SPSS files in a secure place. 24 For more information, please visit deliver.jsi.com. USAID | DELIVER PROJECT John Snow, Inc. 1616 Fort Myer Drive, 11th Floor Arlington, VA 22209 USA Phone: 703-528-7474 Fax: 703-528-7480 Email: askdeliver@jsi.com Internet: deliver.jsi.com

View the publication