Need extreme help with project. Please use Microsoft Access to complete and need database file and document explaining everything as instructions require. Please see phase 1 narrative for original description for project. Phase 3 is what needs to be completed. The sample solution that the phase 3 is referring to with the tables and primary and foreign keys to create everything is attached as well.
Phase 1 original narrative:
Jillian Rodriguez is the owner and operator of a specialty candy provider, Jillian?s Candy Emporium. She has decided to develop a new database system to track her purchases, candy inventory, accounts payable, and cash payments. However, she does not have time to do the development herself. Therefore, she has hired you to design and implement the system for her. She describes the requirements of her purchasing system as follows:
Whenever candies are needed, a purchasing agent enters a purchase order for the items needed directly into the computer system. To accommodate all of her candy needs, the purchasing agent may need to prepare several orders, as Jillian orders candies from many different vendors. The purchasing agent may order several types of candies from a single vendor on one order. Jillian approves and prints these purchase orders and sends them to the vendors. A data entry clerk records the receipt of candy from a vendor on a special screen in the accounting system. At the end of each month, an accounts payable clerk creates checks to pay for all amounts charged through the end of the month. Once the checks are created, Jillian authorizes the checks, prints them, and mails them to vendors. Jillian also uses special screens in the computer system to perform maintenance for inventory items (add, modify and delete items), and to record the disposal of spoiled or damaged candies.
Phase 3 that needs to be completed in access and word document:
Project Phase 3
Jillian?s Candy Emporium Purchasing Cycle
Requirements (worth 10 points): The third phase of the project requires you to implement your logical and physical data models into MS Access. Your project grade will be based on 3 deliverables;
Please, read the instructions carefully before you start working on the project.
1. Create the MS Access tables based on the tables provided in the sample solution from the Project Phase 2. In each table, define the primary key and any foreign keys. For each attribute, define the data type, provide a field description, and define the field properties (metadata). For each table, there should be at least one validation rule and corresponding validation text.
-The sample solution has a somewhat non-normalized approach to the Employee information. Please work with it as it is laid out.
2. Populate your tables with fictitious data, using 2015 for any date fields; you should sufficiently populate your tables so that you can fully demonstrate your design
-Provide 20 rows of data for each event and 10 for each agent and resource, except for employees. You can have fewer employees.
3. Establish appropriate relationships between the tables. Make sure that you enforce referential integrity in all of your relationships.
4. Create seven queries and document why these queries are important for managers/decision makers in the purchasing process at Jillian?s Candy Emporium.
-You should have at least four multiple-table queries in your design.
-At least two of your queries should perform calculations using ?Expression Builder?
-At least two of your queries should be parameter based
-One query should show the 5 most popular candies bought during a specified period (it must be parameter driven). The query result should include the name of the candy (e.g., closed loop) and the number bought and dollar amount bought, sorted by dollar amount.
5. Create three forms and document the function of each form in the purchasing process and which user would use it. These forms should reflect the inputs to the Expenditure Cycle as discussed in the PowerPoint from Chapter 13. Make certain that you format the forms with care; grouping related information and ensuring that the formatting is of the highest presentation quality.
-At least one form must be of the form/sub-form type
-One form should serve the purpose of Purchase Order Entry
-Another form should be used for Vendor Entry/Maintenance
-All of the forms should be appropriate from a business perspective
-At least one form should display extensions; e.g, totals and grand totals
-Utilize control tools, such as input masks, combo boxes, etc.
-Show that you know how to display a closed loop field (e.g., display Vendor Name after entering Vendor ID), and skip over that field as you tab through the data entry.
6. Create two reports for processing/decision makers; document what benefits these reports provide, and how often they will be used. Just as with the forms, the reports must be formatted with the highest presentation quality.
-The first report should be a Purchase Order that can be printed, one per page, and sent to a vendor
-The second report should be a Vendor Purchase History Report
7. Document the internal control structure based on your design. You should identify at least three control techniques that you have employed.
You should use the full range of Access tools. I will look for (1) completeness of solution, (2) depth of MS Access use, (3) cleverness of solution, (4) a strong perspective of the purchasing function, (4) care in form/query/report implementation, and (5) quality of your presentation
Rubric for phase 3
Supporting documentation describes the purpose of the forms
Sample solution to start from that it is referring to is attached.
Jillian's Candies Emporium Tables
ItemNo, Description, QOH, ListPrice
Employees (Purchasing Agent, Data Clerk, Accounts Payable Clerk, and Jillian)
EmployeeNo, EmployeeName, Address, Phone, DateStarted
VendorNo, VendorName, Address, Balance
AccountNo, Bank, Type, DateStarted, Balance
PONo, VendorNo*, EmployeeNo* (Purchasing Agent), EmployeeNo2* (Jillian), Date
PONo*, ItemNo*, Qty, Price
ReceiptNo, EmployeeNo*, VendorNo*, Date, Shipper
ReceiptNo*, ItemNo*, PONo*, Qty, Condition
PaymentNo, EmployeeNo* (A/P Clerk), EmployeeNo2* (Jillian), VendorNo*, AccountNo*,
DisposalNo, EmployeeNo*, Date
DisposalNo*, ItemNo*, QtyDisposed, Reason