Wednesday, November 19, 2008
Print Printable version

INPUTTING DATA

13.1

Using The Online Database
13.1.1 Defra has created an online database that each authority must use in order to record and submit its NI 195 four-monthly surveys and annual returns.
13.1.2 This facility is aimed at simplifying and streamlining the process of reporting. It is also designed to give authorities the maximum amount of assistance in terms of the speed of feedback, and the content of management information.
13.1.3 Access to the online database will be controlled by username and password (see section 13.2). One username and password will be assigned to each local authority.
13.1.4 Survey data can be inputted either:
a. via a spreadsheet; or
b. directly into the online database.
13.1.5 If you are using the spreadsheet to input your data to the database you will need to refer to:
a. section 13.3 (How To Enter Data Into The Spreadsheet);
b. Section 13.4 (Saving The Survey File); and,
c. Section 13.5 (Using The Online Database).
13.1.6 If you are using the online data input tool to input your data, you will need to refer to:
a. Section 13.5 (Using The Online Database); and,
b. Section 13.6 (Using The Online Data Input Form).

13.2

Creating a Username and Password For The NI 195 Database
13.2.1 Before trying to use the NI 195 database, an authority should send an email to Defra, at info@NI195.com, in the following format:

Please register the following staff member of (Authority Name) to use the NI 195 database - An.Other@authority.gov.uk
13.2.2 The actual email format should be in the standard format used by the authority.
13.2.3 Authorities are advised to set up a separate email account for this purpose. This can also be used for multi-person access to the system.

e.g. NI195@authourity.gov.uk
13.2.4 Defra will reply, giving an appropriate unique password to be used by that authority when accessing the NI 195 database.
13.2.5 If the username and/or password are subsequently mislaid, please email Defra at info@NI195.com to request assistance.

13.3

How To Enter Data Into The Spreadsheet
13.3.1 The Spreadsheet
The spreadsheet can be downloaded by clicking here
13.3.2 There are up to 14 columns of information to be filled in for each transect on the spreadsheet. The first column is Survey Type. This is preset. Please do not use any comma punctuation in any of the text fields.
13.3.3 Ward
In the second column (B), enter the Ward Name. Try to remain consistent with your spelling. If necessary, use the ‘copy’ and ‘paste’ functions.
13.3.4 Target Ward
If the transect has had to be surveyed outside a target ward, enter ‘No’ in the third column (C).
13.3.5 Land Use Class
The fourth column (D) is ‘Land Use Class’. This cell has a drop down box with a pre-selected list of land-uses from which the correct entry can be selected.
13.3.6 This is the data entry point for land use class. Each transect should have a land use class recorded, which you can either type in, or you can use the drop down box at the right of the cell. The land-use categories available are:
  • Main Retail and Commercial
  • Other Retail and Commercial
  • High Obstruction Housing
  • Medium Obstruction Housing
  • Low Obstruction Housing
  • Industry and Warehousing
  • Main Roads
  • Rural Roads
  • Other Highways
  • Recreation Areas
13.3.7 Low Density Social Housing
For transects which would have been classified as ‘Low Density Social Housing’ for the purposes of the previous Cleanliness Performance Indicator, BVPI 199, surveyors may insert ‘Yes’ in the fifth column (E).
13.3.8 This facility may be used (or not) at the authority’s discretion. It is designed to assist liaison between local authorities and Arms Length Management Organisations (ALMOs), or with Large Scale Voluntary Transfer (LSVT) Housing Associations.
13.3.9 Date
In the sixth column (F), enter the date of the survey. The date is entered as follows, using ”/” to separate day, month and year. 1st April 2008 would be ”01/04/2008”.
13.3.10 Time
In the seventh column (G), enter the time of survey. Times are entered with colons to separate the hours from the minutes, using the twenty-four hour clock. Therefore, twenty past two in the afternoon would be entered as 14:20.
13.3.11 Surveyors Initials
The eighth column (H) is ‘Surveyors Initials’. Enter both surveyors’ initials if the transect has been surveyed jointly. Otherwise, enter the initials of the surveyor who has completed the survey form. Enter the surveyors’ initials using the first initial of the first name and the first letter of the surname of each surveyor, for example Joe Bloggs would be JB.
13.3.12 Transect Location
In the ninth column (I) Enter the street name / road number / recreation area name in the transect location field. Please do not use any comma punctuation in any of the text fields.
13.3.13 Use the ‘From / To’ fields, (Columns J and K) where appropriate, to indicate the precise location of the transect - e.g. between side roads on a long street, or on an A road, or within a recreation area (entrance, play area etc.).
13.3.14 Gradings For Litter, Detritus, Graffiti and Flyposting
Each column where a grading is to be entered appears with the title ‘Litter Grade’, ‘Detritus Grade’, ‘Graffiti Grade’ or ‘Flyposting Grade’
13.3.15 Use the drop down box for entering the Grade.
13.3.16 For detritus, use the ‘Not Applicable’ (N/A) option as appropriate – for example, if detritus is not applicable on an unsurfaced bridleway or on a site within a recreation area.
13.3.17 Once you have completed a record/transect use the next row to enter your next record/transect.

13.4

Saving The Survey File
13.4.1 Once you have entered all your data you will need to save your file so that it can be uploaded to the online database.
13.4.2 Before you can save the information you will need to carry out the following procedure.
  • Locate your last record.
  • In column ‘A’ there will be some text that reads ‘ NI195’ in every row
  • You will need to delete all the ‘NI195’ entries below your last record (please see following two screen shots)
13.4.2 Screen shot 1
Screen shot 1
13.4.2 Screen shot 2
Screen shot 2
13.4.3 Once the surplus ‘NI195s’ have been removed from column ‘A’ you need to ensure clean edges to the data file.
13.4.4 Navigate to column P and hi-light the entire column. Right click and select the ‘Delete’ option.
13.4.5 Navigate to the first blank row, hi-light the entire row. Right click and select the ‘Delete’ option.
13.4.6 Now you need to save the file with a .CSV extension. From the File drop down menu, select File > Save As.
13.4.7 Select where you wish to save your file and select file type as CSV (comma delimited). Then press ‘Save’. Please see screen shot 3.
13.4.7 Screen shot 3
Screen shot 3
13.4.8 Once you have pressed ‘Save’ you will be greeted by the following dialogue box (or similar depending on the version of Microsoft Excel you are using). Press ‘OK’ to accept the default answers to the questions.
13.4.8 Screen shot 4
Screen shot 4
13.4.8 Screen shot 5
Screen shot 5
13.4.9 Select ‘Yes’ on the next screen (Screenshot 5) and your file will be saved.

13.5

Using The Online Database
13.5.1 Logging In
To log on to the Online Database you will need to use the username and password provided by Defra to your Local Authority (see section 13.2).
13.5.2 If the username and/or password has been lost or misplaced please contact Defra for it to be resent.
13.5.2 Screen shot 6
Screen shot 6
13.5.3 Starting The Process of Entering Or Uploading Data
Once you have pressed ‘Login’ you will be greeted with the ‘My Surveys’ screen. This will initially contain one line for the current year’s NI 195 survey. This is your starting point to uploading or entering data to the system.
13.5.3 Screen shot 7
Screen shot 7
13.5.4 At the beginning of each year, you will find an entry on this screen for each year’s survey that you have already input.
13.5.5 The ‘Survey Start Year’ refers to the year in which the survey starts – for example, the 2008/2009 NI 195 survey will start in 2008.
13.5.6 This screen will show previous BV 199 surveys, if you have input them. To enter the data for these surveys, please use the Contact Defra link on the top line menu bar.
13.5.7 If you click on the ‘View’ button against a survey you will be taken to a screen that will allow you to choose further options about that survey.
13.5.7 Screen shot 8
Screen shot 8
13.5.8 Selecting A Survey To Work On
You can select whichever survey tranche that you are currently working on to add data into.
13.5.9 If you click on the View button for a survey you will be taken to the next screen where you can choose whether to upload data, enter data or submit data.
13.5.9 Screen shot 9
Screen shot 9
13.5.10 This screen shows you how many sites you have already entered into the system.
13.5.11 Adding Data
Data can be added in two ways. You can press either the ‘Upload Data’ button to upload data from a spreadsheet, or the ‘Add Data’ button to input data manually on-screen.
13.5.12 Uploading Data
If you wish to upload data you will need to press the ‘Upload Data’ button.
13.5.13 Once this has been pressed you will be greeted with the screen shown below.
13.5.13 Screen shot 10
Screen shot 10
13.5.14 By pressing the ‘Browse’ button you can search for the file you created in section 13.3.
13.5.15 This is similar to how you would attach a file to an email.
13.5.16 You can only upload CSV files generated as outlined in section 13.3. You will want to ensure that the ‘Ignore column headers’ box remains ticked.
13.5.17 Once you have found your file you will need to press the ‘Upload’ button.
13.5.18 When you have pressed ‘Upload’ the file will be checked to see if it contains the correct type of information. If it doesn’t an error message will be displayed along with a recommendation of how to resolve the issue.
13.5.19 If the file is uploaded successfully and entered into the database, the following page will be displayed:
13.5.19 Screen shot 11
Screen shot 11
13.5.20 This page details the number of sites that you have uploaded in the current session, the number of sites you have uploaded for the current Tranche and the breakdown of the land-uses in the file you have just uploaded.
13.5.21 You can print this page by pressing the ‘Print this page’ button at the bottom of the screen.
13.5.22 To return to the My Surveys screen press the ‘Tranche Data List’ Button. If you press this button you will be returned to the following screen.
13.5.22 Screen shot 12
Screen shot 12
13.5.23 You can see that the screen has changed and you now have a list of different survey sites. If an error has been made during data input you can make any amendments here or delete any duplicate files.
13.5.24 To delete any record press the delete button against that record. You will be asked if you really want to delete the record. You can confirm this by pressing ‘Yes’.
13.5.25 Editing Records
If there is an error with a record you can press ‘Edit’. When you press ‘Edit’ the following screen will load:
13.5.25 Screen shot 13
Screen shot 13
13.5.26 You can change any of the fields by using the drop down boxes.
13.5.27 Once you have amended the relevant details press the ‘Save Data’ button.
13.5.28 You can then press the ‘Tranche Data List’ button to return to the ‘My Surveys’ screen.
13.5.29 If you have encountered any problems with the current tranche of surveying – for example staff sickness has meant it has been completed late or weather anomalies have caused operational difficulties, you may wish to add some comments in the tranche comments box. You can enter up to 250 characters per tranche of survey (Up to 750 characters per survey year.)
13.5.22 Submitting Data
If you are happy that the data displayed on the screen is correct you will want to Submit the data.
13.5.31 Once you have submitted data you will be able to request a report which will detail your results to date.
13.5.32 Once submitted the data is locked and you will only be able to unlock it by emailing Defra via the Contact Defra section.
13.5.33 Once data is submitted the data that is displayed for editing and deleting will be removed from view.

13.6

Using The Online Data Input Form
13.6.1 The online data input form has been developed to operate on a desktop PC.
13.6.2 However, over time it will be developed to work with portable data capture devices (PDA’s etc).
13.6.3 For the purpose of this manual it is assumed that you are manually entering data from paper survey forms.
13.6.4 To access the Data Input form you will need to click on the ‘Add Data’ button.
13.6.4 Screen shot 14
Screen shot 14
13.6.5 Once you have pressed the ‘Add Data’ button the data input form will be loaded.
13.6.5 Screen shot 15
Screen shot 15
13.6.6 You will be presented with a blank form to fill in.

You will need to enter data into each field before being allowed to continue.
13.6.7 Please complete all the appropriate fields on the online form (note that one of the fields, ‘Low Density Social Housing’, is optional – see below).
13.6.8 To fill in the Land Use field click on the drop down menu and select the relevant Land Use.
13.6.9 To fill in the Ward field you will need to type in the name of the Ward. You will need to be as consistent as possible in the spelling of the Ward.
13.6.10 If the transect was in the Target Ward you will need to select the ‘Yes’ box. If the transect wasn’t in the Target Ward you will need to select the ‘No’ box.
13.6.11 The Low Density Social Housing field is optional, and may be filled in or not at the authority’s discretion. If the Transect would have counted as Low Density Social Housing for the BVPI 199 survey you may select the ‘Yes’ Box. If it would not, please select the ‘No’ box. Alternatively, the field may be left blank.
13.6.12 Fill the date field in following this format dd/mm/yyyy.
13.6.13 Fill the time field in following this format hh:mm.
13.6.14 Fill in the surveyor’s initials, for example Joe Bloggs would be entered as JB.
13.6.15 Fill in the location field from the survey form. Only enter the Location in this box - not the ‘from’ or ‘to’ information.
13.6.16 Fill the ‘from’ field in if necessary.
13.6.17 Fill the ‘to’ field in if necessary.
13.6.18 All the grading fields are filled in by using the drop down boxes. Note that only Detritus has a N/A field.
13.6.19 Once you have filled in all the fields press the ‘Save Data’ button.
13.6.20 Once you have pressed the ‘Save Data’ button the following screen will load.
13.6.20 Screen shot 16
Screen shot 16
13.6.21 If you wish to enter more data you need to press the ‘Add More Data’ button. If you have finished entering data you will need to press the Tranche Data List.
13.6.21 Screen shot 17
Screen shot 17
13.6.22 You can see that the screen has changed and you now have a list of different survey sites. If an error has been made during data input you can make any amendments here or delete any duplicate files.
13.6.23 To delete the any record press the delete button you will be asked if you really want to delete the record you can confirm this by pressing Yes. If there is an error with a record you can press edit and you will be returned to the data input screen.
13.6.24 If you have encountered any problems with the current tranche of surveying – for example staff sickness has meant it has been completed late or weather anomalies have caused operational difficulties, you may wish to add some comments in the tranche comments box. You can enter up to 250 characters per tranche of survey (Up to 750 characters per survey year.)
13.6.25 If you are happy that the data displayed on the screen is correct you will want to Submit the data.
13.6.26 Once submitted the data is locked and you will only be able to unlock it by emailing Defra via the Contact Defra link on the top menu line of the screen (see Screenshot 17).
13.6.27 Once data is submitted the data that is displayed for editing and deleting will be removed from view.
13.6.28 The total submitted shows the number of sites submitted to the database as being correct.
13.6.29 Data can only be used to generate reports once it has been submitted.
13.6.29 Screen shot 18
Screen shot 18
13.6.30 From the above screen shot you can see that when you have either added or uploaded data it is displayed for you to either edit or delete. If you press the edit button you will be able to edit the data on the data entry screen. If you press delete you will be asked to confirm if you are sure that you want to delete the relevant record. If you do wish to delete you will need to press ‘Yes’.

13.7

Entering Data from A Hand-Held Computer
13.7.1 If you wish you can use the data entry spreadsheet on a PDA style device that supports Microsoft Excel. Before you upload the information you will need to ensure it is saved as a .CSV file (Please see section 13.3 for further details.)
13.7.2 If you wish to use the website whilst surveying this is possible. At the time of writing we are currently working towards providing a list of devices that the website works with. Please use the Contact Defra link on the top menu line of the screen (see Screenshot 18) if your device does work and you would like to inform us. Updates will be provided via the NI195 website.

13.8

When To Enter The Data From Each Four-Monthly NI 195 Survey
13.8.1 Data entry for each four-monthly NI 195 survey should be carried out, and the data submitted to the online database, within 28 days of the end of the relevant survey period.
13.8.2 Survey Deadlines
The deadlines by which surveying should be completed in 2008/9 are as follows:
a. surveying for the first four-monthly survey should be completed by July 31st 2008;
b. surveying for the second four-monthly survey should be completed by 30th November 2008;
c. surveying for the third four-monthly survey should be completed by 31st March 2009.
  These deadlines will be the same for each year of survey.
13.8.3 Reminder Emails
If the results of a four-monthly survey have not been submitted to the online database within 28 days of the end of the four-monthly period, the online database will send a reminder email to the authority. This email will be resent at 7-day intervals until the survey results have been submitted.