Create a MS Access database using the attached normalized tables, forms, validation rules, queries, reports, controls. Please read carefully the instructions attached.

Do you require help with your paper? Use our custom writing service to achieve better grades and meet your deadlines. Trust our team of writing experts with your work today, and enjoy peace of mind.


Order a Similar Paper Order a Different Paper

Create a MS Access database using the attached normalized tables, forms, validation rules, queries, reports, controls.

Please read carefully the instructions attached.

Create a MS Access database using the attached normalized tables, forms, validation rules, queries, reports, controls. Please read carefully the instructions attached.
Normalized Tables Table Name Primary Key Foreign Key Other Attributes PurchaseOrder PONo PreparedEmployeeNo, AuthorizedEmployeeNo, VendorNo PODate ReceiveOrder ReceiptNo EmployeeNo, VendorNo ReceiptDate MakePayment PaymentNo PreparedEmployeeNo, AuthorizedEmployeeNo, VendorNo, AccountNo PaymentDate, PaymentAmount, FlowerInventory ItemNo Description, QtyOnHand, ListPrice Cash AccountNo AccountType, Bank, AccountBalance Vendor VendorNo VendorName, VendorAddress, Balance Employee EmployeeNo EmployeeName, EmployeeAddress, DateStarted, JobTitle, Phone FI_PO PONo, ItemNo QtyOrdered, OrderPrice FI_RO ItemNo, ReceiptNo ItemCondition PO_RO PONo, ReceiptNo QtyReceived RO_MP ReceiptNo, PaymentNo VALIDATION RULES AND VALIDATION TEXTS Table Name Validation rule Corresponding Validation Text Why the role is implemented FlowerInventory >=50 And <=200 >=0 Quantity on hand (i.e., QtyOnHand) should be between 50 and 200 Price cannot be a negative number The QtyOnHand should never be less than the Reorder Point which we decided to be less than 50 or more than 200 flowers to keep the inventory under control – we don’t want the flowers to spoil. To ensure that the list price is a positive number since we cannot hold less than 0 of a product. Cash >=0 The account balance cannot be negative number To ensure that the bank account balance cannot go below 0 (Sign test which is to use appropriate arithmetic signs) Employee <=Now() The start date must be today’s date or earlier Validity test Input Mask The employee phone number must be entered in a specific format To ensure that a future date will not be entered since employees are entered into the system upon hiring, not prior and to reduce the potential of errors. Specific format for the phone has 10 digits and must be entered in this format to be valid to ensure that incomplete or incorrect data is not unintentionally entered. MakePayment >0 Payment amount must be greater than 0 To make sure that the payment being sent is greater than zero to avoid blank checks being sent or loss of confidence in us by our vendors. PurchaseOrder <="ReceiptDate" Purchase date must be earlier or equal to receipt date To ensure that the order was purchased before the delivery date. Ensures that dates are entered correctly and that flowers are not delivered prior to order being processed. ReceiveOrder <="PurchaseDate" Receipt date must be greater than or equal to purchase date To ensure that there's a purchase order earlier or equal to the receipt date because a purchase order must exist earlier to the date of the receipt of goods. This ensures proper protocols are followed. Vendor >0 Vendor balance must be greater than 0 to pay. If there is a negative balance deduct from payment amount To ensure that vendors are not overpaid; To ensure there is in fact a balance to pay that is greater than zero. Reduces accounting errors and reduces mismanagement of funds. FL_PO >0 Order price must be greater than 0 The order price must be greater than zero to ensure that all orders have valid amounts and that the product is not unintentionally being delivered without intention to receive funds. FL_RO “Excellent” Or “Good” Or “Bad” Can only enter excellent, good or bad Simple validity test confirms if input value is acceptable. Confirms if input value is acceptable. Easier to categorize and manage quality of orders and to track trends or issues when applicable. PO_RO >0 Quantity received must be greater than 0 To ensure that inventory is being received and properly accounted for. RO_MP This table only has primary keys. QUERIES Query Name Table Used Why the query is important for managers/decision makers. Preparer or Authorizer Employee This query shows the names of employees who are permitted to prepare purchase orders and authorize the purchase orders. This query is important to see what roles/whos authorized for what type of tasks Vendor Balance Vendor This query generates a report that shows each vendor’s total current balances in descending order. This query is important so that we can view with whom we have the largest balance (which vendor we owe the most money to.) This may influence which vendor we may want to pay first. Inventory FlowerInventory This query generates a report that shows inventory items that are getting low and will need to be reordered soon . This is important to make sure that the inventory is not lower than 50, this checks which inventories are getting lower we’re getting closer to reorder point February Purchases Employee, PurchaseOrder, FL_PO This query shows the details of purchase orders made in February 2020 and names of employees that prepared these purchase orders. This query is important for decision makers because they can run it to view the quantity and description of items ordered by employees in a specific period which can be helpful in viewing which flowers are more in demand. Roses Purchases Employee, PurchaseOrder, FL_PO, FlowerInventory This query generates a report that shows the number of inventory items (roses) purchased in Feb 2021 and the total of order price. It verifies the number of roses purchased in Feb 2021. It’s helpful to know how many roses were purchased and to easily compare the purchase of roses during other seasons – particularly in months where roses are more difficult to acquire. Vendor Payments Vendor, Make Payment This query generates a report to confirm whether vendors’ balances were paid in full or not. This assists management in managing relationships with vendors, managing expenses, and balancing accounts (which accounts to pay first) when necessary. Average Orders Employee, PurchaseOrder, FlowerInventory, FL_PO This query generates a report that shows the average number of orders placed by each employee during the year of 2021. This enables management to determine which types of flowers generate the most income, which employees are adept at selling which flowers, and assists in catching long term trends in sales. FORMS Form Name Function Who would use Purpose Purchase Order Form To order inventory Employee who prepares purchase orders To record business transaction between the buyer and the seller (vendor) Employee Entry Form To enter employee’s data information The manager/CEO of the company This can be used when the employee is hired. To keep record of employees being employed by the company Vendor Entry Form To enter vendor data Employee who prepares purchase orders To keep vendors records REPORTS Report Name What benefits the report provide How often the report will be used. Top Selling Employees This report shows who is selling the most items per order giving management information on the performance level of each individual employee. Monthly to determine who gets more hours, bonuses, raises etc. Purchase Order Detail by Vendor This report shows the purchase order details for the year listed by the vendor. It includes details such as the vendors balance, PO numbers placed and the detail for each PO placed by the vendor. This report can be used to see what items your vendors are ordering and how often. This can be used to gain an understanding of which items are the most popular amongst your vendors. CONTROLS Control Description General Controls Separation of duties – The preparer of purchase orders is different than who authorizes the purchase order to ensure separation of responsibilities (preventive control) Input Controls Range Control on FlowerInventory Table: Validation rule set to ensure that the values are always within our set limits (>=50 And <=200) Sign Test on FlowerInventory, Cash, Make Payment, Vendor, FL_PO, and PO_RO Tables: Set to ensure that the values are always greater than or equal to zero. Validity Test on Employee Table and FL_RO: Set to ensure that the date entered matches one of the acceptable values (which is that the start date should be today’s date or earlier.); set to ensure that the flower descriptions entered on FL_RO are only from the three acceptable values (Bad, Good and Excellent.) Field Test on Employee Table: Set to ensure that the phone number entered is 10-digits and in a specific format Valid Combination on PurchaseOrder and ReceiveOrder: Set to ensure that purchase date is earlier or equal to receipt date and receipt date is greater than equal to the date of the purchase. Entity integrity control Our primary keys: PONo; ReceiptNo; PaymentNo; ItemNo; AccountNo; VendorNo; EmployeeNo; PONo, ItemNo; ItemNo, ReceiptNo; PONo, ReceiptNo; ReceiptNo, PaymentNo are all unique and not null. Referential integrity EmployeeNo(preparer and authorizer) in PURCHASE-ORDER should exist in EMPLOYEE VendorNo in PURCHASE-ORDER should exist in VENDOR EmployeeNo in RECEIVEORDER should exist in EMPLOYEE VendorNo in RECEIVEORDER should exist in VENDOR EmployeeNo(preparer and authorizer) in MAKEPAYMENT should exist in EMPLOYEE VendorNo in MAKEPAYMENT should exist in VENDOR AccountNo in MAKEPAYMENT should exist in CASH Queries Vendor Payments Query: Verifies that the sum of the payments made to vendors equals the vendor balance Create a MS Access database using the attached normalized tables, forms, validation rules, queries, reports, controls. Please read carefully the instructions attached. Group Project – Stage 3 Kim’s Flowers Inc. Purchasing (Expenditure) Cycle Requirements: The final part of the project requires your team to implement the logical data model in MS Access. Please read carefully the instructions below before you start working on your project. You should submit your database file and a separate word document with answers/explanations as requested below. Create tables in the MS Access file by using the provided docs (i.e., the normalized tables, ERA Diagram) In each table, define the primary key. For each attribute, define the data type, provide a field description, and define the field properties. For each table, there should be at least one validation rule and a corresponding validation text and explain why these validation rules are implemented. Establish relationships between all the tables you created. Make sure that you enforce the referential integrity in all your relationships. Create at least three forms.( see Forms) One of which should be a form with a subform. Describe the function of each form, who would use it, and its purpose. Reference Populate your tables with fictitious data entries. You should sufficiently populate your tables so that you can demonstrate your design (about 20 rows for events and 10 for agents and resources). Create queries and explain why these queries are important for managers/decision makers. You should have at least four multiple table queries in your design, and three other queries. At least one of your queries should perform calculation. You can create a query using either SQL (i.e., SELECT statement) or Expression Builder in Query Design. Create at least two reports for management/decision makers. Describe what benefits these reports provide for decision makers, and how often they will be used. Document the existing internal control structure based on your current design. ERA Diagram

Writerbay.net

Our writing experts are ready and waiting to assist with any writing project you may have. From simple essays, research papers, lab reports, and dissertations, to online classes, you can be sure we have a service that perfectly matches your needs.


Order a Similar Paper Order a Different Paper