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.