Franco's Landscape Services

Mr. Franco Forest owns a small lawn care business with three employees. He owns the lawn mowers, rakes, and weed whackers that are housed in a storage area at the back of his home. He currently keeps his records in a spiral-bound book. He has a page for his list of customers, and their information including when he first started to do their landscaping, and the size of their lawns. He has another section to keep track of payments. Franco usually requires payment on completion of the service, but this rarely happens and he has lost track of who owes him and how much.

Franco realizes that he is not as efficient as he could be in his record keeping and asks you to help him to track customer service dates, payments and lawn care revenue. He may want to get a loan to purchase some more equipment. He provides you with his book of customers, and payments and prices of the various lawn sizes.

To help Franco with the reporting requirements, you need to work with Customer, Payment, and Prices tables that are available in a database. You should also prepare several queries, as well as some reports to share with him. To help him with his loan information, you may need to perform some calculations in a spreadsheet to better advise him.

Project Requirements Due

Franco's cousin has entered some data from his notebook to a database that currently includes Customer, Payment and Prices tables:

1. Create relationships between pairs of tables and enforce referential integrity.

2. Create a form with the customer data on the main form and the Payment data in the sub-form. (Use Create/More Forms/Form Wizard)

3. Add five new customers for 2012.

The five new customers name are: Wiggles Belisle, Wooly Arnold, Winstar Arzu, Winsor Major; Willow Nunez

4. Add additional payments up to Feb 2013 for any 10 customers. That is, you can add many payments for one customer if you wish, but 10 customers must have used Franco's services.

5. Create the following queries:

a. List the customers who have medium-sized lawns, along with their service day. Include first and last names. Do not show the lawn size field in your result. Name this query Q1MediumLawns.

b. Calculate how many customers there are for each lawn size. Name this query Q2CustomersPerSize.

c. Create a new field called Earnings to add the Standard Fee and Additional One Time Fee. Include the customers' First and Last Names and the Amount Paid. Name this query Q3ExpensesA.

d. Use the Q3ExpensesA query to add a new field called Amount Owed to calculate how much each customer owes Franco. Name this query Q3ExpensesB.

e. Calculate how much Franco has earned since he started his business compared to how much his customers owe him. Name this query Q4Earnings.

6. Franco also needs some reports:

a. Create a report to show the list of Franco's customers, grouped by Service Day. Sort the customers in descending order by last name. Give the report the title ‘Customers by Service Day'.

b. Create a report that is grouped by Lawn Size and sorted by the Customer's name. Also include the Earnings and Amount Owed fields. Give the report the title ‘Customers by Lawn Size'. Ensure that information on each customer is printed on a separate page. Also, every other line in the main report should be shaded.

7. You think that perhaps a chart of the customers' information would be more impactful and heard that they are two ways to achieve this chart::

i. Export the query Q2CustomersPerSize to a new sheet in a spreadsheet and sort by the lawn size. Then create a suitable chart. Ensure that the chart is labelled appropriately and is placed on a new sheet. Name the Spreadsheet as MGMT2006_G#_. For example, MGMT2006_G50_ Jane Doe.

ii. Create the same chart using Access, and save it as the same name Q2CustomersPerSize in the database.

8. Franco is thinking of purchasing some new equipment. He is thinking of investing the sum of his earnings for a loan. He has checked with three banks and their interest rates and repayment periods in years are: Bank A: 7.25% over 3 years; Bank B: 6.75% over 4 years and Bank C: 5.75% over 5 years.

Using the same spreadsheet, in a new sheet which you should name LOAN, calculate Franco's monthly payments for each, and advise Franco on the best option.