Constructing a Database In Microsoft Access

This report is to document the stages of development that were undertaken in the creation of a swimming club database. The areas that will help illustrate these aspects are:

* The system outline and requirements - a brief overview of the swimming club, what they wanted from the system and an outline of the proposed information systems inputs, outputs, processing and stored information.

* Physical design - A description of the target environment for the system. Also an overview of the database design with illustrations, and a description of the approach made for designing the components of the system.

* Software construction - Reporting the work that was undertaken to enable the construction of the swimming club database. The procedures for constructing the tables, forms and queries, and the approach to testing the database. Advanced issues such as security are explained and the user guide is discussed.

* Project outcome - A retrospective review of the outcome of the project process. Did the final product meet initial expectations? Reasons for change in direction, and things that would have been done differently in the light of experience.

System Outline & Requirements

The area of application was a swimming club; the club wanted a database solution that would perform its administrative functions. The main issue was that it was to be a reliable system that would hold all the data about the club and its members. The specific types of data were decided upon when discussed with the client. The information was to be:

* Membership maintenance - this would be the function to enter and edit member's details such as name, ID number, date of birth, address etc. Both employees of the club and its members would be able to access this area, with restrictions on editing certain parts. Print outs of requested areas of members details would be available and also searches on specific fields such will be allowed. For example someone working at the club may want to find a member's address so they could type in their name and the system would output all members with that last name and their details.

* Awards - This would record different awards that had been achieved by certain members. Whether it is someone achieving their 100 metres badge or a member winning a club ran competition, the information could be stored and viewed in the database. Also it was agreed that certificates could be printed out through this function by selecting the relevant information and outputting it in a predefined format.

* Personal best times - This function would be used to record members personal best times in a selected stroke and a selected distance. It would allow the member to access their own personal best time file and enter the new time they had set. A list of the whole clubs PB's could be viewed, with them in time and stroke order.

Physical Design

Hardware

The target environment for this project was for the information system to run on a standard personal computer. The computer would not need any special specific capabilities, just a machine running on Microsoft Windows 98 or above.

Microsoft Project

Microsoft Project is a tool used for time management, this tool helped plan what objectives were to be tackled and in what order. By keeping check of the Gantt Chart created it is easy to tell whether the project development is on course for finishing on time. The Gantt for this project can be seen in app.1

Access

Microsoft Access would also need to be installed on the machine as this would be the database management system used for compiling the project. Microsoft Access is a standard database management system used for compiling simple or complex systems. It would be sufficient enough to compile the database in question. Access allows the user to compile tables, forms, queries and reports with the function to relate and link these aspects. This process when completed in the correct manner produces a fully functional database information system.

Database design

The process of designing the structure of the database is an important step as it would provide the foundations for the whole system. The first aspect to take into account was what the proposed outcome of the project will look like. What information was going to be stored and what inputs/outputs needed to be created? The details of this aspect have been covered in the System Outline section. Taking this information and producing an entity model (app.2) was the next step. An entity model would show how the tables of information relate to each other, and would also point out when a link entity was needed due to a 'many to many' relationship occurrence. The Primary keys were distinguished within in each table, the primary key being the unique field of data contained in the table i.e. ID number. A composite key was contained in the link entity that was made up of the primary keys from the inherent entities. These processes make the database relationships far more efficient and operate correctly within Access. Once the foundations of the system have been laid then it is time to progress to making the tables interact through forms, queries and reports.

Software Construction

The physical process that was undertaken to complete the construction of this project in Microsoft Access was the most time consuming and technical phase to be tackled. The stage would throw up numerous problems and subsequently prompt numerous solutions. The first major stumbling block for me was my inexperience with Access; I had to learn the software as I compiled the project as it was totally new to me. I completed a few different basic tutorials that helped get me started. This gave me the confidence to initially try and compile the tables and the types of information that were to be stored in them.

Tables

Using the wizards did not seem the most efficient way for compiling my tables, though the wizard would become my friend at a later stage in the construction. Using the design view seemed more suitable (fig 1); I entered the field names and defined the data types for each of my four tables. The data types were simply put to settings like numbers or text values, the more in depth property restrictions were set in the field properties area using input masks etc (fig 2).

The Primary and composite keys were also set at this stage within each table.

Relationships

The next stage was to distinguish the relationships between each of the tables. By adjusting Access into the relationship window view, all that had to be done here was a simple drag and drop between the Primary keys. The relationship diagram is displayed below:

Forms, Queries and Reports

Once I had my tables linked correctly it was time to tackle adding forms, queries and reports. The first logical form to create was the add member form, its layout was set up through the wizard, all the information was coming from the membership maintenance table. In the design view I added my Bolton Sharks logo, and altered the properties so that it would run the macro 'ClearForm' on activation. 'ClearForm' is the name of the macro I created with the settings GoToRecord and the argument 'New'. This meant the form would be clear each time it was activated. Buttons were added using the toolbox that added a record and closed the form. The form is displayed below:

In order to create the next forms I had to first create some queries, the first was one that would find members by their ID numbers. I used the wizard once again taking my information from the membership maintenance table and using all its data members. In the design view I set the criteria of the MemberID field to [Enter MemberID]. This enabled a prompt to be activated when the query was called upon asking for the user to enter their ID number. I also created one using the same process but having Last Name for the search, in case someone could not remember their ID number. A form was built from the 'find by ID' query, this form was to enable the modify/delete function. Using the wizard I created a standard form with the data coming from the query. It displayed the users' information after they had entered their ID and it also had three buttons created with the toolbox, one to close the form, one to save the record, and one to delete a record.

For the awards form, I created a form from Membership maintenance that had a sub-form from the Awards table. I used the combo box tool to act as a lookup for the user. Through the wizard I selected 'find a record on my form based on the value I selected in my combo-box' this provided the function to bring up the users name once their ID had been selected. In the sub form area was space for the user to enter details of their new awards. Using the formula I had supplied for creating unique award codes it was then stored in the tables by using the add + close button. You may also notice the code guide button; this is a button that runs a C++ application I had designed to give help with the award code. The same process was undertaken for the PB form but with the relevant alterations made.

The next step was to create some reports. These were very simple to create using the wizards. Quite a lot of alterations were needed for the presentation side of the reports; this was done in the design view manually. A couple of the reports were linked to queries in order to perform a search and print to screen operation. All these reports could be printed at the user's discretion.

To link the forms and reports together through one interface I created a form that had nothing on it to start with. I put a Bolton Sharks badge and a few instructions on the form to make it look more appealing. I then proceeded to add buttons that opened each of the forms and reports again using the wizard, good old wizard eh....

This completed my database and added a lot more of a user friendly set-up, one last touch was to add the main interface form as the default opening form on start-up through the tools menu.

Project Outcome

On completion of this database I have to admit I am quite happy with the end product as this was the first time of ever using Microsoft Access and the whole process was a learning curve.

On whether it satisfied the original objectives I believe it did on the whole bar a few minor details. The security issue was a little too advanced for me I think, as it was causing a lot of problems for the database running on a whole. Also the issue of deleting member's details from the system caused problems in the sense that my delete function got rid of their personal details but did not clear the awards and PB sections. This is why I put the instruction on the user guide to inform admin when deletions were necessary. They could then go into the tables manually and delete the records.

As for the issue of what would I do differently had I do it again, I don't really think I would do much differently as the final database was the third attempt at this product so the two unsuccessful attempts would the things I would change. I would have liked to get the security issue sorted but it was hindering my progress and I nearly lost what work I had done while trying to implement it.