SC_AC16_10a
Access Database Assignment SC_AC16_10a
Shelly 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 query, and check your results. Save and close the RentalsCriteriaQuery.
Figure 4: RentalsCriteriaQuery in SQL View
Open the BillboardTypesQuery in SQL View. Add an OR clause to the WHERE clause in the query so that the query retrieves only those records in which the Type field is equal to Digital or Poster. (Hint: Currently, the query only retrieves records where the Type field is equal to Digital.) Run the query, and ensure that the results match those shown in Figure 5. Save and close the BillboardTypesQuery.
Figure 5: BillboardTypesQuery Results
Open the AdRepStateQuery in SQL View. Add an AND clause to the WHERE clause in the query so that the query retrieves only those records in which the State field is equal to OR and the City field is equal to Portland. (Hint: Currently, the query only retrieves records in which the State field is equal to OR.) Run the query, and ensure that the results match those shown in Figure 6. Save and close the AdRepStateQuery.
Figure 6: AdRepStateQuery Results
Open the OregonCountQuery in SQL View. Modify the SELECT clause to count the number of billboards that are located in OR. (Hint: Change the SELECT clause to COUNT(BillboardID)). Use the AS clause to set the alias of the function result to StateCount. Run the query and ensure that the results match those shown in Figure 7 below. Save and close the OregonCountQuery.
Figure 7: OregonCountQuery Results
Open the JoinQuery in SQL view. Add a WHERE clause that joins the Billboard table and the States table. The common field in both tables is State. You will need to qualify the State field in the WHERE clause. Run the query, and ensure that the results match those shown in Figure 8. (Hint: All rows are not displayed in the figure. The order of the records may differ. There should be 37 records in the result.) Save and close the JoinQuery.
Figure 8: Portion of the JoinQuery Results
Open the BillboardCirculationQuery in SQL View. Add a caption to the DEC field in the SELECT clause (Hint: Use the AS clause.) Use Circulation as the caption for the DEC field. Run the query, and confirm that the last field in the query displays as Circulation, as shown in Figure 9 below. Enlarge the size of the caption so the entire caption is displayed. (Hint: Not all records are displayed in the figure. The order of the records may differ.) Save and close the BillboardCirculationQuery.
Figure 9: Portion of the BillboardCirculationQuery Results
Open the SortQuery in SQL View. Modify the query to sort the records in ascending order by the State field. (Hint: Use the ORDER BY clause.) Run the query, and ensure that the results match those shown in Figure 10 below. Save and close the SortQuery.
Figure 10: SortQuery Results
Open the GroupingQuery in SQL View. Modify the query by completing the following tasks:
h. Add the State field to the SELECT statement. The State field should appear before the COUNT(BillboardID) function.
i. Group the records by the State field.
j. Sort the records by the State field in Ascending order.
Run the query, and ensure that the results match those shown in Figure 11 below. Save and close the GroupingQuery.
Figure 11: GroupingQuery Results
Open the AdRepSortQuery in SQL View. Modify the query to sort the records first in descending order by the State field, and then in ascending order by the City field. (Hint: Use the ORDER BY clause.) Run the query, and ensure that the results match those shown in Figure 12 on the next page. Save and close the AdRepSortQuery.
Figure 12: AdRepSortQuery Results
Save and close any open objects in your database. Compact and repair your database, close it, and then exit Access. Follow the directions on the SAM website to submit your completed project.
2
IfSuccessful | GA_Status_Icon | SAM_Logo | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
true | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
false |
ID | First | Last | AssignmentGUID | UserID | |||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
{39B9047B-E2F2-4E70-916E-70CF1D93F4C6} |
ProjectName | SubmissionNum | Max | Score | EngineVersion |
---|
StepNumber | Description | StepScore | StepMaxScore | ErrorText | ActionName | StepActionOrder |
---|
AdRepNumber | Address | City | State | PostalCode | Phone | Salary | DateHired | Insurance | Special Skills | |||||||||||||||||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
AM01 | Miller | Abraham | 54 Quarry Ave. | Portland | OR | 97210 | 503-555-6032 | ¤ 32,5 | 40.0 | 6/1/16 | Fluent in Spanish | |||||||||||||||||||||||||||||||||||||||||
GZ01 | Zdalnza | Gregory | 7 Moose Head Rd. | Boise | 83720 | 603-555-8765 | ¤ 29,67 | 5.0 | 9/14/16 | Trains new employees | ||||||||||||||||||||||||||||||||||||||||||
KS02 | Stenhope | Kathryn | 9 Shennadoah Ave. | Salem | 97311 | 503-555-0003 | ¤ 34,57 | 6.0 | 10/5/15 | Bachelor’s degree in Marketing | ||||||||||||||||||||||||||||||||||||||||||
MR01 | Riley | 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. | 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 | 98101 | 207-555-8534 | ¤ 41,765.00 | 12/12/14 |
AdvertiserID | AdvertiserName | Amount Paid | Current Due | Customer | No | |||||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
BBF32 | Babbage CPA Firm | 464 Linnell Dr. | 98104 | ¤ 5,678.25 | ¤ 7,875.25 | |||||||||||||||||||||||||||||||
BCC76 | Burns | 867 Ridge Ave. | 97720 | ¤ 0.00 | ¤ 5,452.30 | |||||||||||||||||||||||||||||||
BMS53 | Boise Medical Services | 134 Main St. | 6035552883 | ¤ 7,562.40 | ¤ 6,152.25 | Designs own ads | ||||||||||||||||||||||||||||||
CDS29 | Carter Dental Services | 123 Second St. | Dallesport | 98617 | ¤ 3,452.10 | ¤ 5,475.75 | ||||||||||||||||||||||||||||||
CRR15 | Columbia River Rafters | 125 River Blvd. | 97209 | ¤ 4,567.25 | ¤ 3,600.50 | |||||||||||||||||||||||||||||||
CWW01 | Casper | West | 10 Broadway St. | ¤ 4,125.50 | ||||||||||||||||||||||||||||||||
GAD74 | Grant Antique Dealers | 78 Catawba Dr. | Grants Pass | 97526 | ¤ 1,500.00 | ¤ 1, | 200.0 | |||||||||||||||||||||||||||||
GPM12 | Grants Pass Motel | 235 Second Ave. | ¤ 2,460.75 | |||||||||||||||||||||||||||||||||
GRI03 | Germann Inn | 345 Hartsel Ave. | Madras | 97741 | ¤ 1,325.45 | ¤ 2,900.50 | ||||||||||||||||||||||||||||||
HAR01 | Haydee’s Restaurant | 234 Twiddy St. | Eugene | 97402 | ¤ 3,000.00 | ¤ 2,500.00 | ||||||||||||||||||||||||||||||
HCH10 | Hendley County Hospital | 216 Rivard Blvd. | 99201 | ¤ 9,456.75 | ¤ 7,234.60 | |||||||||||||||||||||||||||||||
IOA23 | Idaho | 12 Third St. | Twin Falls | 83301 | ¤ 4,200.00 | ¤ 6,500.00 | ||||||||||||||||||||||||||||||
KDS21 | KAL Design Studio | 116 Pine St. | Arden | ¤ 775.00 | ¤ 1,550.50 | |||||||||||||||||||||||||||||||
KGS04 | Kyle General Store | 421 First St. | Cokeville | ¤ 3,45 | 2.0 | ¤ 2,875.50 | ||||||||||||||||||||||||||||||
L | CA | Lews and Clark Adventures | 100 Main St. | Goldendale | 98620 | ¤ 4,500.00 | ||||||||||||||||||||||||||||||
LFS02 | Lewis Furniture Store | 267 Main St. | Payette | 83661 | ¤ 1,234.50 | ¤ 2,890.70 | ||||||||||||||||||||||||||||||
MBT14 | Mike’s Bike Tours | 234 Gilham St. | 97301 | ¤ 1,090.75 | ¤ 1,345.60 | |||||||||||||||||||||||||||||||
PJG34 | Patricia Jean Greenhouses | 345 Magee Ave. | ¤ 1,345.00 | ¤ 2,450.00 | ||||||||||||||||||||||||||||||||
RER23 | Rivers Edge Restaurant | 125 River Rd. | Kettle Falls | 99141 | ¤ 1,375.00 | |||||||||||||||||||||||||||||||
SLA77 | Smarter Law Associates | 764 Main St. | ¤ 7,500.00 | |||||||||||||||||||||||||||||||||
TVS17 | Theon Veterinary Services | 346 Austin Ave. | ¤ 2,225.60 | |||||||||||||||||||||||||||||||||
WEC05 | Western Energy Company | 12 Polk St. | ¤ 3,450.80 | |||||||||||||||||||||||||||||||||
WSC01 | Wood Sports Complex | 578 Central Ave. | Cave Junction | 97523 | ¤ 2,345.50 | ¤ 3,590.80 |
BillboardID | Location | Height | Width | SqrFt | Illuminated | DEC | Type | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
LHAR04 | Hwy 395 | 8.0 | 2 | 4.0 | 192.0 | Yes | 4395 | Digital | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
LHBE01 | Hwy 97 | Beaver Marsh | 3542 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
LHBU01 | 10.5 | 48.0 | 504.0 | 1509 | Poster | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
LHCA01 | Hwy 199 | 16.0 | 96.0 | 2024 | Junior Poster | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
LHCA03 | Hwy 20 | 10.0 | 32.0 | 3 | 20.0 | 1288 | Bulletin | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
LHCH03 | Chemult | 240.0 | 3411 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
LHCH05 | Hwy 93 | Challis | 506 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
LHCO05 | Hwy 89 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
LHDA03 | Hwy 197 | 1 | 60.0 | 3700 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
LHDC07 | Alpine | 1964 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
LHDD01 | Hwy 26 | Dubois | 1016 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
LHGR01 | Hwy 18 | Grand Ronde | 30.0 | 300.0 | 5182 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
LHGR02 | Hwy 99 | 25.0 | 250.0 | 5838 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
LHKE01 | 7400 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
LHKL01 | Klamath Falls | 4461 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
LHMA01 | 12070 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
LHMP01 | Modoc Point | 5500 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
LHPA03 | Hwy 95 | 12.0 | 288.0 | 3777 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
LHRI01 | Riggins | 1724 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
LHRI02 | Rigby | 14.0 | 6 | 72.0 | 13406 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
LHRO01 | Hwy 101 | Rockaway | 1702 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
LHSA01 | Hwy 114 | 10224 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
LHSA03 | Liberty Road | 9.0 | 2 | 7.0 | 24 | 3.0 | 11808 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
LHSA05 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
LHTW01 | 1564 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
LHWE01 | Weiser | 252.0 | 1748 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
LHWE04 | I-90 Exit 60 | Wallace | 672.0 | 6888 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
LIBO01 | I-84 Exit 57 | 2 | 1.0 | 47.0 | 987.0 | 14104 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
LIRO01 | I-5 Exit 120 | Roseburg | 576.0 | 27618 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
LNPO01 | I-84 Exit 1 | 110667 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
LSCO05 | Hwy 42 | Coquille | 5642 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
LSEU01 | 6th Ave. | 19942 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
LSEU05 | Maxwell St. | 5707 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
LSEU07 | W. 11th St. | 21910 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
LSGO01 | Hwy 142 | 128.0 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
LSKL01 | S. 6th St. | 11414 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
LSSP01 | Hwy 126 | Springfield | 98.0 | 10037 |
BillboardTypeID |
---|
BN |
DL |
JP |
PT |
RentalID | Facing | Cost | Deposit | Months | BeginDate | Discount | Rental Contract | |||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
1700200 | North | 6/5/17 | ||||||||||||
1700201 | East | ¤ 1,600.00 | 5/2/17 | |||||||||||
1700202 | ¤ 1,400.00 | 3/14/17 | ||||||||||||
1700204 | 9/4/17 | |||||||||||||
1700205 | ¤ 800.00 | 7/1/17 | ||||||||||||
1700206 | South | |||||||||||||
1700208 | ¤ 790.00 | 9/12/17 | ||||||||||||
1700209 | ¤ 350.00 | 4/11/17 | ||||||||||||
1700210 | ¤ 300.00 | |||||||||||||
1700211 | ¤ 875.00 | 5/12/17 | ||||||||||||
1700212 | ¤ 900.00 | |||||||||||||
1700213 | ¤ 2,875.00 | 2/1/17 | ||||||||||||
1700215 | ||||||||||||||
1700216 | ¤ 2,750.00 | 6/24/17 | ||||||||||||
1700217 | ¤ 325.00 | 3/21/17 | ||||||||||||
1700219 | ¤ 1,625.00 | 8/13/17 | ||||||||||||
1700220 | ||||||||||||||
1700222 | ¤ 825.00 | 9/14/17 | ||||||||||||
1700223 | ¤ 365.00 | 8/25/17 | ||||||||||||
1700224 | ¤ 3,500.00 | |||||||||||||
1700225 | ¤ 375.00 | 6/12/17 | ||||||||||||
1700226 | ¤ 700.00 | 5/25/17 | ||||||||||||
1700227 | ¤ 1,525.00 | |||||||||||||
1700229 | 8/14/17 | |||||||||||||
1700230 |
StateName | |
---|---|
California | |
NV | Nevada |
Oregon | |
Washington | |
Wyoming |
SELECT AdRep.AdRepNumber, AdRep.LastName
FROM AdRep
ORDER BY AdRep.LastName;
SELECT [AdRep].[AdRepNumber], [AdRep].[FirstName], [AdRep].[LastName]
FROM AdRep
ORDER BY [AdRepNumber];
SELECT [Billboard].[BillboardID], [Billboard].[City]
FROM Billboard;
PARAMETERS __BillboardID Value;
SELECT DISTINCTROW *
FROM [SELECT [Billboard].[BillboardID], [Billboard].[Location], [Billboard].[State] FROM Billboard]. AS [Rentals and Billboards Form]
WHERE ([__BillboardID] = BillboardID);
SELECT DISTINCTROW *
FROM AdRep;
SELECT DISTINCTROW *
FROM Advertiser;
SELECT DISTINCTROW *
FROM Advertiser;
SELECT DISTINCTROW *
FROM Billboard;
SELECT DISTINCTROW *
FROM States;
SELECT DISTINCTROW *
FROM Rentals;
SELECT DISTINCTROW *
FROM Rentals;
SELECT DISTINCTROW *
FROM States;
SELECT DISTINCTROW *
FROM States;
SELECT DISTINCTROW *
FROM Advertiser;
SELECT DISTINCTROW *
FROM Billboard;
SELECT AdRep.AdRepNumber, AdRep.LastName, AdRep.FirstName, AdRep.DateHired, AdRep.[Special Skills]
FROM AdRep
WHERE (((AdRep.DateHired)>#1/1/2016#) AND ((AdRep.[Special Skills]) Like “*Spanish*”));
SELECT AdRepNumber, FirstName, Address, City, State, PostalCode
FROM AdRep;
SELECT AdRep.AdRepNumber, AdRep.FirstName, AdRep.LastName, Advertiser.AdvertiserID, Advertiser.AdvertiserName, Advertiser.[Amount Paid], Advertiser.[Current Due]
FROM AdRep INNER JOIN Advertiser ON AdRep.AdRepNumber = Advertiser.AdRepNumber;
SELECT AdRep.AdRepNumber, Advertiser.AdvertiserID, Rentals.RentalID, [Cost]*[Months] AS [Total Cost]
FROM (AdRep INNER JOIN Advertiser ON AdRep.AdRepNumber = Advertiser.AdRepNumber) INNER JOIN Rentals ON Advertiser.AdvertiserID = Rentals.AdvertiserID;
SELECT AdRepNumber, FirstName, LastName, City, State
FROM AdRep;
SELECT AdRepNumber, FirstName, LastName, Address, City, State
FROM AdRep
WHERE State = ‘OR’;
SELECT BillboardID, Location, City, State, DEC
FROM Billboard;
SELECT BillboardID, Location, City, State, Type
FROM Billboard
WHERE Type = ‘Digital’;
SELECT [FirstName] & ” ” & [LastName] AS StudentName, [_GradingReport].ProjectName, “Submission #” & [SubmissionNum] AS SubmissionCt, [_GradingReport].Score, [_GradingReport].MaxScore, “Score is: ” & [Score] & ” out of ” & [MaxScore] AS Grade, [_GradingReportSteps].[StepNumber] & “. ” & [Description] AS Step, [_GradingReportSteps].[StepScore] & “/” & [_GradingReportSteps].[StepMaxScore] AS StepGrade, [_GradingReportSteps].ActionName, IIf([ifsuccessful]=True,Null,[ErrorText]) AS Feedback, [_GradingReportSteps].IfSuccessful, [_GradingIcons].GA_Status_Icon, [_GradingReportSteps].StepNumber, [_GradingReportSteps].StepActionOrder, [_GradingReportSteps].ID, [_GradingIcons].SAM_Logo, [_GradingReportSteps].Description, [_GradingReport].EngineVersion
FROM _GradingReport, _GradingIcons INNER JOIN _GradingReportSteps ON [_GradingIcons].[IfSuccessful_Status] = [_GradingReportSteps].IfSuccessful;
SELECT COUNT(BillboardID) AS NumberBillboards
FROM Billboard;
SELECT BillboardID, StateName
FROM Billboard, States;
SELECT BillboardID
FROM Billboard
WHERE State = ‘OR’;
SELECT RentalID, BillboardID, Cost, Months, BeginDate
FROM Rentals;
SELECT RentalID, BillboardID, Cost, Months
FROM Rentals;
SELECT AdRepNumber, LastName, FirstName, State
FROM AdRep;