XXX Company's Action Products access database queries

XXX Company's sales department wants to establish a database to track key information about its salespersons. Salespersons are assigned to either industrial or commercial customers and to one of four sales regions (East, North, South, and West). Action Products wishes to record the flowing information for each salesperson: Their name, the date they were hired, their sales commission rate, their sales region, and the category of customer served. The table below shows this information for Action's sales staff.

Emp No Emp Name Date Hired Comm Rate Region Cust Category

2584 A. Baker 1/6/99 5% East Commercial

9589 M. Chamberlin 5/8/98 4% South Commercial

4523 T. Turner 9/12/96 6% North Industrial

9436 S. Davis 11/25/01 3% East Industrial

3845 Y. Ernest 8/2/00 2% South Commercial

5890 F. Gleason 2/9/97 4% West Industrial

3496 D. James 3/7/00 2% West Commercial

6209 P. Mattison 6/9/01 2% North Commercial

6324 T. Thomas 5/5/02 1% North Industrial

7754 H. Hunt 9/12/02 1% East Commercial

8080 K. Enofe 1/1/05 2% East Commercial

8578 G. Scott 1/8/06 3% West Industrial

8290 E. Jones 9/8/05 1% East Commercial

8440 J. Hunt 3/2/06 4% North Industrial

9120 K. Gates 5/5/07 6% South Commercial

2250 D. Duncan 6/6/05 6% North Industrial

2437 J. Hue 3/4/04 3% East Industrial

2690 M. Doub 10/15/03 2% South Commercial

3250 F. Hartman 11/1/06 4% West Industrial

4485 M. Reynolds 1/1/07 6% East Commercial

5432 D. Gleman 2/6/08 4% South Industrial

A. Using Microsoft Access software package, create a table to store the preceding information and enter the sample data provided.

Set a validation rule that limits commission rate between: 1% - 6% (.01 - .06).

Set a validation rule that validates region as East, West, North, and South.

B. Save the database as Action Products and the table as Employees.

C. Generate database queries to answer these questions and get a printed listing of your results.

1. Get a listing of the name, commission rate, and hire date of all salespersons who sell to commercial customers. Sort the result in order from the first hired to the most recently hired salesperson. Set the properties on Commission rate column to percent with no decimals and set the caption to Commission Rate Save the query as Query 1 - Hire Date.

2. Get a listing of the names and commission rates of all salespersons in the eastern region or western regions sorted alphabetically. Save the query as Query 2 - Eastern & Western Salespersons.

3. Get a listing showing the number of salespersons receiving each commission rate and total sales in that rate. Save query as Query 3 - Number of Salespersons by Commission Rate. Use the field property option to set the captions and percents as follows.

Commission rate Number of Salespersons Total Sales

1%

2%

3%

4%

5%

6%

XXX wishes to track its sales using database software. For each sale, the firm wants to record the salesperson's name, the customer's name, the sales date, and the dollar amount of the sale. Sample data for recent sales in the east region follow:

Using Access database software, create an appropriate table for these sales data. Add this new table to the database where you stored the employee table. Enter the sample sales data into your sales table.

Emp No Emp Name Cust Name Sales Date Sales Amount

2584 A. Baker R. Milton Inc. 3/3/08 $14,893

9589 M. Chamberlin K. Cohen Corp. 5/6/08 $25,902

4523 T. Turner D. Wallas, Inc. 4/30/08 $33,988

9436 S. Davis Bidell Associates 7/5/08 $78,920

3845 Y. Ernest Norton Industries 6/8/08 $12,954

5890 F. Gleason Aero Corp. 8/10/08 $29,870

3496 D. James Sunny Dell 10/11/08 $42,674

2584 A. Baker Pine & Weber 7/19/08 $33,945

4523 T. Turner Hillside Associates 8/22/08 $28,950

7754 H. Hunt Skycab, Inc. 2/12/08 $36,078

8080 K. Enofe Only Bathrooms 3/5/08 $54,855

8578 G. Scott Hall & Sons 4/4/08 $25,000

8290 E. Jones All New Kitchen 5/18/08 $35,780

8440 J. Hunt Facelift, Inc. 6/20/08 $12,900

9120 K. Gates Build and Save 7/23/08 $18,450

2250 D. Duncan Kidman Bros. 8/28/08 $24,508

2437 J. Hue Sideway Corp. 9/9/08 $8,990

2690 M. Doub Full Service, Inc. 10/3/08 $26,800

3250 F. Hartman Nasbit & Sons 10/10/08 $30,000

7754 H. Hunt Mills Corp. 10/4/08 $18,860

2584 A. Baker Pincox. Inc. 9/9/08 $13,985

9436 S. Davis Delmar Corp. 9/19/08 $29,000

5890 F. Gleason J. B. Newton 10/4/08 $23,562

9589 M. Chamberlin Almaro & Sons 11/15/07 $40,000

4523 T. Turner Biden & Durben 11/13/07 $45,980

4485 M. Reynolds Devry, Inc. 12/15/07 $35,560

5432 D. Gleman TEK, Ltd. 4/8/08 $65,980

4. Create a relationship between the Employees and Sales tables. Save the relationship.

Using this table of sample data prepare: the following:

5. A form and subform of sales by the salesperson. Name this form Employee Sales. Use the attached layout on page 5. (use the Zoom icon to make form readable)

6. A sales report by customer category as outlined on page 6 (use the Zoom icon to make Report readable). Name this report as Sales by Customer Category.

7. Save the Action Products database.