Note: I will put the 3 ‘type’ options, as well as the ‘emirate’ in a combo box when I create the form later. A combo box is a drop down list where the user can select an option, not having to manually type it in. The validation rule is still necessary because the user is still able to key in anything he wants regardless of the options in the combo box.
I shall now go on to put in the input masks for the table. The input masks I plan to place are shown below.
It is not necessary to capitalize the ‘sex’ and ‘emirate’ fields, since these will be combo boxes, and the user will only have to select an option which is keyed in beforehand, when designing the database.
Jobs Table:
The next table that I shall create will be the jobs table. This shall contain information on the different jobs that are to be done. The different fields and their description are given in the tables below.
For this table I will create the following validation rules.
The next table shows the different input masks I shall place for the fields.
Note: I did not need to create input masks for the Product ID, Customer ID and Employee ID fields since their input masks are created in their respective tables. Theses fields will be linked using ‘relationships’. This means that the data will be masked and appear in a single form. The fields are only needed so that 2 tables can be linked using a relationship. This will be explained when I finish creating all my tables.
Products Table:
The next table that I will create will be the Products Table. This shall contain information on the different hardware equipment to be sold. The different fields and their description are given in the tables below.
Below are the validation rules I will use.
The table below shows the input masks I will place for these fields.
Note: As for all normal text entries such as names, I used the input mask to capitalize the first letter. This is to make the data look more professional on reports. Also, for the ‘Manufacturer’ field, which holds the manufacturer of the product, I will create a combo box as I will for the ‘Emirate’ field in the ‘Customer Details’ table when I design the form later on. This is so that the user can just choose a manufacturer from a list rather than manually typing in the data.
Suppliers Table:
The next table that I shall create will be the Supplier Table. This will contain information on the different suppliers. The different fields and their description are given in the tables below.
Below are the validation rules I will use.
The different input masks I will use are shown in the table below.
Employee Table:
The last table that I will create will be the Employee Table. This will contain information on the employees in CITY CROWN COMPUTER Computers. The different fields and their description is given in the table below.
The validation rules I will use are shown below
The different input masks I will use are shown in the table below.
Forms:
To make data entry easier, I shall design forms for all the tables. This will allow the staff to enter data easier and faster. Also presentation of data on a form looks much more attractive than on a table.
Forms will retrieve the data from tables and present them in a more comfortable way to the user. The user can navigate through the existing records and make modifications. In a form one record is displayed at a time, unless the datasheet view is chosen, where all the records will be displayed similar to a spreadsheet view.
Forms can be very easily created using the form wizard. In a matter of a few easy steps, a professional looking form can be created. I can choose from a range of different preset views, and specify which table I am creating the form for. The design of the form can be customized using the form’s design view. This will enable me to manipulate the form’s appearance such as changing the font, colors, etc. I can also add additional controls to my form, and modify the existing ones.
The form option adds additional functionality to the database. I can use the ‘expression builder’ and ‘code builder’ to perform mathematical equations. I can also hide certain fields which I do not want to view, such as the ID fields in each table for example. I can also make use of combo boxes, list boxes, add frames to group similar related records to make data entry more appealing to the user.
Customer Details Form:
I will start by making the Customer Details form. A basic layout of how I plan to design the form is shown below.
Jobs Form:
The Jobs form is the link between the different tables. This is later described in relationships. A basic layout is shown below.
Products Form:
Suppliers Form:
Employees Form:
Switchboard Manager:
The switchboard manager will be my main menu. The menu includes a number of Command Buttons that link to various different forms, queries and reports, allowing the user to access almost the entire database through the Menu. To make links, I made use of the switchboard manager. It is more professional than a manually made menu form. The switchboard manager can be selected from ‘Tools’ – ‘Database Utilities’ – ‘Switchboard Manager’. I edit the main switchboard and add new switchboard ‘items’ to it which is a link to the forms, queries, reports, etc. Besides the ‘forms’ switchboard, I made a ‘Main Menu’ switch board which will link to all the other switchboards.
The design of the main menu (default switchboard) is shown below:
Reports:
My next task will be to create reports, as a lot of lists will be needed. Reports will allow me to create neat, presentable and organized lists. I will create a report using the report wizard. From the report wizard I shall choose the fields from the tables which I require in the report and in what way I want to group them. After this I will be given an option whether to group according to a certain field and then I will select the layout of my report, i.e. columnar, tabular or justified. Reports will be created as and when needed by the staff to create neat and professional looking documents for customers and suppliers.
A basic report layout is shown below:
Queries:
After I have designed the reports I will now go on to make queries I will do this using the query wizard. I will specify my conditions and then click finish. This will create a query for me within a matter of seconds. It is one of the most powerful and helpful options available in Microsoft Access. It is the most important option used when it comes to the quick and accurate retrieval of data. Being a database, each file in Access will contain a large number of records. The staff of CITY CROWN COMPUTER Computers will need to retrieve only a certain number of records from these huge files. In other words, the staff may want to filter certain records. Thus, in these situations, I will design a query search. A query is based on certain conditions and will thus display records that relate to those particular conditions. Thus, a query will filter out records in seconds. The filtering is also very accurate as the database will not overlook any records.
All queries will be created using the ‘query wizard’. The query wizard offers quick and simple methods to create a good query. First, I will have to specify the table I will be designing the query from. Other than selecting the main tables that I had created, I can also use fields from other tables. Thus I can use fields from one table to display fields from another table. This option is particularly helpful when I will create sub forms or reports using the queries that I have created. After selecting the tables, I will select the fields that I would want to include in my query table. As mentioned earlier, I can also use fields from various tables, usually those that are linked by a relationship. While selecting the query fields, I will have to make sure that I include the field that I will use and the fields from which the data will be displayed. Thus, I will have to select the field that I will have the condition in and the fields that will display the required data when the query is run. The last stage, before saving the query, is when I will have to specify whether I would like a detailed or a summarized query. A detailed query will display all the data that applies to the condition set in the query. However, a summarized query will present only a summary of the details derived from a query.
Below is a sample query design using the ‘Products’ table to show how a query is set up.
A sample prompt box is shown below:
Relationships:
A relationship is a link between two common fields in different tables. The fields can link from one single table to another, from one table to fields in many other tables or several fields from one table to several fields in other tables. The data type of both fields linked in a relationship must be the same. Usually two key fields of two different tables are linked to one another, with a one to many relationship, but of course this is not a must. A link between different fields between tables is also possible.
After creating all my tables, I can link them so that when I later create forms, queries and reports using my tables, I can have data from different tables on one form, query, or report. This prevents data from having to be duplicated. This is how relationships are quite useful. By establishing relationships the data in more than one different table can be represented at one time.
In the ‘tools’ menu, I select ‘relationships’, or simply choose it by selecting its icon. To display all the tables I click on the ‘Show Tables’ Option and select the tables which I want to establish relationships between. Next I drag the field that I want to relate from one table to the related field in the other table.
Validation and Test Plan:
In order to test that my validation rules work as expected, I have created a test plan in which I will make deliberate mistakes while entering the data into my records. While creating a database for any application, it is necessary to always test the data. In the database, I have used some validation rules. However, before I can implement on my system, I will have to make sure that all the validation rules work perfectly fine. This is where the task of the test plan comes in. While creating a test plan, I will prepare certain sets of data that I will assign to each validation rules used. Some data might be correct, whereas for some data I will deliberately introduce certain errors to check if the computer detects them.
The input masks are also an important form of validation because they prevent incorrect formats of data, such as the length of the data or even the data type. Therefore, I will also be testing my input masks that I have used in several places. I will purposely enter incorrect data in some places to see if the computer recognizes the error. If the error is recognized, it means that the data is not suitable for the input mask used.
