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

Normalized Tables

Table NamePrimary KeyForeign KeyOther Attributes
PurchaseOrderPONoPreparedEmployeeNo, AuthorizedEmployeeNo,VendorNoPODate
ReceiveOrderReceiptNoEmployeeNo, VendorNoReceiptDate
MakePaymentPaymentNoPreparedEmployeeNo, AuthorizedEmployeeNo, VendorNo, AccountNoPaymentDate, PaymentAmount,
FlowerInventoryItemNoDescription, QtyOnHand, ListPrice
CashAccountNoAccountType, Bank, AccountBalance
VendorVendorNoVendorName, VendorAddress, Balance
EmployeeEmployeeNoEmployeeName, EmployeeAddress, DateStarted, JobTitle, Phone
FI_POPONo, ItemNoQtyOrdered, OrderPrice
FI_ROItemNo, ReceiptNoItemCondition
PO_ROPONo, ReceiptNoQtyReceived
RO_MPReceiptNo, PaymentNo

VALIDATION RULES AND VALIDATION TEXTS

Don't use plagiarized sources. Get Your Custom Essay on
Create a MS Access database using the attached normalized tables, forms, validation rules, queries, reports, controls.
Just from $13/Page
Order Essay
Table NameValidation ruleCorresponding Validation TextWhy the role is implemented
FlowerInventory>=50 And <=200>=0Quantity on hand (i.e., QtyOnHand) should be between 50 and 200Price cannot be a negative numberThe 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>=0The account balance cannot be negative numberTo 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 earlierValidity testInput MaskThe employee phone number must be entered in a specific formatTo 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>0Payment amount must be greater than 0To 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 dateTo 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 dateTo 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>0Vendor balance must be greater than 0 to pay. If there is a negative balance deduct from payment amountTo 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>0Order price must be greater than 0The 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 badSimple 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>0Quantity received must be greater than 0To ensure that inventory is being received and properly accounted for.
RO_MPThis table only has primary keys.

QUERIES

Query NameTable UsedWhy the query is important for managers/decision makers.
Preparer or AuthorizerEmployeeThis 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 BalanceVendorThis 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.
InventoryFlowerInventoryThis 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 PurchasesEmployee, PurchaseOrder, FL_POThis 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 PurchasesEmployee, PurchaseOrder, FL_PO, FlowerInventoryThis 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 PaymentsVendor, Make PaymentThis 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 OrdersEmployee, PurchaseOrder,FlowerInventory, FL_POThis 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 NameFunctionWho would usePurpose
Purchase Order FormTo order inventoryEmployee who prepares purchase ordersTo record business transaction between the buyer and the seller (vendor)
Employee Entry FormTo enter employee’s data informationThe manager/CEO of the companyThis can be used when the employee is hired. To keep record of employees being employed by the company
Vendor Entry FormTo enter vendor dataEmployee who prepares purchase ordersTo keep vendors records

REPORTS

Report NameWhat benefits the report provideHow often the report will be used.
Top Selling EmployeesThis 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 VendorThis 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

ControlDescription
General ControlsSeparation of duties – The preparer of purchase orders is different than who authorizes the purchase order to ensure separation of responsibilities (preventive control)
Input ControlsRange 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 formatValid 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 controlOur primary keys: PONo; ReceiptNo; PaymentNo; ItemNo; AccountNo; VendorNo; EmployeeNo; PONo, ItemNo; ItemNo, ReceiptNo; PONo, ReceiptNo; ReceiptNo, PaymentNo are all unique and not null.
Referential integrityEmployeeNo(preparer and authorizer) in PURCHASE-ORDER should exist in EMPLOYEEVendorNo in PURCHASE-ORDER should exist in VENDOREmployeeNo in RECEIVEORDER should exist in EMPLOYEEVendorNo in RECEIVEORDER should exist in VENDOREmployeeNo(preparer and authorizer) in MAKEPAYMENT should exist in EMPLOYEEVendorNo in MAKEPAYMENT should exist in VENDORAccountNo in MAKEPAYMENT should exist in CASH
QueriesVendor Payments Query: Verifies that the sum of the payments made to vendors equals the vendor balance

Calculate your order
Pages (275 words)
Standard price: $0.00
Client Reviews
4.9
Sitejabber
4.6
Trustpilot
4.8
Our Guarantees
100% Confidentiality
Information about customers is confidential and never disclosed to third parties.
Original Writing
We complete all papers from scratch. You can get a plagiarism report.
Timely Delivery
No missed deadlines – 97% of assignments are completed in time.
Money Back
If you're confident that a writer didn't follow your order details, ask for a refund.

Calculate the price of your order

You will get a personal manager and a discount.
We'll send you the first draft for approval by at
Total price:
$0.00
Power up Your Academic Success with the
Team of Professionals. We’ve Got Your Back.
Power up Your Study Success with Experts We’ve Got Your Back.
Live Chat+1(978) 822-0999EmailWhatsApp

Order your essay today and save 20% with the discount code GOODESSAY