Projects 1 , BITM 330, Spring 2021 A small high-school bookstore is using the Excel program to manage all sales. A sample items and initial design (sheet: Sales) is presented below: ItemNumber...

Projects 1 , BITM 330, Spring 2021
A small high-school bookstore is using the Excel program to manage all sales. A sample items and initial design (sheet: Sales) is presented below:
ItemNumber
Description
Price
On_Hand
Customer's request
Number Sold
Line Total
1
Pencils doz.
$ XXXXXXXXXX
8

0
$ -
2
Pens doz.
$ XXXXXXXXXX
10

0
$ -
3
Mouse wireless
$ XXXXXXXXXX
6

0
$ -
4
Mouse wired
$ XXXXXXXXXX
3

0
$ -
5
Paper 200
$ XXXXXXXXXX
9

0
$ -
6
Paper 500
$ XXXXXXXXXX
10

0
$ -
7
Memory card 32
$ XXXXXXXXXX
5

0
$ -
8
“Flash” card 64
$ XXXXXXXXXX
4

0
$ -





Total
$ -





Tax
$ -





Grand Total
$ -





Cash
$ -





Change
$ -
Salesperson ID
They would like to expand the store management and they consider:
a) To create an ACCESS solution to manage the business (P1)
b) Improve the current semi manual Excel solution (create Excel solution with Macros, P2).
Requirements for P1:
Create a database to support the sales. Your solution needs to:
a. Create all tables with PKs, normalize tables and establish relationships. Enter sample data according to the Note below.
b. During the process of ordering, salesperson is visually (or using the code) checking the OnHand status and enters the possible number ordered not to have negative inventory. Note: in Excel solution it should be automatically done
c. Make an Update query and assign it to the Order Form
d. Make a report to calculate the total for each order (do not forget to add a sales tax), run it as an interactive query based on order number.
e. Calculate the total sales (make a query or a report)
f. Create a report to calculate the bonuses for all employees (4% of the total sold by each employee).
g. Create a button menu to allow user to choose which action to use!
Hints and ideas: create four tables: Items, Order_Line, Orders and Employees (create this table with minimum number of attributes, like Salesperson_number, FirstName, LastName and Phone_number). After creating relationships, limit data entry for Items between 8 and 12, put 5 to 10 orders with 8 to 15 order lines; put yourself and only 3 more employees. You can use QBE or SQL approach. Missing established relationships and having less data than suggested above will be penalized.
May 01, 2021

Submit New Assignment

Copy and Paste Your Assignment Here