Access Database Assignment  SC_AC16_10aShelly Cashman Access 2016 | Module 10: SAM Project 1a

Shelly Cashman Access 2016 | Module 10: SAM Project 1a
West Coast Outdoor Advertisers
USING SQL

GETTING STARTED
· Open the file SC_AC16_10a_FirstLastName_1.accdb, available for download from the SAM website.
· Save the file as SC_AC16_10a_FirstLastName_2.accdb by changing the “1” to a “2”.
· Hint: If you do not see the .accdb file extension in the Save As dialog box, do not type it. The program will add the file extension for you automatically.
· Open the _GradingInfoTable table and ensure that your first and last name is displayed as the first record in the table. If the table does not contain your name, delete the file and download a new copy from the SAM website.

SPECIAL NOTE: SQL QUERIES
To ensure accurate grading in this project, use the conventions listed below when writing your SQL commands:
· Do not enclose field names and table names in square brackets [] when creating and modifying these SQL queries. All field names and table names consist of single words and do not require brackets.
· Use parentheses () in your SQL code only when specified.
· All SQL commands should end with a semicolon (;).
· Only modify the aspects of the SQL commands that are specified in the assignment steps.

PROJECT STEPS
Create a new query in SQL View based on the States table with the following requirements:
a. Add all fields from the States table to the new query using the asterisk (*) in the Select clause.
b. Be sure to end the SQL command with a semicolon (;).
c. Run the query to produce the results shown in Figure 1 on the next page.
d. Save the query as StatesQuery, and then close the query.

Figure 1: StatesQuery Results

Open the AdRepQuery in SQL View, and then add the LastName field to the SELECT clause (after the FirstName field). Run the query, and ensure that the results match those shown in Figure 2 below Save and close the AdRepQuery.

Figure 2: AdRepQuery Results

Open the RentalTotalsQuery in SQL View. Add a computed field to the SELECT clause (after the Months field) as described below:
e. The computed field should calculate the total cost for the billboard rental by multiplying the Cost field value by the Months field value. (Hint: Enter Cost * Months as the computation.)
f. Use TotalCost as the name(alias) for this computed field. (Hint: Use the AS clause.)
g. Run the query, and ensure that the results match those shown in Figure 3 below. (Hint: Your records may be in a different order.The last row is not displayed in the figure.) Save and close the RentalTotalsQuery.

Figure 3: Portion of RentalTotalsQuery Results

Open the RentalsCriteriaQuery in SQL View. Add a WHERE clause to the query in the position shown in Figure 4 below that restricts retrieval to only those rentals where the cost is greater than 800. (Hint: Figure 4 demonstrates the proper location for the WHERE clause, but it does not show the criteria that should be included in the WHERE clause.) Run the IfSuccessful_Status GA_Status_Icon SAM_Logo

true

false

ID FirstName LastName AssignmentGUID UserID

false First Last {39B9047B-E2F2-4E70-916E-70CF1D93F4C6} {39B9047B-E2F2-4E70-916E-70CF1D93F4C6}

ID FirstName LastName ProjectName SubmissionNum MaxScore Score EngineVersion

ID StepNumber Description IfSuccessful StepScore StepMaxScore ErrorText ActionName StepActionOrder

AdRepNumber LastName FirstName Address City State PostalCode PhoneNumber Salary DateHired Insurance Special Skills

AM01 Miller Abraham 54 Quarry Ave. Portland OR 97210 503-555-6032 ¤ 32,540.00 6/1/16 Fluent in Spanish

GZ01 Zdalnza Gregory 7 Moose Head Rd. Boise ID 83720 603-555-8765 ¤ 29,675.00 9/14/16 Trains new employees

KS02 Stenhope Kathryn 9 Shennadoah Ave. Salem OR 97311 503-555-0003 ¤ 34,576.00 10/5/15 Bachelor’s degree in Marketing

MR01 Riley Max 24 Springtown Rd. Casper WY 83114 781-555-2323 ¤ 30,450.00 8/6/16 Likes to travel; makes routine checks of billboards

RH01 Horton Rowan 12 Heather Rd. Portland OR 97211 207-555-9009 ¤ 35,275.00 7/13/15 Bachelor’s degree in Business Administration

SL01 Lorens Stephanie 15 River Rd. Spokane WA 99202 802-555-3339 ¤ 37,350.00 4/5/15 Working on a Master’s degree in Communications

UG01 Grea-Mayers Ursula 342 Pine St. Seattle WA 98101 207-555-8534 ¤ 41,765.00 12/12/14 Fluent in Spanish

AdvertiserID AdvertiserName Address City State PostalCode Phone Amount Paid Current Due AdRepNumber Customer Notes

BBF32 Babbage CPA Firm 464 Linnell Dr. Seattle WA 98104 ¤ 5,678.25 ¤ 7,875.25 UG01

BCC76 Burns Community College 867 Ridge Ave. Burns OR 97720 ¤ 0.00 ¤ 5,452.30 RH01

BMS53 Boise Medical Services 134 Main St. Boise ID 83720 6035552883 ¤ 7,562.40 ¤ 6,152.25 GZ01 Designs own ads

CDS29 Carter Dental Services 123 Second St. Dallesport WA 98617 ¤ 3,452.10 ¤ 5,475.75 SL01

CRR15 Columbia River Rafters 125 River Blvd. Portland OR 97209 ¤ 4,567.25 ¤ 3,600.50 RH01

CWW01 Casper Western Wear 10 Broadway St. Casper WY 83114 ¤ 0.00 ¤ 4,125.50 MR01

GAD74 Grant Antique Dealers 78 Catawba Dr. Grants Pass OR 97526 ¤ 1,500.00 ¤ 1,200.00 GZ01

GPM12 Grants Pass Motel 235 Second Ave. Grants Pass OR 97526 ¤ 2,460.75 ¤ 2,460.75 AM01

GRI03 Germann Inn 345 Hartsel Ave. Madras OR 97741 ¤ 1,325.45 ¤ 2,900.50 AM01

HAR01 Haydee’s Restaurant 234 Twiddy St. Eugene OR 97402 ¤ 3,000.00 ¤ 2,500.00 RH01

HCH10 Hendley County Hospital 216 Rivard Blvd. Spokane WA 99201 ¤ 9,456.75 ¤ 7,234.60 SL01

IOA23 Idaho Outdoor Adventures 12 Third St. Twin Falls ID 83301 ¤ 4,200.00 ¤ 6,500.00 KS02

KDS21 KAL Design Studio 116 Pine St. Arden WA 99202 ¤ 775.00 ¤ 1,550.50 UG01

KGS04 Kyle General Store 421 First St. Cokeville WY 83114 ¤ 3,452.00 ¤ 2,875.50 MR01

LCA42 Lews and Clark Adventures 100 Main St. Goldendale WA 98620 ¤ 2,500.00 ¤ 4,500.00 SL01

LFS02 Lewis Furniture Store 267 Main St. Payette ID 83661 ¤ 1,234.50 ¤ 2,890.70 MR01

MBT14 Mike’s Bike Tours 234 Gilham




Why Choose Us

  • 100% non-plagiarized Papers
  • 24/7 /365 Service Available
  • Affordable Prices
  • Any Paper, Urgency, and Subject
  • Will complete your papers in 6 hours
  • On-time Delivery
  • Money-back and Privacy guarantees
  • Unlimited Amendments upon request
  • Satisfaction guarantee

How it Works

  • Click on the “Place Order” tab at the top menu or “Order Now” icon at the bottom and a new page will appear with an order form to be filled.
  • Fill in your paper’s requirements in the "PAPER DETAILS" section.
  • Fill in your paper’s academic level, deadline, and the required number of pages from the drop-down menus.
  • Click “CREATE ACCOUNT & SIGN IN” to enter your registration details and get an account with us for record-keeping and then, click on “PROCEED TO CHECKOUT” at the bottom of the page.
  • From there, the payment sections will show, follow the guided payment process and your order will be available for our writing team to work on it.