Creating a database - Mr Parkes wishes to have a computerised order processing system.
TABLE OF CONTENTS
STATEMENT OF THE PROBLEM
RS Supplies in London is a small business which specialises in the import and export of specialist medical equipment for hospitals as well as for other independent businesses. At the moment all orders have to be processed manually including invoices as there is no order processing system. However, the clientele is growing and Mr Parkes wishes to have a computerised order processing system. He has a computer which is used for typing up and storing invoices, but feels that a computerised system for processing orders and producing invoices is needed.
Specifically he would like a system which will:
- Hold details of customers and stock;
- Process and calculate orders;
- Print and store invoices;
- Hold and check customer payment details
Mr Parkes was interviewed to find out more detail about the proposed new order processing system.
Any orders for £10,000 or less will have been paid in advance by customers, and an invoice will follow the payment. Orders for more than £10,000 require instructions from the customer, therefore in invoice will be sent to the customer and the payment will follow this.
Supplier orders and payments
The ordering of products (stock), and payment for them, will not form part of the new system. This will be handled manually by the user.
Integration with existing system
Each month a record of all payments from customers (in the form of invoices) will be stored in an archive file. This file will be deleted after being copied to an archive file in the existing system. This process will be repeated at the end of each month.
Hardware and software
Mr Parkes currently has a Pentium 3 500MHz PC with 64MB RAM, 10Gb hard disk and an inkjet printer. He uses Windows 98 as the operating system and has Microsoft Office XP installed on the system. This hardware will be sufficient enough to implement a system.
The new system will be developed on a college network and on a desktop similar to the one described above. All files will need to fit on a 3½” floppy disk for easy transportation between home and college, and some files may need to be compressed. The college network uses 800MHZ Pentium Dell PC’s with 128MB RAM and are connected to both a laser and inkjet printer.
A comparison of available solutions and software packages can be found in the Design Section.
User’s skill level
Mr Parkes uses his PC for word processing and calculating orders as well as using the internet and E-mail. He is familiar with Internet Explorer, Word and Excel, but has never encountered Access.
Data flow diagram
The following data flow diagram illustrates the process of entering orders, invoicing and payments
Objectives of the new system
The system must perform the following functions:
- Allow customer details and orders to be entered and edited easily and quickly
- Allow invoices to be calculated, created, stored and printed
- Allow payment details to be entered and stored
- Security of data via passwords and backup of data
- Allow the user to search for specific products
This is a preview of the whole essay
The following are performance indicators:
- The system must be flexible and versatile enough to hold any number of orders/invoices for a particular client.
- Invoices should be calculated and printed in under 2 minutes.
- The system should backup the information quickly and must be accessible easily.
- It must take no longer than 10 seconds to search for a particular product.
- It must not take longer than 5 seconds to search for the details of a particular customer.
- The system must be able to allow the user to contact a client for any purpose by phone or e-mail.
Consideration of solutions
There are a number of methods of developing the new system.
- 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.
- 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.
- 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.
- 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.
- 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.
There are four entities in the database related as follows:
The four tables are defined as follows:
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.
All processing of data will be carried out using queries which are run by macros on specific forms as described below:
- 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.
- 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.
- 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.
- CUSTOMER QUERY This query uses the same expression as that for Payment
Query to display the contact details of a specific customer.
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
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)
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:
- INVOICE FORM
- BACKUP FORM
- PAYMENT FORM
- ADD/EDIT CUSTOMERS
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.
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.
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 data set 1: Customer Table
Test data set 2: Invoice table
Test data set 3:
Test data set 4:
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 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.)
- 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.
- It takes around 1-2 minutes to calculate, view and print a customers’ invoice.
- The system is able to backup the tables, which hold important information, easily and can be accessed quickly as well.
- It takes a maximum of 5 seconds to search for a particular product.
- It takes around 5 seconds to search for the details of a customer, whether it’s payment details or contact details.
- The system allows the user to contact a customer by both telephone and e-mail.
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
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
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.
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.
This is done by clicking on CLOSE on the main menu. However, this doesn’t close Access.
You should backup the database to a floppy disk at regular intervals, and store the disk in a safe place.
This manual is for more experienced users of Access as this allows the user to maintain or enhance the system.
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.
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.