This is a document I wrote to summarise ten weeks' summer work at the University of the West of England (UWE), Bristol. The position was not supposed to involve IT - I was originally going to work on the telephones answering calls from prospective students. As luck would have it, there was a gap in the Information and Liaison Service - a small team who supply timely information to whoever wants it, using MS Access and IRIS. This document was written for my supervisor, so there may well be unfamiliar terms.
I have been working at UWE for ten weeks, starting 6 July and terminating
11 September 1998. During this period I have been predominantly working within
ILS, although I have also performed jobs not exclusive to ILS as a member
of the temporary Summer Staff. My main function within ILS has been described
as, ‘making the job easier for the team,’ although in practice I have been
working predominantly alone on a succession of small projects for a significant
part of this period. The primary interest I have had while here has been developing
the various ILS databases. Aside from this I have run reports for faculty
staff, looked at process improvements within ILS, and also responded to day-to-day
requests for help from ILS and EAS staff.
3.0 Database Improvements
I have worked on two databases over the last ten weeks - the Education Liaison database and the Clearing Award Database. The improvements are in three main areas: table design (the content and structure of the actual data), query design and report design.
I have made fairly significant changes to both databases, and have created several new tables to complement and simplify the existing ones.
3.2 The Education Liaison Database
The Education Liaison database now has an up to date record of all the out- and in-visits Tony et al have been involved with. In addition to the data entry, a few modifications to the design have been made in order to make the event details more useful. As well as changing the design, I have also added a significant amount of data to it, adding over one hundred complete records to the database of attended events, and all 59 records of unattended events.
3.2.1 Changes to the Education Liaison Database
The database now contains more useful information about the visits, and a whole new table with details of those events that we chose not to go to. Each attended event now has some or all of the following information. Further to the general rationalisation of the structure, bold items are new additions:
For the cases where we did not go to events but do have paper records of the invitation, I created a new table containing records of these events. These records contain all the information available to us about the event without us actually attending, including in many cases why we didn’t attend.
These changes to the structure were driven by the need to improve the layout of the form used to input the details of these events. The form for both the attended and unattended events has been substantially modified for easier and clearer data-entry, and all the information has now been combined into a single database, whereas previously I had created a separate database for the unattended events.
3.3 The Clearing Award Database
This database holds details of each full time undergraduate and postgraduate awards we offer. The details it holds can loosely be defined as those details which EAS needs to know for its own purposes. These include at present award level, title and duration, the owning faculty and several other details listed in 188.8.131.52. In 5.0 you can find recommendations for the future use of this resource.
This database has been very substantially modified over the past two months. When I arrived it was fairly haphazardly modified, and still had traces of the messy IRIS and ISIS tables which had fed it once upon a time. I have cleaned a lot of the tables up, and more broadly removed poorly implemented ideas and improved or replaced them. The improvements are most evident in the reports than can now be run from the database - the reports themselves have been redesigned or replaced, and many new reports have been created for a variety of purposes.
3.3.1 Changes to the Table Design
The database at present holds a lot of information. For each undergraduate award it can tell you, for 1998:
For postgraduate awards the following 1998 information is held on the database:
We also now have information on the Access and IRIS reports we have run for faculty and department staff. This is contained within a table I have designed and constructed, and which I have filled with information via a form I constructed based on the information in the table. For each report, we know everything that we need to reproduce it. Essentially, I have designed an electronic means of storing the data written on those Report Request Forms I designed (see later for documentation of these) so that they can be, time and resource permitting, analysed for emergent patterns. To date we have records of 57 report requests. ILS has probably run at least this number of reports without report requests, when a report was easily run without the form, or when the report was a duplicate of previously requested report.
3.3.2 Changes to the Report Design
All reports that have been run from the Clearing Database have been either improved by myself or superseded by new reports I have designed. Below are a list of improved reports, and a description of how they have been improved:
184.108.40.206 Clearing Vacancy Listings
This report (7.1) is the one from which we have for two years gathered information from the faculties concerning awards in and out of Clearing, their details and availability etc.
It was originally two separate reports - one with awards in clearing and another with awards out of clearing. In order to produce vacancy listings for any one faculty one had to change the design of the report, enter the faculty, time of day and only then could the user print the first half of the report. This procedure had to be duplicated to print the other half. Having been worked on, the report now automatically produces the faculty name and time of day, and combines awards in and out of clearing and discontinued awards on the same page. This represents a considerable improvement in speed and ease of producing reports.
Further to the speed improvements, I made possible several enhancements with regard to the actual information contained on each sheet. In order to provide more information to the telephone staff, each faculty’s vacancy listing was given the full faculty details as defined in 3.3.2. In addition to this, some faculties’ listings had recommendations for other courses/faculties, so that telephone staff could make recommendations to applicants who could not get the place they wanted.
220.127.116.11 New Vacancy Listings
One of the drawbacks of the Clearing Vacancy Listings is that each faculty requires at least one page, so the complete set of vacancies comes to about 18 pages. This length is a legacy issue which stems from the original design of the document, and from its original purpose as a tool to collect information from faculties. In addition, very few Summer Staff are familiar with which faculties contain which awards, as it is not always intuitive. Following feedback from the telephone staff, I designed an entirely new report solely for telephone and reception staff based on what they asked for, as opposed to what was thought they needed. This attention to the customer paid off, as the report, the design of which was almost entirely specified by the telephone staff, improved the speed with which the staff could address the needs of the applicant, improved the quality of the service they were able to offer, and as far as the telephone staff were concerned, entirely superseded the old vacancy listings.
Each award is included in the report in alphabetical order, and the information available to the staff is arranged in an ergonomic fashion (7.2) with more useful information than had been available before. Each individual award has information on itself, two alternative awards and faculty details (as in 3.3.2) for all three awards. Each page also included the internal and external Accommodation Hotline, the EAS fax number and a summary of the start dates of all undergraduate full-time awards. This allowed telephone staff to very quickly find an award, give advice on its availability and find alternatives for the applicant to consider. If further information was required, the telephone staff could immediately see which prospectus page to look at, or refer either themselves or the caller to the relevant faculty helpline. The report is also much more manageable, at only seven double sided pages.
18.104.22.168 Newspaper Vacancy Listings
The methods with which we have provided information to the Guardian, ECCTIS and The Times have been improved, although to no great ultimate effect on the accuracy of these listings. New reports were designed which were based on a universal newspaper report I designed which required a minimum of modification in order to produce the data in a format specific to the particular requirements of each organisation.
22.214.171.124 World Wide Web (WWW) Vacancy Listings
This year the WWW vacancy listings were supplied to the Media Centre by myself, and to this end I designed a report to produce information in a format they could easily import into their existing WWW template. This information was converted into a Word document as well as an Excel worksheet, in order to illustrate the design of the pages that EAS favoured. This was successfully carried through to the final, internationally available, output by the Media Centre.
3.3.3 Query Design
In order to produce the reports above there were a great many revisions and modifications to existing reports and several new ones were created. As queries are, in essence, the driving force behind reports, each report requires a specific tailored query. In addition, I created queries to answer specific questions and to provide more general information to anyone using the database. One example is the Info form - a form which generates a record, based on a given UCAS code. The user need just specify a UCAS code, and if it exists, details of the award appear on screen. If it does not exist, then no details appear but the user can add them if it is a new awar.
4.0 Other Improvements
My achievements in the field outside the EAS databases are fewer, mainly because most of the direction I have been given has been toward the databases. Nonetheless I have managed to make some inroads into improving some of the processes and working practices, especially within ILS.
4.2 Intuitive File Management
Prior to the reforms, on the shared network drive, I:, there was a single directory containing all the files for EAS, I:\ADMISSIO. I have created several new directories beneath this directory and ordered the files amongst them appropriately. The directory tree is now much more sensible for the whole of EAS.
This directory tree would be more useful if I had had more free a reign to ask the EAS services what they needed and to move files about based on their recommendations. In addition to this, once the method of creating new directories was known, it was embraced by a colleague so wholeheartedly that within ten minutes almost every file had its own directory, and some files currently being used (including the Clearing database described in 3.3!) had been deleted as junk.
At least within ILS this new directory structure has been beneficial in finding files others have been working on. One problem that remains is the use of arcane filenames - I have not been able to improve the intuitive understandability of these.
4.3 Summer Staff Budgetary Tracking
I have created a brand new budgetary tracking system for use with the Summer Staff each year. It is easily maintainable and fully documented. It can cope with overtime, Sunday and Bank Holiday hours and automatically calculates ‘on-cost’ based on a 7% National Insurance contribution. In order to update the details for next year’s Summer Staff, you need simply to change the names, the date of the Sunday at the start of each week, and each member of staff’s hourly rate. Assuming that the Bank holiday falls on the same week, then it is all set. It also produces easy to read weekly budget sheets, should these be required (7.3). The system works within Excel, and contains a full page of instructions to help whoever maintains it. A current version of the system can be found at I:\ADMISSIO\ILS\TEMPS98.XLS. This system should be good for a number of cycles.
4.4 Grant Form Streamlining
In response to a request from Christi and Eira while processing LEA award forms, I added the term dates and maximum length (i.e. 4 year sandwich) of each undergraduate full time award to the Clearing database, so that I could produce a single document that would speed up and improve the accuracy of the grant form processing. The original process was convoluted and encouraged errors:
As you can see, there are 8 steps and 4 sources of information - IRIS, the Prospectus, the UCAS code list and the Attendance calendar. By adding the term dates and award duration to the Clearing database I was able to produce a single document (6.0.??????) which served instead of three of the four sources of information, and which was considerably less irritating than having to flick through the list of UCAS codes, leaf through the Prospectus, examine the tiny writing on the Attendance Calendar and only then be able to fill out the form.
This represents a dramatic improvement in the speed and ease of completing the forms, and consequently reduces the enquiries and requests for clarification made by the LEAs on UWE.
4.5 Report Request Forms
In previous years there have been attempts to make Faculty Co-ordinators’ requests for statistics, status selection lists and other reports more easily dealt with. Last year a form, based on melange of the various native IRIS facility menus, was produced. In a more concerted attempt to see the idea through, this year I produced a form based on the information available, as opposed to the way it was requested (7.4). This took the form of a double-sided, mixed format form (i.e. it asked for both freestyle writing and checkbox filling) which could theoretically address the needs of any requester. This has been successfully used throughout the Confirmation and Clearing period this year, and may well be useful next year too. As part of the Clearing database (although not tied to any data at all) it will be available when this database is next used.
5.1 The Use of the Databases
The databases I have been working on have been improved considerably while I have been working here, but there is plenty of scope for improvement, both in the design and usage of these resources. My primary recommendation is that you do not let the development of these projects stagnate. There is a tendency I have observed within ILS to let things proceed as they are, and to fix problems retroactively, and this is damaging to the team and by extrapolation to EAS and UWE. I hope that with the introduction to the team of two fresh members of staff this stagnation can be eradicated.
5.1.2 The Education Liaison Database
In the Education Liaison database ILS has a valuable resource in terms of tracking visits and preparing for future ones. Having not seen an Education Liaison Officer in action I am unable to make any incisive recommendations for the use of this database, other than it be maintained up to date, and that any incoming Education Liaison Officer be quickly familiarised with it. By properly integrating it with Excel one could automatically generate an event calendar, which could then be manually cross-referenced with the database records in order to prioritise any coincidental events. If the fields pertaining to recommendations (see 3.2.1) were either re-coded numerically, or at least maintained using a numeric scale for recommendations, then we could use Access to export the information to Excel already prioritised.
5.1.3 The Clearing Database
This database has the potential to produce automatically a lot of the information that we currently produce by hand, or by designing queries in each specific instance. The strongest recommendation I can make in this case it that this resource is not allowed to languish undeveloped. It has great potential as a tool for EAS, and should be as completely maintained and updated as possible. Each field should be thoroughly filled with up-to-date data, including at least the following:
This would allow us to produce a great deal of information automatically, such as:
We could also more effectively target specific awards if we could track their intakes against the target data, using a combination of this database and the Access-IRIS link. If we included for each award which A-Levels were favourable, we could even include a guide to recommending other courses based on the applicant’s qualifications!
5.2 Other Recommendations
Aside from the databases, I have a number of other recommendations, both relating to process improvements and also general working practice. To begin I will arrange my recommendations in line with Section 4.0 of this report.
5.2.1 Network File Management and Naming
At present the network drives are underused. My work in providing reasonably intuitive places to store files is a small step along the way to properly utilising this resource; each member of EAS needs to know that there are two network drives that they can use - a public one (I:) and a private one (F:). In addition to that there are places within the public drive where documents can be placed so that they are readable by others. In the face of an observed tendency towards sole document ownership I think there should be a move to educate the EAS staff to place documents that are being worked upon in a shared space, i.e. on the public network drive. This allows members of staff to easily locate and work on documents or information when originators aren’t available, for example if they are ill.
Further to this there is the need to name the files themselves sensibly. Currently, despite the improved directory structure, all statistics analyses and related files, where they are stored on the public drive at all, are named c8xxxxxx.doc or c8xxxxxx.xls where c8 stands for Clearing ’98. This is obviously redundant given its location within the directory relating to Confirmation and Clearing in 1998. While we have only eight significant characters with which to name a file, it is important not to squander characters on redundant categorisation.
My recommendation is that you assign someone who is IT literate and familiar with good directory ‘housekeeping’ to assess the needs of ILS, AS and EAS and to create a useful and intuitive directory structure and then to educate the whole Service regarding the use of the network and intuitive file-naming. Rather than emailing files around CSA, we could then more cheaply and reliably email a pointer to the file. This would ensure that it is always the latest version that is viewed. This also removes the need for frequent emails with huge spreadsheet attachments, which are costly to the university, slow to access and inaccessible when the mailserver is down.
5.2.2 Summer Staff Budgetary Tracking
This system is simple and imperfect, but with a bit of tweaking, it works. It currently does not cope with pay increases during the period of employment, and this could be remedied fairly easily. It also does not cope with employees who work Saturday but less than 37 hours Monday to Friday, because it calculates overtime based on a 37 hour Monday to Saturday week. A less elegant, but more thorough solution would be to enter the working hours for each day of each employee’s period of employment, as found on the Temporary Weekly Timesheet. This would require a new spreadsheet, but one that would be very easy to design. Robert could then enter the details instead of filing duplicates of the paper forms that Payroll hold.
5.2.3 Grant Form Streamlining
This method of improving the speed and accuracy of the LEA form processing works, but to save the time it takes to find and enter in term dates for each award it should be possible for SARI to provide ILS with an electronic list of term dates by UCAS code. These could be imported into the Clearing database instead of manually entered, saving considerable time and further reducing the risk of error.
5.2.4 Provision of Reports and Statistics
While I think that the Report Request Form I designed has been a success, there is obvious room for improvement. The requirement for disabled student- and international student-focused reports has been minimal, compared to the more mundane reports we run. It has been only really questions 1-4 and 6-7 that have been used. I would recommend that if we use this system again next year, those who redesign the form look at the file of the completed forms and redesign it in accordance with what has been asked for this year. I would suggest you consider the position of this form in the wider context of a new approach to providing faculty staff with an easy means to run their own reports.
Each Faculty Clearing Co-ordinator has reports that she or he likes to use, and a small amount of investigation and consultation would be useful to find out which reports are useful to these Co-ordinators. Given the radically different system of applicant admissions that may be in place next year, it seems timely to review the requirements that ILS, EAS and UWE have for reports. My recommendation would be to cease using IRIS for Status Lists and Statistics; there is nothing that they provide that the Access-IRIS link cannot do more quickly, in a more attractive manner and more easily.
A major source of unnecessary trouble that I have seen has been the performance of the Local Reports that IRIS runs. The trouble arises in significant part due to the fact that ITS refuse to allow native IRIS programming to fall outside ITS. If we cease using these reports, then we can circumvent this restriction, and we can debug our own problems, and use ITS expertise more effectively. As far as I can tell, from my relatively removed position, nobody, in ITS or otherwise, knows exactly what IRIS does, how it works and why it does not. If ILS, and Hamish and Phil in particular, were to design and implement Access reports to replace the ones in IRIS, it would be enormously beneficial.
One consequence of this would be the need to provide a PC for running Access reports to whoever needs them during Clearing ’99. Phil could easily design a number of faculty reports and queries that could be used by faculty staff to create their own queries which would then fit into standard template reports. It would be useful to insist that faculty staff take Access training before using ILS’ queries and reports, so that no cries of ‘Unfamiliarity!’ or ‘Innovation!’ could legitimately stand in the way. With Hamish’s added expertise in the field of database design, we might even be able to make to process idiot-proof!
Over ten weeks I have worked on a number of small projects. I have spent most time working on the Education Liaison database, the Clearing database and the Report Request Form. In addition to this I have worked in other ways: helping out as a member of the Summer Staff, providing IRIS reports as requested by EAS, ILS and the faculty staff, working on the Budgetary Tracking System, streamlining the Grant form process and generally supporting the permanent staff, both in ILS and EAS.
I have also looked at improving processes in these areas, and in some cases have improved them, and in other cases have provided my recommendations accordingly (see 5.0)
6.1 Education Liaison Database
I updated the information in the database of attended events and created a new database of unattended events. I modified the design of the database in order to include more salient information and redesigned the information submission form appropriately.
I recommend that the Education Liaison Officer maintain and develop this database, and look at using it to automatically provide a plan for a year’s liaison visits.
6.2 Clearing Database
I heavily modified the design of this database in order to improve the usefulness of its content. I also maintained the content of the database, keeping it synchronised to the Faculties’ own data. I designed and ran new reports for a number of purposes, including much improved data for phone staff. I also maintained and significantly improved upon the existing list used to get information from each faculty about the awards it was putting into Clearing. Using this database I created reports in order to provide newspapers and our own WWW developers with information about which of our awards were available through this year’s Clearing
I recommend again the further development of this database particularly the use of it to provide EAS with information and rules for the Confirmation process next year. ILS should also look hard at getting it to provide us with information automatically.
6.3 Report Requests
I designed a paper form for the submission of report requests by EAS and faculty staff, and created and maintained a database to log these requests.
ILS should cease to use IRIS as a means of providing statistics and status lists. In future ILS should use the Access-IRIS link, and should provide ready-made reports and queries for trained faculty staff to use during clearing.