Task and Resource Scheduling Database Built in Access
The University of Manchester’s Estates and Facilities Department is responsible for managing all drawings and data relating to sites and buildings owned by the university. This includes updating drawings and data for particular buildings, providing information or drawings for third parties, as well as participating in a number of other projects. At the time that they approached us tasks were managed and people’s time was tracked using several spreadsheets. The Department wanted to reduce the amount of effort used in keeping these records up to date, automate the processes involved and keep a record of the work being done going forwards in a more suitable tool. Hence the brief was to create a tool to allow them to maintain their Task List, schedule resourcing against it and match timesheet entries to both the Task List and Resource Schedule.
Xlteq Access Consultants built a database designed to hold all task and resourcing information in order to replace the spreadsheets and keep a running record of all tasks. It was also designed to show resource allocations and to allow timesheet entry for each individual in the team.
Task Creation and Categories
The tasks in the spreadsheet were held on different tabs to show whether they were main tasks or sub-tasks, although not all main tasks had subtasks. There were also a number of columns on each sheet showing how each task was categorised and the corresponding file reference where the data or drawings would be stored.
Once we began the process of transferring this information to a series of database tables, it became clear that a few changes to the way tasks were categorised would be needed and so a new category hierarchy was created by the client and added to the database. There was also the problem that each tab in the existing spreadsheet had many columns, which would not all display in a single Access form. The solution was to list the basic information associated with each task (Figure 1) and then provide a link to more detailed information on another form; the user only needed to double-click on a task to get to information such as the details of the work carried out and any notes from the person responsible. In this way, links could also be provided to any sub-tasks associated with the main task. In this way, all the relevant information was displayed without the user having to scroll backwards and forwards across the screen (Figures 2 and 3).