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?

Reviews

Write a Review

 

Database Management System Questions & Answers

  List the details of parts supplied by quality

Attribute dob is the customer's date of birth, stops is the number of stops (0-??) a flight requires, and onTime is a percentage (0-100) indicating how often a flight is on time. Keys are underlined.

  Design tool for relational databases

Entity relationship model is in wide use as a design tool for relational databases. Why do you think designers have chosen this model, and what makes it an effective design tool?

  Provide explanation about each of the applied techniques

In the assignment report provide explanation about each of the applied techniques. In your Excel workbook file place the results in separate columns in the corresponding spreadsheet.

  Construct a query that will show the number of days

Construct a query that will show the number of days that exist between the first invoice and last invoice, for each month, for each employee, using the DATEDIFF function

  What advice would you give the managers of this company

Advice given to managersThe storage system that best fits the needs of the companyReasons why this is the best storage system.

  How security policies can be utilized to mitigate each risk

Identify which U.S. compliance laws govern for each risk identified and summarize the implications. Evaluate how security policies, procedures, and practices can be utilized to mitigate each risk identified. Recommend appropriate security controls th..

  Design and implement a small database application

Perform the conceptual database design using Entity-Relationship model. You must submit the Entity-Relationship diagram of your database and develop queries in the target DBMS

  Aspect of database or enterprise systems

Find one or more current articles (last six months) describing on aspect of database or enterprise systems. Summarize the article(s) and provide your own perspective, and then browse through the other student posts to learn about other related tec..

  Calculate canonical cover for set of functional dependencies

Calculate a canonical cover for above set of functional dependencies (show each step of your derivation with an explanation).

  Er diagram that you are unable to capture in the sql

ER diagram that you are unable to capture in the SQL statements and brie?y explain why you could not express them.

  Secure storage and retrieval process for healthcare data

Formulate a secure storage and retrieval process for healthcare data. Create a PowerPoint that highlights each of the topics below: Databases, Data dictionaries, data mining, and data warehouses and Characteristics of data quality

  Installing the ms-sql server on your computer

Use Hands-on Projects that are in the back of your Chapter to introduce you to the Microsoft SQL Server 2008.

Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd