Data Manipulation Essay
The tables required in the system will be Customer, Products, Staff and salary. The individual fields in each table will be as follows:
CUSTOMER (CustomerID, Surname, FirstName and customer address)
PRODUCTS (ProductID, Title, Description, Type and Amount)
STAFF (StaffID, Name, address and contact number)
STAFF SALARY (wages per hour, hours per day and days per week)
There will be two reports produced. To produce a report for the products purchased; a query will be run to combine the data from the customer table and the product table. The user will be able to specify the products bought by a customer with the entire amount calculated to give the customer benefits of getting more offers.
The second report will print the amount of salary each staff earns per hour, hours/day and days/week. The user is asked to enter the StaffID and name to enter its database.
The two reports should be able to be viewed on screen with an option of printing it as well. The product purchased report should be printed so that they can give some discounts on particular customers who shop a lot at the store. The staff salary report should also be printed before any meetings so that they can decide on who to get paid more and who shouldn’t.
Backup / Security Strategy
The main computer that has all the data needed should be backed up on a CD every week or even when a large amount of data has been input to the system. This CD should be kept away the main computer incase of a fire, theft or flood.
There should be password security on the system so that people cannot hack into it. Access has a password protection before entering the data so that no one can hack into it.
There will be 4 forms for the data entry.
This form will be used for entering customer details. My initial design is shown below
This form is used to enter all the products available at the store and the prices of each product.
This form will be used to enter the staff details.
This form will be used to enter the salary of each staff working in the store.
There will be 2 reports. These can be either printed or viewed on screen.
Report customer list
This report will come from a query, which combines data the customer table and the product table. The user will be able to enter the Customer name to enter the report for the customer report
This initial design is shown below
Report Staff Salary
This report will show how a staff details and how much they earn and how are they rated between 1 to 5 stars for working. Mr. Smith can rate this every week.
The menu structure for this system will look like this:
User Feedback on Initial designs
The initial designs were shown to the manager of the store for his feedback and approval. Mr. Smith had the following comments and suggestions:
” I have looked at your designs very carefully and they appear to be useful for me and I did however think of 1 or 2 changes that could perhaps change”
1) The product types should include which category it is in such as racing, shooting. It would also be easier and helpful if everytime you don’t have to type in what category it is in.
2) Can the 2 reports be printed all together with a click of a button because it can be time consuming
3) Can you also do something that can calculate the amount of the products and salary for the staff
Four tables are required in this system. They are related as follows:
The tables are defined as follows:
The form designs were amended in accordance with Mr. Smith’s suggestions and final designs are shown below. There is a combo box added for the category of the product, which can be easier to select the category instead typing it all the time.
I will need to create 2 different queries. These will form the sources for my reports.
This query will combine data from 2 tables. Using the customer table and product table. It will ask the user to give the customer name. This will then be asked to find out the customer details or the product details.
For e.g. [Enter Customer name: ]
This query will combine from the two tables, which are the staff table and the salary table. It will ask the user to enter the staff name to see the list of that particular staff detail
For example: [enter staff name: ]
We will be using Ms Access to make the system needed. I will show in steps how to create this type of a system.
First open Ms Access then you will see
Now the same way I need to enter 30 customer list
Now we can type the detail of 30 customers without entering the product ID
With the same way I also need to create the product table. After creating it the design looks like this
Now I need to enter the details of the products
Now we know the product ID so then I will need to add them in the customer table which does not need to be in an order because a customer may want something else.
Now we need to create the relationship between these two table.
Like these tables I created I will need to create another 2 more. After creating the table page will look like this
After creating the staff tables I now need to create the details of them.
Now for the staff salary table I need to enter the details
Now I need to create the relationship between the Staff and the Staff Salary the same way as I did for Customer an Product. So open the relationship window
Now as I have created the relationship between the staff and staff salary when I will open their table it will show
After creating the tables I now need to create 2 query.
Now I need to create a formula for which the system can calculate the total amount of the product from the quantity bought
Now for calculating the VAT on these products I will do:
Now I need to calculate the bill amount, which is, Amount adds the VAT.
To check if these formula’s work I will now test the system
After seeing the amount formula working I will now need to save the work and so I will:
This is how it will look like when it closes the query
For the same way I need to create another query, which relates with staff salary and after it has been done it will look like this
Now that Tables and Query’s are finished I now need to create forms.
Now need to align these in order
Now I need to create title for the form . so I need to open the toolbox.
It will look like this when I open the toolbox
then where it says “Aa” click on it and make a box on the form
Now I need to type in the title name.
Now I have to organize the font and the font size
Now I need to create command buttons for the system.
After creating the commands I now need to create a the background colour
now I need to save this form
For the same way I will create another 4 forms and after I created it, it looks like
Now I need to create reports
Now finally I need to create a Main Menu, which indicate the user to use the system easily. Main Menu will also be create in the Form
University/College: University of California
Type of paper: Thesis/Dissertation Chapter
Date: 29 September 2017