Patient Records Spreadsheet - PDF by yru91928

VIEWS: 151 PAGES: 18

Patient Records Spreadsheet document sample

More Info
									 The National Mental Health and Learning Disability
                 Ethnicity Census

Count me in

 2 0 0 8
Notes on Completing the Census Spreadsheet
            (2008 – Version 4a)

                   31 March 2008
Patient names and IDs do not form part of the census return and all the data will be held anonymously.
Statistical analyses will all be anonymous with no patient attributable information.

These notes accompany the Census Spreadsheet and describe the steps involved in completing it.
It is strongly recommended that you read them thoroughly (even if you participated last year) as part of your
planning for the Census. Doing things in the right order and understanding what the Spreadsheet can do for
you will save you time.

The Census Spreadsheet can only be downloaded from the secure Provider area of the Census website
( by registered providers. You must use this Spreadsheet to return your Census
data by uploading it back to your secure web account in April 2008 (by 30 April at the latest).

The Census Spreadsheet is a “smart” Excel file which can be run on a standard PC operating the Windows OS
and running Excel 2000 or later. For the Spreadsheet to work correctly you must enable the macros, when
prompted. Please be warned that you cannot run any other Excel file while the Census spreadsheet is open
and the file will not appear on the Windows status bar (this is because of the macros).

The Notes are designed to help IT and Administrative staff involved in completing the Census Spreadsheet.
They should be read in conjunction with the Project Protocol which gives further information about the
dataset and can be downloaded from the website For clarification of any
aspect of the Spreadsheet or data capture process please email or telephone
0115 943 7100. The data capture process using the Census Spreadsheet is similar to that used successfully in
previous years.

The data collected last year was comprehensive and of very high quality. A complete and accurate return
depends not only on effective systems for data collection, but on accurate assessment and recording of
ethnicity at ward level.

A range of promotional and training materials to explain the census and assist staff in collecting the
information is being sent to providers (see details at

Finding your way through the Notes                         4

Step one: Preparing your data                              4
Do you have hundreds of patients or just a few?            4
Who is included in the Census?                             4
What information is required?                              4
The Census Dataset                                         5
Extracting data from local systems                         8

Step two: Install certificate and enable macros            8

Step three: Register Census Spreadsheet                    8

Step four: Import data from local system                   9

Step five: Collect data using Data Capture Sheets          11
Why would you use the Data Capture Sheets?                 11
Print Data Capture Sheets to collect missing data and/
or verify imported patient data                            11
Collect data using a standard Excel spreadsheet            12

Step six: Add ward information                             12
When should you add ward information?                      12
Edit ward and site information                             13

Step seven: Add and Edit patient records                   13
Ensure there are records for the full Census population    13
Input additional data to imported records                  14

Step eight: Verify and Copy Census Data                    14

Step eight: Encrypt Census Data                            15

Step nine: Deliver the Census Data to the Census website   15

Appendix                                                   16

Census Code Sheet                                          16

Document History                                           18
Finding your way through the Notes
It is recommended that you read through the whole document as part of your planning for the Census.
Providers who don’t have an electronic patient record system or database can ignore Step Four: Import data
from a local system.
Please make sure you also read the Project Protocol carefully, as this gives full details of the scope of the
Census and dataset definitions.

Step one: Preparing your data
Do you have hundreds of patients or just a few?
It is possible to complete the Census Spreadsheet from scratch, creating new records for each patient and
inputting data to the Census Spreadsheet (“the Spreadsheet”) one record at a time using the data input form.
For providers with a couple of dozen patients this is probably the most sensible way to complete the
Spreadsheet. Such providers can ignore Step Four: Import Data from Local System completely.
However for NHS providers and for private hospitals with many patients and an existing electronic patient
record system the most efficient way to collect the census data will include extracting data from a local
system and importing this data into the Spreadsheet. The Spreadsheet has been designed to support this and
these Notes tell you exactly how to format and prepare this data, specifically in Step Four: Import Data
from Local System.
Where an organisation receives patient data from more than one information provider (or a single new
merged Trust is returning data for a number of previously separate organisations – with their own information
systems) they may wish to make copies of the Spreadsheet and distribute these to individual sites. All these
completed spreadsheets will need to be collated into a single file before the data is encrypted and returned.
We can only accept one Spreadsheet return from each provider for whom we have registered a Census
Lead and an online provider account. (Please note that for private hospitals that are part of a group or
owner company, it is the individual hospital that has been registered for the Census, rather than the overall
group and we expect a separate return from each registered establishment.)
If you plan to make copies of the Spreadsheet for completion by separate sites in your organisation, please
read the sections on Registering the Spreadsheet, Importing Data and Verify Data carefully before proceeding.
Most importantly, you will need to establish your own local protocols to avoid duplication of records.

Who is included in the Census?
The Census sample is all people of all ages who are inpatients in mental health and learning disability
inpatient services at midnight on 31 March 2008 (ie at 00.00am*). There is further information about the
scope of the Census in the Data Capture Protocol.

What information is required?
The Census dataset consists of the data, shown below, some of which are common to most electronic
patient record systems. So far as possible the dataset uses existing NHS standard data items or other forms of
coding in current use (eg ONS Census 2001 categories for ethnic group and religion). 12 items are common to
the Mental Health Minimum Dataset (MHMDS), which is collected by all NHS providers.
The information is also consistent with the standards for independent providers that are required for
registration with the Healthcare Commission.
We would therefore expect nearly all the information to be available in patient records, even if it is not stored
electronically. 5 items (8, 25, 26, 27 and 29) will need to be checked directly with the patient to make sure
that what is recorded is the patient’s own view.

*on the night between 31st March – 1st April 2008

A full range of information and training materials will be available to help staff in checking this information
with patients (see for details).
If you know that existing records of these items are complete and recent and record the patient’s own views
then you will not need to ask them again for the Census.
For this reason it is unlikely that IT or administrative staff can complete the Census without collaboration with
ward staff. Remember that most of this information can be collected or verified before the Census date
(which will give some patients more than one opportunity to answer some of the questions), so that the on
Census day the important thing is simply to check which patients are included.

Must all the data items be collected for every patient?
All the data must be collected for every patient except the 3 optional fields noted immediately below.
Please also note the following:
• The dataset includes 3 optional free text fields for other ethnic group, other language and other religion, in
  case the codes available do not cover what the patient wishes recorded. These items are highlighted in blue
• Patient name, Local patient identifier and Site name need to be recorded on the spreadsheet to assist with
  local management of the data collection, but this information is stripped from the spreadsheet when it is
  uploaded and does not form part of the census return. These items are highlighted in green.
• Patients may refuse to give information about their ethnicity, language, religion or sexual orientation and
  the codes include Not Stated and Not Known options. However with proper preparation and support it
  should be possible to collect this information (for two years running over 99% of all ethnic records were
  complete and valid). We recommend you plan to collect this information as soon as possible. These items
  are highlighted in bold red.
• We are not collecting information on sexual orientation for patients born after 31 December 1991
  (and therefore possibly under 16 on 31 March 2008). The spreadsheet does not require a value for sexual
  orientation where the year of birth is before 1992.
• Lines 37-42 cover information about the ward where a patient is an inpatient. This information is need only
  be collected once for each ward and will be associated with patients on that ward (For further information
  see Step Six).

The Census Dataset
The notes give some simple explanations about the rationale for certain items and changes to coding since
2007, but please also refer to the definitions in the Project protocol.

  No   Data Item                  MHMDS          Matching          Notes
                                  Field Name     HES item

  1    Name of Patient                                             For local management of Census only –
                                                                   does not form part of return
  2    Local Patient Identifier   PTID           LOPATID           For local management of Census only –
                                                                   does not form part of return
  3    Organisational Code        PROCODE        PROCODE           Your organisation’s code
       (Code of Provider)
  4    Organisational Code        PURCODE                          There is a search facility on the census website
       (of Commissioner)
  5    Postcode of Patient’s      POSTCODE       HOMEADD           Full post code of home address if in hospital
       Usual Address                                               less than 1 year. Please see Protocol for
                                                                   default codes

6   Patient Gender              SEX           SEX
7   Patient Date of Birth       DOB           dob          Only the year is required but the
                                                           spreadsheet will accept dd/mm/yyyy
8   Ethnic Category             ETHNIC                     Standard NHS 16 + 1 codes (1=not stated)
9   Other ethnic category                                  Freetext
10 Way Ethnicity Assessed                                  Self assessment of ethnicity is recommended,
                                                           but assessment by relative or staff accepted
                                                           in specific circumstances.
11 Legal Status                 LSTATUS       leglstat
   Classification Code –
   On Census Day
12 Legal Status                 LSTATUS       leglstat
   Classification Code –        START DATE
   On Admission
13 Care Programme               CPALEVEL
   Approach level
14 *Source of Referral          REFERRAL                   Uses existing MHMDS codes plus some
   for Mental Health                                       additional codes including Court Liaison &
                                                           Diversion Services. Existing records may
                                                           need checking.
15 Date of Admission            IPEP –         STARTDATE   Date of admission to this hospital spell with
                                Hospital                   this provider.
                                Provider Spell
    What is the reason the                                 The Census includes people with mental
    patient is in hospital?                                health problems and people with learning
                                                           disabilities and some who have both. It also
16 • Mental Illness
                                                           includes people who have a personality
17 • Learning Disability                                   disorder. But which of these is the reason
18 • Personality Disorder                                  this patient is in hospital? More than one
                                                           can be selected.

19 Consent Status                                          Is the patient informal or detained under
                                                           the Mental Health Act? On 31 March are
                                                           they consenting to receiving treatment?
20 • sight impairment
21 • hearing impairment
22 • has a learning disability*
23 • has autistic spectrum
     disorder including
     Aspergers Syndrome
24 • mobility impairment
25 Sexual orientation of                                   Ask patient: How would you describe your
   patient (only for patients                              sexual orientation?
   born before 31                                          • Heterosexual
   December 1991)                                          • Lesbian or gay
                                                           • Bisexual
                                                           • Other
                                                           • Do not wish to answer
                                                           • Not known
  26 Preferred or First         LANGUAGE                          This is first or “preferred language for
     Language                                                     consultation.” Codes used are consistent
                                                                  with NHS data dictionary.
  27 Is an interpreter required for the purposes of               This includes family and carer.
     communication in relation to patient care?
  28 Other language
  29 Religion and Faith                                           Uses ONS 2001 Census codes.
  30 Other religion                                               Please use to enter detail of religion.
     *Recorded incidents:                                         How many of the following kinds of
  31 • self harm                                                  incident have been recorded for this patient
  32 • accident                                                   during this hospital spell, or within the last
                                                                  three months (if hospital spell is longer)?
  33 • physical assault on
                                                                  Select from number bands rather than
       the patient
                                                                  giving a precise count.
  34 • hands on restraint*
  35 Seclusion*?
36 a) Is this patient on a ward designated as                      This replaces the previous “Intended sex of
       Mixed gender                                                ward” question”
       Men only
       Women only
   b) Is this patient sleeping in accommodation, either a bay or shared bedroom, which is also
      accommodating members of the opposite gender?
   c) Does the patient have access to toilet and bathing facilities designated for single sex use?
      No – Mixed gender
      Yes – Men only
      Yes – Women only
   d) Does the patient have access to a lounge area/dayspace designated for single sex use?
      No – Mixed gender
      Yes – Men only
      Yes – Women only
  37 Location Name                                                Small independent hospitals with only one
                                                                  location can enter hospital name.
  38 Ward Name/identifier                                         Lines 36a, 37-42 recorded once for each ward
                                                                  and not for each patient. Please ensure that
                                                                  only one version of each ward name is used.
  39 Service Type                                                 Is this predominantly a
                                                                  • Mental Health or
                                                                  • Learning Disability Ward?
  40 Ward Age Range
  41 Ward Security Level                                          The security level is “general” unless the
                                                                  ward meets DH definitions for a low,
                                                                  medium or high security ward.
  42 Ward Type                                                    The codes provided should be considered in
                                                                  conjunction with the Ward Security and Age
                                                                  Range items and the Service Type. By careful
                                                                  combination of these it should be possible to
                                                                  accurately describe most types of ward.

Possible values for each line are detailed in the Census Code Sheet (reproduced in the Appendix).
If the Spreadsheet is completed manually the user selects from a list of valid options in a series of
pull down menus.
If data are being imported from a local system, however, the codes and format for data that are imported
must match those of the census dataset. This means that data extracted from local systems will need to be
“cleaned” before it can be imported into the Spreadsheet.
Items that are present in the MHMDS use MHMDS codes, except Source of Referral which includes some
additional options, such as prison and community services.
Some data elements also match items in the Health Episodes Statistics dataset (HES), further details can be
found in the Project Protocol.

Extracting data from local systems
Data to be imported into the Census Spreadsheet must be in CSV format. Providers will need to extract the
required data from their local system to a CSV file and check that the coding and format of this data complies
with the Census Dataset definition before importing it to the Spreadsheet. Step Four provides precise
instructions on which items can be imported in this way and how they should be formatted and ordered.
Now you are ready to run the Census Spreadsheet.

Step two: Install certificate and enable macros
Figure 1: Security Warning screen                             The Spreadsheet contains macros and you will
                                                              lose important functionality if these are not en-
                                                              abled. When the Security Warning appears, you
                                                              will need to install the Yacc Labs Certificate be-
                                                              fore you can Enable the Macros and use the
                                                              When the Security Warning appears click on Details,
                                                              then at the next dialogue box: Digital Signature
                                                              Details, click on View Certificate. Then click on
                                                              Install Certificate and follow the prompts from your
                                                              local OS to install the Yacc Labs certificate.
                                                                (If the Details button is not active you may need
to change the security level in Excel: select Tools from the Excel menu, then Macro, then Security and set the
level to Medium. Then you will be able to install the Yacc Labs Certificate as described above).
When you return to the Security Warning dialogue you should be able to check the Always Trust Macros From
This Publisher box and click on Enable Macros.
The Spreadsheet must be the only Excel file open on your PC. It is not possible to run open or read other
spreadsheets at the same time. If you need to open another spreadsheet you must first save and exit the
Census Spreadsheet. You may safely rename the census.xls file.

Step three: Register Census Spreadsheet

                                                          At the prompts (shown below) enter your organisa-
                                                          tion name and NHS organisation provider code. This
                                                          populates each patient record with the relevant de-
                                                          tails of the provider organisation. Once you have en-
                                                          tered these details the Spreadsheet’s main menu will

                                                       If you wish to distribute copies of the Spreadsheet to
                                                       local information providers you should register the
                                                       Spreadsheet before you make copies for distribution.
                                                       This will ensure that just one provider code is applied
                                                       to all your census data.

Step four: Import data from local system
If you are not importing data from a local electronic patient record system you can ignore Step Four.
Go straight to Step Five. But note the Main Menu screen in Fig 2.

Figure 2: Census Spreadsheet Main Menu                          To import existing patient data click on the
                                                                Import Patient Data button and browse your
                                                                system to find and select the relevant CSV file.
                                                                Please note your CSV file does not include
                                                                the information about the ward in lines (lines
                                                                39-42), which will need to be entered to the
                                                                spreadsheet manually.
                                                                Figure 3 below shows the items (with item
                                                                numbers matching the dataset given above)
                                                                to be included in your import file in the
                                                                correct order:

                                                                Figure 3: Items to be included in import file

  Line No          Field                                 Format
  1                Patient Name                          Text
  2                Patient Identifier                    Text
  3                Organisation Code of Provider         Text
  4                Organisation Code of Commissioner     Text
  5                Patient Postcode                      Text
  6                Patient Gender                        Numeric (1)
  7                Pat_DoB                               Either dd/mm/yyyy or yyyy
  8                Patient Ethnicity                     Char (1)
  9                Other ethnic category                 Text

  10            Way ethnicity assessed                  Numeric (1)
  11            Legal status on Census day              Numeric (2)
  12            Legal status on Admission               Numeric (2)
  13            CPA Level                               Numeric (1)
  14            Source of referral                      Numeric (2)
  15            Admission_Date                          Text: dd/mm/yyyy Numeric (1)
  16            Reason in hospital – MH                 0 = no 1 = yes
  17            Reason in hospital – LD                 Numeric (1) 0 = no 1 = yes
  18            Reason in hospital – PD                 Numeric (1) 0 = no 1 = yes
  19            Consent                                 Numeric (1)
  20            Sight Impairment                        Numeric (1)
  21            Hearing Impairment                      Numeric (1)
  22            Learning Disabilities                   Numeric (1) 0 = no 1 = yes
  23            ASD including Aspergers Syndrome        Numeric (1) 0 = no 1 = yes
  24            Mobility Impairment                     Numeric (1)
  25            Sexual Orientation                      Numeric (1)
  26            Patient’s preferred language            Numeric (3)
  27            Interpreter required                    Numeric (1)
  28            Other language                          Text
  29            Patient’s Religion or faith groups      Numeric (2)
  30            Other religion and faith groups         Text
  31            Incidents of Self Harm                  Char (1)
  32            Accidents                               Char (1)
  33            Incidents of assault on the patient     Char (1)
  34            Incidents of hands on restraint         Char (1)
  35            Incidents of seclusion                  Char (1)
  36a           Mixed/single gender ward                Numeric (1)
  36b           Sleeping accommodation                  Numeric (1)
  36c           Bathing/toilet facilities               Numeric (1)
  36d           Dayspace                                Numeric (1)
  37            Location/site name                      Text
  38            Ward name                               Text
  39            Service type                            Numeric (1)
  40            Ward age range                          Numeric (1)
  41            Ward security level                     Numeric (1)
  42            Ward type                               Numeric (2)
Each column should be separated by a comma and each line by a carriage return. If a column is empty,
it should contain a space. See below for a sample line of csv data to be imported.

Please note that if you Export Local Data Copy (see menu in Figure 2) from the census spreadsheet the file
created will be include the same data items in the same order (except IDs for the Site and Ward will be
added to the end of the line).
When you have imported the file a prompt will confirm that this has been completed successfully.
The patient records you have imported will not be complete. At the very least you will need to add the
ward information for lines 39-42. The Spreadsheet will enable you to browse and view imported patient
records which are incomplete and add any missing data or edit existing data. Use the Data Capture Sheets
that can be printed off from the Spreadsheet to collect missing information (see Step Five).
If you have distributed copies of the Spreadsheet to local information providers you will use the
Import Patient Data function to import correctly formatted data from individual sites into the main provider
Spreadsheet. Local information providers will need to complete the Spreadsheet and then use the Export
Local Data Copy function to create a correctly formatted csv file with all their local data. This can then be
imported into the main provider Spreadsheet.
The spreadsheet cannot easily be used to check for duplicate records so you should run any checks for
duplicate records outside the Spreadsheet.

Step five: Collect data using Data Capture Sheets
Why would you use the Data Capture Sheets?
If you have only a few patients the simplest way to collect the data for the Census will be to print a blank
Data Capture Sheet for each patient, to be filled in from patient records. When all the information has been
gathered in this way it can be entered into the Spreadsheet, one record at a time.
If you have partially populated the Census Spreadsheet by importing data, you can also use the Data Capture
Sheets to collect the missing information for each patient, before inputting this to the Spreadsheet. It is
possible to print off records for each patient either showing all information stored for each patient, or just the
fields that are incomplete.
Last year some providers printed off Data Capture Sheets for every patient when the Census spreadsheet was
complete and stored the individual sheets with patient records, ready to update local patient records systems.
To find the print commands select the Add, edit or delete Census Records button from
the Main Menu. From the main Record screen either click on either the Print Blank
Records button to print blank data capture sheets or select the Print button for other
options to print patient data already in the spreadsheet.
If you are collecting all your patient information using the blank data capture sheets and are
not importing any data from an electronic patient record system, go to Section Six next.

Print Data Capture Sheets to collect missing data and/or verify imported patient data
You can print the current record or all records. The main Census Record screen provides tools for sorting and
filtering records by Site and Ward. If you print all records they will be printed in the same order they were
entered, unless they have been sorted (eg sorted by Ward).
If you filter records by ward then you can choose to print only filtered records – ie the records for one ward.
The checkbox at the bottom enables you to choose whether to print all the available data for each record or,
if unchecked, only the fields that need completing (plus sufficient information to identify the patient).
The Sheets should be used in conjunction with the Census Code Sheet to ensure that correct codes are
understood and used for each field. The Code Sheet can be printed from the final pages of the Appendix or
the Data Capture Protocol.

                                                           The Data Capture Sheet also includes a verification
                                                           check box and provides plenty of space for
                                                           amendment so that existing data can be checked
                                                           and updated at the same time as missing data
                                                           is gathered.

                                                           Figure 4: Print screen

Collect data using a standard Excel spreadsheet
A number of providers last year asked for a standard Excel spreadsheet which they could use for gathering
data. We have designed a correctly formatted spreadsheet which can be downloaded from the website.
Please note that this cannot be used to return your data, only to support your local data gathering activities.
The Blank Spreadsheet contains the same items as in the import file specified in section 4. If you wish to
import data into the Census Spreadsheet from an Excel file you must use this file. Please do not add columns,
re-label, re-order or re-format them. Ensure you use the correct codes for each item or the verification routine
(Step Eight below) will reject the data. Please note that you will have to add Ward details manually as in
Step Six below.

Step six: Add ward information
When should you add ward information?
If you are completing the Spreadsheet manually you are advised to add the information for each Site and
Ward before you start adding patient records to the Spreadsheet.
If you have imported patient data you must edit and complete the Site and Ward information included in your
import before you edit any patient records.
If you are merging data from a number of sources you will need to check that there is no duplication of Site and
                                                                   Ward information on the Spreadsheet.
                                                                   Duplicate ward and site information will have
                                                                   been created wherever there is not a full
                                                                   match (eg importing information about Ward:
                                                                   The Willows and Ward: Willows Ward will
                                                                   create two ward records). Click on the Ward
                                                                   List Report button in the main menu to view a
                                                                   list of all wards in the spreadsheet and
                                                                   identify any duplication. If you have created a
                                                                   duplicate ward use the Rename Ward function
                                                                   to edit the name of the duplicate to match
                                                                   the original. You will then be able to merge
                                                                   information for these two wards (ie the
                                                                   patients associated with the duplicate
                                                                   ward will now be associated with the
                                                                   merged ward).

Figure 5: Wards and Locations Editor screen

Information about Wards and Sites can only be added and edited from the Wards and Locations screen and
not from the main. Census Record screen.
Select Wards and Locations from the Main Menu.

Edit ward and site information
The Spreadsheet collects ward information separately to ensure consistent information is provided about each
ward and to help providers see all the information about each ward in one place. Please think carefully about
how to use the range of codes provided so that taken as a whole they describe individual wards as accurately
as possible (eg age range, security level, function or type, and whether this is primarily a mental health or
learning disability service).
You can use this screen to edit location (site) and ward information already imported or to add details about
new locations and wards.
You must first add the Location Name (and if you are a small hospital you could simply repeat the Hospital
name) by typing in the location field.
To add a new Ward click on the Add Ward button and enter the name of the ward. Make sure the correct
Location name is in the Location field as the Ward will be linked to that Location.
Then select the relevant Age Range, Security Level, Ward Type and Service Type for the ward.
If you wish to edit information about an existing Ward, select from the pull down menu next to the Ward
field. If the spreadsheet is storing 2 different names for the same ward you can merge these by Renaming
one of them to match the other. Then a prompt will ask if you wish to merge the wards – this will ensure any
patient records associated with both wards are linked to the merged ward.
To delete a Ward or Location select the name from the pull down menus and click on the cross button. If you
delete a Ward the records of any patients linked to the ward will not be deleted.

Step Seven: Add and Edit patient records
If you are inputting all your data manually then this is the main input screen you will use.
Even if you have imported data from an electronic patient record system you will probably need to edit or
add to these records.
Select Add, Edit or Delete Census Records from the main menu.
From the Census Record screen you can browse, view, edit, add and delete patient records. The main
functions are shown in Figure 6 below. For most fields the user must select a value from a menu of options.
You must select Add to create a new patient record.
You can browse incomplete imported records and add missing data over several sessions. You can exit the
Spreadsheet at any time saving all completed records. However you will not be able to encrypt and return
the Spreadsheet until every record is complete.

Ensure there are records for the full Census population
The Census covers all inpatients at midnight on the Census date (see Data Capture Protocol for full details
of the Project Scope and Census Population). If data is imported from an existing electronic patient record
system it is likely that some records will need to be deleted and some added to take account of, for example,
patients admitted since the system was last updated and patients who were discharged after midnight on
the Census date.
Use the Add and Delete buttons to create new patient records or delete records of patients not included
in the Census. You can also import records at this stage although it is advisable to import any data before
adding other records. Any records imported to the Spreadsheet once it already contains records will be
appended to existing records. The Spreadsheet is not able to check for duplicates.

Figure 6: Census Record screen

                                                                                           Browse records
                                                                                           Create new record
                                                                                           Delete a record
                                                                                           You can search for a single
                                                                                           record by full name or
                                                                                           patient ID.
                                                                                           Click Clear and then enter
                                                                                           the search term and click
                                                                                           Select from a range of
                                                                                           print options.
                                                                                           Print blank data capture
                                                                                           Save individual patient
                                                                                           records when there is a
                                                                                           value for every field.
Sort or filter all records by location and/or ward. Within each ward they ordered alphabetically by first name.
Click Clear to start a new filter or sort action on all records.

Input additional data to imported records
Additional or corrected data collected on the ward using the Data Capture Sheets should be input to the
Spreadsheet via the Census Record Screen above. Take care to ensure that only correct values are entered.

Step eight: Verify and Copy Census Data
When all patient data has been entered into the Spreadsheet select Verify Census Data from the Main Menu.
This will run a check to ensure that all records are complete and produce a report of any missing data, as in
Figure 7 below. Please try to provide complete data; obviously the data fields relating to ethnicity, language and
religion are particularly important. When you have collected the missing data please input it to the Spreadsheet.
                                                              Figure 7: Patient Validation Report

                                                              If copies of the Spreadsheet are being completed
                                                              by local sites/ information providers they should
                                                              complete the Spreadsheet to this point,
                                                              verifying that all patient information is complete
                                                              and correct.
                                                             Then they should select the Export Local Data
                                                             Copy button from the Main Menu. This will export
                                                             all records to a csv file which is correctly formatted
for importing into the provider’s master Spreadsheet. (see Step Four: Import Data from local system).
If there are any concerns about duplicate patient records we suggest that checks are run on this data before it
is imported into the Spreadsheet.
If a provider wishes to keep a copy of the Census return they should Verify the Data, Exit the Spreadsheet and
create a copy of the Spreadsheet for their own use at this point.

Step eight: Encrypt Census Data
To protect patient confidentiality the Spreadsheet can only be uploaded to the Census website when the data
has been encrypted. Once the data is encrypted it cannot be edited or extracted from the Spreadsheet.
Do not encrypt the data until you are satisfied it is complete and valid. We suggest you save and exit the
Spreadsheet and make a copy of it before encrypting the data.
Once you are ready open the Spreadsheet again and select Encrypt Census Data from the Main Menu.
You will receive the following warning message and can abort the encryption at this point if the data is not
yet complete. (Select No!)

Select Yes to encrypt all the census data. Once the data has been successfully encrypted you will be given an
Upload Key. Make a record of the upload key because you cannot upload you census data to the Census
web site without it and this is the only time it will be displayed to you.
Save and exit the Census Spreadsheet.

Step nine: Deliver the Census Data to the Census website
Log in to the Provider area at and select the Upload Census link. Use the Browse
button to find and select the census.xls (unless you have renamed the file) on your local system and enter
the Upload Key (displayed after the data was successfully encrypted) as prompted. Click the Upload button.
Wait until the next screen reports the success of your upload.
Please be patient! Please check that the report of the number of records received matches the number
you think you have submitted. If there is a discrepancy please contact the Census Team on 0115 943 7100
so that we can ensure that no records are lost.

Thank you for your co-operation.

Dataset codes
6. Gender        0 – Not Known        1 – Male         2 – Female        9 – Not Specified
8. Ethnic Category:
   A – British (White)                              K – Bangladeshi (Asian or Asian British)
   B – Irish (White)                                L – Any Other Asian Background (Asian or Asian British)
   C – Any Other White Background (White)           M – Caribbean (Black or Black British)
   D - White and Black Caribbean (Mixed)            N – African (Black or Black British)
   E – White and Black African (Mixed)              P – Any Other Black Background (Black or Black British)
   F – White and Asian (Mixed)                      R – Chinese (Other Ethnic Groups)
   G – Any Other Mixed Background (Mixed)           S – Any Other Ethnic Group
   H – Indian (Asian or Asian British)              Z – Not Stated
   J – Pakistani (Asian or Asian British)
10. Way Ethnicity Assessed
   1 – Self Assessment              2 – Assessment by Staff (Exceptional Case)
   3 – Assessment by Relative       4 – Not Assessed
11 & 12. Legal Status
   01 – Informal         09 – Section 37/41         19 – Section 135
   02 – Section 2        10 – Section 37            20 – Section 136
   03 – Section 3        12 – Section 38            31 – Criminal Proceedings (Insanity) Act
   04 – Section 4        13 – Section 44            32 – Criminal Courts Act S3
   05 – Section 5.2      14 – Section 46            33 – Section 25A
   06 – Section 5.4      15 – Section 47/49         34 – Section 45a
   07 – Section 35       16 – Section 47            35 – Subject to guardianship – Section 7
   08 – Section 36       17 – Section 48/49         36 – Subject to guardianship – Section 37
                         18 – Section 48
13. CPA Level
   0 – Neither CPA nor SAP      1 – Standard CPA     2 – Enhanced CPA       3 – Single Assessment Process
14. Referral Route (codes added to MHMDS standard codes for this item)
   00 – GP                      09 – Courts            11 – High Security
   01 – Self                    10 – Probation service 12 – Medium security (NHS)
   02 – LA Social Services      25 – Prison            27 – Medium security (independent)
   03 – A & E department        26 – Asylum Services   28 – Other in-patient service ( NHS)
   04 – Employer                                       29 – Other in-patient service ( independent)
   05 – Education Service
   06 – Police
   08 – Carer                                          07 – Other clinical specialty
   23 – Community Team (MH, including crisis
        resolution/home treatment)                     99 – Unknown
   24 – Community Team (LD)
   30 – Court liaison and diversion service
15. Date of admission     dd/mm/yyyy
16-18 Which of the following is the main reason the patient is in hospital? (if necessary select more than
       one, but do not select condition that is present but is NOT the reason the patient is in hospital)
16 mental illness
17 learning disability
18 personality disorder
   0 – No         1 – Yes

9. Consent Status
   1 – Informal – Capable                       2 – Informal – Incapable     3 – Detained – Consenting
   4 – Detained – Capable but refusing          5 – Detained – Incapable     6 – Not known
20. Is the patient blind or does the patient have a sight impairment?
   1 – Is blind       2 – Has a sight impairment     3 – Neither    6 – Not known
21. Is the patient Deaf/deaf or does the patient has a hearing impairment?
   1 – Is Deaf/deaf 2 – Has a hearing impairment    3 – Neither    6 – Not known
22-23. Does the patient have any of these disabilities? (select those that apply)
   1 – Yes   2 – No     6 – Not known
22. has learning disability
23. has autistic spectrum disorder including asperger’s syndrome
24. Does the patient use a wheelchair or have a mobility impairment?
   1. Uses a wheelchair     2. Has a mobility impairment       3. Neither                 6. Not known
25. Sexual orientation
   1 – Heterosexual    2 – Lesbian or gay               3 – Bisexual
   4 – Other           5 – Prefer not to say            6 – Not known
26. First or preferred language
   004 – Arabic                       028 – Italian                     046 – Somali
   005 – Bengali                      029 – Japanese                    048 – Spanish
   007 – British Sign Language        031 – Kurdish                     049 – Swahili
   008 – Yue Chinese                  034 – Makaton                     053 – Tamil
   012 – English                      036 – Mandarin Chinese            056 – Turkish
   014 – Parsi                        039 – Patois/Creole               057 – Urdu
   017 – French                       040 – Polish                      058 – Vietnamese
   020 – German                       041 – Portuguese                  059 – Welsh
   021 – Greek                        042 – Punjabi                     581 – Non verbal communication
   022 – Gujerati                     043 – Russian                     200 – Any other language
   026 – Hindi                        044 – Serbian/Croatian
27 Interpreter Required
   0 – No     1 – Sometimes           2 – Yes       6 – Not known
29. Religion and Faith groups
   01 – None       02 – Christian        03 – Buddhist                  04 – Hindu         05 – Jewish
   06 – Muslim     07 – Sikh             08 – any other religion        09 – not stated
31-35 Number of recorded incidents since 1/1/08 or during this hospital spell (if shorter).
      Select appropriate number range
   Number of Incidents     A–0         B–1          C – 2-4     D – 5-9      E – 10+
31. self harm
32. accident
33. physical assault on the patient
34. hands on restraint
35. seclusion

36a: Ward is
  0 – Mixed gender        1 – Men only        2 – Women only
36b: Sleeping accommodation accommodating members of opposite gender
  0 – No        1 – Yes     2 – Not known
36c: Access to single sex bathing facilities and toilet
  0 – No mixed gender          1 – Yes Men only             2 – Yes Women only          3 – Not known
36d: Access to single sex dayspace/lounge
  0 – No mixed gender          1 – Yes Men only             2 – Yes Women only          3 – Not known
39. Service Type: Is this ward predominanty a (select one)
   1 – Mental health service     2 – Learning disabilities service
42. Ward Type
   01 – assessment and Treatment          02 – high dependency/extra care        03 – PICU
   04 – intensive care                    05 – campus – NHS retained beds        06 – “old long stay”
   07 – long stay (more than a year)      08 – short stay (less than a year)     09 – acquired brain injury
   10 – rehabilitation                    11 – respite                           99 – other
40. Ward Age Range
   1 – CAMHS       2 – Working Age Adult         3 – Old Age
41. Ward Security Level
   1 – General      2 – Low Security          3 – Medium Security       4 – High Security

Document History

Version 1           23 March 2005             Correction to Codes Sheet: definition      Web site
                                              of code 0 for Ward Sex – should be
                                              “mixed” rather than “not specified” (p 13)
Version 1a          26 January 2006           Notes amended for 2006 Census                 Pilot volunteers
Version 2           21 February               Amended post pilot                            Release
Version 3a          21 December               07 version for pilot
Version 3b          Jan 07                    Release
Version 4a          Feb 08                    Update


To top