Implement Tables, Queries, Forms, Reports, & Navigation to meet the following requirements.

An "Add Customer Contact" use case needs to be implemented (in Access) for a sales organization. This will be used by salespeople and their managers to log any contact with a customer (phone, email, messaging, face-to-face,...) to a database system. The UI will be used many times a day and must have good usability (your implementation will be graded in part on usability). 

1. Data on the contact should include the customer contacted and their company, the date and time of the contact, the type of contact (phone, email, etc.), duration of the contact, the main topic of the contact (reason for it), any notes about the contact, whether it resulted in a sale, and if so the amount of the sale.

                  Hint: Don't forget the Salesperson

                  Hint: Properly configure field properties in the table design before creating forms/reports (masks, required,
                           validation rules, captions, control type and source (Lookup tab in field properties), ...)

1. Data on Customers should also be maintained, in separate tables in the database. Customer data should include name, address, company, and contact info.

                  Hint: Reminder that table fields should be simple, not composite

                  Hint: Reminder that a relation (with referential integrity on) should be defined between related tables

1. Validate required data has been entered and is of the right type and legal values for the fields.

                  If you have properly configured field properties in the tables, no additional validation logic should be needed

1. If the customer has been contacted before, users should be able to select them easily.  If not, or if customer information needs updating, then should be easy to do so from this UI

                  Hint: Use buttons to open forms for adding and updating customers (control wizard will walk thru setting up)

                  Hint: Form's pop up, modal, & data entry properties can be useful here

1. Once a customer is chosen, display a tabular subform on the UI of other contacts with the same customer in the last 60 days (read only)

                  Hint: Make a query first. You can use joins to get any info you want from Customers or SalePerson tables, and add 

                            the 60 day where clause there. Then when add subform, "use existing tables and queries", then choose the
                            query rather than the Contacts table. Then Choose from list, and show items using the Customer ID.

                  Hint: See second hint under "Once a salesman selected" 

1. Once a salesman is selected, management wants to see on the UI the total number of contacts the salesman has had in the last 14 days

                Hint: Use a calculated expression for the text property of a label or textbox. The domain aggregate functions
                           can be useful here, in particular DCount()

                Hint: The visible property determines if a control is visible. A macro can be created for the OnChange or
                          AfterUpdate events of the salesperson control to unhide the total number control once a salesperson is selected.  
                          The IF and SETPROPERTY macro commands will be useful for this.

1. Management also wants to see two small charts on the UI - a pie chart showing the proportion of all contacts for each salesperson in the last 30 days, and a histogram showing number of contacts for each salesman today.

                 Hint: Create queries for each chart first. Then tie the charts to the queries.

1. To provide some positive feedback to the salesperson, if it is indicated a sale is made show a label saying "Yeah!" in green font.  In addition, sales over $50,000 should be displayed with green background and blue font.

                 Hint: See the second hint under salesperson for the "Yeah!" label

                 Hint: Conditional formatting can be used for the sales amount

1. There should be a navigation system to get to the UI, as well as tabular views of customers, contacts, and salespeople.

                 Hint: Create / Navigation, then pick layout. This will create a navigation form that displays other forms inside it

                 Hint:  File / Options / Current Database - you can make the navigation form the startup form ('Display Form' option)

1. A grouped report of contacts by salesperson should be accessible from the UI and/or navigation

Answer rating: 100% (QA)

Creating an Access database for managing customer contacts in a sales organization involves designing tables forms queries reports conditional formatting and navigation Heres a stepbystep detailed exp... View the full answer