(solution) Need extreme help with project. Please use Microsoft Access to

(solution) Need extreme help with project. Please use Microsoft Access to

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:

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;

  • Written answers to the questions outlines below
  • The database file in MS Access
  • Your team?s presentation of your results

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

  • Tables/Relationships  – 1 point
    • Set-up the tables and relationships as per the professor?s solution from Phase II
    • Set-up ample test data so that the forms, queries and reports that you create are realistic
    • The test data must be relevant to the year 2015
  • Internal Controls ? 1 point
    • Fully document 3 internal controls that you have implemented in your system
  • Forms  – 2 points
    • The form with the sub-form is worth 1 point; the other two forms are worth 1/2 point each
    • The forms must work ? you must be able to enter data into your tables from the forms
    • The forms should fulfill a business function within the purchasing process as described in the phase 3 definition
    • Care in implementation ? labels line up with fields, labels aren?t truncated, avail yourself of the features of Access (e.g., drop down menus, combo boxes, ?tab past? fields, etc.)
    • Don?t just show ID numbers (e.g., CustomerID, VendorID, EmployeeID, etc.) on the forms. Return the associated name as well, since the number by itself is not meaningful (closed loop verification)

    Supporting documentation describes the purpose of the forms

  • Queries ? 3 points
    • The four multi-table queries are worth 2 points together and the other three queries are worth 1 point together
    • The queries must work ? you must be able to retrieve information from your queries
    • The queries should fulfill a business function within the purchasing process
    • Care in implementation ? using expression builder, parameter queries, etc.
    • Don?t just show ID numbers (e.g., CustomerID, VendorID, EmployeeID, etc.) on the queries. Return the associated name as well, since the number by itself is not meaningful
    • Supporting documentation describes the purpose of the queries
  • Reports  – 2 points
    • Each report will be worth 1 point
    • The reports should fulfill a business function within the purchasing process as described in the phase 3 definition
    • Care in implementation ? labels line up with fields, labels aren?t truncated, avail yourself of some of the features of Access
    • Don?t just show ID numbers (e.g., CustomerID, VendorID, EmployeeID, etc.) on the reports. Return the associated name as well, since the number by itself is not meaningful
    • Supporting documentation describes the purpose of the reports

Sample solution to start from that it is referring to is attached. 

Jillian's Candies Emporium Tables
Candies
ItemNo, Description, QOH, ListPrice
Employees (Purchasing Agent, Data Clerk, Accounts Payable Clerk, and Jillian)
EmployeeNo, EmployeeName, Address, Phone, DateStarted
Vendors
VendorNo, VendorName, Address, Balance
CashAccounts
AccountNo, Bank, Type, DateStarted, Balance
PurchaseOrders
PONo, VendorNo*, EmployeeNo* (Purchasing Agent), EmployeeNo2* (Jillian), Date
PurchaseOrderDetail
PONo*, ItemNo*, Qty, Price
Receipts
ReceiptNo, EmployeeNo*, VendorNo*, Date, Shipper
ReceiptDetail
ReceiptNo*, ItemNo*, PONo*, Qty, Condition
CashPayments
PaymentNo, EmployeeNo* (A/P Clerk), EmployeeNo2* (Jillian), VendorNo*, AccountNo*,
Date, Amount
##CashPaymentsApplied
PaymentNo*, ReceiptNo*
CandyDisposal
DisposalNo, EmployeeNo*, Date
CandyDisposalDetail
DisposalNo*, ItemNo*, QtyDisposed, Reason