Create a relationship between the employees and sales tables

Assignment Help Database Management System

Reference no: EM13806581

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.

Reference no: EM13806581

Questions Cloud

The rejection region in the left tail under the curve. : At a 1% level of significance, the manufacturer should conclude that there has been an increase in the level of interest in this new flavour.

Survey conductor application : Survey Conductor Application: You have been asked by your manager to create a user-friendly application that will present a survey to your customers

Engineers to accept the tickets : Would it be ethical for your engineers to accept the tickets? Why or why not? Would it be ethical for you to accept the tickets? Why or why not?

The driving force behind another terrorist group in egypt : Two al Qaeda groups in Madrid were broken up by Spanish authorities shortly after 9/11. These terrorists had connections with Jihadists in ____. Osama bin Laden was the driving force behind another terrorist group in Egypt, the Egyptian Islamic..

Create a relationship between the employees and sales tables : Create a relationship between the Employees and Sales tables. Save the relationship. 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)

Problems based on market interest rate changing : What is the PV of all the payments from the lottery winning at the time of today?

What is the total change in internal energy : What is the total work done on the gas in the process - What is the total change in internal energy?

Build a project communication plan : Use your imagination and the Greer Communications Planner template (it is one of the templates in the Greer templates file which is located in Doc Sharing) to build a Project Communication Plan that encompasses the information requirements as outl..

How many turns are in the secondary coil : An ac generator supplies 20 A at 440 V to a 10,000 V power line. If the step up transformer has 150 turns in its primary coil, how many turns are in the secondary coil?

User Account

All Pages