When a player first arrives, he must fill in a Player registration form, which is then filed into the team he wishes to play for.
The team is already part of a league, with all player registration forms, result forms and fixture forms filed into that appropriate team file.
At the start of the season, every team is given a fixture list. This displays the times and days of the league games they must play. When arriving for a match, the captain must fill out his team selection stating which registered players he wishes to play for the match, the result of the match is then handed into the Officials by the referee after each match, with the league table being worked out by hand the day after.
The current manual system used currently involves a lot of paperwork, and a lot of unnecessary staff time and costs. The proposed computerised system would make the result handling, fixture organising, registering of players and league calculations a lot simpler and more efficient, as well as reducing costs and providing a more up-to-date system.
The possible solution to updating the system is to design it on two forms of program, Microsoft Access for the player and team database and Microsoft Excel for the calculations of leagues and fixtures.
The database would hold important information regarding the registered players and teams such Captain details, Contact details and the players currently playing for that team.
The excel spreadsheet will hold all relevant calculations and fixtures for the current league. The calculations will be achieved by using the “in-cell” formulas, the leagues will also be present with automatic updating of them when a game has been played and submitted.
Identification of Users
The intended users will be the staff at Pro5 Bristol at Brislington. There would be at most, 2 staff using the program at a time.
One member of staff would be using the Access program, submitting player registration forms and team details, whereas the other member of staff would be working on the Excel program, working closely with the Referee’s entering scores and details of the matches.
Interview
I conducted an interview with Terry Warne who is the General Manager at Pro5 Bristol on Friday 15th November 2002. The summary of the interview is below.
The current system is completely paper-based which causes a lot of hassle with paper-work being filed and kept safe. “A lot of it goes missing, and when it goes missing a lot can go wrong” Mr Warne said. This is typical of the paper-based system, and as the whole operation of Pro5 is ran this way, it can mean a lot of profit loss if things are not done correctly.
The basic downfall of the current system was that it is all paper-based, and this is the main reason for Mr Warne wanting me to create a database system for him.
With 12 courts hosting 4 games a night, that’s 48 inputs into the system on one night alone, and with Pro5 running 5 nights a week that’s 240 games a week, with Pro5 being open 5 days a week that’s 1200 games a week. This means the system I develop will be used very regularly.
Team and Player data input is also required. Mr Warne would like a central place to hold every Players details such as Name, Address and Contact number, as well as the Teams details which include Team Captain, Contact Address and Name.
Another area of Pro5 is Player Bans. These are enforced when the rules are broken. These normally include a length of ban which could be anywhere up to a permanent ban.
Mr Warne has just had a cash input of £5000 from Bristol Rovers Football Club which he has spent on updating their computer systems. He has updated them to two IBM NetVista’s.
The y each have a Pentium 4 2.53 GHz, 256 MB RAM, 40GB Hard Disk and currently run on Windows XP Pro.
They have also been bundled with the latest Mircrosoft XP Office package which includes Word, Excel and Access.
Mr Warne also informed me that he has tried this method before. He took a month long course on Access to set his own database up for Pro5. However with time limitations and work commitments he never did complete the project.
User Needs
The part of the paper-based system that I have chosen to develop and make more useful is the player and team database, and the League Results, fixtures and tables.
The information required for these aspects to work would be data such as Player name, contact details, team, and age, also details of the bans, such as ban length, and reason.
The data required for the excel database would mainly be the results and the league fixture timings and placement.
The user would like the system to be able to print out up-to-date reports on the players, current bans and the teams, also have the ability to list them, with printing an option.
They also want to easily be able to add players details, and team details, which can be selected later on and be able to print out a list of details from the player and team tables.
Finally, the user would like the results and fixtures excel program, to automatically work out the league standings, inputting data such as the goal difference, goals scored, games played, won, lost and drawn all automatically.
The draw back of the last system was the results system, as each one had to be worked out by hand, wasting a lot of work hours and money. So this feature must be a definite.
Limitations
The only limitation I can identify with my program is that there is still one part of it paper-based. This is the forms used by the Referee’s on the pitch to record the goals, match incidents and goal scorers. This is how the information of the games details I passed onto the staff that input it via computer. Validation checks would help to ensure the data is inputted correct, but there is always a risk of human error.
Software Limitations
The customer has requested that the database be built around Microsoft’s Access package, and that the Fixture Program be based around Microsoft’s Excel package.
Hardware Limitations
Microsoft’s Access and Excel must be ran on a system which includes a Pentium 133MHz processor or greater, minimum of 32MB RAM, and with hard-drive space of 2GB or above.
The Pro5 computer uses a much greater spec, so no slowness when using the program shall be observed.
Users Level of I.T Skills
The staffs at Pro5 all have a reasonable level of computer skills. They should have no problem entering data and learning how to use the new system.
Justification For Possible Solution
The database package such as Microsoft’s Access and the spreadsheet package such as Microsoft’s Excel will be perfect for implementing the chosen system for Pro5 Bristol. They have already requested that the system be built using Access and Excel. This I feel is the best choice, although a Visual Basic based program could be used, it would take a lot longer and would involve the company buying new software which is not necessary if there is already a cheaper product that will do exactly the same job.
I already have a good knowledge of Access and Excel based programs, and it is easily available at my home and my place of work.
Using these packages will enable me to;
- Set up necessary tables and relationships
- Produce customised input screens and GUI’s
- Using Excel based in-cell calculations to create automatic updated league tables and fixtures
- Design reports as needed
- Link with Microsoft Word to create Mail Merge letters regarding bans
- Implement a customised menu system
Objectives Of New System
* It should be as simple as possible for the user to access, the whole system should be form driven with an executable Excel file for League Tables.
* It should be as simple and less time consuming as possible for the user to enter teams, players and bans.
* All data must be accessible centrally.
* The system must be able to automatically update league tables
* The user must be able to easily save inputted teams, players and bans.
Data-Flow Diagram (Existing System)
The existing Paper-based system allows data to flow between sources as shown below;
Data-Flow Diagram (Proposed System)
The system I propose to build shall allow data to flow in the specified direction shown below;
Entity Relationship Diagram
Having researched the proposed structure of the Access Database, I have come to the following Entity Relationship;
Design
Overall System Design:
The input, processes, database tables and output are shown below in my System Outline Chart.
Database Design:
My database shall contain 3 entities.
These are PLAYER, TEAM and BAN.
These are related as follows:
A Player can have many Bans
A Player can play for many teams
The Excel League file will then be updated after each match to give an automatically calculated league table.
Tables will be created for each of these entities.
Definition Of Data Requirements
The tables I will create will contain the following data:
tblPlayers
tblTeam
tblBan
Design Of Input Forms
Three data entry forms are needed:
1) frmPlayer
This form will be used to enter the details of the 12 players that can be signed up to play for one team. It must be simple to use with clear data entry points.
The Team Captain Entry point is also used as the unique primary key.
This form will be accessible from frmTeam. It will only bring up the players that play for that specific team.
2) frmTeam
This form is used to enter the details of the team. This includes their name, Team ID, Team captain, contact number and contact address.
Again, the form must be easy to use with each data entry point clear. It will provide a front for the fmPlayer form to be called up from.
3) frmBan
This form is used to enter the details of Bans that will occur during the football matches at Pro5. It will include the Team details, and the details of the ban such as Ban Reason, as well as The Length of the ban. Again, the ease of use is very important, as well as clear data entry points.
The design of the forms is shown in Appendix 2 from pre-database designs I created.
The designs can be found in Figure 2.1, Figure 2.2, and Figure 2.3 in Appendix 2.
Report Design
The format of the reports will be similar. The layout of rptTeams is shown below. It will be the same format which includes ease of use and ease of reading.
Mail Merge
The mail merge option will allow the staff at Pro5 to easily bring up a template design based in Microsoft Word. The query qryBan will be used to provide the data from the database. This will include Player name, reason for ban, length of ban and start and finish dates of the ban.
It will be based on a letter format which will include details of Pro5 as well as contact details.
When the mail merge is selected, instructions on how to proceed will be displayed.
Queries
Queries are used to display certain data from the database and filter out other data.
The only query that is used in my project is qryBan.
It displays all the current bans held on the database, including length of ban, reason, player, and team he belongs to.
Menu Design
Systems Flowchart
As already shown above, the proposed Systems Flowchart will appear as below.
Mail Merge Design
One of the features the Pro5 staff wanted me to include in the program was the ability to create a mail merge letter. This is a template designed in word which includes automatic data taken from the database at the touch of a button. The Pro5 staff can then either view or print a letter viewing the data. The procedure for the mail merge will be;
Select Mail merge from Main Menu
(On-Screen Instructions appear)
Follow instructions to
Open Database window
Select appropriate query (qryBan)
Select Office Links tool from Toolbar and select the Mail Merge option
(Word automatically opens)
If a new letter is required
Then
Write new letter, inserting merge fields
Save letter
Else
Open existing letter
Make any modifications required
Endif
Select Tools, Mail merge from menu to perform the Mail Merge
Close Word to return to Access
Select appropriate report from main menu
Print Report to have a hard copy to which the player in question is mailed with
Security
A password shall be implemented to the database so it is only accessible to staff which know the password and are cleared to run the database and results program. Different access levels are not needed as only 3 staff will trained to use the database.
Test Strategy
My overall Test Strategy will include 5 areas of testing. These are;
Logical Testing
This will be used to test every aspect of the reports, forms and queries included within the database. The test data shall use valid, invalid and extreme data. Test data will also include the input of results within Excel with the Fixture and Result program.
The test data shall be deleted afterwards when the test level set is met accurately.
Functional Menu
Every item on the Main Menu will be tested to make sure each one works, and the adequate function achieved.
System testing
After the initial test procedure is completed, the database and excel program shall be fully tested again to make sure no errors have been introduced to the programs.
Recovery Testing
The system that will be using the database and Excel program shall be restarted while the program is being used to ensure no data is lost or corrupted in the case of a power failure.
Acceptance Testing
The users at Pro5 shall be asked to test the program and ensure that all functions needed are included within the two programs.
This part of testing may include further refinements.
Testing
The evidence showing my testing can be found fully in Appendix 1. The specific Figure in Appendix 1 can be seen by the side of the test procedure below.
System Maintenance
System Overview
The current Pro5 system is 100% paper-based. It is inefficient, unpractical and very hard to keep up-to-date with. The system I have produced is mainly computer-based, with the results coming from the pitches on paper, to be entered straight into the results system. The two computers are both based at the Players Reception at Pro5, with one computer running the Access database and the other running the Excel spreadsheet. This way the system is on hand at ground level, so the implication of data is entered straight away, as it is received.
Tables and Relationships
Tables and relationships have been set up as shown in the Design Section of this write up.
One Player can have many Bans
One Player can play for many Teams
Forms
The overview of the menu system is shown below.
The forms were used as follows:
Main Menu: Implemented Start-up form which loads as soon as the database is loaded up.
All buttons open up forms which allow further access into the database. They make it easier to use and display the data in an easy manner.
Other buttons include “Results and Fixtures” which opens up Pro5results.xls, the Results spreadsheet.
The set of buttons on the right hand side open up Reports and allow different functions to be used with the reports such as print.
The final button in the bottom left hand corner allows the database to be shut down at the touch of a button.
FrmTeams
Custom designed form which displays the current details of the teams registered with Pro5.
Buttons include the Utility Bar on the right hand side that include the Add, Delete, Save and Undo functions, Navigation bar that allows the user to easily navigate all the records, an Exit Button which shuts down the form and returns the user to the Main Menu.
The Players button is a relationship dependent button which allows the user to view the Players currently signed up to the Team the user is currently viewing. For example if the user pressed the Players button in the above screenshot, the user will be displayed with the Registered Players for the team “Kingswood Town”.
FrmPlayers
Custom designed form which displays the current Player data for the specified team.
Buttons include the Save and Undo functions, as well as an Exit button which returns the user to either the Main Menu or The Team form.
FrmBans
Another custom designed form, which displays Data clearly.
Buttons include the utilities Add Ban, Delete and Save, as well as a Navigation bar and an Exit Button.
Queries
The only query included in my database is QryBanQuery.
This query displays the current ban details of everyone who holds a valid ban on the system. This is also used to provide the input necessary for the Mail Merge to become possible.
Reports
All reports used in the database are from the In-built Wizard. They use the “Casual” template which gives them a professional look, as well as making them easy to read the data.
The reports included in my database are:
RptPlayers: Displays the current Player details held on the Database.
RptTeams: Displays the current Team details held on the Database.
RptBans: Displays the current Ban details held on the Database.
Mail Merge:
The current template I have created for the Mail Merge is stored in the folder C:/My Documents/Pro5.
The default Program is Microsoft Word 98, which means that the Mail Merge letter can be used in all Word updates above 98, which includes XP.
The user can choose to save the Word file in a different directory, I chose the named directory as it is easy to find.
Formulas
The purpose of the Excel database is to produce an Automatic program that will update important league tables at the input of a small amount of data.
To produce an automatically updated set of tables, I have had to use several types of formula.
To make the spreadsheet easier to understand, whenever a result is inputted, two columns display the Winner, Loser of Drawn team names.
Eg:
The formula used to produce this result is:
=IF(E7<>"",IF(E7>F7,D7,IF(F7>E7,G7,"Draw")),"")
The formula is then dragged downwards to address the cells below.
The league tables also use formulas to produce their results.
The formula’s above display data taken from other cells, in the above case cells from BM8 to BT11.
The formulas in the cells BM8 to BT11 contain several VLOOKUP values.
These provide the calculation of formulas that take data from other sources within the spreadsheet.
An example of the VLOOKUP formula in cell is BM8 is:
=VLOOKUP($BL8,$W8:$AE11,2,FALSE)
This is then relayed back into the above table, which automatically updates when a score is inputted.
Discussion Of Test Results:
The test procedure went very well and has informed myself of a few minor projects with the database. The Excel spreadsheet file which controls the Results and Fixtures passed every test thoroughly and is ready to be used by Pro5.
The tests produced the identification of a number of errors which include;
- The Previous Button in the form FrmBans displayed the last record in the table when it was pressed. This was an error as it should display the previous record in the table. This has been address and the previous button now works as it should.
- When attempting to leave the form FrmBan without entering the necessary Primary Key which is Player Name, the form allowed to be closed. This should not be the case when using the database as it would then be taking up extra diskspace which would include unnecessary data. This again was an error on my part, due to incomplete records. This has again been addressed and now functions as it should do.
- If Microsoft Word is open when the user opens the Mail Merge template from Access, the computer system produces an error “Out Of Memory”. The solution to this problem is merely to educate the users into not opening Word before the mail merge is needed.
The complete package I have produced weighs in at approximately 1.70 MB. This is however too large to be transported onto Pro5’s systems using a Floppy Disk (1.44MB).
I have zipped it up using the Maximum Compression utility within WinRAR and it is now 0.7MB which can be easily copied onto Pro5’s system using a Floppy Disk.
- The resolution of the Monitor at Pro5 is set to 1024 by 768 pixels. I have designed the database and Excel spreadsheet at the resolution of 1152 by 864 pixels. The result of this was that the text and colours of the forms appeared different and too large for the screen resolution. This has been adjusted and is now no longer a problem.
User Documentation
I have produced a User Manuel to use with the database and spreadsheet I have created.
This can be found within Appendix 3 at the end of this write up.
Appraisal
The package I have completed includes the Player/Ban/Team Database, the Excel Results and Fixtures spreadsheet and the Mail Merge. It was been successfully installed on Pro5’s system using WinRAR (File Zipping Package) I produced.
It has been completed in the way I have outlined and originally designed. The user has agreed and is pleased with the final result.
The original objectives I outlined in my analysis at the beginning of my project have been achieved as below;
- It should be as simple as possible for the user to access, the whole system should be form driven with an executable Excel file for League Tables.
I have created an easily usable Access database, as well as Excel Spreadsheet which can automatically update league tables when a result is inputted. The Access database is easily accessible and easy to navigate. The utilities and tools I have provided can be easily utilised.
- It should be as simple and less time consuming as possible for the user to enter teams, players and bans.
This has been achieved with a main menu that can be easily navigated and that can call up several sub-forms at the touch of a button. The data entry points are clear which allows the user to input data with ease.
- All data must be accessible centrally.
This has been achieved by creating a main menu. This allows the data to be manipulated and handled from a central point. This allows the data to be easily read and understood.
- The system must be able to automatically update league tables
The Excel spreadsheet I have created allows the user to update easily read tables and not calculate the league tables by hand. This allows the user to easily produce smart up-to-date tables which also saves on working time. I feel I have achieved this to a very high level.
- The user must be able to easily save inputted teams, players and bans.
The utilities I have provided within the database allow easily saveable data within the database. I have also provided clear data input points; these allow the user to be able to easily see where the data will need to be inputted. This allows a clear and up-to-date database with no duplicate data and without unnecessary redundant data.
Mr Warne’s Appraisal
Mr Warne and his staff at Pro5 Bristol are extremely pleased with the outcome of my Project. It has made every aspect of Pro5 easier to handle, and allows less time spent on running this leisure business.
The main aspects that Mr Warne commented on were:
-
The Simple Format Of The Forms- He said that the database system was easy to use with all functions easy to find and use.
-
The Excel Spreadsheet- He commented on how it was a “Godsend”. It has really made working out the league tables and results a lot easier, freeing up more time to deal with player and team records.
-
Player And Team Details- This was an aspect Mr Warne and his team were especially impressed with. Never before have they had all the Pro5 Team and Player record within one place. They used to have problems with Players changing their details such as telephone number after moving house. Now they can change the details by simply running a search for the Players record.
- All Data Held in One Place- All the data required to run Pro5 is held within 2 files. Mr Warne is thankful that he can finally store all data safely and efficiently. Records can be pulled up at any time with a quick search and records can be saved easily.
Extended Project: As I have designed a program for the running of Pro5, a good extension to this project would be to design an accounts program using either Access or Excel to run alongside of the 2 programs. This would create an even more efficient system.
Overall I have felt I have met the users specification set out at the start of the project and produced a set of tools for Pro5 that will make the job a lot easier to handle. It will also provide a clearer set of information for the players and teams at Pro5, as results and fixtures, as well as Player registration and ban details have become much easier to handle.
