Creating a database - Mr Parkes wishes to have a computerised order processing system.

Performance Indicators

The following are performance indicators:

1. The system must be flexible and versatile enough to hold any number of orders/invoices for a particular client.
2. Invoices should be calculated and printed in under 2 minutes.
3. The system should backup the information quickly and must be accessible easily.
4. It must take no longer than 10 seconds to search for a particular product.
5. It must not take longer than 5 seconds to search for the details of a particular customer.
6. The system must be able to allow the user to contact a client for any purpose by phone or e-mail.

DESIGN

Consideration of solutions

There are a number of methods of developing the new system.

1. Manual system. This would be very tedious as invoices have to be word processed, printed and then kept in a folder for future reference. Clients would have to send an order by fax only and not over the phone, which  means that the user would have to look up products and their prices in catalogues which would be very time-consuming. The calculation of invoices would be a lengthy process and there may be a lot of errors.
2. Spreadsheet system. This would allow Mr Parkes to keep details of customers, products and payments on separate sheets. The calculation of invoices would be made easier to some degree. However, it would take a long time to enter data and the format of reports is much harder in Excel as well.
3. A purposely designed system. This would obviously have a lot of advantages as the system would cater for every aspect of the business’ activities and functions. However the cost of this kind of package is very high and Mr Parkes would have to think twice before going ahead with this.
4. Existing system. The existing system doesn’t have the ability to calculate, store and print invoices and consists of a very lengthy procedure (as stated in Manual system above), so it would not be appropriate.
5. Access database. The new system can be developed using Access, as it is a relational database which is currently installed on the user’s computer and on the college network.

Final choice software

This system will be implemented using Access XP. This package is suitable as it has many features which can be used in developing the system, such as:

• The ability to link tables via a primary key and foreign key;
• The ability to create a menu by using the “switchboard” function;
• The use of macros to automate certain tasks;
• The use of mail merge to create standardised documents;
• The ability to set a password to prevent unauthorised access;
• Facilities which allow the creation of reports which can be formatted as required, and previewed before printing.

Database design

Entity-relationship diagram

There are four entities in the database related as follows:

Table design

The four tables are defined as follows:

CUSTOMER TABLE

PAYMENT TABLE

PRODUCT TABLE

INVOICE TABLE

Form Design

There will be four forms for data entry:

CUSTOMER FORM        This form allows the user to add, edit and delete customer details, as well as allowing the user to contact a customer using an AutoDialer.

PAYMENT FORM        This allows the user to ‘import’ customer details from the Invoice form, enter the total amount of a customers’ order, and store the data in the Payment table.

INVOICE FORM        This allows the user to enter the details of a customers’ order(s), such as the product and its price etc. It also allows the user to create and print an invoice for any customer. The user can contact customers via e-mail through the use of a hyperlink to the e-mail client.

PRODUCT TABLE        This allows the user to add, edit and delete products.

FORM

Query design

All processing of data will be carried out using queries which are run by macros on specific forms as described below:

1. Query1        When an order is entered through the Invoice form, the data is stored in        

                  Invoice table. From this table, the data is sent to the query, which

                allows the user to search for a specific order and check the details.

                This is possible because an expression is entered in the criteria section

                of the Customer ID field in the query.  

                Invoices are created and printed from this form via a report with the

                same name as the query.

2. ProductQuery        The user can search for specific products using the PRODUCT

CODE. The following expression will be entered into the criteria section of the Product Code field of the query:

[ENTER PRODUCT CODE]. When the query is opened, a dialogue box will appear asking the user to enter the product code, after which the query appears displaying the relevant data.

3. PAYMENT        This allows the user to search for the payment details of

QUERY                specific customers using their CUSTOMER ID.

                        The expression: [ENTER CUSTOMER ID], is entered in the

                        Criteria section of the Customer ID field in the query. This

                        displays the total amount of the order.

4. CUSTOMER QUERY        This query uses the same expression as that for Payment

Query to display the contact details of a specific customer.

Report design

Reports are used to produce the invoice as they can be customised by the user.

Query1        This report is used to create the invoice. It is made from the query of the same name. The invoice structure consists of the records for each product a customer

        

Macro design

Macros are used for opening and closing forms, queries and reports, as well as ‘importing’ data from one form to another, backup of tables, message boxes and record navigation.

QTYmacro                This is a macro which imports the Customer name and ID

from the Customer Order Form to the Payment Form.

(This is run when the user clicks on “IMPORT CUSTOMER

DETAILS” on the Payment Form, but only runs when the

Customer Order Form is open and minimised)

MSG11                This macro displays two dialogue boxes when certain

                        conditions in the design of the macro are satisfied. This

                        macro is attached to the “Total” field on the Payment Form.

                        (This macro is run when the user enters an amount in the

                        “Total” field, which is less than £10,000 or greater than

                        £10,000 and then clicks on “CHECK PAYMENT”)

BACKUP INVOICE TABLEmacro        This macro is used to backup data

                                                from the Invoice table to a database on                                                a floppy disk.

                                                (This macro is run when the user clicks                                                on “BACKUP INVOICE TABLE” on                                                 the Backup Form. There are three more                                                macros like this which copy the tables                                                in Table design – above – to the floppy                                                disk)

Menu design        

The menu will be created manually. Below are the notes on the implementation of the menu:

There will be six buttons on the main menu which open the following forms:

• ProductQuery
• INVOICE FORM
• BACKUP FORM
• PAYMENT FORM
• ADD/EDIT CUSTOMERS
• CLOSE

The main menu will appear automatically when the database is opened, and the main database window will be hidden. The application will show the company name and logo in the top left-hand corner (Title bar) of the application window. This will be done using TOOLS, STARTUP.

Security

The database will be password protected as it is vital that only authorised personnel have access to the database. All printed invoices will be stored in a separate file and kept safely in an archive, therefore fraud/tampering with documents can be avoided.

Test Strategy

The test strategy will include the following:

• The testing of each form, macro, report query;
• Testing the functionality of the system i.e. each menu item, command button;
• Checking the system by running through a series of tests;
• Establishing whether or not the system meets the end-user’s requirements.

Test plan

Test data set 1: Customer Table

Test data set 2: Invoice table

Test data set 3:

Test data set 4:

User testing

The system will be tested by the end-user. This testing may reveal some functions and features of the system which do not work as the user would have liked them to.

IMPLEMENTATION AND TESTING

Test results

Test 1: Test password/startup

                

(Only “147” accepted)

(Main menu opens after correct password is entered, displaying company name and logo on title bar)

Test 2: Test Main Menu options

(All forms open, application closes when “CLOSE” is selected – see screenshots in User Guide.)        

Test 3: Delete customer record for “DJ001”

(Record deleted after “DELETE RECORD” was selected)

Test 4: Check that totals are calculated correctly by opening Query1 for “AP001”

The invoice is calculated by selecting the required products from the combo boxes, entering the quantity required of each product, then clicking in the Total field to calculate the total for that record. Each successive product is entered by the same procedure, except that a new record has to be added for every new product which is to be entered. After this, the following is to be done:

 

The above screenshot shows the entry of the Customer ID “AP001”, in the dialogue box which appears after “MAKE INVOICE” is selected.

 Below is the screenshot of the records for “AP001” which appear in Query1:

(Query1 opens showing correct records and totals for “AP001”)

Test 5: Check Invoice details for “AP001”

The above screenshot shows the entry of the Customer ID “AP001” when “VIEW INVOICE”  is selected.

Below is a screenshot of the Invoice for “AP001” in print preview:

(The Invoice opens in print preview showing the total for the whole order including VAT.)

Test 6: Check Print Invoice function for “AP001”

The above screenshot shows the entry of the Customer ID “AP001” when “PRINT INVOICE” is selected.

Below is a screenshot of the printing dialogue box after the Customer ID has been entered.

(The Invoice for “AP001” prints correctly)

Test 7: Check AutoDialer for D. JACKSON

The above screenshot shows the automatic entry of the phone number to be dialled.

This is done by clicking on the number to be dialled and then clicking on the “phone” icon.

Below is a screenshot of the number for D. JACKSON being dialled

(Number dialled correctly)

Test 8: Check E-mail hyperlink

The above screenshot shows the hyperlink for

The screenshot below shows the dialogue box for Dial Up Networking

This screenshot shows the Hotmail Sign-In page

(Correct web page opens)

Test 9: Backup of Invoice Table

The above screenshot shows the ControlTip text “BACKUP OF INVOICE TABLE”

which, when selected, copies the invoice table to a database on a floppy disk.

(The backup is successful and no error messages were displayed)

Test 10: Check Import of customer details for “AP001”

The above screenshot is of the Payment form displaying the details of “DJ001”

Below is a screenshot of the Invoice form from which the details of “AP001” will be imported.

The Invoice form is minimised and the “IMPORT CUSTOMER DETAILS” button on the Payment form is selected to perform the operation, as shown on the next page.

The above screenshot shows the imported details for “AP001”

(The details were imported successfully)

Test 11: Check “CHECK PAYMENT” function for “AP001”

The above screenshot shows the dialogue box “PAYMENT CHECK”.

(The dialogue box appears displaying the message “CONTACT CUSTOMER FOR INSTRUCTIONS”)

Test 12: Test Quit Application

(Application closes when “CLOSE” is selected and the above screen is displayed.)

EVALUATION

Performance criteria

1. The system can hold any number of orders for a particular client because each client has a unique CUSTOMER ID which distinguishes one customers order from another customers order.
2. It takes around 1-2 minutes to calculate, view and print a customers’ invoice.
3. The system is able to backup the tables, which hold important information, easily and can be accessed quickly as well.
4. It takes a maximum of 5 seconds to search for a particular product.
5. It takes around 5 seconds to search for the details of a customer, whether it’s payment details or contact details.
6. The system allows the user to contact a customer by both telephone and e-mail.

USER MANUAL

Introduction

This application is designed to:

• Hold details of customers and their orders;
• Hold details of products;
• Create and print invoices;
• Allow the user to search for both customers and products.

It is designed to run on a PC with Windows 98 as its operating system and needs Access XP to be installed. It requires around 2 Mb of disk space to store the application and at least the same again to store data.

Starting the system

Load Access XP and from the menu select File, Open. The database is called

RS Supplies.

Security and passwords

You will be asked to enter the password on opening the database. The password is currently 147, but you should change this immediately by opening the database in OPEN EXCLUSIVE mode, then select Tools, Security, Unset Database Password.

The main menu

When the correct password  is entered, the main menu will open, as shown below:

The Invoice Form

The Backup Form

The Product Query

(Product Query for product code “C5073”)

The Payment Form

The Customer Form

Invoice Form

Selecting this form will bring up the following:

Calculate and print invoices

The following steps are used to calculate and print invoices:

• The combo boxes on the right-hand side are used to enter information in the corresponding text boxes on the left-hand side;
• The quantity of a product is typed in manually;
• Once all data has been entered, click on the TOTAL text box;
• This calculates the total for the chosen quantity and adds VAT at 17.5%;
• If more products need to be added, simply click on NEW RECORD and enter the CUSTOMER ID, then carry on as above;
• Once the order is complete, go back to the first record and click on MAKE INVOICE. You will be asked to enter the Customer ID. After this, the query opens, check the data, then close the query.
• You will return to the Invoice Form, where you click on VIEW INVOICE to view the invoice in print preview;
• Once you are satisfied with the invoice, close the report window and click on PRINT INVOICE, which will print the invoice.

Payment Form

Payments

The following steps are used to keep track of payments:

• Once the invoice has been printed, open the PAYMENT FORM from either the INVOICE FORM or the main menu;
• Click on IMPORT CUSTOMER DETAILS to ‘import’ the customer name and ID from the Invoice form (the invoice form must be open for this operation to work);
• Enter the total amount of the order in the TOTAL field;
• Click on CHECK PAYMENT to determine whether or not a payment has been made for that order.

Quit Application

This is done by clicking on CLOSE on the main menu. However, this doesn’t close Access.

Backups

You should backup the database to a floppy disk at regular intervals, and store the disk in a safe place.

TECHNICAL MANUAL

This manual is for more experienced users of Access as this allows the user to maintain or enhance the system.

Macros

The screenshot of the macro MSG11 gives an overview of the message box macro:

This macro is attached to the On Click event property of the “CHECK PAYMENT” button on the Payment form

The above two screenshots show the macro MSG11 in design view. There are two different conditions which display two different messages.

The screenshot of the macro QTYmcr gives an overview of the SetValue macro:

This is a SetValue macro which ‘imports’ data from the Invoice form to the Payment form.

This screenshot of the BACKUP INVOICE TABLEmacro gives an overview of the copy object macro:

This macro is used to copy the INVOICE TABLE from the database

RS Supplies.mdb to the database BACKUP.mdb on the floppy disk.

The NEW NAME field in the arguments section of the macro window (shown above0 is left blank as this allows the object being copied to have the same name when it is copied or restored.

Queries

The following query, Query1 is used to calculate invoices:

The following query, ProductQuery is used to search for a particular product:

The following query, PAYMENT QUERY is used to search for the payment details of a particular customer:

The following query, CUSTOMER QUERY is used to search for the details of a particular customer.

(3,746 words)