Data Sources and Collections
In line with the UK National Statistics publication guidance, information on the various data available within the ISD NHSScotland Workforce Information pages can be found within our metadata 
[119K] document.
Further information on the various workforce information sources are detailed below.
Information on the various data source where workforce information was captured throughout the years are detailed below. The majority of these systems are now no longer in existence as a result of the introduction of Scottish Workforce Information Standard System (SWISS) in 2006.
- SWISS
- Workforce Data Mart
- MEDMAN (system discontinued)
- NAMS (system discontinued)
- ERBOD (system discontinued)
- SWAPS (system discontinued)
- MIDAS
- GPCD
The main source of workforce statistics is SWISS (Scottish Workforce Information Standard System). Workforce information was first captured through SWISS in 2007 for the data as at 30th September 2007. It included all workforce data with the exception of the medical and dental data.
The introduction of SWISS is done in several phases:
- Phases 1 and 2: information is available on the employee, their employment, including remuneration, and their absence.
- Phase 3 (completed Sept 07): Information is available on the employee's organisational structure, work area.
- Phase 4 (2009): Information on posts, including vacancies.
As SWISS continues to evolve, this has resulted in changes to elements of the phases detailed above. In turn this has impacted on the development and capture of particular staff in post information. Further details around the various phases of SWISS can be obtained from the SWISS team.
(Steven Williamson - Tel:-0131 275 6894 email: steven.williamson@nhs.net )
Data are collected electronically and feeds into the Workforce Information Repository (WIR), either nightly (Payroll and web front-end updates) or weekly (HR interface updates).
Historically Medical and Dental information was captured via the MEDMAN web based system. However during July 2008 Medical and Dental information was migrated from the MEDMAN web based system to the Scottish Workforce Information Standard System. This alignment provided the opportunity to capture all NHSScotland staff in post information in the one system, which has resulted in more robust information being captured and reported.
The information available in SWISS is at individual level for all staff:
- Employee contains personal identifiable fields:
- NI number, name, age.
- Employment contains information about staff current job:
- Employer, hours, start date, payscale
- Absence contains information about employees absence periods:
- Start and end of absence, absence type, and hours lost.
- Employment Earnings contains information about the employees earnings:
- Financial year, pay period, payscale, gross pay, hourly rates.
Data should be updated by NHS Boards on a regular basis, allowing data analysis at any time. However workforce tables are published at specific times of the year, either quarterly (March, June, September, December), bi-annually (June and December) or annually in December.
Monthly high-level reports are also sent out to NHS boards for monitoring purposes and thus for management of information only.
The Workforce Information team is currently in the process of creating and developing a Workforce Data Mart. This data mart will bring together the various historic data sources of workforce information as well as housing monthly snap-shots taken from the SWISS system. SWISS is a dynamic data base where staff details can be changed on a daily basis and therefore historic information is not preserved. The monthly SWISS snap-shots housed within this data mart will ensure these historic details are preserved, and therefore support national, regional and local workforce planning.
The structure and design of the data mart should enable the end user to routinely extract, process and analyse the required information in a more timely and effective manner. The data mart will include information for:
- Historic – Staff in post data from 1998 -2006
- 2 annual snap-shots of staff in post data as at 30th September 2007-2008
- Monthly snap-shots of staff in post data from January 2009
- Monthly Employment Earnings (Pay) information from January 2009
- Aggregated Sickness Absence information – Monthly from December 2008
- Aggregated Sickness Absence information – Rolling year beginning with year January 2008 – December 2008
- Aggregated Equality & Diversity data from January 2009
The development of the data mart will not only enable the team to provide a more timely information service to our customers, including NHS Boards and the Scottish Government, but it will also enable the team to be better placed to support national policy as laid out in the ‘Better Health, Better Care’: Action Plan.
The development of the data mart is currently in the build stage. The research, planning and analysis stages are complete. Once built the data mart will under go extensive user-acceptance testing. It will then go through an implementation stage where it will become ‘LIVE’ and be in use by ISD.
Training will initially be provided to the ISD Workforce information team who will use the data mart for routine reporting and publications. The Workforce data mart should be available for use some time in the first half of 2010.
MEDMAN dataset (Medical and Dental Manpower Scheme)
For a number of years ISD and its predecessors have performed an annual census, at 30 September, of medical and dental, hospital and community health service (HCHS) staff. Electronic data collection began in 1980 and has continued ever since.
MEDMAN (a web-system) came into force in 2004 to replace a data collection made with a combination of disks, packs and the ISD (M) 27 form (notification of appointments and staff changes for registrars grades and above only). Junior grades were still collected via a paper exercise once a year.
The collection is at individual contract level with individuals being recorded more that once if they hold more than one contract within an NHS organisation or with different organisations.
The census covers all medical and dental HCHS staff. Note that:
- It includes staff (usually consultants and registrars) on honorary contracts. These are doctors who don't have a paid contract with the NHS.
- It excludes Primary Care Service staff such as General Medical Practitioners (GMPs) and General Dental Practitioners (GDPs) who usually have independent contractor status (although an increasing number are salaried) and for whom data are collected via other means.
In 2001 and 2002 an attempt was made to collect data on Salaried GPs, Salaried GDPs and General Vocational and Professional Trainees (GDP trainees) who are NHS employees. However, the data this provided was incomplete. Where GMPs or GDPs have additional part-time contracts in the HCHS sector, these are covered by the medical and dental census.
- Locum doctors and dentists, who work as and when required are included in the census and included in MEDSNAP but excluded from the FFONE analyses files and therefore from the published statistics.
- Consultant and staff grade posts vacant at the 30th September are included in the FFONE files from 1996, with Whole Time Equivalent (WTE) information available from 2001.
The medical and dental staff census provides the only central and national data source for this part of the NHSScotland workforce. The data are used to produce the Internet publication Workforce Statistics. Historically the data were published through publications known as Scottish Health Statistics and Medical and Dental Staff in Scotland.
The data are also used to respond to Information Requests (many coming from the Scottish Government); to support Workforce Planning Mechanisms and Workforce Reviews; and to provide evidence to the Doctors and Dentists Review Body (DDRB) on Doctors and Dentists Pay.
Data provide information at NHS board, region and Scotland level for most of the tables published. Tables include information on grades, specialties, country of qualification, age, gender, contract type (full- or part-time) and consultant vacancy rates.
In 2008 MEDMAN is no longer in use as all data was uploaded into SWISS during the summer and data were updated by NHS Boards in SWISS from then on. The first publications using medical and dental data from SWISS instead of MEDMAN were as at 30th September 2008. Consultant vacancies available in MEDMAN were no longer collected in MEDMAN as the removal of staff in post caused problems with updating the vacancies. Until they were added to SWISS in 2009 consultant vacancies were collected via a temporary paper collection exercise for the December 2009 release.
NAMS (National Manpower Statistics)
The NAMS data collection system was introduced in 1979 and was reviewed several times since. It covered the non-medical and -dental staff employed by NHSScotland and did not include vacancies. One of the main changes took place in 1999 when all the workforce systems were transferred to an Oracle platform as part of the Y2K project (Year 2000 Project). This project was set up to allow the amendment of the database so as to cope with the change of dates in the new millennium. Indeed from 1996 the expected Completion of Certificate of Specialist Training (CCST) date for Specialist registrars was wrongly picked up by the database (e.g 1900 for 2000, 1901 for 2001 etc).
The NAMS data once obtained from SEMA (now AtosOrigin) were coded and quality assured. The NAMS/ERBOD classification system appended a range of codes to each NAMSand ERBOD record held on the Oracle database for each census period (c. 150 000 records for NAMS, c. 200 000 records for ERBOD) to facilitate data analysis. These codes allowed classifying the records into: staff group, specialty, sub-specialty, qualification and grade.
The classification system was introduced in 1991, for NAMS, as part of the Management Information System (known as MIS). It was referred to, internally, as MIS coding although, for external use, (e.g. web definitions) it was called analysis coding.
The system was originally a dBase application, which evolved during the years until it was incorporated into the Oracle database as part of the Y2K project. It was then a component of the MGS Oracle database shared by NAMS and ERBOD. All NAMS census files from 1990 and ERBOD ones from 1999 were coded.
NAMS process also included an exclusion process applied to the data to identify records, which either should not have appeared in any analysis extract or should have appeared in the analysis extract with an exclusion marker so that they could be included or excluded from analysis as required.
With the introduction of the Y2K project, the exclusion process, which had previously been applied by SEMA to the payroll files before the NAMS extract was produced, was then the entire responsibility of ISD.
The criteria for exclusion were held in the MGS Oracle database as reference tables that were shared by NAMS and ERBOD. This reference tables were maintained by ISD from input from trusts/boards.
They were updated prior to each census on the basis of information returned by trusts/boards, for NAMS only, via an exclusion reference file update form and at validation stage when trusts/boards completed their exclusion list confirmation return. These updates were also applied to ERBOD, when appropriate.
Examples of records that were excluded from analysis:
- Staff employed by organisations which use SSPS but were not part of the NHS, e.g. the Robert Gordon University in Aberdeen, several hospices
- All medical and dental staff, for NAMS only, which was a non-medical and -dental staff census
Examples of records that were to be marked for exclusion:
- Staff who worked for the NHS but were not formally employed by an NHS trust or Board, e.g. Board members, GP staff funded through a GP budget
- Staff whose salary was paid by an NHS Trust/Board but was wholly or partly recharged to a non-NHS authority, e.g. chaplains, University research staff, local authority staff
- Staff who work on an as and when required basis, e.g. bank staff were excluded from NAMS to ensure a more accurate indication of staff in post at a snapshot point in time. However, they were included in ERBOD so that their pay could be included for the total paybill for the NHS in Scotland (NHSiS, now NHSScotland).
- Staff who were excluded from payroll statistics could also be excluded from workforce statistics if their reason for exclusion applied to both.
Records could be partly excluded. For example, staff who were partly funded by a trust and partly funded by a non-NHS body (University, Charity, Local authority, Research Company etc.) or a GP. The trust element of their work was included, while the non-trust share of their work was excluded.
The database output was a data file with each record representing details from an employee's pay records as they stood at the census date i.e. the census covered all those in post at this date. This file was used for all analyses. This collection process was used to provide data at two census points a year, 30th September and 31st March. However, in the later years only data at 30th September was used for analysis.
An individual employee may have appeared more than once on a census if they had more than one pay number on payroll - this was usually the case if the individual worked at more than one organisation at the time of the census. Headcount figures from NAMS were, therefore, not true headcount statistics and were in fact counts of contracts.
The staff groups covered were as follow:
- Nursing and Midwifery
- Scientific & Professional
- Professions Allied to Medicine (PAMS, now Allied Health Professions - AHPs)
- Technical
- Ambulance staff
- Works
- Senior Managers
- Administrative & Clerical
- Ancillary
- Trades
- Health Care Assistants
Two files were created from NAMS : a full one (census) and an aggregated one (aggcensus), which only contained some of the fields, to allow speedier analyses to be carried out.
Census file from 1979 to 1989 only exist in fixed-width text format making their analysis difficult. The SPSS programs coding all records were not retained and so there is no record of how published statistics were derived.
From 1990 MIS coding was applied to all records in the aggregated file, of which SPSS files are still available.
Full census files with the ISD analysis coding do not exist for the period 1990-1997 (due to various reasons, such as lack of space to run the programmes and keep the new files), which made it very difficult to carry out analyses when they required fields that were not in the aggregate file (e.g. age).
From 1998/1999 the Analysis coding was introduced in the Oracle database and made the process much simpler. The nursing and midwifery staff group still needed further coding to derive these categories correctly until the introduction of sub-specialties in 1998. All data back to 1990 was recoded later on in a mapping exercise, which allowed for an easy coding only using the analysis coding.
Along the years there were many changes made to the data and analyses. A few examples follow:
- Nursing and midwifery
- From the early nineties nurses in training were removed from payroll and thus from NAMS analyses due to a revision of the training process (Project 2000) and nurses in training status.
- In 1980, WTE calculation was changed from a 40-hour week to a 37.5-hour week basis.
- Excluding staff on low hours
- Between 1992 and 1993 exclusion on low hours changed from 0.25 to 2 hours.
- Administrative and Clerical
- Prior to September 1992 management grades, including senior managers were included in the A&C 1-10 grades. From 30 September 1993, a new grading system for general and senior managers was introduced with no correspondence between the old and new pay spines.
- From April 2000 a new grading system (Executive Level) was introduced.
- Contracting out
- Over the last couple of decades many services requiring works, ancillary and trades staff have been contracted to outside agencies and employers. NHSScotland no longer directly employs the staff involved. This resulted in a significant decrease in the NAMS statistics on these staff groups and had significant effect on statistics for the workforce as a whole. Private Finance Initiative (PFI) and Public Private Partnership (PPP) contracts established end 1990s also impacted (reduced) workforce statistics.
The statistics derived were used in the following outputs:
- Publications
- Scottish Health Statistics (now defunct)
- Workforce Statistics (internet publication)
- SKIPPER (pc based package)
- PAMS Health Briefing (last published in 2001), using a PAMSLINK file from NAMS, grouping several years together (1985 onwards)
- Ad hoc service (many requests emanating from the Scottish Executive, now Scottish Government)
- Workforce Planning Mechanisms:
- e.g. Student Nurse Intake Planning (SNIP) data on trends and the dynamics of nurse supply used in national model used to recommend appropriate student nurse intakes. Different censuses from NAMS were linked together to produce a linked file to provide supply figures used in the SNIP process.
- They fed other workforce collections:
- Occupational Health & Safety Minimum Dataset ? ISD provided by staff group the staff hours and WTE for each submitting organisation.
ERBOD (Earnings Related Base of Data)
ERBOD was the mechanism through which data on the pay of staff that were directly employed by NHSScotland was collected between the late eighties and 2004.
This data covered all staff and included cumulative data on pay of NHSScotland employees.
This collection was done twice yearly, at 31st March and 30th September. There were approximately 160,000 records for the September census and 220,000 for the March one.
It took three to four months to collect, process and validate the data. The data were extracted from the Scottish Standard Payroll System (SSPS) by SEMA (now AtosOrigin) and once the data were transferred, it was loaded into the ISD ERBOD Oracle database. The database performed a number of quality checks and derived a number of additional data items. The NAMS/ERBOD classification system appended a range of codes to each NAMS and ERBOD record held on the Oracle database for each census period (c. 150 000 records for NAMS, c. 200 000 records for ERBOD) to facilitate data analysis. These codes allowed classifying the records into: staff group, specialty, sub-specialty, qualification and grade.
The system was originally a dBase application, which evolved during the years until it was incorporated into the Oracle database as part of the Y2K project. It was then a component of the MGS Oracle database shared by NAMS and ERBOD. All NAMS census files from 1990 and ERBOD ones from 1999 were coded.
A file for data analysis was output from the system at a users request. The analyses files were transferred to SPSS format (.sav) and used by the Analyst Team. It was also used to cross-validate MEDMAN and NAMS.
Prior to the financial year 1998/99, ERBOD collected data at an aggregate level on a quarterly basis. Data consisted of aggregated record for each unique combination of pay scale, occupation code, gender and full time/part time contract. Attached to this were fields such as gross pay, whole time equivalents, basic pay, independent elements of overtime and enhanced pay, individual allowance payments, employers charges. Nothing remains of this database but a few historical data files are retained.
From the financial year 1998/99 the database began collecting data on the pay of individual employees. Data were collected biannually for pay over the first half of a financial year or a whole financial year. All but two trusts in Scotland, the Scottish Ambulance Service and Dumfries and Galloway Primary care Trust (PCT), used SSPS to pay employees that were directly employed.
The analysis extracts from Oracle were called halfyyyy.dat and fullyyyy.dat files (e.g.half0203.dat for the April 02 to end September 02 period and full0203.dat for the April 02 to end March 03 period). Analyses were then done via SPSS, creating halfyyyy.sav and fullyyyy.sav files.
ERBOD provided the only national source of data on cost of the NHSScotland pay bill and historically was used to provide this information to the Scottish Executive (SE) in support of Scottish evidence to the national (UK ) Pay Review Bodies.
In later years the Scottish Government expressed a desire to obtain more detailed analysis of pay and to use this to model and gauge the effects to the pay bill of changes to government policy on NHS pay. Unfortunately the Workforce Team was unable to meet these requirements due a limited knowledge of the data held and suitability for this purpose.
In particular, knowledge of the data had proved difficult to obtain because of the complexities of SSPS and the lack of appropriate documentation or data definitions available from SSPS. With regard to suitability it is worth pointing out that the primary function of SSPS is to pay employees correctly and of secondary importance is the collection of accurate and appropriate information on the workforce and their pay. As a consequence some required data items were not held and there were no data standards for some fields. This often made the data extracted for ERBOD purposes difficult to interpret or utilise. The difficulties in interpretation resulted in only the most basic of pay bill analyses being supplied on a frequent basis. However, two additional uses of ERBOD were identified and utilised.
Firstly, ERBOD proved to be the only central source of information on bank nursing and midwifery staff employed by NHSScotland. These are pools or banks of staff who are employed by trusts to work as and when required. Bank staff are often used to provide cover for vacant posts or absent staff. Although reliant on correct identification of bank staff on SSPS, which is not prevalent, ERBOD could provide information on the number of bank staff employed, the number of hours for which they were paid and the costs of their pay. This information was fed back to the service through the Workforce module of SKIPPER.
From 2007 information on bank nurses has been collected via SWISS.
Secondly, ERBOD was used to measure the level of Compliance with the New Deal with respect the hours worked by junior doctors. The New Deal was agreement reached in 1991 to improve the working conditions of junior doctors. The agreement included limiting the working week to 56 hours on average. As part of a new contract for doctors in training a new pay banding system was introduced in December 2000 to encourage and make transparent the level of compliance with the New Deal. This allowed, for the first time, the gathering of data from payroll and ERBOD on compliance. This collection allowed the compliance situation to be established at two census points in time each year 31st March and 30th September.
From the Aug.03-Jan.04 rota period data has been collected via a paper exercise twice a year, with the exception of the period covering Aug.06 to Jul.07 (two rota periods), when no collections were done.
The variable length of records in the ERBOD analysis files prevented equivalent records being held in SPSS format. Instead two SPSS data files were derived from each data file. The first contained records without the variable-length allowance fields (approximately 200,000 records). The second file held the allowance information with a record per employee and allowance (approximately 800,000 records).
SKIPPER was a PC-based package that was updated on a roughly quarterly basis and that was distributed to the Scottish Executive, Trusts and Health Boards and the Scottish Parliament Information Centre (SPICe). Its main purpose was to allow Trusts and Health Boards to benchmark themselves against other Trusts and Scotland as a whole.
The workforce module allowed comparison at a National, Health Board and Trust level of staff in post at an annual census point. Data was available at a summary level for all staff groups with a more detailed breakdown by grade and speciality for Medical and Dental, Nursing and Midwifery and AHPs staff. Scotland and Health Board level data was available from the early nineties with Trust level data being available from 1999 onwards.
Comparative analyses on Vacancy Rates and Agency Usage was available for Nursing and Midwifery staff. Vacancy rates were also included for Consultants and Staff grades within the Medical and Dental Staff group and for all categories of AHPs Staff.
There also was a series of screens on Junior Doctors and Dentists Compliance with the New Deal.
SWAPS (Scottish Workforce and Pay Statistics)
SWAPS replaced ERBOD and NAMS as the main non-medical & Junior Doctor data source in 2004 (data at 31st March), combining the two databases into one. It also amended ERBOD to include extra NAMS fields to the ERBOD data. So all in all SWAPS held information on all NHS staff, including Allied Health Professions, Junior Doctors and nurses and midwives. Data were still mainly sourced from the Scottish Standard Payroll System (SSPS) and extracted by AtosOrigin.
As for NAMS and ERBOD there were non-standard providers (National Education for Scotland (or NES) and the Golden Jubilee National Hospital), which used alternative payroll systems. The Workforce Collection Team always attempted to get almost equivalent extracts, for the non-standard records, from their payroll systems for SWAPS. These data were collected via disks. There were approximately 110 records for the Golden Jubilee, this number reducing each census as employees move on to standard payroll. For NES there were approximately 300 records (all non-medical and dental staff).
The rationale behind this was to increase efficiency as time was spent data processing both these systems and also maintaining two Oracle databases. Both these datasets had personal and appointment fields most of which were the same with a few fields that were on NAMS and not ERBOD. On ERBOD pay details were also collected.
A more efficient way of working was to merge the two and have one data source, SWAPS, from which NAMS could be extracted using Business Objects(B/O) (a new B/O universe called SWAPS had been created to that effect), whereas ERBOD was still extracted from the Oracle database. With the introduction of SWAPS data were still collected twice yearly. Unlike ERBOD SWAPS had the full exclusion process applied to it (as did NAMS ).
As NAMS was an extract at a specific time of the year (a snapshot at 31st March and 30th September), week 26 or 52 of the financial year was selected accordingly in B/O to reflect this snapshot date. The databases performed a number of quality checks and derived a number of additional data items before analyses could be finalised for publications and answer Information Requests and Parliamentary Questions.
The information held was as follows:
- Personal Details
- name, date of birth, sex, ethnic origin, NI number, pay number.
- Appointment Details
- grade, type of contract, date of leaving, pay scale, financial detail, job description, contracted hours, occupation code.
- Earnings
- basic pay, gross charge, enhancements, protection, allowances, overtime, tax period.
SWAPS was eventually replaced by SWISS in 2007 (data at 30th September).
MIDAS (Management Information and Dental Accounting System)
GPCD (General Practitioner Contractor Database)
GPCD is a A centralised database of GP and general practice details, held at ISD but with data maintained by NHS Boards. Its principal purpose is to hold Performer List information for all NHS Board areas in Scotland. Data is held on General Practices in Scotland, GPs (incl. performers; GP principals) and other professionals in general medical practices, their practice population sizes, and contract details - all in relation to new General Medical Services (nGMS) contract.
Additional workforce information is routinely collected by the Workforce team as it is not readily available via a central system. These collections provide the additional information required to support the national annual planning cycle. These include:
- Vacancy Information
- Agency Nurses and Midwives
- Clinical Nurse Specialists
- Doctors in Training Compliance with the European Working Time Directive
- Consultant Programmed Activities (PAs)
- Student nurse intake and attrition rates
Annual aggregated return of Whole Time Equivalent vacancies for Nursing, Midwifery and Allied Health Professionals at 31st March. Data are provided from NHS Organisations. From 2007 onwards, this information is collected at 30th September.
Due to the migration of Hospital and community Medical and Dental workforce from the MEDMAN web based system into SWISS in July 2008 consultant vacancy information was no longer available centrally. As a result consultant vacancy information is now collected directly from each NHS Board.
It is envisaged that as SWISS develops all vacancy information will be collected via the WIR.
Information on hours worked and cost of agency nurses and midwives are collected on an annual basis as at 31st March.
It is also worth noting that for the year ending 31st March 2001 return, NHS Boards were requested to collect information for the first time on the cost of employing agency nurses. For this reason information on costs are only available from 2001 onwards.
Revisions were made to this collection during 2008 to allow for direct comparisons with the quarterly national procurement reports. This provides the opportunity to monitor more robustly the usage of agency staff.
Information on Clinical Nurse Specialists are collected annually at 30th September. This provided additional information surrounding this particular staff group that was required for future planning purposes.
Information on numbers (Headcount and WTE) for each NHS Board by specialty, AfC band, age group, contract type and gender is available.
However due to the continuing development of SWISS, CNS information is now centrally available via this system.
Doctors in Training Compliance with the European Working Time Directive
This data collection was introduced in 2001 to monitor the contracted hours of Doctors in training. Data is collected on each 6 month rota period, February to July and August to January, where the percentage of compliance with the allowed contracted hours is reported.
For Junior Doctors to comply with the new deal working hours should not have exceeded 58 hours from August 2004 to January 2007, and 56 hours from August 2007.
Consultant Programmed Activities (PAs)
A consultant is contracted to work 10 four hour sessions (programmed activity) per week make sure we say why we collect this and comment on SWISS has the ability but data validation still needs to be done.
Student nurse intake and attrition rates
Information on nurse intake, transfers in and out of nurse training courses in Scottish Higher Education Institutions are collected by NHS Education for Scotland (NES) annually. Whilst ISD do not collect these data, the workforce team publish this information on behalf of NES to help inform the Scottish Governments Student Nurse Intake Programme modelling exercise.
