John Miller owns a lawn service
John Miller owns a lawn service and tracks all charges for mowing, landscaping, tree trimming, and other services in a database. Some customers are eligible for discounts. You have been asked to create a new table that tracks customer payments and to create queries that will help John with decision making.
In tblPayments, make the following changes to the field properties: Forthe field PmtID, add a custom format to display PMT before the IDnumber and make it the primary key with new values in sequentialincremental order. For the CustID, create a Lookup field using thetblCustomers table, including the fields CustID, CustLastName, andCustFirstName. Sort in Ascending order by CustID and hide the keycolumn. Add Customer as the caption. For the PmtMethod field,create a lookup field that lists the following values: Cash, Check,MasterCard, Visa, American Express, and Discover. Limit the user’sselection to what is on the list. Add the caption Method.
Enter the following data in tblPayments:
CustID ScheduleID PmtAmount PmtDate PmtMethod
Anderson 5 $225 3/3/2018 American Express
Saunders 6 $75 5/20/2018 Visa
Wong 7 $950 5/5/2018 MasterCard
Mead 8 $175 4/30/2018 Visa
Fowler 10 $85 4/12/2018 Visa
Sanders 11 $75 3/13/2018 Visa
Create a one-to-many relationship between ScheduleID in tblScheduleand the ScheduleID in tblPayments and enforce referential integrity.
Create a query that calculates any customer discounts on eachservice. Display CustFirstName, CustLastName, ScheduleID,ServiceDate, DiscountType, a field to display the discount amountnamed DiscountAmt, and a field that displays the actual amount duenamed AmtDue in the query. The LawnMember discount is 10%. TheReferral discount is 25%. The Contract discount is 20%. Only displayservices that receive a discount. Save the query as qryDiscounts.
Create a query that finds all services scheduled between 4/1/2018 and 5/31/2018. The query results should display ScheduleID, ServiceDate, PmtAmount, PmtDate, and PmtMethod. Show only the customers who paid with Visa. Sort in Ascending order by PmtAmount. Save the query as qryVisaDates
Create a Top Values query that displays the 10 highest service rates being charged to customers. The query results should display CustFirstName and CustLastName and ChargeAmount. Save the query as qryTopValues
Sample Solution
The post John Miller owns a lawn service appeared first on nursing writers.
“Looking for a Similar Assignment? Get Expert Help at an Amazing Discount!”
“Is this question part of your assignment? We Can Help!”
John Miller owns a lawn service was first posted on July 26, 2020 at 1:50 pm.
©2020 "Best Essays". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement. Please contact me at ukbestwriting@gmail.com
