Data Processing Task. Mr Peters, the man who runs this particular Samsung store has approached me and asked me to investigate his current system of usage for keeping records of customers buying products from the store.
Data Processing task
4 Bridge Street
Banbridge Co. Down
BT323NB
Northern Ireland
Contents
Analysis
Background Information:
Page 5
Location:
Page 6
Introduction:
Page 7
Current System
Page 8
Problems with the current system:
Page 8
Objectives of the new system:
Page 10
Inputs, Processes and Outputs:
Page 11
Information Requirements:
Page 13
Quantative and Qualitive Objectives
Page 14
System Design
Page 16
Design
Entities with Attributes
Page
Possible Solutions
Page
Form Design
Page
Switchboard Design
Page
Report Design
Page
Calculations
Page
Mail Merge
Page
Queries Data Dictionary
Page
Macros Data Dictionary
Page
Testing
Test introduction
Page
Test Plan
Page
Results
Page
User Guide
Description of Package
Page
Hardware and Software Configuration
Page
Installation Guide
Page
Operating Instructions
Page
Switchboard
Page
Order Forms
Page
Macro Guidelines
Page
Reports
Page
Mail Merge
Page
Troubleshooting Guide
Page
Background Information
Samsung is a Korean company that has risen to prominence in many fields over the years. Samsung is a Korean company that has risen to prominence in many fields over the years. The size and the scope of the company is almost unimaginable, as they are one of the largest and most diverse companies in the world. While the company is mostly known for their technology division [cell phones, radios, MP3 players, computer screens, and the like] they have many other divisions as well.
The company has expanded from a humble storefront launched in the late 1930s to the largest company in Korea and the second largest company in the world. In addition to the electronics division, which is the most profitable and successful one in the world, the company also has forayed into finance, chemicals, retail, and straight-up entertainment-all with great degrees of success.
The company was once even larger than it is now, but the government, concerned about the power that the company amassed, required the company to drop some of its sub-divisions. Because the company is so large and powerful, they have been accused of bankrupting some other companies - or worse, making the market so that other companies don't stand a chance at the start. They are also very much so the leader in electronic components that other companies use to build products - and as such, can control the market value and inflate prices at will. This is why the government stepped in and attempted to somewhat limit the huge conglomerate's power and scope.
However, it is important to note that the company is responsible for at least 1/5 of the country's exports. As a result, they are in a position of power because of the money they supply to the government. Additionally, their vast number of employees are essential to the country's economy.
The company has been around for quite some time, and although there has been some controversy about their strength and power in South Korea, there is no denying that they benefit the economy greatly by providing jobs, exporting their services, and otherwise doing things that smaller companies would not be equipped to do. Samsung is a powerhouse of a company, with many people worldwide using their products, particularly the electronics such as cell phones and MP3 players. It's not surprising that the company continues to thrive in the ways it does.
Location
Map 1
The map below shows the location of 'Samsung' in Bridge Street in Banbridge.
Map 2
The map below shows the location of Banbridge within Northern Ireland.
Introduction
Mr Peters, the man who runs this particular Samsung store has approached me and asked me to investigate his current system of usage for keeping records of customers buying products from the store. He currently uses a manual system but he feels it will be much better if he uses a computer system to carry out these tasks.
He is interested in improving his record keeping, for example the way he stores his customers, his products and the customers order details. Recently the shop has done very well in business due to the latest product that rivals the iPad- The Samsung Galaxy Tablet. Since the customer base has increased it is far much more logical to computerise the records and it also offers a better service to the customers as they will have a print out copy of their orders rather than the more unprofessional way of just handwriting it.
This shop sells all the things you would expect to find in an electronic shop. It features TV's, laptops, cameras, phones and tablets. Mr Peters has currently ten members of staff in his business. There is now competition in this area with the opening of a new electronics shop not far away from where Samsung is situated. Mr Peters is very aware of this stiff competition and therefore he wants to improve the image of Samsung and improve the service that the company currently offers their customers.
I have been hired by company to take a look at the system. If I find that my results show that this current system isn't good enough I will have to think about computerising the system.
Is the Current System suitable?
* At the moment there is a normal manual system and this makes it very difficult to keep track of all the orders and it is extremely slow. If a customer wants to make an order they have to do so over the phone and so that's why I will incorporate email into the system.
* When an employee takes an order from a customer it is taken down on a piece of paper and it is filed away into a filing cabinet so it can always be tracked to. This sort of system means that sheets can be easily lost and misplaced as an employee could put the wrong record in one part of the filing cabinet.
* When the order is taken there is a receipt made out also with the price, order number and date written on and a copy is placed beside the original order and kept in the filing cabinet. The other copy of the receipt is given to the customer.
* Eventually over time with all these receipts building up there will be a lack of space in the office and in the filing cabinets and therefore some of them have to be thrown out. This is a problem as when some order sheets and receipt are thrown out they cannot be referred back to and this leaves the company in a bad position.
* At the moment the staff at the company has to add up all the weekly orders using a calculator and this takes a very long time. The manager wants a system that will automatically count the money that is taken in every week. They also want to see the money taken in for every week and track down the money that has been taken in previous weeks.
* The manager also wants a system that will see which product has sold the best over a certain week or a certain month. It has to be easily calculated with this new system. They want this as the way the system is now means that there is a lot of filing to be done and also there is a lot of calculations to be done and this can be very time consuming. This is why they want the new system as it will make life a lot easier.
* Over time technology has developed immensely. Having this type of system means that the company is automatically going to be lagging behind its competitors in terms of making sales. This system is also redundant as there is a lot of repeated entries that have no need to be there and they would be gone if the system was computerised
* The current system is very expensive to have and to keep running. For instance, there will be a need for pens to be replaced quite a lot. There will also be a need for other utensils such as paper clips, rubbers, pencils and staplers. The biggest factor and the biggest expense is the amount of paper that the company have to go through with this kind of system.
* There could be an invalid address given to a delivery man when he goes out to deliver the products and he will be left stuck with nowhere to go.
* If the delivery man delivers to the wrong address and he doesn't notice then the company will not realise until the customer phones in. This means that this could have lost an order and therefore could have lost the money from this order. There is no record of the product being received or sent until the customer phones in and this can have a very negative effect on the company.
* Many details are taken over the phone so when the employee is taken down the orders they will be rushing to get it all done. When they are rushing there is a higher chance they are going to make a mistake and if they make a mistake a wrong order might be processed to a customer and you could end up losing that customer for not being very trustworthy.
* When a new customer is added or a new order is taken it would be taken down using handwriting by either a blue or black pen. Other people in the company will obviously have different handwriting and they mightn't be able to read the other persons handwriting so this will cause confusion within the company.
* In the companies office the files are locked away in filing cabinets at the back of the store. A night there is an alarm system intact and this will try and catch anyone who wants the things in the filing cabinet. A professional burglar would most likely have no problem breaking into the shop and stealing from the filing cabinets as the locks could be very easily broken. There is also a realistic view that a fire could break out, deliberately or not, which could destroy all the companies' records. Since there are no back ups this could potentially keep the company closed for a long time and lose them a lot of money.
* When customer details are removed from the filing cabinets they would have to be placed back in the same order. All customer details are in alphabetical order and if one record is placed in the wrong order this could have a knock on effect to all the other customer details meaning they could then all be placed in the wrong order.
* Letters can also be sent to customers telling them of payments required or telling them of their order details. Using the current system these letters would be handwritten and this could lead to bad handwriting which the customer wouldn't be able to read. There could also be spelling mistakes and/or bad grammar used by the company and this will look disastrous for the company and make them lose customers in the long term.
What does the new system aim to do?
If a new computerised system where to be introduced it would have to shell out a few problems of the old system. Firstly there would be no more writing for the employees. Everything would be done on the computer using a database package. In the database there would be customer records and employee records. These records would be easily added to and edited. The records can also be deleted easily. This saves time over having to look for different files in filing cabinets and if something was to be deleted you would have to start the record over again. The system will also automatically be able to put data in to reports so data about a customer could be accessed easily and is laid out in an organised format of my choice. Another aim of this system is that an employee must be able to search for a particular customer and look at current orders and previous orders. This should be the same for products so if you went to search for a product you would be able to see the quantity.
Objectives of the new system
Having interviewed Mrs Bingham and having documented current working practices the following end-user requirements (system objectives) have been identified:
* All of the staff of the business will be using this system but obviously managers will use it more frequently. All of the staff have trained computer skills as they deal with computers every day but not a lot of them will be familiar with a database so therefore I have to make it user friendly. Documentation of the program will also be necessary in order to familiarise the user with the software.
* The user must be able to add new customer records, edit customer records, delete customer records and search for a particular customers record.
* The user must be able to add new employee records, edit employee records and search for a particular employee's record.
* The user must be able to add new products, edit products and search for a product within the database.
* The user must be able to add customer orders, edit customer orders and cancel orders when appropriate.
* If a customer is wondering when they will receive their order, a member of staff should be able to easily bring up their order and tell them the date they ordered it and when they should receive it.
* The user must be able to search for and view orders of a particular customer.
* The user must be able to search for and view quantity of a particular product.
* The user must be able to search for and view details for a particular order.
* The user must be able to generate reports including a list of all customers, orders and products..
* The user must be able to generate a report showing product demand to date for the current month and for the current year.
* The user must be able to create a mail merge for all customers within the business. The user should have the ability to alter the actual template or letter used. This may involve the user having access to a word processor with the ability to alter the letter and compose a new template.
* The switchboard should be programmed to appear from the moment that the system has been opened ...
This is a preview of the whole essay
* The user must be able to generate a report showing product demand to date for the current month and for the current year.
* The user must be able to create a mail merge for all customers within the business. The user should have the ability to alter the actual template or letter used. This may involve the user having access to a word processor with the ability to alter the letter and compose a new template.
* The switchboard should be programmed to appear from the moment that the system has been opened and everything else around it should be minimised to prevent confusion from inexperienced users.
* The user interface should have a professional look to it and easy to access feel and the Samsung logo should be included on system screens and reports.
Outputs required from the proposed system
* A report showing a complete list of all customers, the list should be sorted in alphabetical order and printed for the purpose of obtaining a hardcopy.
* A report showing a complete list of all products, the list should be sorted in alphabetical order and printed for the purpose of obtaining a hardcopy.
* A report showing a complete list of all orders, the list should be sorted in alphabetical order and printed for the purpose of obtaining a hardcopy.
* A report detailing the popularity of products for the current month sorted in order of most to least demanded product.
* A report detailing the popularity of products for the current year sorted in order of most to least demanded product.
* A mail merge letter informing all clients of upcoming special offers on Samsung products. Customer details such as title, name and address will be retrieved to create these personalised mail merged letters.
Inputs required in order to achieve the predetermined outputs
* Customer details (Customer ID, surname, forename, title, address 1, town/city, postcode, telephone number, balance £, balance (euro) )
* Order ID (surname, forename, address, town/city, postcode, home telephone number, mobile number, gender, date of birth, qualification, national insurance number)
* Order details (product name, product cost)
* Product details (name, colour, stock, price, image)
* The order forms of the customers to enable me to form queries, which in turn enable me to form reports.
* Information on all the specific customers that the company supplies to.
* I will then be able to form a table of information and allow me to use the mail merge function to post out the letters informing the customers of how much they currently owe.
Processes required to turn input into output
* Add customer details when a new customer orders a product.
* Edit customer details when they change address, surname or contact number
* A search is needed for customer details using client surname, needed to locate client details quickly.
* Add product details when a new product is issued to the company
* Edit product details if the price has been reduced due to sales or no business on the particular product.
* A search is needed for products using product id when you need to locate product details quickly.
* Add order details when a new product is ordered by a customer.
* Edit order details if the order is changed to maybe buy more things
* Delete an order if the customer doesn't want the product anymore.
* A search is needed for orders using Order id, needed to locate treatment details quickly.
* Client books an appointment, details recorded including date and time of the appointment, treatment name and the therapist responsible for the appointment.
* Edit order details when the customer requests a different order or a different product.
* Cancel an order when the customer wants to cancel without ordering something else instead.
* A search is required for all future orders for a particular customer based on customer surname. This is needed in the event that a customer forgets their order.
Information Requirements
* Information needed for the proposed system includes details of current customers, orders and products.
* Mr Peters currently stores all client details on paper in a cupboard in the back of the shop. This information will be entered into the new computer system. The new system will allow customer details to be entered into the computer directly without the need for a data capture form.
* Mr Peters currently stores all order details on paper in a cupboard in the back of the shop. This information will be entered into the new computer system. The new system will allow order details to be entered into the computer directly without the need for a data capture form.
* Product details are currently included in a small booklet that is available at the front desk of the store. This information detailing products will be entered into the new computer system.
* Outputs from the system can be viewed in two different ways:
* On screen (a soft copy) showing the results of a query or a report.
* Printed on paper (a hard copy) showing the contents of a query, report, or mail merged letter.
Quantitative Objectives
- The system should be able to add new records within one minute.
- Perform searches on the information stored within 20 seconds.
- Be able to update tables within 10 seconds.
- Perform a backup of the system within one minute.
- Produce a report of all customers and their orders within 20 seconds.
Quantitative Objectives
* The system should be able to add new records within one minute.
* Perform searches on the information stored within 20 seconds.
* Be able to update tables within 10 seconds.
* Perform a backup of the system within one minute.
* Produce a report of all customers and their orders in the shop within 20 seconds.
* The interface will be simple
* The new system will be easy to use and read.
* It should be possible to produce a report on all customer details
* It should be possible to produce a report on all product details
* It should be possible to produce a report on the orders between given dates.
* It should be possible to edit current records.
* It should be possible to add new records.
* It should be possible to delete old records.
* It should be possible to produce a report on the most popular products within the store .
* It should be possible to produce a report on all customer details
* It should be possible to produce a report on all product details
* It should be possible to produce a report on the orders between given dates.
* It should be possible to produce a report of customers who have bought the most products.
* It should be possible to produce a report showing the most successfully selling price bracket.
* It should be possible to use mail merge to send a letter to all new customers.
* It should be possible to use mail merge for all customers with an outstanding bill.
* It should be possible to produce a report displaying last month's profit.
* It should be possible to use mail merge to inform customers of upcoming new products and sales in the shop.
System Design
The following table shows the Inputs, Outputs, Processes and Tables that will be used on the final system
Inputs
Customer Details
Customer ID, Surname, Forename, Initial, Address1, Town/City, Postcode, Telephone Number, Balance £, Balance €
Order Details
Order ID, Customer ID, VAT, Order Date, Delivery Price, Total £, Total €
Items Ordered
Order ID, Product ID, Quantity, Total Price
Product Details
Name, Colour, Stock, Price, Image
Outputs
Reports
A list of customers and payment details
A list of products and the amount which was sold
A customers order
A particular customers receipt
Processes
* Add, Edit, Delete Customers
* Add, Edit, Delete Ordered Items
* Add, Edit, Delete Order Details
* Add, Edit, Delete Product Details
Tables
Customers
Order
Order Details
Products
Field
Data Type
Length
Description
Validation
BusinessID
Auto number
Long Integer
A unique one or two digit to represent a business
Unique Primary Key
BusinessName
Text
20
The name of the business
Presence Check
Address1
Text
20
The first line address of the business
Presence check
Town/City
Text
20
The town or city the business is situated in
Presence Check
Postcode
Text
20
The postcode of the business
Presence Check
Telephone number
Numeric
The telephone number of the business
Prescence Check
Type Check
Length Check
Balance (£)
Currency
The balance of the business in pounds.
Presence Check
Type Check
Balance (€)
Currency
The balance of the business in euros
Presence Check
Type Check
Entities with attributes
Business Table
Order Table
Field
Data Type
Length
Description
Validation
OrderID
Auto Number
Long Integer
A unique one or two digit code to represent a particular order from a customer
Unique Primary Key
BusinessID
Number
Long Integer
A unique one or two digit code to represent a business
Presence Check
VAT
Text
20
The value added tax to a particular order
Presence Check
Order Date
Date/ Time
The date the order was made by the business
Presence Check
Delivery Price
Currency
The added price of delivery of the product
Presence Check
Type Check
Total (£)
Currency
The total price of an order by a particular business in Pounds
Presence Check
Type Check
Total (€)
Currency
The total price of an order by a particular business in Pounds
Presence Check
Type Check
Order Details Table
Field
Data Type
Length
Description
Validation
OrderID
Number
Long Integer
A unique one or two digit code to represent a particular order from a business
Presence Check
ProductID
Number
Long Integer
A unique one or two digit code to represent a particular Samsung product
Presence Check
Quantity
Number
Long Integer
The number of particular products being ordered by a customer
Presence Check
Total Price
Currency
The individual product price multiplied by the quantity ordered
Presence Check
Product Table
Field
Data Type
Length
Description
Validation
ProductID
Auto number
Long Integer
A unique one or two digit code to represent a particular product
Presence Check
Name
Text
50
The name of the product which the ProductID represents
Presence Check
Colour
Text
0
The colour of the particular product
Presence Check
Stock
Number
Long Integer
The number of stock available of the product
Presence Check
Price
Currency
The cost of the product without VAT
Presence Check
Image
OLE Object
An image showing what the particular product looks like
Presence Check
Payment Table
Field
Data Type
Length
Description
Validation
BusinessID
Number
Long Integer
A unique one, two or three digit number identifying the business
Unique Primary Key
Date of Payment
Date/Time
Short Date
The date that the business paid the company by any means of payment method.
Unique Primary Key
Payment Method
Lookup Table
6
The method used to pay the company either using Cash, Visa or a cheque.
Presence Check
Amount Paid
Currency
2 D.P
The amount in pounds been paid to the company by the business
Presence Check
New Balance
Currency
2 D.P
The new balance that is still due for the business to be paid.
Employee Table
Field
Data Type
Length
Description
Validation
EmployeeID
Auto Number
Long Integer
A unique one or two digit code to represent a particular employee.
Unique Primary Key
Surname
Text
20
The surname of the particular employee
Presence Check
Forename
Text
20
The forename of the particular employee
Presence Check
Address1
Text
50
The first line address of the particular employee
Presence Check
Town/City
Text
20
The town or city where the employee works
Presence Check
Postcode
Text
20
The postcode of the particular employee
Presence Check
Telephone No
Numeric
The home telephone number of the employee
Presence Check
Type Check
Wage Table
Field
Data Type
Length
Description
Validation
EmployeeID
Number
Long Integer
A unique one or two digit code to represent a particular employee
Presence Check
Wages
Currency
2 D.P
The wage the particular employee recieves
Presence Check
When Paid?
Date/Time
The date the wage gets paid to the employee.
Presence Check
Total Outcome
Currency
2 D.P
The outcome of money due to the employee receiving their wages
Presence Check
Entity Relationship Model
Possible Solutions
There are many different types of new computerised systems, and the best one will be chosen which best suits the business.
Spreadsheet - Spreadsheets can automatically calculate arithmetical equations and the 'what if' tool, which allows you to simply and intuitively identify and evaluate the impact of variations in your model's critical variables, is also available. The Microsoft Excel software package is very user friendly and easy to use, requiring very little ICT training for the employees in the workplace. Data can be stored on separate sheets and data can be searched using Auto filter. However it would be difficult to produce a form for the user to enter data which would not be very complicated.
Bespoke system - A bespoke system is designed specifically for the user and it does exactly what it is required to do. An advantage of this system is that it requires minimal staff training and it is written to improve the businesses process. However, a major disadvantage on the other hand is that it is extremely costly and time consuming.
Database System (Microsoft Access) - Microsoft Access is a powerful relational database management system with specialist features. The many features in this package include the creation of tables, forms and reports to any appropriate style, reflecting the business image. Access is readily available, cheap supported and well documented. Relationships can be created between tables. Data redundancy and inconsistency can be eliminated by normalisation. Access is not an easy system to learn and problems can arise during development.
Visual Basic - A high level language program could be used where a code is added by dragging and dropping controls and defining their behaviour. This system is costly and time consuming.
Outsourced System - As an outsourced system has been tailored to meet the particular requirements of the user there shall be fewer implementation problems and staff will need less training as the system has been specifically produced to suit or improve the business's procedures. However the system will be expensive and will take a considerable amount of time to design.
Chosen Solution
After careful consideration, I have decided that a database system will be implemented as this will benefit the business the most. I have past experience of using Microsoft Access so I believe I will be able to successfully design the database to its full potential. Documentation can be produced in order to allow the user to understand the system. Forms and reports can be automatically produced in a simple way, aiding the user who doesn't have much experience.
Customer Form
Product Form
Search Customer with Address Form
A box will appear asking for the address
Search Customer with Postcode Form
A box will appear asking for the postcode
Search Customer with Surname Form
A box will appear asking for the surname
Search Customer with Forename Form
A box will appear asking for the forename
Search Customer with Title Form
A box will appear asking for the title
Search Customer with Town Form
A box will appear asking for the town
Search Order with OrderID form
A box will appear asking for the OrderID
Search Product with Colour form
A box will appear asking for the colour
Search Product with Name Form
A box will appear asking for the name
Search Product with ProductID
A box will appear asking for the ProductID
Main Switchboard
Customers Switchboard
Products Switchboard
Orders Switchboard
Report Design
Customer Information
Search Customer with Address
Search Customer with Postcode
Search Customer with Surname
Search Customer with Forename
Search Customer with Title
Search Order with OrderID
Search Product with Colour
Search Product with Name
Search Product with ProductID
Calculations:
Order subform Calculations
Total Price
The multiplication of the Product Price, by the Quantity ordered.
=Sum([Price]*[Quantity])
Total Price of Product ordered
The sum of, the quantity of the product bought by its price.
=Sum(([Quantity])*([Price]))
Total Price of the Order
The adding of the VAT, delivery price to the total price of the order.
=([Subtotal]+[VAT]+[Delivery Price])
VAT
This is calculated by multiplying the subtotal by 0.2
=[Subtotal]*0.2
Subtotal
This is multiplying the quantiy by the total price. This will be done by using a text box in the subform. The textbox is called Text16
=[frmOrderDetails Subform].[Form]![Text16]
Data dictionary:
Primary key field: Provides information about an entity and uniquely identifies the entity. E.g. Course ID, Student ID
Foreign Key: A non-key attribute in one table which is a key attribute in another table.
Composite key: A key that consists of two or more attributes.
Mail Merge Letters
Mail Merge 1: Payment details
4 Bridge Street
Banbridge Co. Down
BT323NB
Northern Ireland
Dear, "Title" "Forename" "Surname"
We are writing to inform you that your order place on "Date" has been processed successfully.
The totals for your order are:
Subtotal - £ "Total"
Vat - £ "VAT"
Overall Total -£ "Total Price"
We operate on a one payment basis. This is to ensure a more regular and efficient payment. The payments can be made by Credit Card, Debit Card or cheque.
We will be in contact with you in the next few days regarding this.
Thank for your continued support, it is much appreciated.
Yours Sincerely
Jim Peters
Manager of Samsung Store, Banbridge
Mail Merge 1: Payment details
4 Bridge Street
Banbridge Co. Down
BT323NB
Northern Ireland
Dear, Mr John McColgan
We are writing to inform you that your order place on 14/01/11 has been processed successfully.
The totals for your order are:
Subtotal - £ 1000
Vat - £ 175
Overall Total -£ 1175
We operate on a one payment basis. This is to ensure a more regular and efficient payment. The payments can be made by Credit Card, Debit Card or cheque.
We will be in contact with you in the next few days regarding this.
Thank for your continued support, it is much appreciated.
Yours Sincerely
Jim Peters
Manager of Samsung Store, Banbridge
Mail Merge 2: Order Details
4 Bridge Street
Banbridge Co. Down
BT323NB
Northern Ireland
Dear "Title" "Forename" "Surname"
On behalf of Samsung, we are delighted that you chose us for your electrical goods.
The details of your order are
Order Number - "OrderID"
Products Ordered - "Name"
Amount Ordered - "Quantity"
= £Total"
£"Total Price" Inc VAT
We hope the products have reached you in perfect condition.
If there are any problems with the products, please contact our store on 028 40625568.
Thank for your shopping with us, it is greatly appreciated
Yours Sincerely,
Jim Peters
Manager of Samsung Store, Banbridge
Mail Merge 2: Order Details
4 Bridge Street
Banbridge Co. Down
BT323NB
Northern Ireland
Dear Mr John McColgan
On behalf of Samsung, we are delighted that you chose us for your electrical goods.
The details of your order are
Order Number - 1
Products Ordered - Samsung RV510 15.6 inch HD notebook
Amount Ordered - 1
= £366.39
£430.50 Inc VAT
We hope the products have reached you in perfect condition.
If there are any problems with the products, please contact our store on 028 40625568.
Thank for your shopping with us, it is greatly appreciated
Yours Sincerely,
Jim Peters
Manager of Samsung Store, Banbridge
Queries Data Dictionary
Customer Information:
This is a query to display all of the Customers information; their names, addresses, telephone numbers; taken from the customer table.
Customers by Location:
This is a parameter query which asks you to enter a town. This is achieved by in the criteria for Town/City, entering [Enter Town/City]. This query may be useful if someone within Samsung wished to know where there customers are from to see where they get their best business from.
Customers by Address
This is a parameter query which asks you to enter a town. This is achieved by in the criteria for Address1, entering [Enter address]. This query may be useful if someone within Samsung wished to know where there customers are from to see where they get their best business from.
Customers by Postcode
This is a parameter query which asks you to enter a postcode. This is achieved by in the criteria for Postcode, entering [Enter Postcode]. This query may be useful if someone within Samsung wished to know where there customers are from to see where they get their best business from.
Customers by Surname
This is a parameter query which asks you to enter a surname. This is achieved by in the criteria for Surname, entering [Enter Surname]
Customers by Forename
This is a parameter query which asks you to enter a forename. This is achieved by in the criteria for Forename, entering [Enter Forename]
Customers by Title
This is a parameter query which asks you to enter a title. This is achieved by in the criteria for Title, entering [Enter Title]
Product by Name
This is a parameter query which asks you to enter a product name. This is achieved by in the criteria for Name, entering [Enter Name]
Product by ProductID
This is a parameter query which asks you to enter a ProductID. This is achieved by in the criteria for ProductID, entering [Enter ProductID]
Order by OrderID
This is a parameter query which asks you to enter an OrderID. This is achieved by in the criteria for OrderID, entering [Enter OrderID]
Product by Colour:
This is a parameter query which asks you to enter a colour. This is achieved by in the criteria for colour, entering, [enter colour]. This query is useful as you may have already decided on your favourite colour and you can just search straight away
This query contains data from the products table and it contains the following fields; ProductID, Name, Colour, Stock, Price and Image.
Product by Price:
This is a parameter query which asks you to a enter a price bracket. This is achieved by in the criteria for price bracket, entering, [:enter price bracket]. This query is useful for checking which products are of a certain price bracket. For example, if a customer wants to look at paintings price between £500-1000, then this value should be entered to display the products needed. This query contains data from the product table and it contains the following fields; ProductID, Name, Colour, Stock, Price and Image.
Customer details:
This is a parameter query which asks you to a enter a customers surname. This is achieved by in the criteria for name, entering, [Enter surname]. This query is useD to find details about a certain client, and it is useful in saving time, from looking through a list to find the client you want. It contains data from the customer table, and it includes the following fields; CustomerID, Forename, Surname, Title, Address1, Town/City, Postcode, Telephone No, Balance (£) Balance (€).
Macros-Data Dictionary
This section shows all the different macros that I have used within the database, showing designs of how they are created in the design aspect of the macro.
Macro: "AutoExec"
This macro will open up the "main splash page" automatically when the database is opened. This form will inform the user that the password they have entered has been accepted and will have a command button which will allow them to enter the main switchboard. The macro will also minimise the menu for the database.
Macro: "Updating tables"
This macro will run the four queries that will be used to update the main form and sub form tables so that the data entered into the main form itself will then be transferred into these tables. When the data is entered into these two tables' queries can be carried out on it to help the company obtain their management statistics to help them with the operation of the company. When selected this macro will display a message box informing the user that they are about to update the tables. After the Ok button has been pressed the macro will run the four queries which will first put the data from the main form and the sub form into two make tables. The macro will then run the two update queries which will transfer this data into the main form table and the sub form table
Test Plan
Test No.
Test Area
Description of Test
Expected Result
Actual Result
Page No.
Input Mask
Does an input mask appear for the telephone number in the customer table?
Numbers must be of the form "028 "000\ 00000.
Only numbers of the form "028 "000\ 00000 were accepted.
2
Input Mask
Does an input mask appear for the customers postcode
A postcode must be of the form >LL00\ 0LL;0;_
Only postcodes of the form >LL00\ 0LL;0;_ were accepted.
3
Look-up tables
Does a look-up table appear with the relevant title to choose from in the customer table
When I select the empty title box, a look up table should appear displaying titles.
A look-up table appears with numerous titles to chose from.
4
MS Access Validation
Does validation ensure date is entered into the date box.
When I don't enter a date into the field a box should appear telling me to do so
When entering data the validation would only allow me to enter a date.
6
Calculations in order form and subform.
'Price × Quantity = Total Price'
When the price is £366.39 and the quantity is 1 the products price should be £366.39.
The product price was correctly calculated.
5
Look-up in Product Order Subform
Does information from the product table appear in the subform when the ProductID is entered?
When I enter the ProductID, the relevant information for the product should appear.
The corresponding information appeared in the subform.
7-10
Calculations in order mainform and subform.
'VAT =Sum of the subtotal x 0.2'
The VAT is 20% and this should be calulated
The amount of VAT was correctly calculated.
Total in pounds is Subtotal plus the VAT added to the delivery price
When the subtotal is 366.39 and the VAT is 73.28 and the delivery price is 5.00, the total should be 444.67
The total in pounds was counted correctly at 444.67
Total in euros is Subtotal plus the VAT added to the delivery price and multiplied by 1.17.
When the subtotal is 366.39 and the VAT is 73.28 and the delivery price is 5.00 and multiplied by 1.17, the total should be 445.52
The total in euros was calculated correctly
Subtotal is price multiplied by the quantity
When the price is 366.39 and the quantity is 1 the subtotal should be 366.39
The subtotal was calculated correctly
1
Order form Data Transfers
Are the prices from the subform for each painting ordered, carried across to the order form and added to give total price??
If there are three products ordered with a price 366.39, 498.37 and 275.79 respectively, then the total price in the main form should be £1373.66. Of course the other calculations were also done
The total price was correctly calculated to be £1373.66
2
Order form Data Transfers
Is the total number of products successfully counted and brought across to the main order form?
If three products are ordered and the total number of paintings ordered should be 3.
The total number of instruments was correctly calculated to be 2
3
Database opens
Does the database open when I click into it
When I double click on my databae it should open straight away
When I double clicked the database did open.
4
Database Password
Does the 'enter password' box appear when the database is opened. When the password is entered, does it open?
When I open the database, I should be met by a message box telling me to enter the password. If I entered it correctly, the database should then open.
A message box appeared, and when I entered the password, the database successfully opened.
5
Splash page
Does the splash form open when the database is opened.
When I opened the database, I should be met by a splashboard, welcoming me to the database.
The splashboard opened successfully when the database was opened.
6
Splashboard
Does the splashboard enter button work?
When I press the enter button on the splashboard, it should open up my database.
The button worked successfully, opening up the database switchboard.
7
Switchboard
Does the customer button on the main switchboard work?
When I press the customer button, it should open a new switchboard page with customer options on it.
The button successfully opened the customer switchboard page when pressed.
8
Switchboard
Does the edit/delete customer button work in the customer switchboard?
When pressed the edit/delete customer button should bring me to the customer form.
The button successfully opened the customer form.
9
Switchboard
Does the add new customer button work in the customer switchboard?
When pressed the add new customer button should open the customerform.
The button successfully opened the customer form, allowing new customers to be entered.
20
Switchboard
Does the customer list button work
When I press the customer list button it should bring me to the customer list form
The button successfully brings me to the customer list form.
21
Switchboard
Does the return to main menu button work?
When I press the return to main menu button it should bring me back to the main switchboard
The button successfully brought me back to the main switchboard
22
Switchboard
Does the Order button work?
When pressed, the order button should bring me to the order switchboard.
The button successfully brings me to the orders switchboard page.
23
Switchboard
Does the make new order button work?
When pressed, the button should bring open the Orders form.
The button successfully opens the order form, allowing me to make new orders
24
Switchboard
Does the view a current order button work
When pressed the button should open the order form, allowing me to view or edit current orders
The button successfully opened the order form, allowing me view and edit orders.
25
Switchboard
Does the return to main menu button work?
When I press the return to main menu button it should bring me back to the main switchboard
The button successfully brought me back to the main switchboard
26
Switchboard
Does the Products button work?
This button should bring me to the products switchboard
The button successfully brings me to the Product switchboard.
27
Switchboard
Does the add a product button work?
This button should bring me to the product form and let me add a new product
The button successfully brings me to the Product form and lets me add a new product.
28
Switchboard
Does the edit/delete a product button work?
This button should bring me to the product form and let me edit or delete an existing product
The button successfully brings me to the Product form and lets me edit or delete an existing product.
29
Switchboard
Does the product list button work?
This button should bring me to the product list form and let me view all of the products.
The button successfully brings me to the Product list form and lets me view existing products.
30
Switchboard
Does the return to main menu button work?
When I press the return to main menu button it should bring me back to the main switchboard
The button successfully brought me back to the main switchboard
31
Switchboard
Does the quit application button work?
Once I hit the quit application button the switchboard should close
The switchboard successfully closes when I hit the quit application button
32
Forms
Does the 'next' button work on the frmOrder form.
Once I press the next button it should bring me to the next order record.
The button works and brings me to the next record.
33
Forms
Does the 'previous' button work on the frmOrder form.
Once I press the previous button it should bring me to the previous order record.
The button works and brings me to the previous record.
34
Forms
Does the 'close form' button work
Once I press the 'close form' button the form should close and bring me to the switchboard
The form does close and brings me to the switchboard
35
Forms
Does the frmSearchCustomerWithAdress work
Once I click this form it should ask me for an address and then bring me to the form
When I type in the address it brings me to the form
36
Forms
Does the frmSearchCustomerWithPostcode work
Once I click this form it should ask me for a postcode and then bring me to the form
When I type in the postcode it brings me to the form
37
Forms
Does the frmSearchCustomerWithSurname work
Once I click this form it should ask me for a surname and then bring me to the form
When I type in the surname it brings me to the form
38
Forms
Does the frmSearchCustomerWithTitle work
Once I click this form it should ask me for a title and then bring me to the form
When I type in the title it brings me to the form
39
Forms
Does the frmSearchCustomerWithTown work
Once I click this form it should ask me for a town and then bring me to the form
When I type in the town it brings me to the form
40
Forms
Does the frmSearchCustomerWithForename work
Once I click this form it should ask me for a forename and then bring me to the form
When I type in the forename it brings me to the form
41
Forms
Does the frmSearchProductWithColour work
Once I click this form it should ask me for a colour and then bring me to the form
When I type in the colour it brings me to the form
42
Queries
Does the qrySearchCustomerWithAddress work
Once I click this query it should ask me for an address and then bring me to a query
When I type in the address it brings me to the query
43
Queries
Does the qrySearchCustomerWithPostcode work
Once I click this query it should ask me for a postcode and then bring me to a query
When I type in the postcode it brings me to the query
44
Queries
Does the qrySearchCustomerWithSurname work
Once I click this query it should ask me for an surname and then bring me to a query
When I type in the surname it brings me to the query
45
Queries
Does the qrySearchCustomerWithForename work
Once I click this query it should ask me for a forename and then bring me to a query
When I type in the forename it brings me to the query
46
Queries
Does the qrySearchOrderWithOrderID work
Once I click this query it should ask me for an OrderID and then bring me to a query
When I type in the OrderID it brings me to the query
47
Reports
Does the rptCustomerInfo work
Once I click this report it should open showing me information for all of the customers
When I click on this report it shows me all the information
48
Reports
Does the rptSearchCustomerWithAddress work
Once I click on this report it should ask me for the address and then bring me to the report
When I type in the address it brings me to the Report
48
Reports
Does the rptSearchCustomerWithPostcode work
Once I click on this report it should ask me for the postcode and then bring me to the report
When I type in the poscode it brings me to the Report
50
Reports
Does the rptSearchCustomerWithSurname work
Once I click on this report it should ask me for the surname and then bring me to the report
When I type in the surname it brings me to the Report
51
Reports
Does the rptSearchCustomerWithForename work
Once I click on this report it should ask me for the forename and then bring me to the report
When I type in the forename it brings me to the Report
52
Reports
Does the rptSearchOrderWithOrderID work
Once I click on this report it should ask me for the OrderID and then bring me to the report
When I type in the OrderID it brings me to the Report
53
Date
Is the date correct on the splash page
The date should match the actual date on the splash page
It does match the actual date.
54
Time
Is the time correct on the splash page
The time should match the actual time on the splash page
It does match the actual time
Test 1
The customer table before the telephone number is entered
Only the sequence of numbers in the input mask have been allowed
Test 2
The customer table before the postcode is entered
The customer table after the postcode is entered
Test 3
The customer table before selecting the Title box
The customer table after selecting the lookup table for the Title box
Test 4
The box popping up after entering the something wrong intot he date field
Test 5
Whenever I entered the ProductID, all of the results were shown.
Test 6,7,8,9,10
The following shows the order form with no calculations done.
The following below shows with all the calculations done.
The following shows all the calculations done in design view.
Test 11,12
The three products in the subform before the quantity is entered
I give them all a quantity of one and the total price was then shown in the main form
Test 13
Below shows my database before I have opened it
When I clicked on this my database successfully opened
Test 14
As soon as I opened my database I was met with this box
I then entered the password and it let me into the database
Test 15
When I entered the password I was met with a splash page.
Test 16
I then selected the 'Click here to enter' button.
Test 17
I wanted to test that all the buttons worked so the first button I pressed was 'Customers'
Test 18
I then selected Edit/Delete customers
Test 19
I then selected add new customers
Test 20
I then selected the customer list button
Test 21
I then selected the return to main menu button
Test 22
I then selected the orders button
Test 23
I selected the Make new Order button
Test 24
I selected the View Current orders button
Test 25
I selected the Return To Main Menu button
Test 26
I selected the products button
Test 27
I selected the Add a product button
Text 28
I selected the edit/delete product button
Test 29
I selected the product list button
Test 30
I selected the return to main menu button
Test 31
I selected the Quit application button
Test 32
Once I press the 'next' button it should bring me to the next record. This the first record.
This the record after I press the 'next' button
Test 33
Once I press the 'previous' button it should bring me to the previous record. This the second record.
This is the record after I hit the 'previous' button.
Test 34
Once I press the close for button it should bring me to the main switchboard.
It does bring me to the main switchboard
Test 35
When I select the frmSearchCustomerWithAddress this box will appear
When I enter the address it should bring me to the form and it does
Test 36
When I select the frmSearchCustomerWithPostcode this box will appear
It will then bring me to the form when I type in the postcode
Test 37
When I select the frmSearchCustomerWithSurname this box will appear
When I enter the surname it will bring me to the form
Test 38
When I select the frmSearchCustomerWithTitle this box will appear
When I enter the title I will then be brought to the form.
Test 39
When I select the frmSearchCustomerWithTown this box will appear
When I enter the town name I will be brought to the form
Test 40
When I select the frmSearchCustomerWithForename this box will appear
When I enter a forename I will be brought to the report
Test 41
When I select the frmSearchProductWithColour this box will appear
When I enter the colour this form then appears
Test 42
When I select the qrySearchCustomerWithAddress this box will appear
When I type in the address it will then bring me to this query
Test 43
When I select the qrySearchCustomerWithPostcode this box will appear
When I enter the postcode it brings me to this query
Test 44
When I select the qrySearchCustomerWithSurname this box will appear
When I enter the surname it brings me to this query
Test 45
When I select the qrySearchCustomerWithForename this box will appear
When I enter the forename it will bring me to this query
Test 46
When I select the qrySearchOrderIDWithOrder this box will appear
When I enter the OrderID it brings me to the query
Test 47
When I press rptCustomerInfo it should bring me to the report showing the customer info. Below shows after I have just pressed it
Test 48
When I select the rptSearchCustomerWithAddress this box will appear
When I enter the address I will be brought to the report
Test 49
When I select the rptSearchCustomerWithPostcode this box will appear
When I enter the postcode it will bring me to the report
Test 50
When I select the rptSearchCustomerWithSurname this box will appear
When I enter the surname it brings me to this report
Test 51
When I select the rptSearchCustomerWithForename this box will appear
When I enter the forename this report will then open
Test 52
When I select the rptSearchOrderWithOrderID this box will appear
Test 53
Does the date match the actual date?
Test 54
Does the time match the actual time?
Description of Package
This system design for Samsung allows the user to perform their daily operations more easily. This is because all their transactions are done electronically rather manually, therefore helping to increase efficiency and integrity of data.
* Store product, customer and order details
* Add, delete, edit and process all details
* Locate information quickly
* Ensure accuracy, completeness and efficiency by use of validation
* Automatically update and process orders going from the company
* Calculate orders automatically
* Calculate total amount owed by particular customer
* Create letters and invoices by mail merge in order to contact customers about a range of issues
* Create reports on all relevant information in an easy to understand format
Hardware and Software Configuration
As the system was made by a standard PC using Microsoft Access 2003 can be accessed and operated widely. The user will also need another Microsoft programme called Microsoft Word in order to produce mail merge letters. The system can be portably stored on a USB pen, CD or more suitably on a computer hard drive with a storage capacity of 20mb. Can be used using either a LAN or WAN.
Installation Guide
If the user is installing the system from a USB they must follow the steps in order for it to be installed properly.
. Plug the USB into your computer, the computer will locate the USB then
2. Go to my computer on your desktop and click on the USB which should open
3. When you have it opened right click on the system and then click copy
4. Copy the file to the desired location then it should be ready to run
If the user is installing from a CD-ROM they must follow the steps in order to install the system properly.
. Insert the CD-ROM into the disk drive
2. Go to my computer on your desktop and click on the CD-ROM which should open
3. When you have it opened right click on the system and then click copy
4. Copy the file to the desired location then it should be ready to run
Operating Instructions
Passwords
First of all open the Microsoft Access program and click "file" "open". Locate the file and open in "open exclusive"
You then have to go to 'Tools' then 'Security' then 'Set Database Password'. The password we set was "samsung. If the user gets this wrong a warning will show. This proves that it is a safe system.
When inside the system click "tools" "security" "unset database password" so the user can change it to suit their preference.
Passwords are great way to help prevent from hacking, however it is important to keep the password private to only authorised personnel with the business.
Switchboard
The main switchboard where the user can navigate easily through the relevant information. The switchboard opens automatically when you click enter on the opening splash board. I will discuss this later when describing the macros. To select one of the options the user must click on the box to the left of the option, most of the navigation of the system can be done through this switchboard.
Customers
* To enter, select the 'Customers' option from the main switchboard menu.
* To add a record select the first option in the above window.
* This will then take you into a current form that you add a new customer to the database
* To edit or delete a Customer record, select the second option in the window.
* This will then take you into a blank form so you can edit or delete a customer in the database
* The customer list button will take to a form where you can few all of the existing customers in the database
* To return to the main switchboard, select 'Return to Main Menu'.
Products
* To enter, select the products option from the main switchboard menu.
* To edit/delete a record select the option, 'Edit/Delete Product' in the next window.
* This will then take you into a current form that you can either amend or delete a product record.
* To add a new record select the next option in window, 'Add a Product'
* This will then take you into a blank form so you can enter the details of a new product entering the shop.
* To return to the main switchboard, select 'Return to Main Menu'.
Orders
* To enter, select the Order option from the main switchboard menu.
* To make a new order select the first option in the next window, 'Make New Order'.
* This will then take you into a current form that you can make a new order.
* To edit or delete a record select the 'View the Current Order' button on the switchboard above.
* This will then take you into a blank form so you can edit the details of a new order.
* To return to the main switchboard, select 'Return to Main Menu'
Order Form
* To look at the next or previous order you must click the corresponding navigation button and it will bring up the next or previous order.
* The Subtotal, VAT, Total and Total Price are all data transfers from the sub form to the main this will be updated if amendments are made.
Macros
The macros can be found in the database window in the macros section. The table below summaries they macros used in the system.
Where found
Macro name
Description
Queries/Form it is based on
Macros
AutoExec
This macro minimises all windows when you open the database, and opens t he splash board.
Splash form
Macros
OpenMain
This macro minimised the splash board and opens the switchboard once clicked, 'Enter'.
Switchboard form
Macro Guidelines
The macros in the system are designed to make the system as easy as possible for the user as the switchboard is brought up automatically once the Enter button is pressed and so decisions can be made quickly. This will help the user operate the system without complications.
A macro should only be run when you are sure that it is appropriate, as if a macro is accidentally run it can be difficult to restore the information.
Reports
The report stage is perhaps one of the most significant parts of the system as this is the output from the system, that the manager or owner will look at for sales figures and revenue intake, and the user of the system will use to obtain the answers to everyday queries which they may have, or a client may want to know. To view the reports the user must click the Reports section in whichever area of the switchboard they desire reports regarding, for example, Customer reports are found in the Customer section of the main switchboard. To access reports regarding income, profits, sales, etc, the use must select the 'Management statistics' button on the main switchboard. A message may appear asking for information to run a certain report (parameter queries), for example, if you want to view the 'Customers by Town/City report, you must enter in a town to run the report when selecting it. When the number is entered the report will appear with the details for the clients in that for-entered town. Below is a brief summary of reports used.
Report Name on Switchboard
Report Name
Description
Query based on
Search Customer with Address
Search Customer with Address
This reports all the details for a customer by entering an address.
Search Customer with Address
Customer Information
Customer Information
This lists all the details for each Customer.
Customer Information
Search Product with Colour
Search Product with Colour
This reports all of the products by entering a certain colour
Search Product with Colour
All Product Information
All Product Information
This lists all the details for each product.
All Product Information
All Order Information
All Order Information
This lists all the details for each order.
All Order Information
Mail Merge
A sample letter, which can be used to inform customers about their order details has been included in the system. To utilise this either select the mail merge option on the switchboard to run the mail merge macro or open the letter in word and go to Tools on the toolbar and click Mail Merge. Click the merge next to the number three and click on merge on the subsequent window. A new word document with all the letters should appear. Go to the file in the toolbar and click print to print the letters. They are now ready to be sent to the individual customers. If you wish to create your own mail merge for a different function, go to Tools on the toolbar and click Mail Merge, Then at the number one go to create option and select the form letters, then at number to retrieve the data from the database that you wish to use, once completed click the merge next to the number three and click on merge on the subsequent window. A new word document with all the letters should appear.
Backing up the System
A USB Memory stick will be required. Follow these instructions to back up the data:
. Close the database.
2. Click on My Documents and then Samsung folder.
3. Locate the file called Samsung held within the file.
4. Insert the CD/USB Memory stick.
5. Right-click on the database file Samsung.mdb) and select copy.
6. Open up USB/CD and paste the database file.
This is one method to back-up the database however within the main switchboard which can be seen below there is an option to back up on memory stick
Troubleshooting Guide
This section shall describe and resolve some of the potential difficulties encountered within this system.
Problem
Action Required
When inputting the correct password a message appears saying "not a valid password".
The password is case sensitive and must be typed in lower case letters. Take off the caps lock and retype the password.
Certain fields in my order and product order tables appear as 0 in the table yet are correct in the forms.
After data as been amended, always run the macro to update the tables in order to complete calculated fields.
When some specific data is entered a message appears saying invalid data.
There have been some specific validation rules set such as Postcode and Phone number to only be allowed a certain number of digits. To amend this make sure you have the correct number of digits used.
To change or delete validation rules
The user can go to the tables sections and go to the related tables and cut the rule out so it won't appear, or they can amend the numeric value.
When opening a order, no data appears apart from #Error in the calculated fields.
If Samsung has no orders for that week the order form will appear blank and therefore fields involving calculations will not be able to function.
When I enter a number in a parameter query an error occurs or no data appears in my report.
An incorrect number must have been entered. Check that for order number the order exists
Mark McEvoy ICT Coursework
| Page