A retail Pet Store has asked you to design a database to capture the important aspects of their business data. In this assignment, you will build on the basic design to add tools to assist the user to interact with the database.

Tables

The store manager has asked if you can add two new tables to the database to help capture Invoice and Payment data. Each Sales record should have at least one Invoice associated with it, and each of these Invoices will have at least one Payment record. Invoices need to capture the SaleID that the invoice is for, the Date that the Invoice was created, and the Shipping Address data (which may or may not be the same as the Customer address). Payments need to capture the InvoiceID, the Date of the payment, the Amount of the payment, and the Type of payment (Cash, Cheque or Credit Card – you do NOT need to record any details of credit cards at this time.) The manager has also asked you to modify the Pets table to include a Final Price for each pet, by calculating the sales tax amount and adding it to the Price (assume a 12% tax rate for this field). The basic design of the database also needs to be extended to include user tools, like Forms and Reports.

Forms

There should be a basic form for editing or adding records to each of the Locations, Pets, Employees and Customers tables. There needs to be a form for recording basic Sales records, which should contain Lookup fields to select the required field values from the Locations, Pets, Employees and Customers tables. There should be a form for editing Sales and Invoices together. This form should show all of the Sale record data, and contain a Sub-form (in datasheet format) that allows the user to create Invoice records. The main Sales form should contain a calculation that COUNTS all the Invoices for that Sale (there may be more than one). There should be a form for editing Invoice and Payment records. This form should show Invoice data and contain a Sub-form to display and allow the user to enter Payment records. The main Invoice form should contain a calculation to show the total of the Payment amounts associated with each Invoice.

Reports

The manager would like to see two Reports created. One report will show a list of all existing Sales records for the current year, organized by store Location, and sorted by Customer last name. You should show the total count of Sales records for each Location (Group Totals), and for the company overall (Grand Totals). The other report will show a list of unpaid Invoices, grouped by Customer last name, showing the total dollar amount outstanding for each Customer. This report should also show the number of days each Invoice has gone unpaid (the difference between the invoice date and the current date, in days.) To test this report, you will need to create several Invoice records without creating any Payment records. In order to produce the forms and reports above, you may need to add queries to generate or calculate the required data. You may build any query you need to do this, although the final database you build should only contain useful queries; do not leave “testers” or experimental queries in the final design.