Pedro’s Home Maintenance Services
Table of Contents
TOC \o “1-3″ \h \z \u Introduction
Database Design
Service Table
Customer Table
The Invoice Table
Form Design
Invoice
Query
Query 1 Amount Earned Per Customer
Query 2: Pedro’s Earning Per Service
Query 3: Remaining Balance Per Customer
References
Table of Figures
TOC \h \z \c “Figure” Figure 1 Service Table Design
Figure 2 Service Table Data
Figure 3 Customer Table
Figure 4 Customer Table Data
Figure 5 Parent Table: Invoice Design
Figure 6 Child Table: Invoice Details Design
Figure 7 Relational Diagram
Figure 8 Service Maintenance Form
Figure 9 Customer Maintenance Form
Figure 10 Initial Invoice Table
Figure 11 Invoice Form and Subform
Figure 12 Invoice: Parent Table Data
Figure 13 Invoice Details: Child Table Data
Figure 15 Query 1 Relationship
Figure 16 Query 1 Result
Figure 17 Query 2 Relationship
Figure 18 Result of Query 2
Figure 19 Query 3 Table Relationships
Figure 20 Query 3 Result
Introduction
Due to the different changes that are going on in the current business environment, together with the growing importance of information in the entire business process, database is considered as a must, in order to maintain competitive advantage for many organizastions and companies. A database is a collection of information, which allows information to be gathered and organized quickly and easily. Thus, database information can be formatted and printed as a report (Wix & Price 2004, p. 1).
This paper present the development of Pedro’s Home Maintenance Service’s system in handling the data and information of their customers, together with their transactions.
Database Design
Service Table
Figure SEQ Figure \* ARABIC 1 Service Table Design
The service table is consists of three important field of attributes with accordance to the current business rule of Pedro’s. Thus, the table can now handle data regarding the service and the hourly rate. The service_code is the primary key of the table. It is the key field that exclusively identifies the table throughout the database structures (Hernandez 2003, p. 261). Thus, primary key is considered as a vital aspect in maintaining the relationships between tables in the entire database (Williams & Lane 2004, p. 146).
Figure SEQ Figure \* ARABIC 2 Service Table Data
That is the reason why it is very important for the primary to have the unique value. Figure 2 shows the data of Pedro’s Service Table, when the owner decided to add new service, the dog grooming. Both the dog walking and dog grooming can have the service code of PET, however, due to uniqueness that is required by primary key, it was named as PETG. In entering data, it can be observe that after reopening one table, all of the fields or data, are arranged alphabetically based on the primary key. The said process is called sorting.
Customer Table
Figure SEQ Figure \* ARABIC 3 Customer Table
Recording information regarding the customers is considered as one of the most important aspect in any business. The table is consists of the primary key, the name of the customer, the two lines of address, the postal code, the contact person, and the contact number. All of the said information is important in the entire process and transactions inside Pedro’s. The entire field size and data field of each column was changed with accordance to the characteristics of the data to be entered. This is important, not only because it can help to save space, and speed up the process, but also to enable Access to stop someone from entering a value that may not be fitted on a given form (Prague, Irwin & Reardon 2003, p. 61).
Figure SEQ Figure \* ARABIC 4 Customer Table Data
The Invoice Table
The invoice table can be considered as the most crucial table in the database, because it is connected or related to the first two tables. Aside from that, it is important to consider that one customer can have as many services as he or she wants. That is why it will create a one-to-many relationship, where in one row in one table is connected to many rows in another table (Welling & Thomson 2003, p. 173). This can be observed in the table of invoice, where in it will be connected to another table that will show the details regarding individual transactions. Thus, creating a child-parent relationship, where in the child table is completely dependent on the parent table and the child table can only exist if its parent table does (Powell 2006, p. 8). Figure 5 and 6 shows design of the parent and child invoice tables.
Figure SEQ Figure \* ARABIC 5 Parent Table: Invoice Design
Figure SEQ Figure \* ARABIC 6 Child Table: Invoice Details Design
It can be observed that the tblInvoice holds the primary of the tblCustomer, and the tbl_Invoice_Line_Details holds the primary key of the tblInvoice and tblService. All of the said rows or columns are called the foreign key. Foreign key is one or more columns in a table whose values match the values in one or more columns in another table, and the value in the foreign key of the first table is commonly from the primary key of the second table (Sheldon 2005, p. 120). The process of declaring the foreign key relationship will help to prevent from getting a referential integrity violation in the database, thus ensuring that the end-user of the system cannot put a row in the fact table that does not have a corresponding row in all of its related dimension tables (Kimball & p. 334). Figure 7 shows the entire relationship or connection between the different tables.
Figure SEQ Figure \* ARABIC 7 Relational Diagram
Form Design
After creating the database and tables, forms can help the ease the task of the end users in entering the data (Cronan 2007, p. 26). Figure 3 shows the maintenance form for the service of Pedro’s.
Figure SEQ Figure \* ARABIC 8 Service Maintenance Form
Figure 10 and 11 shows the maintenance forms that was created using wizard. This form will enable the end-user of the system to enter the maintenance data for further expansion of Pedro’s. The said forms are important because it gives a room for improvement of the system. It will help to add new lines of services as well as customers, without hassles. This is because the data that are stored in the two tables are data that can change overtime.
Figure SEQ Figure \* ARABIC 9 Customer Maintenance Form
Invoice
The invoice form is the most difficult form to create. This is because it is a combination of two forms, and it there is a need to use different functions in order to retrieved other data from the database. Using the Wizard, both the parent and child invoice will be converted into form. The parent table will be created as columnar form, and the child table will be in tabular form. Figure 12 shows the initial design of the invoice form.
Figure SEQ Figure \* ARABIC 10 Initial Invoice Table
Then the two forms will be integrated in order to make it easy to input data into the database. The result can be seen in figure 13.
Figure SEQ Figure \* ARABIC 11 Invoice Form and Subform
Based on the said initial design, further improvements were added in order to make the system more user-friendly. It focuses mainly on the usage of the different controls such as textbox, combo box as well as the benefits of clear and readable labels. All of the labels were altered into more meaningful prompt to the users, because it will serve as the instructions for the users to input the correct information to the database. All of the unimportant fields in the subform were deleted, primarily the Invoice_Line_No and Invoice_No because both field will be given by the system and must not be altered. Above all, some of the textboxes were replaced by combo box control such as the customer_code and service_code. It is because the combo box helps to presents a list from which the values can be chosen, however the list is not shown on the form, until the user ask to see it (Whitehorn & Marklyn 2005, p. 317). As a result, it can help to save space, at the same time, help the end-user to save time by remembering the details that are related to the said field, thus giving them the choice, at the same time preventing errors. One advantage of MS Access is that it can change control type, by the menu change to. The combo box was populated by inputting the SQL query in the row source property.
It can also be observed that picture or image was added to the form. This is to add attractions towards the end user that will help to awaken his or her interests. In order to copy the current design, as well as the standard format of invoice, it will be important to remove the navigation button from the subform. This was done by altering the format of subform by opening the form property menu, and changing the navigation buttons to NO. This will help to make the form more professional and look more integrated.
Aside from that, new fields or textboxes were added in the form, in order to show additional and important details such as the subtotals and the grand totals per each invoice. One of the major dilemmas is how to retrieve the value of the rate per hour of a service that was chosen from the service_code combo box. In order to solve the said problem, DLookUp() function was used.DLookUp() is being used in a query in order to show values found in a table that is not included in the query (Groh & Stockman 2007, p. 467). Expression 1 was used in order to retrieve the value of the service that was chosen by the end user in the combo box. The general syntax of DLookUp is DLookUp(“[Field]”, “[Table]”, “<Criteria>”).
Expression 1:
=DLookUp(“[Hourly_Rate]“,”tblServices”,”[Service_Code] =’” & [Service_Code] & “‘”)
In order to computer the amount per transaction, Expression 2 was placed in the control source of the textbox to handle the amount. Thus, in order to get the total amount or grand total, Expression 3 was applied.
Expression 2:
=[Hours]*[Text11]
Expression 3:
=Sum([Hours]*[Text11])
Aside from that, command button was placed in the form in order to add new customer details per transaction. This was done by using the command button wizard.
Figure 12 and 13 shows the initial transactions of Pedro’s in invoicing.
Figure SEQ Figure \* ARABIC 12 Invoice: Parent Table Data
Figure SEQ Figure \* ARABIC 13 Invoice Details: Child Table Data
14 Improved and Final Invoice Form
Query
Retrieving the data is considered as the most important aspect of any system. A system cannot be considered as feasible or helpful if it cannot produce report or cannot help the end-user to extract the information that are necessary in order to analyze the performance of the system user, that can help to the process of decision-making.
Query 1 Amount Earned Per Customer
Query 1 extract the list of the dollar value of the sales for each customer for November 2008, sorted in descending manner.
Expression 4: SQL: Query 1
Figure SEQ Figure \* ARABIC 15 Query 1 Relationship
Figure 15 shows the relationship between the tables in the query. This is because, all of the table are important in order to retrieve information. The group by clause was used because the need to use aggregate function (sum) is inevitable. It is used together with aggregate functions in order to combine groups of record or data in a single record (Allison & Berkowitz 2005, p. 116). This is important because Pedro wishes to view the result that is grouped by the customers. In order to arrange the result in descending manner, it will be important to use the clause order by. Aside from that, it will also be important to focus on criteria that the data to be retrieved had happened in November 2008. Figure 16 shows the result of the query.
Figure SEQ Figure \* ARABIC 16 Query 1 Result
Query 2: Pedro’s Earning Per Service
The second query retrieves data that shows the total earnings of Pedro per services.
Expression 5: SQL – Query 2
Figure SEQ Figure \* ARABIC 17 Query 2 Relationship
The same clauses and approaches were used in this query, but the data were grouped by service. In addition to the criteria, because Pedro, wishes to retrieve data about the total earnings per service, it will be understandable that the transaction were already paid by the customer.
Figure SEQ Figure \* ARABIC 18 Result of Query 2
Query 3: Remaining Balance Per Customer
Expression 6: SQL – Query 3
Figure SEQ Figure \* ARABIC 19 Query 3 Table Relationships
The same clauses and approaches were used in this query, but data were grouped by customer, summed up the remaining balance or unpaid bills per each customer. Figure 20 shows the result of the query.
Figure SEQ Figure \* ARABIC 20 Query 3 Result
References
Allison, C & Berkowitz, N 2005, SQL for Microsoft Access, Wordware Publishing
Cronan, J & Sandberg, B 2007, Microsoft Office Access 2007 Quicksteps: Quicksteps, McGraw-Hill Professional
Groh, M, Stockman, J & Prague, C, Powell, G, Reardon, J, Irwin, M 2007, Access Bible, John Wiley and Sons
Hernandez, M J 2003, Database Design for Mere Mortals: A Hands-on Guide to Relational Database Design, Addison-Wesley
Prague, C, Irwin, M & Reardon, J 2003, Access 2003 Bible, Cary N. Prague
Powell, G 2006, Beginning Database Design, Wiley and Sons
Sheldon, R & Moes, G 2005, Beginning MySQL, Wiley and Sons
Welling, L & Thomspon, L 2003, PHP and MySQL Web Development, Sams Publishing
Whitehorn, M & Marklyn, B 2003, Accessible Access, Springer
Williams, J & Lane, D J 2004, Web Database Applications with PHP and MySQL: Building Effective Database-Driven Web Sites, O’Reilly
Wix, J & Price, C 2004, Information Technology: Certificate 1, 2003, Max Johnson
Credit:ivythesis.typepad.com
0 comments:
Post a Comment