Mr. Dharmen Trivedi is the CEO of Trivedi Industries, which deals in the manufacturing and trading of cast iron pipe, fittings and valves. His factory and head office are located in Howrah. Several clients come to him daily to place orders for pipes and fittings and metal rods to be used for making bridges and in underground water tunnels. The pipes are also used by the government for infrastructure. My father Mr. Dinesh Trivedi often purchases pipes from him for use in my mother’s botanical research labs.

In India and especially in the state of West Bengal the surname ‘Trivedi’ is very common and Mr. Dharmen Trivedi is not my relation. My father often faced the problem of not getting the goods ordered on time and the employees taking a long time to answer his queries. He knew I was looking for a client for my O’level Computing coursework and introduced me to Mr. Dharmen Trivedi. Since Mr. Dharmen Trivedi was a good friend I agreed to help him out and solve his problems that he was having with his business. The present system as I learnt included no use of computers and all the transactions were stored manually along with the handwritten bills and invoices.

The present situation

The information I gathered from my client was summed up in the following points. The fittings and pipes are manufactured from sizes 80 mm diameter- 1000 mm diameter, and as per clients’ specifications and drawings.

º These drawings are all printed and are stored in their respective files and folders. The specifications for the pipes and fittings are submitted in a haphazard fashion, and these too are printed sheets, which are copied down manually into a book where all the customer information is stored.

º The suppliers for the company also face the same difficulty of submitting the reports of the raw materials that the company requires. Therefore as a result, miscalculations and confusions are frequent problems that the company faces. The clients give the dimensions, which are received by fax or post and manually stored in files and folders that are very space consuming.

º The weights of the sizes of these pipes or fittings have to be found out from the Indian Standard specification in order to quote the rates (Weight x Rate per Kg = Price). This data has to be manually entered and calculated on a calculator, thus proving to be time consuming and a very repetitive process. This is a boring and tedious job, as the data must be typed out on a typing machine. Plus this data has to be very accurate and within the limits as it is related with the income tax. The rates are typed onto letterheads and sent back to the clients via courier, post or fax. Typing errors are very common and in a matter of money typing of the rates correctly is a very crucial aspect. Imagine an extra zero somewhere. That could result in a few thousand Rupees turning into a few Crores! Once the clients make sure the rates are correct, they place their order, and the pipes or fittings are manufactured. Then the Performa invoice is again manually typed and sent to the clients for the payments and settlements of the dues.

º Many letters also have to be typed out to each and every customer, stating the due dates and the bills. These letters have to be typed over and over again as there are many customers and so many bills.

º Once the payments come in, the manufactured materials are dispatched to the specified destination as per the clients order. But before doing this all the customer information has to be dug out and the company has to be sure that it is sending the right goods to the correct places.

º Final bills along with the test and guarantee certificates are posted or faxed back to the client.

º Customers in many cases are also suppliers as they continuously trade with Trivedi Industries, buying and selling valves and fittings to them.

The problem from my viewpoint

Now the present system of the business has these problems:

º One small file gets misplaced, and then the whole system collapses

º All the transactions are recorded manually on paper and in huge books of which not many are named

º Problems in handling of data and in storing it properly. These results in severe loss of data and the folders and files can get misplaced very easily. Hence all the records of the customers and suppliers are very easily lost and this creates a lot of problem and confusion for the company.

º Listings of different information should be done in an organized way so that they are informative as well as pleasing to the eyes.

I suggested that if my client could go for a computerized system as it would eventually weed out the present problems by organizing data and retrieving it properly.

My client after discussions with his consultants and taking into consideration certain factors of his business, finally agreed to my proposal of introducing a new computer based system.

He felt that this would help him:

º Increase the efficiency of work in the office

º Take away the need for large space consuming files and folders

º Data entry would also be very easy and fast due to the computer based system.

Approach

I accompanied my client to his office on the 16th of June 2005, during my summer holidays. The outer office where my client met his visitors was well lit and decorated. But once inside the building I realised that the place was a complete mess. Nameless files were scattered all over the place with an employee trying to put some order into the piles of files in one huge cupboard. I was later informed that those were the books where all the financial transactions were recorded. Now anyone who would have seen those books could have said that the place needed a lot of changes.

Disadvantages of the present system:

Manual data entry makes work very tedious and slow

Files and folders of various sizes clutters up workspace

The work area is not at all organized

Data loss is very common due to misplacements of files and folders and books

Data is not very safe and can easily be stolen

The company is suffering losses as the system is not very efficient as the manual data entry slows down the work process.

Advantages of the present system:

The employees develop a strong hand by writing a lot

The present system can easily qualify to enter into a museum

3. The employees get a good workout carrying all the heavy books around!

Main tasks and subtasks

º Getting rid of all the files and books by changing to a new computerized system

º Using a software:

1. To create a suitable interactive mode of entry of data

2. Variable criteria can be used to make sure that the values obtained through the calculations are all correct

3. All the final outputs must be tested as well and it must be ensured that all of them are displaying the data in a very organized fashion

4. The user should have searching facilities, so that necessary information can be looked up with ease.

º Calculation of the cost of each transaction – this is convenient for the user and should be performed automatically when the transaction is recorded.

º Test data then must be used to test and implement the new system and make sure that all the forms and relations are working in perfect synchronization.

Criteria for success

I shall consider my solution successful if:

º All sections of the business are put together into a computerized system

º All data is input, stored and saved instantly and conveniently

º Data is complete and correct.

º Quick searches allowing access to any information on the business is done

º There are no errors in calculations

º The human computer interface is user-friendly – the user should be able to navigate easily around the database and should not have any difficulty inputting the data (as he is not used to handling computers except computer games, which have a colourful and easy HCI.)

º Desired output can be produced easily –printing can be easily done

º My client is satisfied with the way the system works.

Comparison table

FEATURE	OLD SYTEM	NEW SYSTEM
SPACE CONSUMING	ü
ORGANIZED		ü
EASE OF NAVIGATION		ü
CAPABILITY TO SORT	ü	ü
USER FRIENDLY		ü

DESIGN AND PLANNING OF THE SYSTEM

Software choices

This is the way I propose to start working on the project, based on my discussions with the client and my assumptions on the on the expected solutions.

Range of software

The main problem I faced while deciding on the software to be chosen was that there were enough calculations to be done quickly and accurately as well as organizing, sorting, storing and retrieving them. The first part of the design of the new system would be pointing towards spreadsheet software. The second part obviously pointed towards the use of a database management system.

List of software

For this application I could either use a spreadsheet program or a relational database management program. A word processor could also be included in the list but as it does not suffice to all the needs of such an application such as conducting calculations, it would not be appropriate.

Choice of software

I decided that the best thing to use for this application would be an application package, as I have never learnt to use a programming language. A Relational Database Management system would be the perfect I realized, as all the business transactions can easily be stored along with records of various customers and suppliers and also reports of these transactions can be seen on demand. Also various calculations can be conducted automatically through the forms and the values can be obtained very fast. I had learnt to use MS Access in class 9 and I am confident of handling this problem with it. I spoke to my client and I explained to him that for his company a RDBMS namely Microsoft Access would be perfect. I also ensured him that after having this new type of system installed he would find that his company would become much more efficient and work would be carried out much faster. Also possible data loss can be prevented.

Microsoft Access is a relational database management system from Microsoft, packaged with Microsoft Office Professional, which combines the Jet relational database engine with a graphical interface. The development environment provides productivity-enhancing features for both advanced developers and beginning users.

Hence I felt that this package would not only be very user friendly and very efficient but would also require minimum skills to operate. This would allow most of my client’s employees to use this package and thus he would benefit from it. Thus I feel that Ms Access is best suited for this purpose.

Link software with problem

The system created with MS Access should be able to efficiently produce output on:

º Orders, purchases and jobs – details of all orders, purchases and jobs which have not been completed and need attention. A summary of sales and purchases and jobs whenever it is required by the user– this will help to organise the business better and should be automated and easy.

º I will use the Orders, Suppliers and Customers tables to store my base data.

º I will make forms for easy and interactive data entry.

º Sub forms can be created to use alongside the main forms for easier data manipulation

º Reports are also necessary and the appropriate criteria must be selected for each of the different reports

Ø Calculations are also included in the application as discussed earlier and hence the forms must also include automatic calculating capabilities

Ø The database must be made very user friendly so that even people with minimal computer operating skills like he employees can use it.

º The system should help my client’s firm organise their business dealings better and hence provide a better service to their customers. It should be intuitive and user-friendly to make it as useful as possible for my user in everyday business and MS Access provides all these facilities.

º All the records should be in an organized fashion along with the reports

º A summary of the sales purchases and jobs whenever it is required by the user– this will help to organise the business better and should be automated and easy.

º The forms should be easy to navigate through and hence must also be very user friendly so that data entry is made much faster

º The data could also be sorted in such a way that going through them is much easier

º The work output should increase, that is the efficiency of the business should increase.

º The amount of work done by one employee in one day should be greater with the introduction of the new system

º The client should be able to easily enter all his data correctly without making errors

º The client should be able to retrieve records on demand and view reports of desired criteria

Outline of the design

Minimum Hardware requirements for MS Access 2002

System Requirements:

OS Required - Microsoft Windows 98 / Microsoft Windows 98 Second Edition/ Microsoft Windows 2000 Professional/ Microsoft Windows NT 4.0 SP6/ Microsoft Windows NT 4.0 SP6/ Microsoft Windows Millennium Edition

Min Processor Type - Intel Pentium - 133 MHz

Min RAM Size - 72 MB

Min Hard Drive Space - 170 MB

Peripheral / Interface Devices - Mouse or compatible device, CD-ROM, SVGA monitor

System Requirements / Platform - Windows

School Hardware available

PC3- MAIN COMPUTING LAB

Operating system- Windows XP Professional Service pack 2

Processor- 650 MHz

L2 Cache -256KB

Memory - 256 MB

Main board - BIOSTAR M6TWG

Mouse- Microsoft PS/2 compatible

Keyboard- Standard 101/102 key

Monitor- Plug and play with INTEL 82810 chipset

I have recommended the following desktop computer to my client, as this meets all the requirements of MS ACCESS and is ideal for office use.

IBM (Lenovo) Thinkcentre S51

º Processor: Intel Pentium 4 3.2GHz

º Memory: 512MB (installed) / 2GB (max)

º Hard Drive: 40 GB

º Integrated Features: CD-ROM, 3.5" 1.44 MB floppy

º Graphics Controller: Intel Graphics Media Accelerator 900

º Connectivity: USB 2.0, Serial, Parallel, VGA

º Included Operating System: Microsoft Windows XP Professional

º Monitor: ThinkVision L150p (LCD monitor, as these save space and do not impair vision that much)

º Printer: HP LaserJet 3030 for printing out reports and records etc.)

º Mouse: Samsung optical wheel mouse

º Keyboard: Microsoft keyboard

Design of Forms for Data entry

Data capture forms are necessary for the customers to submit their orders to the business. Also the suppliers must submit the report on the goods via forms. Similarly people seeking a job in the company must enroll themselves through forms. Here are rough structures of data entry forms.

The fields for the tables are:

TABLES


Customer	This would hold all the information about the customer’s personal details and the customer’s billing address along with a customer ID
Customer Information	This holds the customer ID along with the details on the customer’s purchases and the due date by which the customer has to pay a sum of money which is also included in this form as ‘Total cost’
Supplier	This shows the suppliers details that include office address and the office telephone numbers of the suppliers. This also contains a supplier ID that is a unique code.
Supplier Information	This table represents the billing information about the supplier and states the total amount payable to the supplier by a particular due date.
Job Applications	This is for people who want to enroll themselves for the job. It holds information about the applicant’s personal details such as name and address along with other details such as previous job information and qualifications.
Orders	This table contains the main transaction information between the client and the company. It holds the material bought by a customer and the total cost along with service charges and delivery costs. It also has the customer name and the order number, which is the primary key for this table.

Structure

I am planning to create about 6-8 tables that control all the business transactions and store the recorded data. These tables are related to their corresponding forms and data is directly to be entered into the forms so that data input is made faster and simpler. The tables titled Customers, Orders etc should have about 8-14 fields as there are lots of field criteria that are necessary for input.

A typical customer table should have this format.

Field name	Type	Condition
Customer Name	Text (20-25 characters)	The display control is a text box.
Telephone	Number(10-15 characters)	There should be no text in this field and hence a message box should show an error message every time text is entered.
Office Phone	Number(10-15 characters)	There should be no text in this field and hence a message box should show an error message every time text is entered.
Address(Residence)	Text(30-35 characters)	There can be numbers in this field.
Address(Office)	Text(30-35 characters)	There can be numbers in this field.
Order	Text(20 characters)	There may be numbers in this field as the customers specify the number of goods they want to purchase)
Dimensions	Text(20 characters)	Here the dimensions for the purchased goods are entered
Other specifications	Text(20-25 characters)	In case the customers want to specify a special material to use for the goods, this is where it is entered
Shipping	Yes/No	This is just a simple check box which means “yes” if ticked and no if left out

I have planned to make at least 8 forms that correspond to their respective tables. Reports are also necessary for this application, as Mr. Dharmen Trivedi needs to see an organized representation of his orders, suppliers, customers etc. As calculations are executed on the forms themselves there is no need for queries for this application.

The Job Application form has a subform that is needed for the input of other personal employee data like Father’s name and blood group. The Supplier form would have the following format

Validation Checks on data entered

The data entered must be accurate and specific, and thus rules must be applied so that the data being entered is valid.

Field name	Type	Condition
Supplier Name	Text (20-25 characters)	The display control is a text box.
Telephone	Number(10-15 characters)	There should be no text in this field and hence a message box should show an error message every time text is entered.
Office Phone	Number(10-15 characters)	There should be no text in this field and hence a message box should show an error message every time text is entered.
Address(Residence)	Text(30-35 characters)	There can be numbers in this field.
Address(Office)	Text(30-35 characters)	There can be numbers in this field.
Material	Text(20 characters)	This field requires the display control as a combo box and the Row source type should be field list. This way the different materials can be listed and the desired material can be just selected.
Cost Per Piece	Currency	This field is for entering the price per unit of the good being supplied by the supplier.
Tax/Service Charge	Currency	This field is to specify the tax or the service charge that can be applied to the item being bought.
Number of items	Number	This is to specify the number of items being bought.
Total Amount	Currency	This field is to specify the total amount for the goods bought.
Due date	Time/date	This represents the date on which the amount of money is due.

Test Plan

I had to make sure that all the forms and reports were functioning in the correct manner and they were all showing the data in an organized yet accurate fashion. For this I had to enter test data into the forms and make sure that the data was processed accurately. For the test data I decided to ask my client for some of his bills of transactions. He agreed to let all these original transactions be used in the forms and he provided me with recent bills to clients, receipts and tax invoices. These were very helpful for using as my test data as they contained all the information there was to enter for the forms.

Test data

I had visited my client’s office earlier and had seen a few files and folders. I thought that it would be appropriate if my client could provide me with some bills of transactions, which I could use as my test data to enter into the forms and reports. This way I could make sure that the formulae in the forms are giving me correct results and I could also make sure that the forms are user friendly and can be easily handled. I could make sure that there are no difficulties in entering the data and the reports could be tested for organised representation of the data. My client provided me with a file. This basically consisted of original bills of transactions, tax invoices and original bills to his clients. These were very recent and hence it would be very suitable to use for the testing of the new system

Tests conducted for all customer forms
Field Name	Test Type	Test data	Expected results
Customer name	Presence Check	Don’t enter data	“Data needed in the required field”
Date fields	Format check	Enter wrong date format in input mask	“The value you have entered for this field is not appropriate”
Currency fields	Type check	Enter text into currency field	“The value you entered is not valid for this field”

Tests conducted for all forms
Field Name	Test Type	Test data	Expected results
Supplier name	Presence Check	Don’t enter data	“Data needed in the required field”
Date fields	Format check	Enter wrong date format in input mask	“The value you have entered for this field is not appropriate”
Telephone number	Type check	Enter text into telephone field	“The value you entered is not valid for this field”
Currency fields	Type check	Enter text into currency field	“The value you entered is not valid for this field”
Name fields	Length check	Enter a name more than the allowed number of characters	No more characters can be entered into the field after the character limit has been reached.

IMPLEMENTATION

INTRODUCTION

I have chosen to use MS Access due to the problems that I had to resolve as discussed earlier. The tables and the forms had been made as planned. I made 8 tables that covered up for the different work (transactions) that the company had to go through manually. A separate section for the employees was also created where the employee tables were made and the forms for the employees personal information was added. I made a separate “Job Applications” form for the user to record details of new employees wanting to join the company. This included the job applicants personal details, including blood group, father’s name etc. Also in this form were included the details of the applicant’s previous job details and qualifications. Hence the client could easily navigate through the various applicants and employ the people based on their qualifications.

Formulae

I have used formulae in my forms for the calculations that I needed to put in. For these I opened the form in design view and then by right clicking on the total amount box I went to properties. Then under the control source tab I entered the following formula (for order form) =[CostPerPiece]*[Number of items]+[Tax/ServiceCharge]

This way by entering the formula for the calculation under the control source I could set the desired calculation. This way calculating values is made a lot easier as all you have to do is click on the total amount box after entering the other data in the fields that are involved in the calculation.

Input mask function

I tried to use the input mask function in a form but then when I tried to enter a value, nothing happened and the “dd/mm/yy” did not move when a new date was to be entered. I then went to properties and then menu by right clicking on the data tab in design view and then set the format as short date and in the input mask option, I set “99/99/00;0;_” . This change made it work

The input mask function was needed as:

Ø This makes it easier for the data entry process as you know what values are to be entered into the field

Ø Errors while data entry are reduced

Ø The person keying in the data knows exactly where and how to enter it

Combo box for searching fields

I have added the combo box search field to some of my forms as:

It is easier than the normal search

Ø It is ready for the user to type in the criteria

Ø It is a faster procedure

Ø The user does not have to select the search button every time he wants to search

Subform

I felt that the subform in a form should move along with the main form and hence I had to remove the navigation buttons in the subform. So I:

Ø Opened the main form in design view

Ø Right clicked the subform control and clicked “Subform in new window”, and this opened it in a separate window

Ø I went to properties and selected “No” on “Navigation buttons”

Ø I saved and closed the subform window

Buttons

In the forms I have also added buttons that are needed for easier navigation and easier manipulation of the records in the forms. To add buttons in the forms I opened the form in design view and I selected the command button option. I dragged the button across the desired area and then the command button wizard opened up, where I selected Record Operations and then selected Delete record or Add new record etc. After this I selected an appropriate picture for the button and before clicking on Finish I assigned an appropriate name for the button.

Relationships

The relationships that I have assigned for my tables are very simple yet it works in the desired fashion. I have shown the relationships below:

Switchboard

I have created a switchboard to use for the database. Switchboards make navigation easier and also make the database look more organized. For creating the switchboard I went to tools and then database utilities and then selected switchboard manager. From here I got the option of creating a new switchboard and I clicked ‘Yes’. Then I entered the appropriate name for the switchboard and assigned all the links. I went to the switchboard manager and made the main switchboard the default. I named this Menu to make sure that people using the database knew what to do next. Then I went to startup and selected the switchboard as my startup screen. I made all my forms open in ADD MODE for my client to add information into the forms.

Switch board contents

Company logo

My client wanted a logo of his company on all of his forms and reports and tables. This was for the sole purpose of personalization of the reports and forms so that presentation would be better. My client designed this logo and hence it adds a personal touch to his work.

Switchboard formatting

I wanted to insert a picture into my switchboard, the logo of my client’s business. So I went to the design view and by clicking on “Insert” I went to “picture”. From there I browsed through the files and selected my logo file. I opened the logo and placed it in the appropriate place on the switchboard and on the forms.

Switchboard return Button on forms

I wanted to add a button to my forms so that return back to the main menu (switchboard) from the form itself. For this I opened the form in design view and I selected the “button” tool. I placed the new button in the form and I selected the command operation “Open Form”. Here I

selected the switchboard and I put the company’s logo as the button’s picture. I have shown the buttons below;

Screen shots of a few of my forms

AMMENDMENTS TO DESIGN

Switchboard return button

I felt that the switchboard return button on the forms was just not necessary and would just lead to confusion. Hence I deleted that button, as my client that it would not be necessary. The form can just be closed by clicking on the close button and as the switchboard never closes when you open a form, it would be there as soon as the form is closed.

Subform

I felt that the form ‘Job Applications’ would not require a subform as the criteria for both the forms were quite different and because there were a variety of problems that I faced:

º The Add Record button did not work for both the forms as it was made in the Job Applications form

º The navigation buttons did not work for the Personal Details form as it was a subform and hence the records in the Personal details form did not advance

º Due to this same issue the records on the Personal Details form did not get deleted when the delete button was pressed

º I had to install PROPLUS. MSI for this change to occur and I could not find this on the Office 2000 disc

Hence I told my client about the problem and we agreed to have the two things as separate forms.

Date entry

In the forms I inserted an input mask for the date field as explained earlier. For the data input to be less complicated I added a label next to the date field, which had “DD/MM/YY” on it. For this I opened the form in design view and selected the label tool. Next I drew the label where I wanted it and inserted the text into it.

Automatic date entry

I wanted to make my forms a bit more convenient and thus I wanted the dates to be entered automatically in the “date” field in all the forms. This would lead to faster data entry, as one does not have to keep entering the date every time. Hence I opened the form in design view and right clicked on the date field and selected “properties”. Next under Default Value I entered “Date ()”. This made the date get automatically inserted into the appropriate field.

Navigational buttons in forms

I wanted to remove the navigational buttons in my forms. This would not only make the forms less complicated but would also make sure that no confusion occurs during navigation within the forms themselves. For this I opened the form in design view and then by right clicking in the blank area in the page header, I selected properties. Next I went to Navigation buttons and under the drop down menu I selected "No". This change removed the navigational buttons in the forms. But now I had to replace those buttons with new navigation buttons that would advance to the next record on clicking. Hence I selected the button tool and then I drew the button in the appropriate place. In the menu that opened up I selected "Record operations" and then I selected "go to next". This step was repeated again, only this time "go to previous was selected. I added the appropriate image for each button. This way managed to set the navigation buttons on the forms.

I have drawn the system flowchart for the present system.

TESTING

I started testing according to the test plan I had done in my design section. While testing I realised that I needed to change some of my designs due to errors as well as when my client asked for a specific change.

Testing

Firstly I had to check and make sure that all the forms are functioning in the correct manner. The calculations also had been tested to make sure that it determines the correct results and the formulae also must be correct in order for the above to take place. The forms should provide correct representation of data and I must test my relationships to make sure that they are working properly as well. The tables must be linked to each other in the right manner so that the data on a linked table can be viewed directly on the corresponding table. My reports had been tested as well and organized representation of the data was ensured. The headings under the reports were clear and spaced out so that people who are viewing them can see all the headings with ease without getting confused.

The new system had to be tested before any major changes could be made to it to improve it. I asked my client for the test data, which he provided me. The data had to be entered into the forms and then the relationships had to be tested for functionality.

CHANGES ON AND AFTER TESTING

At the Testing stage I found that my client could make mistakes and would like to subsequently change the data already input. I felt the client would like to do so right on the form instead of through the tables as my main objective was user friendliness. Hence I made all my forms open in EDIT MODE as my client wanted to add information into the forms.

Combo box in supplier form

I wanted a combo box in the Supplier form that would show the available materials. Hence first I made a table called STOCK, and then in that under the sing column ‘Materials’ I entered the materials that I wanted. Having done this I opened the form in design view and selected the combo box button. After selecting the table, which I wanted to import values from, I named the combo box ‘Material’. This way I could get the materials in stock in the combo box.

Orders form

For this form I had to make sure that the formula was working correctly. For an accurate result of “Total Cost” the number of items had to be multiplied by the cost per item and to this the sales tax had to be added which was 4/100 of the total cost of items. Now the sales tax along with the cost of items and the delivery charges gave the total cost that had to be paid by the customer. The customer could have the goods shipped and for this another extra Rs 350 had to be added to the total cost. Hence I added the required data in the Orders form

using a tax invoice for sales and checked the result for the total cost which matched with that in the tax invoice and thus the Orders form was functioning correctly as its prime function was to calculate the total cost.

Job applications form

I had already deleted the subform from the Job Applications for due to the reasons discussed earlier. Hence this form had to be tested for making sure that the records could be entered and displayed accurately. The input mask for the date was checked where the input mask showed “__/__/__” for dd/mm/yy. This turned out to work correctly. All the buttons in the forms were also tested and made sure that they carried out the desired actions. The type check validation for all the forms were also checked to make sure that there were no numbers in a field that had letters and vice versa.

Total number of records in form

For the Order form my client wanted to see the number of orders that had been placed and wanted to delete whatever was done with. I had already made a Delete Record Button for him. Now I had to link the orders entered to a field which would show the number. I set the criteria Required: Yes for the Customer Name field in the Orders table. Then I made a Text Box and under Properties for it I entered a formula next to the Control Source. The formula I used was =Count([CustomerName]).

The Calculated fields being shown on the Orders Table.

Form area

The form area for the tables consisted of field size, which was kept as ‘Long integer’ for those fields that were numeric or those that had auto number in them. For a field that was alpha numeric such as one for an address, the field size was kept as 30. For a name, this was kept to 20 characters. For the auto number fields the new values were set as increment. And the indexed was set as YES (No duplicates) where the field was set as the primary key and hence had to be unique and thus could not have any duplicates. For the date field the format was kept as short date. In a field that required the use of a combo box, the display control in the lookup tab was put as combo box. The row source type was ‘Field list’ and the row source consisted of the values that were to show in the combo box. This was typed in the form “"xxxx";" xxxx ";" xxxx "; xxxx” where the x’s are the fields for the combo box. In a field that used the field type as currency, the format was standard. The decimal place was set as auto. Only for the currency field was the indexed kept as YES (Duplicates OK).

Pictures

Switchboard- I had to put a few pictures on the switchboard related to the products manufactured by the company. For this I had to get pictures of pipes and cast iron fittings from the Internet, from Google images. Then I opened the switchboard in design view and by going to insert, I put the pictures that I wanted by browsing through the files and placed it in the appropriate place. But the company logo was scanned on a scanner and then placed on the switchboard. The logo was obtained from the company’s letterhead that my client provided me with.

Forms- I placed all the pictures on the forms by opening the forms in design view and then clicking on the clipart icon. Next I went to the appropriate clipart and inserted it into the form.

Form formatting

The background for each of the forms was selected when the form wizard was run. The form wizard displayed all the backgrounds and form formats available and I selected those for my forms that best suited their purpose. Buttons were inserted in all the forms as stated earlier but I arranged them in a universal format. That it I placed the delete record, find record and add record buttons in the way that I felt was appropriate. This is shown in the diagram below:

Borders

The borders for all the forms are kept as solid type and their colour is set, matching that of the background. There is no special effect used for the fields (hence the special effect is kept as flat). There are labels used that show the format of the date as “dd/mm/yy”. For these the back style is transparent and the special effect is flat. The border colour is kept as black and the border width was set to ‘hairline’. In the switchboard the buttons have the special effect ‘raised’ and the back style is transparent as the company logo is put as the background picture. The border colour is black and I have kept the width to 3pt.

Report formatting

My reports had to be designed in such a way that helped in the way they displayed the data. Hence all the report headers were spaced out evenly so that each could be read without any confusion. The borders were set to the same colour of the text. Here the borer style was kept as sizable if I needed to resize it in the future.

Report layout

The report layout had to be changed for some reports as my client wanted to be able to read one full record on one page as this was easier for him to view the data. This was specially required for the job applications report as my client wanted to view the report of one applicant at a time so that it would be easier to view the details and he could then employ people based on their qualifications. For this I went to the reports section in my database and I deleted the previous version of the job applications report. Then I selected new and I started the report wizard. I selected the table that I wanted for generating the report, and I selected the fields that I wanted to show in the report. In most of the forms I did not add any grouping levels besides the customer information form. No sort order was set either. As stated the layout was kept as columnar. The report wizard offered a variety of visual styles that could be applied to the reports and I selected the one that best suited the job applications. Finally I named this report as ‘JobApplications’. I have the printout on the next page.

Job applications report

Grouping the headings in the switchboard

The switchboard had many forms that were randomly placed under the forms page of the switchboard. I decided that the forms had to be arranged under appropriate headings. Hence I first wrote down on paper all the different headings that I wanted for my switchboard under the ‘Forms’. The forms were laid out as follows:

Shipping

My client wanted to have a field called delivery charges under the orders form. This field was to be added to the ‘Total cost’ as the delivery cost. I realised that I had a checkbox called ‘Shipping’ in the Orders form. Hence I thought that it would be a good idea if I made a new field called ‘Delivery charges’. This field was only to be filled if the shipping checkbox was ticked. After asking my client I found out that the delivery charges was kept as a constant Rs. 350 but it could vary depending on where the customer was located. So I just left that field as a ‘currency’ and the new formula for the total cost was as follows:

= ([AmountRequired]*[Cost of materials])+[Sales Tax]+[Delivery charges]

Form properties

CUSTOMER FORM
Back colour	16777215
Border colour	13209
Picture size mode	Clip
Picture alignment	Center
Border width	Hairline
Font size	9
Font name	Arial

ORDERS AND CUSTOMER INFORMATION FORM
Picture	D:\ My Pictures\finger_print.gif
Picture Type	Embedded
Picture size mode	clip
Picture Alignment	Center
Picture Tiling	Yes
Field Properties
Border Colour	8421504
Border Width	Hairline
Font Name	Times New Roman
Font Size	10
dd/mm/yy label
Border Width	Hairline
Fore Colour	8404992

SUPPLIER AND SUPPLIER INFORMATION FORM
Picture	C:\My pictures\pipes2.jpg
Picture type mode	Clip
Picture alignment	Center
Back colour	16777215
Back style	Solid
Border colour	0
Border width	2 pt
Fore colour	0
Font weight	Bold
Font name	Arial
Font size	8

ALL EMPLOYEE FORMS
Picture	C:\My pictures\Blueprint.jpg
Picture type mode	Clip
Picture alignment	Center
Back colour	16777215
Back style	Transparent
Border colour	0
Border width	Hairline
Fore colour	16711680
Font weight	Bold
Font name	Arial
Font size	9

EVALUATION OF THE SOLUTION

For this application, the chosen software Microsoft Access has proved to be very user friendly, which was the main idea of the situation. The database shall be stored on my client’s office computer, and he shall take regular backups of the data on compact discs. The new system shall be used parallel to the old paper based one and after my client is satisfied with the database then he shall completely switch over to the new system.

Effectiveness of the solution

1. All the data is completely managed on the computer and can be printed out, thus removing the need for any hand written or manual work.

2. The calculations performed on the forms are giving correct and accurate results and thus data integrity is maintained.

3. Validation checks and input masks ensure that the data being entered is correct and valid. Also presence checks ensure that the required fields have data in them.

4. Searches can be performed on forms so that records can be found easily and very fast. This reduces the time taken work to be done.

5. The interface is very user friendly and the forms all have pictures on the buttons to make sure that the user knows exactly how to navigate through the system and operate it correctly.

6. The switchboard has been set to open on startup and it provides a convenient interface for navigating through the various reports and forms.

7. I have not included too many graphics as these tend to make the work area very cluttered and this may distract the user.

8. The reports can be printed at the press of a button and all the desired outputs of the new system are working correctly.

Effectiveness with reference to criteria for success

º All the sections of the business have been put together in this computerized system

º Data entry has been made easy and the data entered is checked to see if it is valid or not

º All calculations provide correct values for costs

º Search buttons on the forms allow the user to easily search through the form and find records that he wishes to see

º The formulae for the calculations are correct as the values obtained in the test data match with those automatically calculated

º The Human computer interface has been made very suitable to my client’s requirements, and user friendliness has been observed throughout the system

º Printing of reports is easy, and forms can be printed out too

º My client has seen the new system and is satisfied with the way it works.

Effectiveness of MS ACCESS

It is a very user-friendly package, and a very good package for beginners as it is robust –not too many rules, syntaxes and limitations. Common mistakes made by beginners are taken in and automatically corrected. It has several features that make it user-friendly for the client and me. The on-line help, in particular, I found to be very useful.

The software is object oriented. This allowed me to think in terms of the forms, queries and reports and not so I did not have to worry about how to code the software. The same could be said for the various Wizards and the Autoform feature, which constructed forms and reports at the touch of a button. The command button wizard provided an intuitive way of creating form buttons for easy navigation.

I think perhaps the most useful feature of all was the Properties Box. I found that most of the specifications I required, or the changes I wished to make could be performed using the Properties Box. For example, I used it to ensure that the formatting of each form was uniform, to specify the validation rules and texts and to set the tab index.

Interface

I feel that my interface is very user friendly. I have placed command buttons wherever possible and have made sure that they are visible to anyone using the database. I have used color schemes to segregate forms of different categories. I have tried to make the colour schemes and backgrounds match with the purpose of the forms. The reports have fewer colours so that when the reports are printed they do not consume a lot of ink from the printer. This makes the system very economical as well. My switchboard improved navigation within the database.

Weaknesses & possible improvements of system

My system does not have any specific weaknesses, for almost all my criteria have been met, and I have made a fully working system. Possible weaknesses of my system are:

º In the orders form, the user cannot add more orders in one record, although such a situation does not occur here.

º No facility has been made in order to sort the records, for example, sorting the employees’ records with increasing salary

º The system does not display a message when the due date for shipping goods has reached

Thus, the following improvements could be made to my system:

º Make an orders subform in the main orders form to allow the entry of more orders

º Create more queries to allow better sorting of data.

º Make the system display a message when the current date is near the required date of an order.

Backup

I have not been able to create a systematic method for the backup of the database. This is another weakness of my system. This is essential to avoid serious problems for the business in the event of the database being deleted due to hard disk failure, accidental deletion etc. I have decided to train the end-user to copy the database periodically. The computer in their office has WinZip software on it so the most convenient and least space-consuming way for my client to save her the database will be to copy it into a WinZip archive. A copy of this archive can then be stored firstly onto a different area of the hard disk and secondly on to an external medium such as a compact disk. The only problem with this is that the user will need a CD –RW drive, which he currently lacks.

Conclusion

The experience as a whole of engineering a solution from its origins has been invaluable and deeply satisfying on several levels. Academically, I have gained a great deal of knowledge about working with databases, file handling and the user interface; I am now able to look more critically at other packages and how they are engineered. I had not had much experience of database management packages or Microsoft Access, but I am now more confident in this field. On a personal level, I have derived satisfaction from a sense of achievement, the feeling that the time and effort invested in the solution have reaped their rewards. My client too is impressed with the new system and I feel proud to be a student in this field.