Data Manipulation.

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.

Part Three

Initial Designs

Form Design

There will be 4 forms for the data entry.

Frm Customer

This form will be used for entering customer details. My initial design is shown below

Frm Product

This form is used to enter all the products available at the store and the prices of each product.

Frm Staff

This form will be used to enter the staff details.

Frm Salary

This form will be used to enter the salary of each staff working in the store.

Report Design

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.

 

Menu Design

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

Final Design

Table design

Four tables are required in this system. They are related as follows:

 

The tables are defined as follows:

Form Design

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.

Query Design

I will need to create 2 different queries. These will form the sources for my reports.

QryCustomerlist

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: ]

QryStaffSalary

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: ]

        

Implementation

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