Details of this Paper

XXX Company's Action Products access database queries




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.;Please follow the attached form and report layouts.


Paper#66653 | Written in 18-Jul-2015

Price : $47