Access Database Assignment

guilardres

UCCfinal.zip

Home >Applied Sciences homework help >Access Database Assignment

UCC final/Instructions_SC_AC16_10a.docx

Shelly Cashman Access 2016 | Module 10: SAM Project 1a

C:\Users\akellerbee\Documents\SAM Development\Design\Pictures\g11731.png

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.

UCC final/Instructions_SC_AC16_2a.docx

Shelly Cashman Access 2016 | Module 2: SAM Project 1a

C:\Users\akellerbee\Documents\SAM Development\Design\Pictures\g11731.png

Shelly Cashman Access 2016 | Module 2: SAM Project 1a

West Coast Outdoor Advertisers

CREATING AND MODIFYING queries

GETTING STARTED

Open the file SC_AC16_2a_FirstLastName_1.accdb, available for download from the SAM website.

Save the file as SC_AC16_2a_FirstLastName_2.accdb by changing the “1” to a “2”.

0. Hint: If you do not see the .accdb file extension in the Save As dialog box, do not type it. Access 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.

PROJECT STEPS

Open the Billboard Listing Query in Design View. Delete the Facing field from the query, and then save, run, and close the query.

Create a new query in Query Design View based on the AdRep table with the following options:

a. Add the fields AdRepNumber, LastName, FirstName, and DateHired to the query in that order.

b. Add an ascending sort to the DateHired field.

c. Save the query with the name AdRep Hired Query.

Run the AdRep Hired Query, and then close it.

Create a new parameter query in Design View based on the Billboard table with the following options:

d. Add the BillboardID, Location, DEC, City, and State fields (in that order) from the Billboard table to the query.

e. Add the criterion [Enter State] (including brackets) to the State field.

f. Save the query with the name State Billboard Query.

Run the State Billboard Query to confirm that it works. (Hint: If you use WY for the parameter value, the query should return four records.) Save and close the query.

Create a new query in Design View based on the AdRep and Advertiser tables with the following options:

g. Add the AdvertiserName field from the Advertiser table to the query.

h. Add the FirstName, LastName, and PhoneNumber fields (in that order) from the AdRep table to the query.

i. Save the query with the name AdRep-Advertiser Query.

Run the AdRep-Advertiser Query, and then save and close it.

Open the Unique Cities Query in Design View. Modify the query to list all cities only once. (Hint: Click the second field in the Design grid, open the property sheet, and change the Unique Values property for the query.) Run the query, and then save and close it. (Hint: The query should return 18 records.)

Create a Crosstab query based on the Billboard table with the following options:

j. Use only data from the Billboard table in the crosstab.

k. Use the State field for the row headings.

l. Use the Type field for the column headings.

m. Use a Count of the BillboardID field as the calculated value for each row and column intersection, and include row sums in the crosstab query.

n. Save the query with the name State-Type Crosstab.

View the query, and then save and close it.

Open the Top Values Query in Design View. Modify the query to sort the Current Due amounts in descending order. Change the Return value to display only the top 25% of the records. Run the query, and then save and close it.

Open the Oregon Digital Query in Design View. Add criteria to select only those records where the Type field is equal to Digital and the State field is equal to OR. Run the query, and then save and close it.

Open the SqrFt Area Query in Design View. Modify the query by creating a calculated field to compute the square footage of a bulletin board. Enter SqrFt: [Height]*[Width] in the Zoom dialog box of the first empty column in the design grid. Run the query, and then save and close it.

Open the Caption Query in Design View. Modify the query by adding the caption for the Facing field to Direction. Run the query, and then save and close the query.

Open the No Current Due Query in Design View. Modify the query to show only records where the Current Due field is equal to 0. Run the query, and then save and close it.

Open the Washington Advertisers Query in Design View, hide the State field in the query, and then save and close it. (Hint: Do not delete the State field from this query.)

Open the Billboard Type Query in Design View. Add criteria to select only those records where the Type field is equal to Bulletin or to Digital. Run the query, and then save and close it.

Open the Multiple Sort Query in Design View. Modify the query to sort the records in ascending order first by the AdRepNumber field and then by the AdvertiserName field. Run the query, and then save and close it.

Open the Small Audience Query in Design View. Add criteria to select only those records where the value in the DEC field is less than 2000. Run the query, and then save and close it.

Open the Billboards by Type Query in Design View and modify it by adding Totals to the query, as described below:

o. For the Type field, set the total row to Group By.

p. For the BillboardID field, set the total row to Count.

Run the query, and then save and close it.

Open the G Advertisers Query in Design View. Add the G* wildcard criteria to the AdvertiserName field, so that the query only returns records when the advertiser name begins with G. Run the query, and then save and close it.

Use the Report Wizard to create a new report (shown in Figure 1 below) based on the All Advertisers Query with the following options:

q. Include all fields from the All Advertisers Query.

r. Group the report by LastName and use no additional grouping in the report.

s. Use no additional sorting in the report.

t. Use a Stepped layout and Portrait orientation for the report

u. Set the title of the report to All Advertisers Report.

Preview the report, and then save and close it.

Figure 1: All Advertisers Report

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.

UCC final/Instructions_SC_AC16_3a.docx

Shelly Cashman Access 2016 | Module 3: SAM Project 1a

Shelly Cashman Access 2016 | Module 3: SAM Project 1a

C:\Users\akellerbee\Documents\SAM Development\Design\Pictures\g11731.png

West Coast Outdoor Advertisers

CREATING AND MODIFYING tables and other database objects

GETTING STARTED

Open the file SC_AC16_3a_FirstLastName_1.accdb, available for download from the SAM website.

Save the file as SC_AC16_3a_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. Access will add the file extension for you automatically.

PROJECT STEPS

Open the Relationships window and add the Billboard table to it. Create a one-to-many relationship between the BillboardID field in the Billboard table and the BillboardID field in the Rentals table. Make the relationship enforce referential integrity and cascade update related fields. Do not make the relationship cascade delete related records. Save the relationship.

With the Relationships window still open, modify the relationship between the AdRep and Advertiser tables to cascade update related fields. Do not make the relationship cascade delete related records. Save the relationships and close the window.

Open the Billboard table in Design View. Use the Lookup Wizard to change the Type field to a Lookup field. Type in the following four values (in the order shown) as the list of possible values for the field: Digital, Bulletin, Poster, and Junior Poster. Limit the field values to only the items in the list, and do not allow multiple values for the field.

With the Billboard table still open in Design View, delete the Facing field.

With the Billboard table still open in Design View, add a new field named SqrFt after the Width field. This field is a calculated field. Use the expression Height * Width to calculate the field values. Save and close the Billboard table.

Open the Rentals table in Design View and make the following updates:

a. Change the BillboardID field to a required field.

b. Change the Field Size property for the Facing field to 6.

c. Change the data type of the Cost field to Currency, and change the Decimal Places property to 0.

d. Change the default value of the Months field to 1.

Save the changes to the Rentals table. (Hint: Because there was a change to a field size, the “Some data may be lost” warning message will appear. Continue saving the table. The data fits within the valid ranges, so ignore this warning and continue saving the table.)

Switch to viewing the Rentals table in Datasheet View, and then add the Total row to the table. The Total row should average the values in the Cost field. Sort the records in ascending order by the AdvertiserID field. Save the changes to the table, and then close the datasheet.

Create an Update query to update the value of the Discount field in the Rentals table for all records to No. Run the query, and then save it as Discount Query. (Hint: 24 records will be updated by this query.)

Open the AdRep table in Datasheet View, and then open the subdatasheet for Abraham Miller (who has the AdRepNumber field value of AM01). In the subdatasheet, update the Germann Inn record (which has the AdvertiserID field value of GRI03) by changing the Current Due field value to 2900.50. Close the subdatasheet and then close the AdRep table.

Open the Advertiser table in Datasheet View. Find the record for Goldendale City Foundation (which has the AdvertiserID field value of GCF56) and delete this record. Close the Advertiser table.

Open the Billboard table in Datasheet View, and then apply a Filter by Form to find all records where the State field is equal to ID and the Type field is equal to Digital. Toggle the filter and, for the record returned, change the DEC field value to 1724. Toggle the filter, and then save and close the table.

Create a Split Form based on the Billboard table. Save the form as Billboard Split Form, and then close it.

Create a Delete query for the Rentals table. Delete all rentals where the BillboardID field value is LSEU05 and the Facing field value is West. Save the query as Delete Rentals Query, run the query (Hint: Only one record will be deleted.), and then close the query.

Create an Append query for the More States table. Append the State and the StateName field values in the More States table to the States table in the current database. Save the query as Append States Query, run the query (Hint: Two records will be appended.), and then close the query.

Open the Insurance Query in Design View, and then modify it by adding the Insurance field to the Design grid. Insurance is a multivalued field, and each option should appear on a separate row. Run the query, save it, and then close the query.

Open the Rentals Update Form in Form View, and then navigate to the last record (with the RentalID field value 1700227). Change the Months field value to 4. Navigate to the previous record in the form (with the RentalID field value 1700226). Change the Discount field value to Yes.

Use the Rentals Update Form to add a new record to the Rentals table with the values shown in Figure 1 below.

Figure 1: Rentals Update Form

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.

UCC final/Instructions_SC_AC16_4a.docx

Shelly Cashman Access 2016 | Module 4: SAM Project 1a

C:\Users\akellerbee\Documents\SAM Development\Design\Pictures\g11731.png

Shelly Cashman Access 2016 | Module 4: SAM Project 1a

West Coast Outdoor Advertisers

CREATING reports and forms

GETTING STARTED

Open the file SC_AC16_4a_FirstLastName_1.accdb, available for download from the SAM website.

Save the file as SC_AC16_4a_FirstLastName_2.accdb by changing the “1” to a “2”.

· 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.

PROJECT STEPS

Open the Basic Billboards List report in Layout View. Group the report by the State field, and then sort the report by the BillboardID field in ascending order. Do not add any additional grouping or sorting options to the report. Close the Group, Sort & Total pane. (Hint: Click the Group & Sort button.) Save and close the Basic Billboards List report.

Modify the Advertiser Financial Report to match Figure 1 on the following page by performing the following tasks:

a. Open the Advertiser Financial Report in Layout View.

b. Sum the values in the Amount Paid column and the Current Due column for each state’s advertisers and for all advertisers.

c. Switch to Print Preview to view the report and to check that the values in the subtotal control and the total control are displayed completely.

d. Return to Layout View and, if necessary, expand the size of the subtotal control and the total control so that they are displayed completely.

Save the report without closing it.

With the Advertiser Financial Report still open in Layout View, apply conditional formatting to the Current Due column. If the amount currently due is greater than $6,000, display the value in bold, Green font (7th column, 1st row of the Standard Colors palette).

Save the report again, and then close it.

Figure 1: Advertiser Financial Report

Open the Advertiser by AdRep Report in Layout View, and then create a summary report. (Hint: To create a summary report, hide the detail lines.) Save and close the report.

Open the AdRep Phone List report in Layout View. Apply the Office theme to this object only. Save and close the report.

Use the Label Wizard to create mailing labels for the AdRep table, and include the following options:

e. Use Avery C2163 as the label size.

f. Use Arial font, 11 pt. font size, Light font weight, and Black (1st column, 6th row of the Basic Colors palette) font color with no special font styles for the labels. (Hint: These formatting options may be the default settings for your label.)

g. On the first line of the label, include the FirstName field, a space, and the LastName field.

h. On the second line of the label, include the Address field.

i. On the third line of the label, include the City field, a comma (,), a space, the State field, a space, and the PostalCode field. Your label should match Figure 2 below.

Figure 2: Prototype AdRep Label

j. Sort the labels by the PostalCode field.

k. Save the Labels as Labels AdRep (which is the default name).

Confirm that your labels match Figure 3 below, and then close the Labels AdRep report. (Hint: If a message appears warning that some data may not be displayed, click OK.)

Figure 3: Labels AdRep Report

Use the Report Wizard to create the report shown in Figure 4 on the next page. The report is based on the Advertiser and Rentals tables and should include the following options:

l. Include the AdvertiserID and AdvertiserName fields from the Advertiser table.

m. Include the RentalID and BillboardID fields from the Rentals table.

n. The data will automatically be grouped by the AdvertiserID field, but do not add any additional grouping levels.

o. Sort the report by the RentalID field in ascending order.

p. Use the Stepped layout and Landscape orientation.

q. Save the report as Advertisers and Rentals Report. (Hint: Do not include the (.) period following Report.)

Preview the report, and then close it.

Figure 4: Advertisers and Rentals Report

Open the AdRep Contact Form in Layout View, and then modify the form by performing the following tasks:

r. Select all the labels and controls in the Detail section of the form. (Hint: Do not select the form title label in the Form Header section.)

s. Place the selected controls in the Stacked control layout.

t. Add the DateHired control after the PhoneNumber control as shown in Figure 5 on the next page.

u. Move the LastName control after the FirstName control as shown in Figure 5. (Hint: Be sure to select both the label and the control for the LastName field.)

Save and close the form.

Figure 5: AdRep Contact Form

Open the Rentals Update Form in Layout View, and then bold the BeginDate control. Save and close the form.

Open the States Entry Form in Layout View, and then add the current date to the form. Use the option button for the second date format. Do not include the time on the form. Save and close the form.

Open the Advertiser Address Form in Form View, and then use the Advanced Filter/Sort option to create a filter with the following properties:

v. The records in the form should be sorted in ascending order by the AdvertiserName field.

w. The form should only return records where the State field contains “OR”.

x. Toggle the filter to confirm it is working. (Hint: There should only be nine records included in the form.)

Save and close the form.

Use the Form Wizard to create a form based on the Billboard table with the following options:

y. Include the BillboardID, Location, City, State, Type, and DEC fields (in that order) on the form.

z. Select the Columnar layout for the form

aa. Save the form as Basic Billboard Form. (Hint: Do not include the (.) period following Form.)

Close the form.

Save and close any open database objects. Compact and repair your database, close it, and then exit Access. Follow the directions on the SAM website to submit your completed project.

UCC final/Instructions_SC_AC16_5a.docx

Shelly Cashman Access 2016 | Module 5: SAM Project 1a

C:\Users\akellerbee\Documents\SAM Development\Design\Pictures\g11731.png

Shelly Cashman Access 2016 | Module 5: SAM Project 1a

West Coast Outdoor Advertisers

Multiple-table forms

GETTING STARTED

Open the file SC_AC16_5a_FirstLastName_1.accdb, available for download from the SAM website.

Save the file as SC_AC16_5a_FirstLastName_2.accdb by changing the “1” to a “2”.

0. If you do not see the .accdb file extension in the Save As dialog box, do not type it. Access will add the file extension for you automatically.

1. 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.

PROJECT STEPS

Open the Advertiser table in Design View. Use the Input Mask Wizard to create an input mask for the Phone field. Accept the default input mask for the Phone Number field. Store the data without the symbols in the mask. Save the change to the Advertiser table without closing it.

Switch the Advertiser table to Datasheet View, and then add a new field named Customer Notes at the end of the table. Use Long Text as the data type for the field. Save the change to the table.

With the Advertiser table still open in Datasheet View, update the record for Boise Medical Services (which has the AdvertiserID BMS53) as described below:

a. Using the input mask, enter 603-555-2883 in the Phone field.

b. Enter the text Designs own ads in the Customer Notes field.

Close the table.

Open the Rentals table in Design View, and then add a new field with the name Rental Contract following the Discount field. Use Attachment as the data type for the field, and use Rental Contract as the caption property of the field. Save the change to the table, and then close the table.

Modify the AdRep Data Entry Form to match Figure 1 on the following page by completing these tasks:

c. Open the AdRep Data Entry Form in Design View.

d. Open the field list for the form.

e. Add the PhoneNumber field to the form in the location shown in Figure 1.

Save and close the AdRep Data Entry Form.

Figure 1: AdRep Data Entry Form – Design View

Select the States table, and then create a new form in Design View. Update the form to match Figure 2 on the following page by completing these tasks:

f. Add the State and StateName fields (from the States table) to the Detail section of the form, in the approximate location shown in Figure 2. If necessary, reposition the controls and their labels after adding them to the form.

g. Save the form with the name State Data Entry Form, but do not close the form.

h. Add a title to the form. The title of the form should match the name of the form.

Save and close the State Data Entry Form.

Figure 2: State Data Entry Form – Design View

Open the AdRep Master Form in Design View, and then ensure that the Use Control Wizards button is selected. With the AdRep Master Form still open in Design View, use the Subform/Subreport Wizard to add a subform with the following options:

i. Use the Advertiser table for the subform.

j. Select the AdvertiserID, AdvertiserName, and Current Due fields from the Advertiser table to add to the subform.

k. Accept the default link (“Show Advertiser for each record in AdRep using AdRepNumber”) to link the main form to the subform.

l. Save the subform as Advertiser subform (which is the default name).

m. If necessary, reposition the subform to match the approximate location shown in Figure 3a on the following page and Figure 3b on page 5.

Save and close the form.

Figure 3a: AdRep Master Form – Form View

Figure 3b: AdRep Master Form – Design View

Open the State-Billboard Master Form in Design View, and then modify it to match Figure 4a on the following page and Figure 4b on page 7 by resizing the width of the subform as shown in the figures. (Hint: The right edge of the subform is at the 5.5" ruler mark.) Save, but do not close the form.

With the State-Billboard Master Form still open in Design View, modify the form title to match Figures 4a and 4b by completing the following tasks:

n. Change the font color of the title to Dark Red (1st column, 7th row of the Standard Colors palette).

o. Change the font size of the title text to 20 pt.

p. Apply the Raised special effect to the title.

Save and close the form.

Figure 4a: State-Billboard Master Form – Form View

Figure 4b: State-Billboard Master Form – Design View

Open the Rentals Financial Form in Design View, and then modify the form by completing the following tasks:

q. Delete the Discount control and the accompanying label.

r. Change the label for the Facing control to Direction. (Hint: Do not type the period.)

Save and close the form.

Open the Rentals Contact Form in Design View, and then modify the form to match Figure 5 on the following page by completing these tasks:

s. Select the BillboardID, Cost, Months, and BeginDate controls and labels simultaneously.

t. Adjust the spacing of these fields to have equal vertical spacing.

Save, but do not close the form.

With the Rentals Contact Form still open in Design View, change the tab order so that users tab to the AdvertiserName field immediately after the RentalID field. (Hint: Move the AdvertiserName field upward in the tab order.) Save and close the form.

Figure 5: Rentals Contact Form – Design View

Open the Basic Billboard Form in Design View, and then change the background color of the form to Light Gray 2 (1st column, 3rd row of the Standard Colors palette). Save the form, but do not close it.

With the Basic Billboard Form still open in Design View, modify the form to match Figure 6 on the following page by performing these tasks:

u. Select the Location label, Location control, Height label, and Height control simultaneously. Align the controls to the top.

v. Select the Location control, City control, State control, and Type control simultaneously. Do not select any labels, and make sure the Height control is not selected. Align the controls to the left.

Save, but do not close, the form.

With the Basic Billboard Form still open in Design View, add a title to the form using Basic Billboard Form as the name. (Hint: This is the default name). Save, but do not close the form.

With the Basic Billboard Form still open in Design View, change the Tab Stop property to No for the SqrFt field. Save and close the form.

Figure 6: Basic Billboard Form – Design View

Open the AdRep Skills Query in Design View, and then modify the query to find all ad reps who were hired after 1/1/2016 and who have special skills in Spanish. (Hint: The Special Skills field has the Long Text data type. The query should return one record.) Run the query, and then save and close it.

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.

UCC final/Instructions_SC_AC16_6a.docx

Shelly Cashman Access 2016 | Module 6: SAM Project 1a

C:\Users\akellerbee\Documents\SAM Development\Design\Pictures\g11731.png

Shelly Cashman Access 2016 | Module 6: SAM Project 1a

West Coast Outdoor Advertisers

advanced report techniques

GETTING STARTED

Open the file SC_AC16_6a_FirstLastName_1.accdb, available for download from the SAM website.

Save the file as SC_AC16_6a_FirstLastName_2.accdb by changing the “1” to a “2”.

0. 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.

1. To complete this Project, you will also need to download and save the following support file from the SAM website:

2. Support_SC_AC16_6a_States.txt

PROJECT STEPS

Import the data from the file Support_SC_AC16_6a_States.txt, available for download from the SAM website, and then append the records to the States table. The text file is a delimited file with a comma separating the two fields. Do not create a new table, and do not save the import steps.

Open the Basic Billboards List report in Design View, and then complete the following tasks:

a. Add the current date and time to the Report Header section.

b. In the Date and Time dialog box, use the option button to select the second date format (e.g., 01-Aug-18) and the second time format (e.g., 4:18 PM).

c. Resize the report so that the right boundary of the report is approximately at the 8" mark on the horizontal ruler.

Save the report without closing it.

With the Basic Billboards List report still open in Design View, complete the following tasks:

d. Select the State Header, and then display the Property Sheet for this section.

e. Change the Repeat Section property to Yes.

f. Change the Force New Page property to Before Section.

Save and close the report.

Modify the Advertisers by AdRep Report to match Figure 1 below by completing the following tasks:

g. Open the Advertisers by AdRep Report in Design View.

h. Open the field list for the report.

i. Add the LastName field to the AdRepNumber Header section of the report in the postion shown in Figure 1. (Hint: You may need to reposition and resize the LastName control and/or its label after inserting it into the report.)

With the Advertisers by AdRep Report still open in Design View, complete the following tasks:

j. Select the AdvertiserID, AdvertiserName, Amount Paid, and Current Due controls simultaneously.

k. Group the controls.

l. Resize the controls by dragging their right edge to the 4" mark on the ruler as shown in Figure 1.

m. Bold the controls

Save and close the Advertisers by AdRep Report.

Figure 1: Advertisers by AdRep Report – Design View

Modify the Advertiser Financial Report to match Figure 2 below by completing the following tasks:

n. Open the Advertiser Financial Report in Design View.

o. Change the Text11 label in the Page Header section to Total Amount. (Hint: Do not type the period.)

p. Format the text box control in the Detail section containing the =[Amount Paid]+[Current Due] calculation so that it displays with the Currency number format and two decimal places, as shown in Figure 2.

Save and close the Advertiser Financial Report.

Figure 2: Advertiser Financial Report – Report View

Open the AdRep Phone List report in Design View, and then change the Alternate Back Color property for the Detail section to No Color. Save and close the report.

Open the AdRep Master Report in Design View, and then use the Subform/Subreport Wizard to add a subreport with the following options:

q. Add the subreport below the AdvertiserName label in the Detail section, at approximately the 1" mark on the vertical ruler.

r. Use the Rentals table as the source of the subreport.

s. Select the RentalID, BillboardID, BeginDate, and Months fields (in that order) from the Rentals table to add to the subreport.

t. Ensure that the Choose from a list option button is selected, and accept the default link Show Rentals for each record in AdReps and Advertisers using AdvertiserID for the subreport.

u. Save the subreport as Rentals subreport (which is the default name). (Hint: The width of the subreport may expand the width of the report. Do not manually adjust the width.)

v. Delete the label associated with the subreport.

Save the AdRep Master Report, switch to Report View, confirm your report matches Figure 3 below, and then close the report.

Figure 3: AdRep Master Report – Report View

Open the Billboards by Type Report in Design View, and then modify the report to match Figure 4 on the following page by completing these tasks:

w. Resize the width of the report as shown in Figure 4. (Hint: The right border of the report is at the 7" mark on the horizontal ruler.)

x. Move the DEC label and control so that it appears under the State control in the approximate location shown in Figure 4. (Hint: Align the top of the label and control with the 1.5" vertical ruler.)

y. Select the Height, Width, SqrFt, and Illuminated controls simultaneously. Do not select the labels.

z. Align the controls to the left as shown in Figure 4.

Save and close the report.

Figure 4: Billboards by Type Report – Design View

Open the Rentals Financial Report in Design View, and then modify the report by completing the following tasks:

aa. Select all labels in the Page Header section.

ab. Bold the labels.

ac. Select the AdvertiserName control in the Detail section, and then change the Can Grow property to Yes.

ad. Resize the Detail section of the report by dragging the lower boundary of the section to just below the controls in the report, as shown in Figure 5 on the following page.

Save and close the report.

Figure 5: Rentals Financial Report – Design View

Modify the Advertising Rentals Report to match Figure 6 on the following page by performing these tasks:

ae. Open the Advertising Rentals Report in Design View.

af. Use the Group, Sort, and Total pane to add a footer section to the Group on AdvertiserID section.

ag. Add a text box control in the AdvertiserID Footer section in the position shown in Figure 6.

ah. Convert the text box control into a calculated control that sums the TotalCost field. (Hint: Your control should contain the formula =Sum([TotalCost]).)

ai. Format the control so that it displays with the Currency number format and two decimal places.

aj. Change the label to Advertiser Total as shown in Figure 6.

ak. Reposition the control and its label to the approximate location shown in Figure 6. (Hint: The left edge of the text box should be located at the 4" mark on the horizontal ruler.)

Save and close the report.

Figure 6: Advertising Rentals Report – Design View

Create a new blank report in Design View, and then complete the following tasks:

al. Open the Property Sheet for the report, and then set the Record Source for the report to the States table.

am. Save the report with the name State Report, but do not close the report.

an. Add the State and StateName fields to the report, and then reposition them to the approximate location shown in Figure 7 on the following page.

ao. Add the title State Report to the Report Header section.

ap. Add page numbers to the report at the Top of Page (Header) position, using the Page N of M format and Right alignment.

Save and close the report.

Figure 7: State Report – Design View

Open the Billboards by State Report in Design View. Group the report by the State field, and then sort the report by the BillboardID field in ascending order. Do not add any additional grouping or sorting options to the report. Save and close the the Billboards by State Report. (Hint: This report does not contain any fields.)

Modify the Rentals Total Report to match Figure 8 on the following page by performing these tasks:

aq. Open the Rentals Total Report in Design View.

ar. Add a text box control to the Report Footer section in the position shown in Figure 8.

as. Convert the text box control into a calculated control that sums the TotalCost field. (Hint: Your control should contain the formula =Sum([TotalCost]).)

at. Format the control so that it displays with the Currency number format and zero decimal places.

au. Change the label to Total as shown in Figure 8.

av. Reposition the control and its label to the approximate location shown in Figure 8. (Hint: The left edge of the text box should be located at the 4" mark on the horizontal ruler.)

Save and close the report.

Figure 8: Rentals Total Report – Design View

Save and close any open database objects. Compact and repair your database, close it, and then exit Access. Follow the directions on the SAM website to submit your completed project.

UCC final/Instructions_SC_AC16_7a.docx

Shelly Cashman Access 2016 | Module 7: SAM Project 1a

C:\Users\akellerbee\Documents\SAM Development\Design\Pictures\g11731.png

Shelly Cashman Access 2016 | Module 7: SAM Project 1a

West Coast Outdoor Advertisers

advanced form techniques

GETTING STARTED

Open the file SC_AC16_7a_FirstLastName_1.accdb, available for download from the SAM website.

Save the file as SC_AC16_7a_FirstLastName_2.accdb by changing the “1” to a “2”.

0. 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.

PROJECT STEPS

Create a new form in Design View by completing the following tasks:

a. Open the Property Sheet for the new form, and then set the record source for the form to the States and Billboards query.

b. Add the Location, City, StateName, Illuminated, DEC, and Type fields (in that order) to the form in the approximate location shown in Figure 1 on the following page.

c. Adjust the sizing of the controls to approximately match those shown in Figure 1.

d. Add the BillboardID field to the form, and then move the control and its label to the approximate location shown in Figure 1.

e. Save the form with the name States and Billboard Record Source Form and close it.

Figure 1: States and Billboard Record Source Form – Design View

Open the State Master Form in Design View, and then make the following changes:

f. Add a title to the form as shown in Figure 2 on the following page.

g. Expand the Form Header section to the approximate location shown in Figure 2.

h. Change the background color of the Detail section to Light Gray 1 (1st column, 2nd row of the Standard Colors palette).

Save and close the State Master Form.

Figure 2: State Master Form – Design View

Open the Advertiser Master Form in Design View. Add a combo box control to the form in the position shown in Figure 3 on the following page. Use the Combo Box Wizard to create the combo box with these options:

i. Ensure that the I want the combo box to get the values from another table or query. option button is selected.

j. Select the AdRep table as the source of the combo box values.

k. Add the AdRepNumber, FirstName, and LastName fields to the combo box.

l. Sort the combo box by the AdRepNumber field in ascending order.

m. Remove the check mark from the “Hide key column (recommended)” check box.

n. Ensure that AdRepNumber is selected as the field in which to store a value. Select the Store that value in this field: option button, and then select the AdRepNumber field.

o. Assign AdRepNumber as the name of the combo box.

p. If necessary, move the combo box control and label to match the approximate location shown in Figure 3.

Confirm that your form matches Figure 3, and then save and close it.

Figure 3: Advertiser Master Form – Design View

Modify the AdReps and Advertisers Form to match Figure 4 on the following page by completing these tasks:

q. Open the AdReps and Advertisers Form in Design View.

r. Add a text box control to the form in the position shown in Figure 4.

s. Create a calculated control that concatenates the FirstName field, a space, and the LastName field. (Hint: Your control should contain the formula =[FirstName]&' '&[LastName]).

t. Change the label to Name as shown in Figure 4.

u. If necessary, move and resize the text box control and label to match the approximate location shown in Figure 4.

Save and close the AdReps and Advertisers Form.

Figure 4: AdReps and Advertisers Form – Design View

Open the Rentals and Billboards Form in Design View, and then use the Subform/Subreport Wizard to add a subform with the following options:

v. Use the Billboard table for the subform.

w. Select the BillboardID, Location, and State fields from the Billboard table to add to the subform.

x. Accept the default link (“Show Billboard for each record in Rentals Contact Query using BillboardID”).

y. Save the subform as Billboard subform (which is the default name).

z. Resize the width of the subform to match that shown in Figure 5 on the following page. (Hint: The right edge of the subform is at the 6" mark on the ruler.)

aa. If necessary, move the subform to match the approximate location shown in Figure 5.

Confirm that the form matches Figure 5, and then save and close it.

Figure 5: Rentals and Billboards Form – Design View

Open the Rentals Master Form in Design View. Use the Command Button Wizard to add a command button with the following options:

ab. Select Form Operations as the category and Close Form as the action.

ac. Select the text option button with the text Close Form.

ad. Assign the name Close Form to the button.

ae. Position the button as shown in Figure 6 on the following page.

Save and close the Rentals Master Form.

Figure 6: Rentals Master Form – Design View

Open the AdRep Search Form in Design View, and then modify it by completing the following tasks:

af. Select the Name to Find control. (Hint: Select the control, not the label.)

ag. Open the Property Sheet for the control, and then change the name of the control to Name_to_Find. (Hint: Do not type the period.)

ah. With the Property Sheet still open, select the Name to Find label, and then change the special effect for the label to Etched.

ai. Close the Property Sheet, save the changes, and then close the form.

Open the Advertiser Financial Form in Design View, and then place a rectangle around the Name to Find label and control as shown in Figure 7 on the following page. Save the change to the form, and then close it.

Figure 7: Advertiser Financial Form – Design View

Open the Billboards by State Form in Design View, and then complete the following tasks:

aj. Add a tab control to the form in the approximate position shown in Figure 8 on the following page, resizing it to match the figure.

ak. Change the name of the first tab to Datasheet. (Hint: Do not type the period.)

al. Change the name of the second tab to Graph. (Hint: Do not type the period.)

Save the changes to the form, and then close it.

Figure 8: Billboards by State Form – Design View

Open the Rentals by Advertiser Form in Design View, and then make the following changes:

am. Delete the AdvertiserID control and label.

an. Save the form, and then switch to Datasheet View.

ao. Resize all columns in the Rentals by Advertiser Form to best fit the data they contain.

Save and close the form.

Open the AdRep Rental Data Form in Design View, and then select the Datasheet tab control. Resize the subform to the approximate size shown in Figure 9 on the following page. Save, but do not close, the AdRep Rental Data Form.

Figure 9: AdRep Rental Data Form – Datasheet Tab

With the AdRep Rental Data Form still open in Design View, modify it to match Figure 10 on the following page by completing these tasks:

ap. Select the Chart tab control, and then select the chart.

aq. Edit the chart object, and then change the chart type to Clustered Bar as shown in Figure 10 on the next page.

ar. Change the placement of the legend to Bottom, as shown in Figure 10.

Save and close the form.

Figure 10: AdRep Rental Data Form – Chart Tab

Open the Billboard Master Form in Design View. Add a combo box control to the form in the position shown in Figure 11 on the following page. Use the Combo Box Wizard to create the combo box with these options:

as. Ensure the Find a record on my form based on the value I selected in my combo box. option button is selected.

at. Add the City field as the field to include in the combo box.

au. Ensure that there is a check mark in the “Hide key column (recommended)” check box.

av. Assign the label City to Find to the combo box.

aw. If necessary, move and resize the combo box control and label to match the approximate location shown in Figure 11.

Confirm that your form matches Figure 11. Save and close the form.

Figure 11: Billboard Master Form – Design View

Open the AdRep Combo Sort Form in Design View, and then modify the combo box control by completing the following tasks:

ax. Select the Name to Find control. (Hint: Select the control, not the label.)

ay. Open the Property Sheet for the control, and then click the Row Source property.

az. Click the Build button, and then sort the last names in ascending order.

ba. Save the changes to the query, close the query window, save the changes to the form, and then close the form.

Save and close any open database objects. Compact and repair your database, close it, and then exit Access. Follow the directions on the SAM website to submit your completed project.

UCC final/Instructions_SC_AC16_8b.docx

Shelly Cashman Access 2016 | Module 8: SAM Project 1b

C:\Users\akellerbee\Documents\SAM Development\Design\Pictures\g11731.png

Shelly Cashman Access 2016 | Module 8: SAM Project 1b

All Around Outside Maintenance

MACROS, NAVIGATION FORMS, CONTROL LAYOUTS

GETTING STARTED

Open the file SC_AC16_8b_FirstLastName_1.accdb, available for download from the SAM website.

Save the file as SC_AC16_8b_FirstLastName_2.accdb by changing the “1” to a “2”.

0. Hint: If you do not see the .accdb file extension in the Save As dialog box, do not type it. Access will add the file extension for you automatically.

PROJECT STEPS

Open the Preview Client Financial Report macro in Design View. Change the View argument to Print Preview. Save the change to the macro, and then close the Preview Client Financial Report macro.

The Open Basic Property List macro contains an error. Complete the following tasks to correct the macro:

a. Open the Open Basic Property List macro in Design View.

b. Click the Single-Step button.

c. Run the macro.

d. Correct the error. (Hint: The macro should open the Basic Property List report in Print Preview mode.)

e. Click the Single-Step button again to deselect it.

Save and close the Open Basic Property List macro.

Create a new macro with two submacros by following the directions below:

f. When the Macro Design window opens, display the Action Catalog.

g. Add the first submacro to the macro, using Open Property Table as the name for the submacro.

h. In the first submacro, use the OpenTable action to open the Property table in Datasheet View and in Edit data mode.

i. Add a second submacro to the macro, using Open Client Table as the name for the macro.

j. In the second submacro, use the OpenTable action to open the Client table in Datasheet View and in Edit data mode.

k. Save the macro with the name OpenTables.

Confirm that your macro matches Figure 1 below, and then close the macro.

Figure 1: OpenTables Macro

Open the Client Types table in Datasheet View, and then create a data macro for the table by completing the following tasks:

l. Click the Before Change button on the Table Tools tab.

m. Create the macro shown in Figure 2 on the next page.

Save and close the macro, and then save and close the Client Types table.

Figure 2: Data Macro for a Before Change Event

Create a Datasheet form based on the State table, and then save the form with the default name State. Close the form.

Create a Navigation form for the database with the following options:

n. Use the Horizontal Tabs layout.

o. Add the Manager, Client, and Property Datasheet forms to the Navigation form in that order.

p. Change the title (in the Form Header) to Basic Navigation Form. (Hint: Do not type the period.)

q. Save the navigation form with the name Basic Navigation Form. (Hint: Do not type the period.)

Confirm that your form matches Figure 3 on the next page. Save and close the Basic Navigation Form.

Figure 3: Basic Navigation Form – Form View

Open the Client form in Datasheet View, and then complete the following tasks to create a UI macro for the form:

r. Select the ClientNumber column. (Hint: The ClientNumber column has the caption “CL#”.)

s. Click the Property Sheet button.

t. Click the Build button for the On Click event, and then select Macro Builder.

u. Create a macro that opens the Client Master Form when a user selects a value in the ClientNumber column. The macro actions are shown in Figure 4 on the next page.

Save and close the macro, and then save and close the form.

Figure 4: UI Macro Associated with an On Click Event in the Client Form

Open the Main Menu Navigation Form in Layout View, and then complete the following tasks:

v. Add the Forms List form to the Main Menu Navigation Form as the last horizontal tab.

w. Rename the Forms List tab as Master Forms. (Hint: Do not type the period.)

x. Move the Manager tab so that it appears first in the list.

Confirm that the form matches Figure 5 on the next page, and then save and close the form.

Figure 5: Main Menu Navigation Form – Form View

Open the Open Master Forms form in Design View, and then add a command button to the form in the approximate position shown in Figure 6 on the next page. Use the Command Button wizard to create the command button while incorporating the following options:

y. Select Miscellaneous as the category and Run Macro as the action.

z. Select Forms Macro.Open Property Master Form as the macro.

aa. Select the Text option, and then enter the text Open Property Master Form. (Hint: Do not type the period.)

ab. Name the command button Open_Property_Master_Form. (Hint: Do not type the period.)

Save the changes to the form.

With the Open Master Forms form still open in Design View, complete the following tasks:

ac. Select the three buttons on the form, and then use the Size/Space menu to adjust the size to To Widest.

ad. Use the Size/Space menu to adjust the spacing of the button controls to Equal Vertical.

ae. Align the buttons to the Left.

Confirm that your form matches Figure 6, and then save and close the Open Master Forms form.

Figure 6: Open Master Forms Form – Design View

Open the Manager Layout Form in Layout View, and then modify the form to match Figure 7 on the next page by completing the following tasks:

af. Select the cell containing the value for the FirstName field, and then split the cell horizontally.

ag. Delete the LastName label.

ah. Move the cell containing the value for the LastName field to the position shown in Figure 7 (to the right of the FirstName field in the cell created in Step 11a).

ai. Change the FirstName label to Manager Name (including the space), as shown in Figure 7.

aj. Move the StartDate label and StartDate field to the position shown in Figure 7.

ak. Select the control layout for the form, and then change the control margins to Medium and the control padding to Narrow.

Save and close the form.

Figure 7: Manager Layout Form – Layout View

Open the States and Properties Tabular Report in Layout View. Select the control layout, and then change the report layout to Stacked as shown in Figure 8 on the next page. (Hint: To select a control layout, click the four-headed arrow in the upper-left corner of the report.) Save the change to the report, and then close it.

Figure 8: States and Properties Tabular Report – Layout View

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.

UCC final/Instructions_SC_AC16_9a.docx

Shelly Cashman Access 2016 | Module 9: SAM Project 1a

C:\Users\akellerbee\Documents\SAM Development\Design\Pictures\g11731.png

Shelly Cashman Access 2016 | Module 9: SAM Project 1a

West Coast Outdoor Advertisers

ADMINISTeRING A DATABASE SYSTEM

GETTING STARTED

· Open the file SC_AC16_9a_FirstLastName_1.accdb, available for download from the SAM website.

· Save the file as SC_AC16_9a_FirstLastName_2.accdb by changing the “1” to a “2”.

2. Hint: If you do not see the .accdb file extension in the Save As dialog box, do not type it. Access will add the file extension for you automatically.

· To complete this Project, you will also need to download and save the following support file from the SAM website:

· Support_SC_AC16_9a_BillboardTypes.accdb

PROJECT STEPS

Open the database in Backstage View, and then click the View and edit database properties link. When the Properties dialog box displays, create a custom property for the database as described below:

a. Create a custom property named Rent. (Hint: Do not type the period.)

b. Select Date as the type.

c. Enter 08/08/2016 as the value.

Confirm that your custom property matches the one shown in Figure 1 on the next page. (Hint: Your database may contain other properties, depending on the version of Access used to open the file or the method by which the file was downloaded from SAM. Ignore these additional properties.) Save and close the Properties box.

Figure 1: Custom Properties Tab in the Properties Dialog Box

With the database still open in Backstage View, click the Options tab. Select the Main Menu Navigation Form as the display form for the database, as shown in Figure 2 on the next page. (Hint: The display form is sometimes referred to as the startup form.)

Save the changes to the options. (Hint: When the Access dialog box appears and displays the message, “You must close and reopen the current database for the specified option to take effect.”, click the OK button.)

Figure 2: Access Options Dialog Box

Open the AdRep table in Design View, and then create a custom input mask for the AdRepNumber field as described below:

d. The AdRepNumber field must consist of two letters and two numbers.

e. All letters should display as uppercase. (Hint: Enter >LL99 as the input mask.)

f. Save the changes to the table.

With the AdRep table still open in Design View, modify the AdRepName multiple-field index by completing the following tasks:

g. Open the Indexes dialog box.

h. Change the sort order for the LastName field to Descending.

i. Save the changes to the table, and then close it.

Open the Advertiser table in Design View, and then create a single-field index on the AdvertiserName field using AdvertiserName as the index name. (Hint: Be careful not to include spaces after the index name.) The index should allow duplicate values. Save the changes to the table design.

With the Advertiser table still open in Design View, create a multiple-field index as described below:

j. Name the index AdvertiserLocation. (Hint: Be careful not to include spaces after the index name.)

k. Use the State field, sorted in Descending order, as the first field in the index.

l. Use the City field, sorted in Ascending order, as the second field in the index.

m. Save the changes to the table design, and then close the table.

Open the Rentals table in Design View, and then open the Property Sheet for the table and make the following updates:

n. Create a validation rule for the table requiring that the Deposit field value is always less than or equal to the Cost field value. (Hint: Enter [Deposit]<=[Cost] as the validation rule.)

o. Enter Deposit cannot exceed Cost as the validation text.

Close the Property Sheet, and then save the changes to the table. (Hint: Because there was a change to data integrity rules, the “existing data may not be valid” warning message will appear. Click Yes and continue saving the table. If another message occurs, click the Yes button.)

With the Rentals table still open in Design View, create a multiple-field index as described below:

p. Name the index RentalsInformation. (Hint: Be careful not to include spaces after the index name.)

q. Use the AdvertiserID field, sorted in Ascending order, as the first field in the index.

r. Use the RentalID field, sorted in Ascending order, as the second field in the index.

s. Save the changes to the table, and then close it.

Create a Datasheet form for the Advertiser table, and then save the form with the name AdvertisersForm. (Hint: Do not type the period.) Close the form.

Create a blank form based on the 1 Right application part. (Hint: The blank form will appear in the Navigation Pane as a forms object named “SingleOneColumnRightLabels” and is saved automatically.)

Import a table from an encrypted database by completing the following steps:

t. Open the encrypted database Support_SC_AC16_9a_BillboardTypes.accdb, available for download from the SAM website, in Exclusive mode, using the password billboard when the Enter Password dialog box appears. Enable the content.

u. Decrypt the Support_SC_AC16_9a_BillboardTypes.accdb database. (Hint: You will need to use the password billboard again.) Close the database.

v. Open (or return to) the SC_AC16_9a_FirstLastName_2.accdb database.

w. Import the BillboardType table from the database Support_SC_AC16_9a_BillboardTypes.accdb into a new table with the name BillboardType. (Hint: Ensure that the Tables tab is selected in the Import Object dialog box.)

x. Do not save the Import steps.

Use the Table Analyzer to analyze the Rentals table for redundancy. Implement the following options when using the Table Analyzer Wizard:

y. Select the Rentals table as the table to analyze, and let the wizard decide what fields go in each table.

z. Rename Table 1 as AltRentals using the Rename table button, as shown in Figure 3 on the next page.

aa. Rename Table 2 as AltMonths using the Rename table button, as shown in Figure 3 on the next page.

ab. Rename Table 3 as AltFacing using the Rename table button, as shown in Figure 3 on the next page.

ac. When the wizard displays the next screen with the question, “Do the bold fields uniquely identify each record in the proposed table?”, click the Next button.

ad. When the wizard displays the next screen, “Correcting typographical errors for table ‘AltMonths’.”, click the Next button. Ensure that the “No, don’t create a query” radio button is selected, then click Finish to close the Table Analyzer Wizard.

ae. When the Table Analyzer Wizard dialog box appears with the message, “The command or action TileHorizontally isn’t available now”, click OK. Close the AltRentals, AltMonths, and AltFacing tables.

af. Confirm that the Navigation Pane matches Figure 4 below, and then save and close the form.

ag.

Figure 3: Table Analyzer Wizard

Figure 4: Navigation Pane with Tables Created in Step 13

Switch to viewing database items by the custom category named “Rental Information” in the Navigation Pane. Add the following two forms to the Rental Information Form group:

ah. Rentals

ai. Rentals and Billboards Form

Confirm the Rental Information Form group matches Figure 5 below.

Add a new group to the Rental Information category as described below:

aj. Name the new group Rental Reports.

ak. If necessary, move the Rental Reports group so that it appears between the Rental Information Form and the Unassigned Objects group.

al. In the Navigation Pane, add the Basic Billboards List report to the new group.

Confirm that the Rental Reports group and item in the Navigation Pane match Figure 5.

Figure 5: Navigation Pane Viewed by Rental Information Category

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.

UCC final/Instructions_SC_AC16_CS8-10a.docx

Shelly Cashman Access 2016 | Modules 8-10: SAM Capstone Project 1a

C:\Users\akellerbee\Documents\SAM Development\Design\Pictures\g11731.png

Shelly Cashman Access 2016 | Modules 8-10: SAM Capstone Project 1a

Carpenter Family Camp

Macros, navigation forms, database administration, using sql

GETTING STARTED

Open the file SC_AC16_CS8-10a_FirstLastName_1.accdb, available for download from the SAM website.

Save the file as SC_AC16_CS8-10a_FirstLastName_2.accdb by changing the “1” to a “2”.

0. 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.

PROJECT STEPS

Open the Preview Parent Contact Report macro in Design View. Change the View argument to Print Preview. Save the change to the macro, and then close the Preview Parent Contact Report macro.

Create a new macro with two submacros by following the directions below:

a. When the Macro Design window opens, display the Action Catalog.

b. Add the first submacro to the macro, using Open Camper Table as the name for the submacro.

c. In the first submacro, use the OpenTable action to open the Camper table in Datasheet View and in Edit data mode.

d. Add a second submacro to the macro, using Open Reservation Table as the name for the macro.

e. In the second submacro, use the OpenTable action to open the Reservation table in Datasheet View and in Edit data mode.

f. Save the macro with the name OpenTables.

Confirm that your macro matches Figure 1 on the next page, and then close the macro.

Figure 1: OpenTables Macro

Open the Payments table in Datasheet View and create a data macro for the table by performing the following tasks:

g. Click the Before Change button on the Table Tools tab.

h. Create the macro shown in Figure 2 on the next page.

Save and close the macro, then save and close the Payments table.

Figure 2: Data Macro for Before Change Event

Create a Navigation form for the database with the following options:

i. Use the Horizontal Tabs layout.

j. Add the Camper, Parent, and Reservation Datasheet forms to the Navigation form in that order.

k. Change the title (in the Form Header) to Main Navigation Form. (Hint: Do not type the period.)

l. Save the navigation form with the name Main Navigation Form. (Hint: Do not type the period.)

Switch to Form View, and confirm that your form matches Figure 3 on the next page. Save the form and close the Main Navigation Form form.

Figure 3: Main Navigation Form - Form View

Open the Counselor form in Datasheet View and perform the following tasks to create a UI macro for the form:

m. Select the CounselorID column.

n. Click the Property Sheet button.

o. Click the Build button for the On Click event and select Macro Builder.

p. Create a macro that opens the Counselor Master Form when a user selects a value in the CounselorID column. The macro actions are shown in Figure 4 on the next page.

Save and close the macro, then save and close the form.

Figure 4: UI Macro Associated with On Click Event in the Counselor Form

Open the Main Menu Form in Layout View and perform the following tasks:

q. Add the Forms List form to the Main Menu Form as the last horizontal tab.

r. Rename the Forms List tab as Master Forms. (Hint: Do not type Period.)

s. Move the Counselor tab so that it appears first in the list.

Confirm that the form matches Figure 5 on the following page and then save and close the form.

Figure 5: Main Menu Form – Form View

Open the Open Master Forms form in Design View and add a command button to the form in the approximate position shown in Figure 6 on the following page. Ensure that Use Control Wizards is selected, and use the following options when creating the command button using the Command Button wizard:

t. Select Miscellaneous as the category and Run Macro as the action.

u. Select Forms.Open Parent Master Form as the macro.

v. Select the Text option and enter the text Open Parent Master Form. (Hint: Do not type Period.)

w. Name the command button Open_Parent_Master_Form. (Hint: Do not type the period.)

Save the changes to the form but do not close it.

With the Open Master Forms form still open in Design View, complete the following actions:

x. Select the three buttons on the form and use the Size/Space menu to adjust the size to Widest.

y. Use the Size/Space menu to adjust the spacing of the button controls to Equal Vertical.

z. Align the buttons to the Left.

Confirm that your form matches Figure 6 on the following page. Save and and close the Open Master Forms form.

Figure 6: Open Master Forms – Design View

aa. Create a custom property named Confirm. (Hint: Do not type the period.)

ab. Select Date as the type.

ac. Enter 05/10/2017 as the value.

Confirm that your custom property matches the one shown in Figure 7 on the following page. (Hint: Your database may contain other properties, depending on the version of Access used to open the file or the method by which the file was downloaded from SAM. Ignore these additional properties.) Save and close the Properties box.

Figure 7: Custom Properties Tab in the Properties Dialog Box

With the database still open in Backstage View, click the Options tab. Select the Main Menu Form as the display form for the database, as shown in Figure 8 on the following page. (Hint: The display form is sometimes referred to as the startup form.)

Figure 8: Access Options Dialog Box

Open the Counselor table in Design View, and then create a custom input mask for the CounselorID field as described below:

ad. The CounselorID field must consist of two letters and two numbers.

ae. All letters should display in uppercase. (Hint: Enter >LL99 as the input mask.)

af. Save the changes to the table.

With the Counselor table still open in Design View, create a single-field index on the LastName field. The index should allow duplicate values. Save the changes to the table design and then close the table.

Open the Parent table in Design view and create a multiple-field index as described below:

ag. Name the index ParentLocation. (Hint: Do not type Period.)

ah. Use the State field, sorted in Descending order, as the first field in the index.

ai. Use the City field, sorted in Ascending order, as the second field in the index.

aj. Save the changes to the table design, and then close the table.

Open the Payments table in Design View, and then open the Property Sheet for the table and make the following updates:

ak. Create a validation rule for the table requiring that the Deposit field value is always less that or equal to the TotalFees field value. (Hint: Enter [Deposit]<=[TotalFees] as the validation rule.)

al. Enter Deposit cannot exceed TotalFees as the validation text.

Close the Property Sheet, save the changes to the table, then close the table. (Hint: Because there was a change to data integrity rules, the “existing data may not be valid” warning message will appear. Click No and continue saving the table.)

With the Navigation Pane open, switch to viewing database items by the custom category named Family Information in the Navigation Pane. Add the following Parent Master Form and the Camper Master Form to Family Information Forms group. Confirm the Family Information Forms group matches Figure 9 on the following page.

Add a new group to the Family Information category as described below:

am. Name the new group Family Reports. (Hint: Do not type Period.)

an. If necessary, move the Family Reports group so that it appears between the Family Information Forms group and the Unassigned Objects group.

ao. In the Navigation Pane, add the Parent Contact Report to the Family Reports group in the Navigation Pane to match Figure 9 on the following page.

Figure 9: Navigation Pane Viewed by Family Information Category

Create a new query in SQL View based on the Adventure table:

ap. Add all fields from the Adventure table to the new query using the asterisk (*) in the Select clause.

aq. Be sure to end the SQL command with a semicolon (;).

ar. Run the query to produce the results shown in Figure 10 below.

as. Save the query as AdventureQuery, and then close the query.

Figure 10: AdventureQuery Results

Open the ActivityQuery in SQL View, and then add the Audience field to the SELECT clause (after the Description field.) Run the query, and ensure that the results match those shown in Figure 11 on the following page. Save and close the the ActivityQuery.

Figure 11: ActivityQuery Results

Open the PaymentsQuery in SQL View. Add a computed field to the SELECT clause (after the Deposit field) as described below:

at. The computed field should calculate the remaining payment for the camp session by subtracting the Deposit field value from the TotalFees field value. (Hint: Enter TotalFees-Deposit as the computation.)

au. Use Remaining as the name (alias) for this computed field. (Hint: Use the AS clause.)

av. Run the query and ensure that the results match those shown in Figure 12 on the following page. (Hint: Your records may be in a different order.) Save and close the query.

Figure 12: PaymentsQuery Results

Open the ReservationsCriteriaQuery in SQL View. Add a WHERE clause to the query in the position shown in Figure 13 below that restricts retrieval to only those reservations where the lodgingfee is greater than 150. (Hint: Figure 13 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 ReservationsCriteriaQuery.

Figure 13: ReservationsCriteriaQuery in SQL View

Open the ParentStatesQuery 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 State field is equal to PA or NJ. (Hint: Currently, the query only retrieves records where the State field is equal to PA.) Run the query, and ensure that the results match those shown in Figure 14 on the next page. Save and close the ParentStatesQuery.

Figure 14: ParentStatesQuery Results

Open the ReservationSessionQuery 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 SessionID field is equal to 2 and the LodgingFee field is equal to 0. (Hint: Currently the query only retrieves records in which the SessionID field is equal to 2.) Run the query, and ensure that the results match those shown in Figure 15 below. Save and close the ReservationSessionQuery.

Figure 15: ReservationSessionQuery Results

Open the MassachusettsCountQuery in SQL View. Modify the SELECT clause to count the number of parents that are located in MA. (Hint: Change the SELECT clause to COUNT(ParentID). 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 16 below. Save and close the MassachusettsCountQuery.

Figure 16: MassachusettsCountQuery Results

Open the JoinQuery in SQL View. Add a WHERE clause that joins the Reservation table and the Parent table. The common field in both tables is ParentID. You will need to qualify the ParentID field in the WHERE clause. Run the query and ensure that the results match those shown in Figure 17 below. (Hint: All rows are not displayed in the figure. The order of the records may differ. There should be 26 records in the result.) Save and close the JoinQuery.

Figure 17: JoinQuery Results

Open the CounselorSkillsQuery in SQL View. Add a caption to the Specialty field in the SELECT clause. (Hint: Use the AS clause.) Use SpecialSkills as the caption for the Specialty field. Run the query, and confirm that the last field in the query displays as SpecialSkills, as shown in Figure 18 on the next page. If necessary, enlarge the size of the caption so that the entire caption is displayed. Save and close the CounselorSkillsQuery.

Figure 18: CounselorSkillsQuery 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 19 below. Save and close the SortQuery.

Figure 19: SortQuery Results

Open the GroupingQuery in SQL View. Modify the query by completing the following tasks:

aw. Add the State field to the SELECT statement. The State field should appear before the COUNT(ParentID) function.

ax. Group the records by the State field.

ay. Sort the records by the State field in Ascending order.

Run the query, and ensure that the results match those shown in Figure 20 below. Save and close the GroupingQuery.

Figure 20: GroupingQuery Results

Save and close any open database objects. Compact and repair your database, and then exit Access. Follow the directions on the SAM website to submit your completed project.

UCC final/SC_AC16_10a_me_1.accdb

IfSuccessful_Status	GA_Status_Icon	SAM_Logo
true
false

ID	FirstName	LastName	AssignmentGUID	UserID
false	Wilson	Aguilar-Pulla	{843287C1-4760-45FE-B1DA-62BD415C6C88}	{843287C1-4760-45FE-B1DA-62BD415C6C88}

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	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 St.	Salem	OR	97301		¤ 1,090.75	¤ 1,345.60	KS02
PJG34	Patricia Jean Greenhouses	345 Magee Ave.	Seattle	WA	98101		¤ 1,345.00	¤ 2,450.00	UG01
RER23	Rivers Edge Restaurant	125 River Rd.	Kettle Falls	WA	99141		¤ 1,200.00	¤ 1,375.00	GZ01
SLA77	Smarter Law Associates	764 Main St.	Portland	OR	97209		¤ 7,500.00	¤ 0.00	AM01
TVS17	Theon Veterinary Services	346 Austin Ave.	Casper	WY	83114		¤ 2,225.60	¤ 2,500.00	MR01
WEC05	Western Energy Company	12 Polk St.	Kettle Falls	WA	99141		¤ 3,450.80	¤ 0.00	GZ01
WSC01	Wood Sports Complex	578 Central Ave.	Cave Junction	OR	97523		¤ 2,345.50	¤ 3,590.80	KS02

BillboardID	Location	City	State	Height	Width	SqrFt	Illuminated	DEC	Type
LHAR04	Hwy 395	Arden	WA	8.0	24.0	192.0	Yes	4395	Digital
LHBE01	Hwy 97	Beaver Marsh	OR	8.0	24.0	192.0	Yes	3542	Digital
LHBU01	Hwy 395	Burns	OR	10.5	48.0	504.0	Yes	1509	Poster
LHCA01	Hwy 199	Cave Junction	OR	6.0	16.0	96.0	No	2024	Junior Poster
LHCA03	Hwy 20-26	Casper	WY	10.0	32.0	320.0	No	1288	Bulletin
LHCH03	Hwy 97	Chemult	OR	10.0	24.0	240.0	Yes	3411	Digital
LHCH05	Hwy 93	Challis	ID	8.0	24.0	192.0	No	506	Poster
LHCO05	Hwy 89	Cokeville	WY	10.0	20.0	200.0	No	506	Poster
LHDA03	Hwy 197	Dallesport	WA	8.0	20.0	160.0	No	3700	Junior Poster
LHDC07	Hwy 89	Alpine	WY	6.0	8.0	48.0	No	1964	Junior Poster
LHDD01	Hwy 26	Dubois	WY	8.0	24.0	192.0	Yes	1016	Poster
LHGR01	Hwy 18	Grand Ronde	OR	10.0	30.0	300.0	Yes	5182	Bulletin
LHGR02	Hwy 99	Grants Pass	OR	10.0	25.0	250.0	Yes	5838	Poster
LHKE01	Hwy 395	Kettle Falls	WA	6.0	10.0	60.0	No	7400	Junior Poster
LHKL01	Hwy 97	Klamath Falls	OR	10.0	24.0	240.0	Yes	4461	Digital
LHMA01	Hwy 97	Madras	OR	8.0	24.0	192.0	Yes	12070	Digital
LHMP01	Hwy 97	Modoc Point	OR	8.0	24.0	192.0	Yes	5500	Poster
LHPA03	Hwy 95	Payette	ID	12.0	24.0	288.0	No	3777	Bulletin
LHRI01	Hwy 95	Riggins	ID	8.0	24.0	192.0	Yes	1724	Digital
LHRI02	Hwy 20	Rigby	ID	14.0	48.0	672.0	Yes	13406	Bulletin
LHRO01	Hwy 101	Rockaway	OR	5.0	8.0	40.0	No	1702	Junior Poster
LHSA01	Hwy 114	Salem	OR	8.0	24.0	192.0	Yes	10224	Poster
LHSA03	Liberty Road	Salem	OR	9.0	27.0	243.0	Yes	11808	Poster
LHSA05	Hwy 99	Salem	OR	8.0	24.0	192.0	Yes	10224	Poster
LHTW01	Hwy 93	Twin Falls	ID	6.0	12.0	72.0	No	1564	Junior Poster
LHWE01	Hwy 95	Weiser	ID	10.5	24.0	252.0	No	1748	Poster
LHWE04	I-90 Exit 60	Wallace	ID	14.0	48.0	672.0	Yes	6888	Bulletin
LIBO01	I-84 Exit 57	Boise	ID	21.0	47.0	987.0	Yes	14104	Bulletin
LIRO01	I-5 Exit 120	Roseburg	OR	12.0	48.0	576.0	Yes	27618	Bulletin
LNPO01	I-84 Exit 1	Portland	OR	14.0	48.0	672.0	Yes	110667	Bulletin
LSCO05	Hwy 42	Coquille	OR	8.0	25.0	200.0	Yes	5642	Digital
LSEU01	6th Ave.	Eugene	OR	10.0	24.0	240.0	Yes	19942	Poster
LSEU05	Maxwell St.	Eugene	OR	12.0	24.0	288.0	Yes	5707	Poster
LSEU07	W. 11th St.	Eugene	OR	10.0	24.0	240.0	Yes	21910	Digital
LSGO01	Hwy 142	Goldendale	WA	8.0	16.0	128.0	No	1748	Digital
LSKL01	S. 6th St.	Klamath Falls	OR	8.0	24.0	192.0	Yes	11414	Poster
LSSP01	Hwy 126	Springfield	OR	7.0	14.0	98.0	Yes	10037	Junior Poster

BillboardTypeID	Description
BN	Bulletin
DL	Digital
JP	Junior Poster
PT	Poster

RentalID	BillboardID	Facing	Cost	Deposit	Months	BeginDate	Discount	AdvertiserID
1700200	LHBU01	North	¤ 775.00	¤ 0.00	3.0	6/5/17	false	BCC76
1700201	LNPO01	East	¤ 1,600.00	¤ 0.00	4.0	5/2/17	false	CRR15
1700202	LNPO01	West	¤ 1,400.00	¤ 0.00	8.0	3/14/17	true	SLA77
1700204	LHAR04	North	¤ 2,500.00	¤ 0.00	1.0	9/4/17	false	KDS21
1700205	LHSA03	North	¤ 800.00	¤ 0.00	2.0	7/1/17	false	MBT14
1700206	LHSA03	South	¤ 800.00	¤ 0.00	2.0	7/1/17	false	MBT14
1700208	LHGR02	North	¤ 790.00	¤ 0.00	2.0	9/12/17	false	GAD74
1700209	LHTW01	North	¤ 350.00	¤ 0.00	5.0	4/11/17	false	IOA23
1700210	LHDA03	South	¤ 300.00	¤ 0.00	6.0	6/5/17	true	CDS29
1700211	LHCO05	North	¤ 875.00	¤ 0.00	4.0	5/12/17	false	KGS04
1700212	LHWE01	South	¤ 900.00	¤ 0.00	1.0	9/12/17	false	BMS53
1700213	LSGO01	West	¤ 2,875.00	¤ 0.00	10.0	2/1/17	false	LCA42
1700215	LHMA01	South	¤ 3,000.00	¤ 0.00	3.0	6/5/17	false	GRI03
1700216	LHCA03	West	¤ 2,750.00	¤ 0.00	4.0	6/24/17	true	CWW01
1700217	LHCA01	North	¤ 325.00	¤ 0.00	7.0	3/21/17	false	WSC01
1700219	LHPA03	South	¤ 1,625.00	¤ 0.00	3.0	8/13/17	false	LFS02
1700220	LSEU01	East	¤ 775.00	¤ 0.00	2.0	7/1/17	false	HAR01
1700222	LHDD01	East	¤ 825.00	¤ 0.00	1.0	9/14/17	false	KGS04
1700223	LHKE01	South	¤ 365.00	¤ 0.00	3.0	8/25/17	false	HCH10
1700224	LHAR04	South	¤ 3,500.00	¤ 0.00	10.0	2/1/17	false	LCA42
1700225	LHKE01	North	¤ 375.00	¤ 0.00	3.0	6/12/17	false	RER23
1700226	LHCH05	North	¤ 700.00	¤ 0.00	4.0	5/25/17	true	IOA23
1700227	LIBO01	West	¤ 1,525.00	¤ 0.00	4.0	7/1/17	false	BMS53
1700229	LHPA03	North	¤ 1,625.00	¤ 0.00	3.0	8/14/17	false	LFS02
1700230	LHGR02	North	¤ 790.00	¤ 0.00	1.0	2/1/17	false	GPM12

State	StateName
CA	California
ID	Idaho
NV	Nevada
OR	Oregon
WA	Washington
WY	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 Billboard;

SELECT DISTINCTROW * FROM States;

SELECT DISTINCTROW * FROM Rentals;

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 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;

UCC final/SC_AC16_2a_me_1.accdb

IfSuccessful_Status	GA_Status_Icon	SAM_Logo
true
false

ID	FirstName	LastName	AssignmentGUID	UserID
false	Wilson	Aguilar-Pulla	{9A01FA72-F75B-4623-8E75-A0BC10834675}	{9A01FA72-F75B-4623-8E75-A0BC10834675}

ID	FirstName	LastName	ProjectName	SubmissionNum	MaxScore	Score	EngineVersion

ID	StepNumber	Description	IfSuccessful	StepScore	StepMaxScore	ErrorText	ActionName	StepActionOrder

AdRepNumber	FirstName	LastName	PhoneNumber	Address	City	State	PostalCode	Salary	DateHired
AM01	Abraham	Miller	503-555-6032	54 Quarry Ave.	Portland	OR	97210	¤ 32,540.00	6/1/16
GZ01	Gregory	Zdalnza	603-555-8765	7 Moose Head Rd.	Boise	ID	83720	¤ 29,675.00	9/14/16
KS02	Kathryn	Stenhope	503-555-0003	9 Shennadoah Ave.	Salem	OR	97311	¤ 34,576.00	10/5/15
MR01	Max	Riley	781-555-2323	24 Spring Rd.	Casper	WY	83114	¤ 30,450.00	8/6/16
RH01	Rowan	Horton	207-555-9009	12 Heather Rd.	Portland	OR	97211	¤ 35,275.00	7/13/15
SL01	Stephanie	Lorens	802-555-3339	15 River Rd.	Spokane	WA	99202	¤ 37,350.00	4/5/15
UG01	Ursula	Grea-Mayers	207-555-8534	342 Pine St.	Seattle	WA	98101	¤ 41,765.00	12/12/14

AdvertiserID	AdvertiserName	Address	City	State	PostalCode	Current Due	AdRepNumber
BBF32	Babbage CPA Firm	464 Linnell Dr.	Seattle	WA	98104	¤ 7,875.25	UG01
BCC76	Burns Community College	867 Ridge Ave.	Burns	OR	97720	¤ 5,452.30	RH01
BMS53	Boise Medical Services	134 Main St.	Boise	ID	83720	¤ 6,152.25	GZ01
CDS29	Carter Dental Services	123 Second St.	Dallesport	WA	98617	¤ 5,475.75	SL01
CRR15	Columbia River Rafters	125 River Blvd.	Portland	OR	97209	¤ 3,600.50	RH01
CWW01	Casper Western Wear	10 Broadway St.	Casper	WY	83114	¤ 4,125.50	MR01
GAD74	Grant Antique Dealers	78 Catawba Dr.	Grants Pass	OR	97526	¤ 1,200.00	GZ01
GCF56	Goldendale City Foundation	65 Simpson Dr.	Goldendale	WA	98620	¤ 2,400.00	SL01
GPM12	Grants Pass Motel	235 Second Ave.	Grants Pass	OR	97526	¤ 2,460.75	AM01
GRI03	Germann Inn	345 Hartsel Ave.	Madras	OR	97741	¤ 2,870.65	AM01
HAR01	Haydee's Restaurant	234 Twiddy St.	Eugene	OR	97402	¤ 2,500.00	RH01
HCH10	Hendley County Hospital	216 Rivard Blvd.	Spokane	WA	99201	¤ 7,234.60	SL01
IOA23	Idaho Outdoor Adventures	12 Third St.	Twin Falls	ID	83301	¤ 6,500.00	KS01
KDS21	KAL Design Studio	116 Pine St.	Arden	WA	99202	¤ 1,550.50	UG01
KGS04	Kyle General Store	421 First St.	Cokeville	WY	83114	¤ 2,875.50	MR01
LCA42	Lewis and Clark Adventures	100 Main St.	Goldendale	WA	98620	¤ 4,500.00	SL01
LFS02	Lewis Furniture Store	267 Main St.	Payette	ID	83661	¤ 2,890.70	MR01
MBT14	Mike's Bike Tours	234 Gilham St.	Salem	OR	97301	¤ 1,345.60	KS01
PJG34	Patricia Jean Greenhouses	345 Magee Ave.	Seattle	WA	98101	¤ 2,450.00	UG01
RER23	Rivers Edge Restaurant	125 River Rd.	Kettle Falls	WA	99141	¤ 1,375.00	GZ01
SLA77	Smarter Law Associates	764 Main St.	Portland	OR	97209	¤ 0.00	AM01
TVS17	Theon Veterinary Services	346 Austin Ave.	Casper	WY	83114	¤ 2,500.00	MR01
WEC05	Western Energy Company	12 Polk St.	Kettle Falls	WA	99141	¤ 0.00	GZ01
WSC01	Wood Sports Complex	578 Central Ave.	Cave Junction	OR	97523	¤ 3,590.80	KS01

BillboardID	Location	City	State	Height	Width	Illuminated	DEC	Type	Facing
LHAR04	Hwy 395	Arden	WA	8.0	24.0	Yes	4395	Digital	North/South
LHBE01	Hwy 97	Beaver Marsh	OR	8.0	24.0	Yes	3542	Digital	North/South
LHBU01	Hwy 395	Burns	OR	10.5	48.0	Yes	1509	Poster	North/South
LHCA01	Hwy 199	Cave Junction	OR	6.0	16.0	No	2024	Junior Poster	North/South
LHCA03	Hwy 20-26	Casper	WY	10.0	32.0	No	1288	Bulletin	West Only
LHCH03	Hwy 97	Chemult	OR	10.0	24.0	Yes	3411	Digital	North/South
LHCH05	Hwy 93	Challis	ID	8.0	24.0	No	506	Poster	North/South
LHCO05	Hwy 89	Cokeville	WY	10.0	20.0	No	506	Poster	North/South
LHDA03	Hwy 197	Dallesport	WA	8.0	20.0	No	3700	Junior Poster	North/South
LHDC07	Hwy 89	Alpine	WY	6.0	8.0	No	1964	Junior Poster	North Only
LHDD01	Hwy 26	Dubois	WY	8.0	24.0	Yes	1016	Poster	East/West
LHGR01	Hwy 18	Grand Ronde	OR	10.0	30.0	Yes	5182	Bulletin	East/West
LHGR02	Hwy 99	Grants Pass	OR	10.0	25.0	Yes	5838	Poster	North Only
LHKE01	Hwy 395	Kettle Falls	WA	6.0	10.0	No	7400	Junior Poster	North/South
LHKL01	Hwy 97	Klamath Falls	OR	10.0	24.0	Yes	4461	Digital	North/South
LHMA01	Hwy 97	Madras	OR	8.0	24.0	Yes	12070	Digital	North/South
LHMP01	Hwy 97	Modoc Point	OR	8.0	24.0	Yes	5500	Poster	North/South
LHPA03	Hwy 95	Payette	ID	12.0	24.0	No	3777	Bulletin	North/South
LHRI01	Hwy 95	Riggins	ID	8.0	24.0	Yes	1524	Digital	North/South
LHRI02	Hwy 20	Rigby	ID	14.0	48.0	Yes	13406	Bulletin	North/South
LHRO01	Hwy 101	Rockaway	OR	5.0	8.0	No	1702	Junior Poster	North/South
LHSA01	Hwy 114	Salem	OR	8.0	24.0	Yes	10224	Poster	North/South
LHSA03	Liberty Road	Salem	OR	9.0	27.0	Yes	11808	Poster	North/South
LHSA05	Hwy 99	Salem	OR	8.0	24.0	Yes	10224	Poster	North/South
LHTW01	Hwy 93	Twin Falls	ID	6.0	12.0	No	1564	Junior Poster	North/South
LHWE01	Hwy 95	Weiser	ID	10.5	24.0	No	1748	Poster	North/South
LHWE04	I-90 Exit 60	Wallace	ID	14.0	48.0	Yes	6888	Bulletin	East/West
LIBO01	I-84 Exit 57	Boise	ID	21.0	47.0	Yes	14104	Bulletin	East/West
LIRO01	I-5 Exit 120	Roseburg	OR	12.0	48.0	Yes	27618	Bulletin	North/South
LNPO01	I-84 Exit 1	Portland	OR	14.0	48.0	Yes	110667	Bulletin	East/West
LSCO05	Hwy 42	Coquille	OR	8.0	25.0	Yes	5642	Digital	East/West
LSEU01	6th Ave.	Eugene	OR	10.0	24.0	Yes	19942	Poster	East Only
LSEU05	Maxwell St.	Eugene	OR	12.0	24.0	Yes	5707	Poster	East/West
LSEU07	W. 11th St.	Eugene	OR	10.0	24.0	Yes	21910	Digital	East/West
LSGO01	Hwy 142	Goldendale	WA	8.0	16.0	No	1748	Digital	East/West
LSKL01	S. 6th St.	Klamath Falls	OR	8.0	24.0	Yes	11414	Poster	East/West
LSSP01	Hwy 126	Springfield	OR	7.0	14.0	Yes	10037	Junior Poster	East/West

State	StateName
ID	Idaho
OR	Oregon
WA	Washington
WY	Wyoming

SELECT Advertiser.AdvertiserID, Advertiser.AdvertiserName, Advertiser.Address, Advertiser.City, Advertiser.State, Advertiser.PostalCode, Advertiser.AdRepNumber FROM Advertiser;

SELECT AdRep.LastName, Advertiser.AdvertiserName, Advertiser.[Current Due] FROM AdRep INNER JOIN Advertiser ON AdRep.AdRepNumber = Advertiser.AdRepNumber ORDER BY AdRep.LastName, Advertiser.AdvertiserName;

SELECT Billboard.BillboardID, Billboard.Location, Billboard.City, Billboard.State, Billboard.Height, Billboard.Width, Billboard.Illuminated, Billboard.DEC, Billboard.Type, Billboard.Facing FROM Billboard WHERE (((Billboard.State)="WA"));

SELECT Billboard.BillboardID, Billboard.Location, Billboard.State, Billboard.Type FROM Billboard;

SELECT Billboard.Type, Billboard.BillboardID FROM Billboard;

SELECT Billboard.BillboardID, Billboard.Location, Billboard.State, Billboard.Illuminated, Billboard.DEC, Billboard.Type, Billboard.Facing FROM Billboard;

SELECT Advertiser.AdvertiserName, Advertiser.Address, Advertiser.City, Advertiser.State, Advertiser.PostalCode FROM Advertiser;

SELECT Advertiser.AdRepNumber, Advertiser.AdvertiserName, Advertiser.[Current Due] FROM Advertiser;

SELECT Advertiser.AdvertiserID, Advertiser.AdvertiserName, Advertiser.[Current Due], Advertiser.AdRepNumber FROM Advertiser;

SELECT Billboard.BillboardID, Billboard.Location, Billboard.City, Billboard.State, Billboard.Type FROM Billboard;

SELECT Billboard.BillboardID, Billboard.City, Billboard.State, Billboard.DEC FROM Billboard;

SELECT Billboard.BillboardID, Billboard.Location, Billboard.Height, Billboard.Width FROM Billboard;

SELECT Advertiser.AdvertiserID, Advertiser.AdvertiserName, Advertiser.[Current Due], Advertiser.AdRepNumber FROM Advertiser;

SELECT Advertiser.City FROM Advertiser ORDER BY Advertiser.City;

SELECT Advertiser.AdvertiserID, Advertiser.AdvertiserName, Advertiser.State, Advertiser.[Current Due], Advertiser.AdRepNumber FROM Advertiser WHERE (((Advertiser.State)="WA"));

UCC final/SC_AC16_3a_me_1.accdb

IfSuccessful_Status	GA_Status_Icon	SAM_Logo
true
false

ID	FirstName	LastName	AssignmentGUID	UserID
false	Wilson	Aguilar-Pulla	{B2FF44D2-772C-476E-8A05-5D240A67E61A}	{B2FF44D2-772C-476E-8A05-5D240A67E61A}

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
AM01	Miller	Abraham	54 Quarry Ave.	Portland	OR	97210	503-555-6032	¤ 32,540.00	6/1/16
GZ01	Zdalnza	Gregory	7 Moose Head Rd.	Boise	ID	83720	603-555-8765	¤ 29,675.00	9/14/16
KS02	Stenhope	Kathryn	9 Shennadoah Ave.	Salem	OR	97311	503-555-0003	¤ 34,576.00	10/5/15
MR01	Riley	Max	24 Spring Rd	Casper	WY	83114	781-555-2323	¤ 30,450.00	8/6/16
RH01	Horton	Rowan	12 Heather Rd.	Portland	OR	97211	207-555-9009	¤ 35,275.00	7/13/15
SL01	Lorens	Stephanie	15 River Rd.	Spokane	WA	99202	802-555-3339	¤ 37,350.00	4/5/15
UG01	Grea-Mayers	Ursula	342 Pine St.	Seattle	WA	98101	207-555-8534	¤ 41,765.00	12/12/14

AdvertiserID	AdvertiserName	Address	City	State	PostalCode	Current Due	AdRepNumber
BBF32	Babbage CPA Firm	464 Linnell Dr.	Seattle	WA	98104	¤ 7,875.25	UG01
BCC76	Burns Community College	867 Ridge Ave.	Burns	OR	97720	¤ 5,452.30	RH01
BMS53	Boise Medical Services	134 Main St.	Boise	ID	83720	¤ 6,152.25	GZ01
CDS29	Carter Dental Services	123 Second St.	Dallesport	WA	98617	¤ 5,475.75	SL01
CRR15	Columbia River Rafters	125 River Blvd.	Portland	OR	97209	¤ 3,600.50	RH01
CWW01	Casper Western Wear	10 Broadway St.	Casper	WY	83114	¤ 4,125.50	MR01
GAD74	Grant Antique Dealers	78 Catawba Dr.	Grants Pass	OR	97526	¤ 1,200.00	GZ01
GCF56	Goldendale City Foundation	65 Simpson Dr.	Goldendale	WA	98620	¤ 2,400.00	SL01
GPM12	Grants Pass Motel	235 Second Ave.	Grants Pass	OR	97526	¤ 2,460.75	AM01
GRI03	Germann Inn	345 Hartsel Ave.	Madras	OR	97741	¤ 2,870.65	AM01
HAR01	Haydee's Restaurant	234 Twiddy St.	Eugene	OR	97402	¤ 2,500.00	RH01
HCH10	Hendley County Hospital	216 Rivard Blvd.	Spokane	WA	99201	¤ 7,234.60	SL01
IOA23	Idaho Outdoor Adventures	12 Third St.	Twin Falls	ID	83301	¤ 6,500.00	KS02
KDS21	KAL Design Studio	116 Pine St.	Arden	WA	99202	¤ 1,550.50	UG01
KGS04	Kyle General Store	421 First St.	Cokeville	WY	83114	¤ 2,875.50	MR01
LCA42	Lewis and Clark Adventures	100 Main St.	Goldendale	WA	98620	¤ 4,500.00	SL01
LFS02	Lewis Furniture Store	267 Main St.	Payette	ID	83661	¤ 2,890.70	MR01
MBT14	Mike's Bike Tours	234 Gilham St.	Salem	OR	97301	¤ 1,345.60	KS02
PJG34	Patricia Jean Greenhouses	345 Magee Ave.	Seattle	WA	98101	¤ 2,450.00	UG01
RER23	Rivers Edge Restaurant	125 River Rd.	Kettle Falls	WA	99141	¤ 1,375.00	GZ01
SLA77	Smarter Law Associates	764 Main St.	Portland	OR	97209	¤ 0.00	AM01
TVS17	Theon Veterinary Services	346 Austin Ave.	Casper	WY	83114	¤ 2,500.00	MR01
WEC05	Western Energy Company	12 Polk St.	Kettle Falls	WA	99141	¤ 0.00	GZ01
WSC01	Wood Sports Complex	578 Central Ave.	Cave Junction	OR	97523	¤ 3,590.80	KS02

BillboardID	Location	City	State	Height	Width	Illuminated	DEC	Type	Facing
LHAR04	Hwy 395	Arden	WA	8.0	24.0	Yes	4395	Digital	North/South
LHBE01	Hwy 97	Beaver Marsh	OR	8.0	24.0	Yes	3542	Digital	North/South
LHBU01	Hwy 395	Burns	OR	10.5	48.0	Yes	1509	Poster	North/South
LHCA01	Hwy 199	Cave Junction	OR	6.0	16.0	No	2024	Junior Poster	North/South
LHCA03	Hwy 20-26	Casper	WY	10.0	32.0	No	1288	Bulletin	West Only
LHCH03	Hwy 97	Chemult	OR	10.0	24.0	Yes	3411	Digital	North/South
LHCH05	Hwy 93	Challis	ID	8.0	24.0	No	506	Poster	North/South
LHCO05	Hwy 89	Cokeville	WY	10.0	20.0	No	506	Poster	North/South
LHDA03	Hwy 197	Dallesport	WA	8.0	20.0	No	3700	Junior Poster	North/South
LHDC07	Hwy 89	Alpine	WY	6.0	8.0	No	1964	Junior Poster	North Only
LHDD01	Hwy 26	Dubois	WY	8.0	24.0	Yes	1016	Poster	East/West
LHGR01	Hwy 18	Grand Ronde	OR	10.0	30.0	Yes	5182	Bulletin	East/West
LHGR02	Hwy 99	Grants Pass	OR	10.0	25.0	Yes	5838	Poster	North Only
LHKE01	Hwy 395	Kettle Falls	WA	6.0	10.0	No	7400	Junior Poster	North/South
LHKL01	Hwy 97	Klamath Falls	OR	10.0	24.0	Yes	4461	Digital	North/South
LHMA01	Hwy 97	Madras	OR	8.0	24.0	Yes	12070	Digital	North/South
LHMP01	Hwy 97	Modoc Point	OR	8.0	24.0	Yes	5500	Poster	North/South
LHPA03	Hwy 95	Payette	ID	12.0	24.0	No	3777	Bulletin	North/South
LHRI01	Hwy 95	Riggins	ID	8.0	24.0	Yes	1524	Digital	North/South
LHRI02	Hwy 20	Rigby	ID	14.0	48.0	Yes	13406	Bulletin	North/South
LHRO01	Hwy 101	Rockaway	OR	5.0	8.0	No	1702	Junior Poster	North/South
LHSA01	Hwy 114	Salem	OR	8.0	24.0	Yes	10224	Poster	North/South
LHSA03	Liberty Road	Salem	OR	9.0	27.0	Yes	11808	Poster	North/South
LHSA05	Hwy 99	Salem	OR	8.0	24.0	Yes	10224	Poster	North/South
LHTW01	Hwy 93	Twin Falls	ID	6.0	12.0	No	1564	Junior Poster	North/South
LHWE01	Hwy 95	Weiser	ID	10.5	24.0	No	1748	Poster	North/South
LHWE04	I-90 Exit 60	Wallace	ID	14.0	48.0	Yes	6888	Bulletin	East/West
LIBO01	I-84 Exit 57	Boise	ID	21.0	47.0	Yes	14104	Bulletin	East/West
LIRO01	I-5 Exit 120	Roseburg	OR	12.0	48.0	Yes	27618	Bulletin	North/South
LNPO01	I-84 Exit 1	Portland	OR	14.0	48.0	Yes	110667	Bulletin	East/West
LSCO05	Hwy 42	Coquille	OR	8.0	25.0	Yes	5642	Digital	East/West
LSEU01	6th Ave.	Eugene	OR	10.0	24.0	Yes	19942	Poster	East Only
LSEU05	Maxwell St.	Eugene	OR	12.0	24.0	Yes	5707	Poster	East/West
LSEU07	W. 11th St.	Eugene	OR	10.0	24.0	Yes	21910	Digital	East/West
LSGO01	Hwy 142	Goldendale	WA	8.0	16.0	No	1748	Digital	East/West
LSKL01	S. 6th St.	Klamath Falls	OR	8.0	24.0	Yes	11414	Poster	East/West
LSSP01	Hwy 126	Springfield	OR	7.0	14.0	Yes	10037	Junior Poster	East/West

State	StateName
CA	California
NV	Nevada

RentalID	BillboardID	Facing	Cost	Months	BeginDate	AdvertiserID
1700200	LHBU01	North	775.0	3.0	6/5/17	BCC76
1700201	LNPO01	East	1600.0	4.0	5/2/17	CRR15
1700202	LNPO01	West	1400.0	8.0	3/14/17	SLA77
1700204	LHAR04	North	2500.0	1.0	9/4/17	KDS21
1700205	LHSA03	North	800.0	2.0	7/1/17	MBT14
1700206	LHSA03	South	800.0	2.0	7/1/17	MBT14
1700208	LHGR02	North	790.0	2.0	9/12/17	GAD74
1700209	LHTW01	North	350.0	5.0	4/11/17	IOA23
1700210	LHDA03	South	300.0	6.0	6/5/17	CDS29
1700211	LHCO05	North	875.0	4.0	5/12/17	KGS04
1700212	LHWE01	South	900.0	1.0	9/12/17	BMS53
1700213	LSGO01	West	2875.0	10.0	2/1/17	LCA42
1700215	LHMA01	South	3000.0	3.0	6/5/17	GRI03
1700216	LHCA03	West	2750.0	4.0	6/24/17	CWW01
1700217	LHCA01	North	325.0	7.0	3/21/17	WSC01
1700219	LHPA03	South	1625.0	3.0	8/13/17	LFS02
1700220	LSEU01	East	775.0	2.0	7/1/17	HAR01
1700221	LSEU05	West	775.0	2.0	7/1/17	HAR01
1700222	LHDD01	East	825.0	1.0	9/14/17	KGS04
1700223	LHKE01	South	365.0	3.0	8/25/17	HCH10
1700224	LHAR04	South	3500.0	10.0	2/1/17	LCA42
1700225	LHKE01	North	375.0	3.0	6/12/17	RER23
1700226	LHCH05	North	700.0	4.0	5/25/17	IOA23
1700227	LIBO01	West	1525.0	6.0	7/1/17	BMS53

State	StateName
ID	Idaho
OR	Oregon
WA	Washington
WY	Wyoming

SELECT DISTINCTROW * FROM Rentals;

SELECT AdRep.AdRepNumber, AdRep.FirstName, AdRep.LastName FROM AdRep;

UCC final/SC_AC16_4a_me_1.accdb

IfSuccessful_Status	GA_Status_Icon	SAM_Logo
true
false

ID	FirstName	LastName	AssignmentGUID	UserID
false	Wilson	Aguilar-Pulla	{91DBA1E6-8D4A-4255-B4D2-5ABC37DF22FD}	{91DBA1E6-8D4A-4255-B4D2-5ABC37DF22FD}

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
AM01	Miller	Abraham	54 Quarry Ave.	Portland	OR	97210	503-555-6032	¤ 32,540.00	6/1/16
GZ01	Zdalnza	Gregory	7 Moose Head Rd.	Boise	ID	83720	603-555-8765	¤ 29,675.00	9/14/16
KS02	Stenhope	Kathryn	9 Shennadoah Ave.	Salem	OR	97311	503-555-0003	¤ 34,576.00	10/5/15
MR01	Riley	Max	24 Springtown Rd.	Casper	WY	83114	781-555-2323	¤ 30,450.00	8/6/16
RH01	Horton	Rowan	12 Heather Rd.	Portland	OR	97211	207-555-9009	¤ 35,275.00	7/13/15
SL01	Lorens	Stephanie	15 River Rd.	Spokane	WA	99202	802-555-3339	¤ 37,350.00	4/5/15
UG01	Grea-Mayers	Ursula	342 Pine St.	Seattle	WA	98101	207-555-8534	¤ 41,765.00	12/12/14

AdvertiserID	AdvertiserName	Address	City	State	PostalCode	Amount Paid	Current Due	AdRepNumber
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	¤ 7,562.40	¤ 6,152.25	GZ01
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	Lewis 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 St.	Salem	OR	97301	¤ 1,090.75	¤ 1,345.60	KS02
PJG34	Patricia Jean Greenhouses	345 Magee Ave.	Seattle	WA	98101	¤ 1,345.00	¤ 2,450.00	UG01
RER23	Rivers Edge Restaurant	125 River Rd.	Kettle Falls	WA	99141	¤ 1,200.00	¤ 1,375.00	GZ01
SLA77	Smarter Law Associates	764 Main St.	Portland	OR	97209	¤ 7,500.00	¤ 0.00	AM01
TVS17	Theon Veterinary Services	346 Austin Ave.	Casper	WY	83114	¤ 2,225.60	¤ 2,500.00	MR01
WEC05	Western Energy Company	12 Polk St.	Kettle Falls	WA	99141	¤ 3,450.80	¤ 0.00	GZ01
WSC01	Wood Sports Complex	578 Central Ave.	Cave Junction	OR	97523	¤ 2,345.50	¤ 3,590.80	KS02

BillboardID	Location	City	State	Height	Width	SqrFt	Illuminated	DEC	Type
LHAR04	Hwy 395	Arden	WA	8.0	24.0	192.0	Yes	4395	Digital
LHBE01	Hwy 97	Beaver Marsh	OR	8.0	24.0	192.0	Yes	3542	Digital
LHBU01	Hwy 395	Burns	OR	10.5	48.0	504.0	Yes	1509	Poster
LHCA01	Hwy 199	Cave Junction	OR	6.0	16.0	96.0	No	2024	Junior Poster
LHCA03	Hwy 20-26	Casper	WY	10.0	32.0	320.0	No	1288	Bulletin
LHCH03	Hwy 97	Chemult	OR	10.0	24.0	240.0	Yes	3411	Digital
LHCH05	Hwy 93	Challis	ID	8.0	24.0	192.0	No	506	Poster
LHCO05	Hwy 89	Cokeville	WY	10.0	20.0	200.0	No	506	Poster
LHDA03	Hwy 197	Dallesport	WA	8.0	20.0	160.0	No	3700	Junior Poster
LHDC07	Hwy 89	Alpine	WY	6.0	8.0	48.0	No	1964	Junior Poster
LHDD01	Hwy 26	Dubois	WY	8.0	24.0	192.0	Yes	1016	Poster
LHGR01	Hwy 18	Grand Ronde	OR	10.0	30.0	300.0	Yes	5182	Bulletin
LHGR02	Hwy 99	Grants Pass	OR	10.0	25.0	250.0	Yes	5838	Poster
LHKE01	Hwy 395	Kettle Falls	WA	6.0	10.0	60.0	No	7400	Junior Poster
LHKL01	Hwy 97	Klamath Falls	OR	10.0	24.0	240.0	Yes	4461	Digital
LHMA01	Hwy 97	Madras	OR	8.0	24.0	192.0	Yes	12070	Digital
LHMP01	Hwy 97	Modoc Point	OR	8.0	24.0	192.0	Yes	5500	Poster
LHPA03	Hwy 95	Payette	ID	12.0	24.0	288.0	No	3777	Bulletin
LHRI01	Hwy 95	Riggins	ID	8.0	24.0	192.0	Yes	1724	Digital
LHRI02	Hwy 20	Rigby	ID	14.0	48.0	672.0	Yes	13406	Bulletin
LHRO01	Hwy 101	Rockaway	OR	5.0	8.0	40.0	No	1702	Junior Poster
LHSA01	Hwy 114	Salem	OR	8.0	24.0	192.0	Yes	10224	Poster
LHSA03	Liberty Road	Salem	OR	9.0	27.0	243.0	Yes	11808	Poster
LHSA05	Hwy 99	Salem	OR	8.0	24.0	192.0	Yes	10224	Poster
LHTW01	Hwy 93	Twin Falls	ID	6.0	12.0	72.0	No	1564	Junior Poster
LHWE01	Hwy 95	Weiser	ID	10.5	24.0	252.0	No	1748	Poster
LHWE04	I-90 Exit 60	Wallace	ID	14.0	48.0	672.0	Yes	6888	Bulletin
LIBO01	I-84 Exit 57	Boise	ID	21.0	47.0	987.0	Yes	14104	Bulletin
LIRO01	I-5 Exit 120	Roseburg	OR	12.0	48.0	576.0	Yes	27618	Bulletin
LNPO01	I-84 Exit 1	Portland	OR	14.0	48.0	672.0	Yes	110667	Bulletin
LSCO05	Hwy 42	Coquille	OR	8.0	25.0	200.0	Yes	5642	Digital
LSEU01	6th Ave.	Eugene	OR	10.0	24.0	240.0	Yes	19942	Poster
LSEU05	Maxwell St.	Eugene	OR	12.0	24.0	288.0	Yes	5707	Poster
LSEU07	W. 11th St.	Eugene	OR	10.0	24.0	240.0	Yes	21910	Digital
LSGO01	Hwy 142	Goldendale	WA	8.0	16.0	128.0	No	1748	Digital
LSKL01	S. 6th St.	Klamath Falls	OR	8.0	24.0	192.0	Yes	11414	Poster
LSSP01	Hwy 126	Springfield	OR	7.0	14.0	98.0	Yes	10037	Junior Poster

State	StateName
CA	California
NV	Nevada

RentalID	BillboardID	Facing	Cost	Months	BeginDate	Discount	AdvertiserID
1700200	LHBU01	North	¤ 775.00	3.0	6/5/17	No	BCC76
1700201	LNPO01	East	¤ 1,600.00	4.0	5/2/17	No	CRR15
1700202	LNPO01	West	¤ 1,400.00	8.0	3/14/17	No	SLA77
1700204	LHAR04	North	¤ 2,500.00	1.0	9/4/17	No	KDS21
1700205	LHSA03	North	¤ 800.00	2.0	7/1/17	No	MBT14
1700206	LHSA03	South	¤ 800.00	2.0	7/1/17	No	MBT14
1700208	LHGR02	North	¤ 790.00	2.0	9/12/17	No	GAD74
1700209	LHTW01	North	¤ 350.00	5.0	4/11/17	No	IOA23
1700210	LHDA03	South	¤ 300.00	6.0	6/5/17	No	CDS29
1700211	LHCO05	North	¤ 875.00	4.0	5/12/17	No	KGS04
1700212	LHWE01	South	¤ 900.00	1.0	9/12/17	No	BMS53
1700213	LSGO01	West	¤ 2,875.00	10.0	2/1/17	No	LCA42
1700215	LHMA01	South	¤ 3,000.00	3.0	6/5/17	No	GRI03
1700216	LHCA03	West	¤ 2,750.00	4.0	6/24/17	No	CWW01
1700217	LHCA01	North	¤ 325.00	7.0	3/21/17	No	WSC01
1700219	LHPA03	South	¤ 1,625.00	3.0	8/13/17	No	LFS02
1700220	LSEU01	East	¤ 775.00	2.0	7/1/17	No	HAR01
1700222	LHDD01	East	¤ 825.00	1.0	9/14/17	No	KGS04
1700223	LHKE01	South	¤ 365.00	3.0	8/25/17	No	HCH10
1700224	LHAR04	South	¤ 3,500.00	10.0	2/1/17	No	LCA42
1700225	LHKE01	North	¤ 375.00	3.0	6/12/17	No	RER23
1700226	LHCH05	North	¤ 700.00	4.0	5/25/17	Yes	IOA23
1700227	LIBO01	West	¤ 1,525.00	4.0	7/1/17	No	BMS53
1700229	LHPA03	North	¤ 1,625.00	3.0	8/14/17	No	LFS02

State	StateName
CA	California
ID	Idaho
NV	Nevada
OR	Oregon
WA	Washington
WY	Wyoming

SELECT DISTINCTROW * FROM AdRep;

SELECT DISTINCTROW * FROM Advertiser;

SELECT DISTINCTROW * FROM Rentals;

SELECT DISTINCTROW * FROM States;

SELECT DISTINCTROW * FROM AdRep;

SELECT [AdRep].[AdRepNumber], [Advertiser].[AdvertiserID], [Advertiser].[AdvertiserName], [Advertiser].[Current Due] FROM AdRep INNER JOIN Advertiser ON [AdRep].[AdRepNumber] =[Advertiser].[AdRepNumber];

SELECT DISTINCTROW * FROM Advertiser;

SELECT DISTINCTROW * FROM Billboard;

UCC final/SC_AC16_5a_me_1.accdb

IfSuccessful_Status	GA_Status_Icon	SAM_Logo
true
false

ID	FirstName	LastName	AssignmentGUID	UserID
false	Wilson	Aguilar-Pulla	{88C86842-9E48-4B2A-AD25-ECC9AB012339}	{88C86842-9E48-4B2A-AD25-ECC9AB012339}

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	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	Amount Paid	Current Due	AdRepNumber
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	¤ 7,562.40	¤ 6,152.25	GZ01
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	Lewis 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 St.	Salem	OR	97301	¤ 1,090.75	¤ 1,345.60	KS02
PJG34	Patricia Jean Greenhouses	345 Magee Ave.	Seattle	WA	98101	¤ 1,345.00	¤ 2,450.00	UG01
RER23	Rivers Edge Restaurant	125 River Rd.	Kettle Falls	WA	99141	¤ 1,200.00	¤ 1,375.00	GZ01
SLA77	Smarter Law Associates	764 Main St.	Portland	OR	97209	¤ 7,500.00	¤ 0.00	AM01
TVS17	Theon Veterinary Services	346 Austin Ave.	Casper	WY	83114	¤ 2,225.60	¤ 2,500.00	MR01
WEC05	Western Energy Company	12 Polk St.	Kettle Falls	WA	99141	¤ 3,450.80	¤ 0.00	GZ01
WSC01	Wood Sports Complex	578 Central Ave.	Cave Junction	OR	97523	¤ 2,345.50	¤ 3,590.80	KS02

BillboardID	Location	City	State	Height	Width	SqrFt	Illuminated	DEC	Type
LHAR04	Hwy 395	Arden	WA	8.0	24.0	192.0	Yes	4395	Digital
LHBE01	Hwy 97	Beaver Marsh	OR	8.0	24.0	192.0	Yes	3542	Digital
LHBU01	Hwy 395	Burns	OR	10.5	48.0	504.0	Yes	1509	Poster
LHCA01	Hwy 199	Cave Junction	OR	6.0	16.0	96.0	No	2024	Junior Poster
LHCA03	Hwy 20-26	Casper	WY	10.0	32.0	320.0	No	1288	Bulletin
LHCH03	Hwy 97	Chemult	OR	10.0	24.0	240.0	Yes	3411	Digital
LHCH05	Hwy 93	Challis	ID	8.0	24.0	192.0	No	506	Poster
LHCO05	Hwy 89	Cokeville	WY	10.0	20.0	200.0	No	506	Poster
LHDA03	Hwy 197	Dallesport	WA	8.0	20.0	160.0	No	3700	Junior Poster
LHDC07	Hwy 89	Alpine	WY	6.0	8.0	48.0	No	1964	Junior Poster
LHDD01	Hwy 26	Dubois	WY	8.0	24.0	192.0	Yes	1016	Poster
LHGR01	Hwy 18	Grand Ronde	OR	10.0	30.0	300.0	Yes	5182	Bulletin
LHGR02	Hwy 99	Grants Pass	OR	10.0	25.0	250.0	Yes	5838	Poster
LHKE01	Hwy 395	Kettle Falls	WA	6.0	10.0	60.0	No	7400	Junior Poster
LHKL01	Hwy 97	Klamath Falls	OR	10.0	24.0	240.0	Yes	4461	Digital
LHMA01	Hwy 97	Madras	OR	8.0	24.0	192.0	Yes	12070	Digital
LHMP01	Hwy 97	Modoc Point	OR	8.0	24.0	192.0	Yes	5500	Poster
LHPA03	Hwy 95	Payette	ID	12.0	24.0	288.0	No	3777	Bulletin
LHRI01	Hwy 95	Riggins	ID	8.0	24.0	192.0	Yes	1724	Digital
LHRI02	Hwy 20	Rigby	ID	14.0	48.0	672.0	Yes	13406	Bulletin
LHRO01	Hwy 101	Rockaway	OR	5.0	8.0	40.0	No	1702	Junior Poster
LHSA01	Hwy 114	Salem	OR	8.0	24.0	192.0	Yes	10224	Poster
LHSA03	Liberty Road	Salem	OR	9.0	27.0	243.0	Yes	11808	Poster
LHSA05	Hwy 99	Salem	OR	8.0	24.0	192.0	Yes	10224	Poster
LHTW01	Hwy 93	Twin Falls	ID	6.0	12.0	72.0	No	1564	Junior Poster
LHWE01	Hwy 95	Weiser	ID	10.5	24.0	252.0	No	1748	Poster
LHWE04	I-90 Exit 60	Wallace	ID	14.0	48.0	672.0	Yes	6888	Bulletin
LIBO01	I-84 Exit 57	Boise	ID	21.0	47.0	987.0	Yes	14104	Bulletin
LIRO01	I-5 Exit 120	Roseburg	OR	12.0	48.0	576.0	Yes	27618	Bulletin
LNPO01	I-84 Exit 1	Portland	OR	14.0	48.0	672.0	Yes	110667	Bulletin
LSCO05	Hwy 42	Coquille	OR	8.0	25.0	200.0	Yes	5642	Digital
LSEU01	6th Ave.	Eugene	OR	10.0	24.0	240.0	Yes	19942	Poster
LSEU05	Maxwell St.	Eugene	OR	12.0	24.0	288.0	Yes	5707	Poster
LSEU07	W. 11th St.	Eugene	OR	10.0	24.0	240.0	Yes	21910	Digital
LSGO01	Hwy 142	Goldendale	WA	8.0	16.0	128.0	No	1748	Digital
LSKL01	S. 6th St.	Klamath Falls	OR	8.0	24.0	192.0	Yes	11414	Poster
LSSP01	Hwy 126	Springfield	OR	7.0	14.0	98.0	Yes	10037	Junior Poster

RentalID	BillboardID	Facing	Cost	Months	BeginDate	Discount	AdvertiserID
1700200	LHBU01	North	¤ 775.00	3.0	6/5/17	false	BCC76
1700201	LNPO01	East	¤ 1,600.00	4.0	5/2/17	false	CRR15
1700202	LNPO01	West	¤ 1,400.00	8.0	3/14/17	true	SLA77
1700204	LHAR04	North	¤ 2,500.00	1.0	9/4/17	false	KDS21
1700205	LHSA03	North	¤ 800.00	2.0	7/1/17	false	MBT14
1700206	LHSA03	South	¤ 800.00	2.0	7/1/17	false	MBT14
1700208	LHGR02	North	¤ 790.00	2.0	9/12/17	false	GAD74
1700209	LHTW01	North	¤ 350.00	5.0	4/11/17	false	IOA23
1700210	LHDA03	South	¤ 300.00	6.0	6/5/17	true	CDS29
1700211	LHCO05	North	¤ 875.00	4.0	5/12/17	false	KGS04
1700212	LHWE01	South	¤ 900.00	1.0	9/12/17	false	BMS53
1700213	LSGO01	West	¤ 2,875.00	10.0	2/1/17	false	LCA42
1700215	LHMA01	South	¤ 3,000.00	3.0	6/5/17	false	GRI03
1700216	LHCA03	West	¤ 2,750.00	4.0	6/24/17	true	CWW01
1700217	LHCA01	North	¤ 325.00	7.0	3/21/17	false	WSC01
1700219	LHPA03	South	¤ 1,625.00	3.0	8/13/17	false	LFS02
1700220	LSEU01	East	¤ 775.00	2.0	7/1/17	false	HAR01
1700222	LHDD01	East	¤ 825.00	1.0	9/14/17	false	KGS04
1700223	LHKE01	South	¤ 365.00	3.0	8/25/17	false	HCH10
1700224	LHAR04	South	¤ 3,500.00	10.0	2/1/17	false	LCA42
1700225	LHKE01	North	¤ 375.00	3.0	6/12/17	false	RER23
1700226	LHCH05	North	¤ 700.00	4.0	5/25/17	true	IOA23
1700227	LIBO01	West	¤ 1,525.00	4.0	7/1/17	false	BMS53
1700229	LHPA03	North	¤ 1,625.00	3.0	8/14/17	false	LFS02

State	StateName
ID	Idaho
OR	Oregon
WA	Washington
WY	Wyoming

PARAMETERS __State Value; SELECT DISTINCTROW * FROM Billboard AS [State-Billboard Master Form] WHERE ([__State] = State);

SELECT DISTINCTROW * FROM AdRep;

SELECT DISTINCTROW * FROM Billboard;

SELECT DISTINCTROW * FROM Rentals;

SELECT DISTINCTROW * FROM States;

SELECT DISTINCTROW * FROM Advertiser;

SELECT [Advertiser].[AdvertiserID], [Advertiser].[AdvertiserName], [Rentals].[RentalID], [Rentals].[BillboardID] FROM Advertiser INNER JOIN Rentals ON [Advertiser].[AdvertiserID] =[Rentals].[AdvertiserID];

SELECT DISTINCTROW * FROM Billboard;

SELECT AdRep.AdRepNumber, AdRep.LastName, AdRep.FirstName, AdRep.DateHired, AdRep.[Special Skills] FROM AdRep;

SELECT Rentals.RentalID, Rentals.BillboardID, Rentals.Cost, Rentals.Months, Rentals.BeginDate, Rentals.AdvertiserID, Advertiser.AdvertiserName, Advertiser.Phone FROM Advertiser INNER JOIN Rentals ON Advertiser.AdvertiserID = Rentals.AdvertiserID;

UCC final/SC_AC16_6a_me_1.accdb

IfSuccessful_Status	GA_Status_Icon	SAM_Logo
true
false

ID	FirstName	LastName	AssignmentGUID	UserID
false	Wilson	Aguilar-Pulla	{7F91C578-CDE3-4D8A-AAA0-3D317893496E}	{7F91C578-CDE3-4D8A-AAA0-3D317893496E}

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	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	Lewis 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 St.	Salem	OR	97301		¤ 1,090.75	¤ 1,345.60	KS02
PJG34	Patricia Jean Greenhouses	345 Magee Ave.	Seattle	WA	98101		¤ 1,345.00	¤ 2,450.00	UG01
RER23	Rivers Edge Restaurant	125 River Rd.	Kettle Falls	WA	99141		¤ 1,200.00	¤ 1,375.00	GZ01
SLA77	Smarter Law Associates	764 Main St.	Portland	OR	97209		¤ 7,500.00	¤ 0.00	AM01
TVS17	Theon Veterinary Services	346 Austin Ave.	Casper	WY	83114		¤ 2,225.60	¤ 2,500.00	MR01
WEC05	Western Energy Company	12 Polk St.	Kettle Falls	WA	99141		¤ 3,450.80	¤ 0.00	GZ01
WSC01	Wood Sports Complex	578 Central Ave.	Cave Junction	OR	97523		¤ 2,345.50	¤ 3,590.80	KS02

BillboardID	Location	City	State	Height	Width	SqrFt	Illuminated	DEC	Type
LHAR04	Hwy 395	Arden	WA	8.0	24.0	192.0	Yes	4395	Digital
LHBE01	Hwy 97	Beaver Marsh	OR	8.0	24.0	192.0	Yes	3542	Digital
LHBU01	Hwy 395	Burns	OR	10.5	48.0	504.0	Yes	1509	Poster
LHCA01	Hwy 199	Cave Junction	OR	6.0	16.0	96.0	No	2024	Junior Poster
LHCA03	Hwy 20-26	Casper	WY	10.0	32.0	320.0	No	1288	Bulletin
LHCH03	Hwy 97	Chemult	OR	10.0	24.0	240.0	Yes	3411	Digital
LHCH05	Hwy 93	Challis	ID	8.0	24.0	192.0	No	506	Poster
LHCO05	Hwy 89	Cokeville	WY	10.0	20.0	200.0	No	506	Poster
LHDA03	Hwy 197	Dallesport	WA	8.0	20.0	160.0	No	3700	Junior Poster
LHDC07	Hwy 89	Alpine	WY	6.0	8.0	48.0	No	1964	Junior Poster
LHDD01	Hwy 26	Dubois	WY	8.0	24.0	192.0	Yes	1016	Poster
LHGR01	Hwy 18	Grand Ronde	OR	10.0	30.0	300.0	Yes	5182	Bulletin
LHGR02	Hwy 99	Grants Pass	OR	10.0	25.0	250.0	Yes	5838	Poster
LHKE01	Hwy 395	Kettle Falls	WA	6.0	10.0	60.0	No	7400	Junior Poster
LHKL01	Hwy 97	Klamath Falls	OR	10.0	24.0	240.0	Yes	4461	Digital
LHMA01	Hwy 97	Madras	OR	8.0	24.0	192.0	Yes	12070	Digital
LHMP01	Hwy 97	Modoc Point	OR	8.0	24.0	192.0	Yes	5500	Poster
LHPA03	Hwy 95	Payette	ID	12.0	24.0	288.0	No	3777	Bulletin
LHRI01	Hwy 95	Riggins	ID	8.0	24.0	192.0	Yes	1724	Digital
LHRI02	Hwy 20	Rigby	ID	14.0	48.0	672.0	Yes	13406	Bulletin
LHRO01	Hwy 101	Rockaway	OR	5.0	8.0	40.0	No	1702	Junior Poster
LHSA01	Hwy 114	Salem	OR	8.0	24.0	192.0	Yes	10224	Poster
LHSA03	Liberty Road	Salem	OR	9.0	27.0	243.0	Yes	11808	Poster
LHSA05	Hwy 99	Salem	OR	8.0	24.0	192.0	Yes	10224	Poster
LHTW01	Hwy 93	Twin Falls	ID	6.0	12.0	72.0	No	1564	Junior Poster
LHWE01	Hwy 95	Weiser	ID	10.5	24.0	252.0	No	1748	Poster
LHWE04	I-90 Exit 60	Wallace	ID	14.0	48.0	672.0	Yes	6888	Bulletin
LIBO01	I-84 Exit 57	Boise	ID	21.0	47.0	987.0	Yes	14104	Bulletin
LIRO01	I-5 Exit 120	Roseburg	OR	12.0	48.0	576.0	Yes	27618	Bulletin
LNPO01	I-84 Exit 1	Portland	OR	14.0	48.0	672.0	Yes	110667	Bulletin
LSCO05	Hwy 42	Coquille	OR	8.0	25.0	200.0	Yes	5642	Digital
LSEU01	6th Ave.	Eugene	OR	10.0	24.0	240.0	Yes	19942	Poster
LSEU05	Maxwell St.	Eugene	OR	12.0	24.0	288.0	Yes	5707	Poster
LSEU07	W. 11th St.	Eugene	OR	10.0	24.0	240.0	Yes	21910	Digital
LSGO01	Hwy 142	Goldendale	WA	8.0	16.0	128.0	No	1748	Digital
LSKL01	S. 6th St.	Klamath Falls	OR	8.0	24.0	192.0	Yes	11414	Poster
LSSP01	Hwy 126	Springfield	OR	7.0	14.0	98.0	Yes	10037	Junior Poster

RentalID	BillboardID	Facing	Cost	Months	BeginDate	Discount	AdvertiserID
1700200	LHBU01	North	¤ 775.00	3.0	6/5/17	false	BCC76
1700201	LNPO01	East	¤ 1,600.00	4.0	5/2/17	false	CRR15
1700202	LNPO01	West	¤ 1,400.00	8.0	3/14/17	true	SLA77
1700204	LHAR04	North	¤ 2,500.00	1.0	9/4/17	false	KDS21
1700205	LHSA03	North	¤ 800.00	2.0	7/1/17	false	MBT14
1700206	LHSA03	South	¤ 800.00	2.0	7/1/17	false	MBT14
1700208	LHGR02	North	¤ 790.00	2.0	9/12/17	false	GAD74
1700209	LHTW01	North	¤ 350.00	5.0	4/11/17	false	IOA23
1700210	LHDA03	South	¤ 300.00	6.0	6/5/17	true	CDS29
1700211	LHCO05	North	¤ 875.00	4.0	5/12/17	false	KGS04
1700212	LHWE01	South	¤ 900.00	1.0	9/12/17	false	BMS53
1700213	LSGO01	West	¤ 2,875.00	10.0	2/1/17	false	LCA42
1700215	LHMA01	South	¤ 3,000.00	3.0	6/5/17	false	GRI03
1700216	LHCA03	West	¤ 2,750.00	4.0	6/24/17	true	CWW01
1700217	LHCA01	North	¤ 325.00	7.0	3/21/17	false	WSC01
1700219	LHPA03	South	¤ 1,625.00	3.0	8/13/17	false	LFS02
1700220	LSEU01	East	¤ 775.00	2.0	7/1/17	false	HAR01
1700222	LHDD01	East	¤ 825.00	1.0	9/14/17	false	KGS04
1700223	LHKE01	South	¤ 365.00	3.0	8/25/17	false	HCH10
1700224	LHAR04	South	¤ 3,500.00	10.0	2/1/17	false	LCA42
1700225	LHKE01	North	¤ 375.00	3.0	6/12/17	false	RER23
1700226	LHCH05	North	¤ 700.00	4.0	5/25/17	true	IOA23
1700227	LIBO01	West	¤ 1,525.00	4.0	7/1/17	false	BMS53
1700229	LHPA03	North	¤ 1,625.00	3.0	8/14/17	false	LFS02
1700230	LHGR02	North	¤ 790.00	1.0	8/1/17	false	GPM12

State	StateName
ID	Idaho
OR	Oregon
WA	Washington
WY	Wyoming

SELECT DISTINCTROW * FROM AdRep;

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 Rentals.RentalID, Rentals.BillboardID, Rentals.Cost, Rentals.Months, [Cost]*[Months] AS TotalCost, Rentals.BeginDate, Advertiser.AdvertiserName, Rentals.AdvertiserID FROM Advertiser INNER JOIN Rentals ON Advertiser.AdvertiserID = Rentals.AdvertiserID;

UCC final/SC_AC16_7a_me_1.accdb

IfSuccessful_Status	GA_Status_Icon	SAM_Logo
true
false

ID	FirstName	LastName	AssignmentGUID	UserID
false	Wilson	Aguilar-Pulla	{4B095C0C-D278-4E35-967B-CCFB0CA139FD}	{4B095C0C-D278-4E35-967B-CCFB0CA139FD}

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	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	Lewis 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 St.	Salem	OR	97301		¤ 1,090.75	¤ 1,345.60	KS02
PJG34	Patricia Jean Greenhouses	345 Magee Ave.	Seattle	WA	98101		¤ 1,345.00	¤ 2,450.00	UG01
RER23	Rivers Edge Restaurant	125 River Rd.	Kettle Falls	WA	99141		¤ 1,200.00	¤ 1,375.00	GZ01
SLA77	Smarter Law Associates	764 Main St.	Portland	OR	97209		¤ 7,500.00	¤ 0.00	AM01
TVS17	Theon Veterinary Services	346 Austin Ave.	Casper	WY	83114		¤ 2,225.60	¤ 2,500.00	MR01
WEC05	Western Energy Company	12 Polk St.	Kettle Falls	WA	99141		¤ 3,450.80	¤ 0.00	GZ01
WSC01	Wood Sports Complex	578 Central Ave.	Cave Junction	OR	97523		¤ 2,345.50	¤ 3,590.80	KS02

BillboardID	Location	City	State	Height	Width	SqrFt	Illuminated	DEC	Type
LHAR04	Hwy 395	Arden	WA	8.0	24.0	192.0	Yes	4395	Digital
LHBE01	Hwy 97	Beaver Marsh	OR	8.0	24.0	192.0	Yes	3542	Digital
LHBU01	Hwy 395	Burns	OR	10.5	48.0	504.0	Yes	1509	Poster
LHCA01	Hwy 199	Cave Junction	OR	6.0	16.0	96.0	No	2024	Junior Poster
LHCA03	Hwy 20-26	Casper	WY	10.0	32.0	320.0	No	1288	Bulletin
LHCH03	Hwy 97	Chemult	OR	10.0	24.0	240.0	Yes	3411	Digital
LHCH05	Hwy 93	Challis	ID	8.0	24.0	192.0	No	506	Poster
LHCO05	Hwy 89	Cokeville	WY	10.0	20.0	200.0	No	506	Poster
LHDA03	Hwy 197	Dallesport	WA	8.0	20.0	160.0	No	3700	Junior Poster
LHDC07	Hwy 89	Alpine	WY	6.0	8.0	48.0	No	1964	Junior Poster
LHDD01	Hwy 26	Dubois	WY	8.0	24.0	192.0	Yes	1016	Poster
LHGR01	Hwy 18	Grand Ronde	OR	10.0	30.0	300.0	Yes	5182	Bulletin
LHGR02	Hwy 99	Grants Pass	OR	10.0	25.0	250.0	Yes	5838	Poster
LHKE01	Hwy 395	Kettle Falls	WA	6.0	10.0	60.0	No	7400	Junior Poster
LHKL01	Hwy 97	Klamath Falls	OR	10.0	24.0	240.0	Yes	4461	Digital
LHMA01	Hwy 97	Madras	OR	8.0	24.0	192.0	Yes	12070	Digital
LHMP01	Hwy 97	Modoc Point	OR	8.0	24.0	192.0	Yes	5500	Poster
LHPA03	Hwy 95	Payette	ID	12.0	24.0	288.0	No	3777	Bulletin
LHRI01	Hwy 95	Riggins	ID	8.0	24.0	192.0	Yes	1724	Digital
LHRI02	Hwy 20	Rigby	ID	14.0	48.0	672.0	Yes	13406	Bulletin
LHRO01	Hwy 101	Rockaway	OR	5.0	8.0	40.0	No	1702	Junior Poster
LHSA01	Hwy 114	Salem	OR	8.0	24.0	192.0	Yes	10224	Poster
LHSA03	Liberty Road	Salem	OR	9.0	27.0	243.0	Yes	11808	Poster
LHSA05	Hwy 99	Salem	OR	8.0	24.0	192.0	Yes	10224	Poster
LHTW01	Hwy 93	Twin Falls	ID	6.0	12.0	72.0	No	1564	Junior Poster
LHWE01	Hwy 95	Weiser	ID	10.5	24.0	252.0	No	1748	Poster
LHWE04	I-90 Exit 60	Wallace	ID	14.0	48.0	672.0	Yes	6888	Bulletin
LIBO01	I-84 Exit 57	Boise	ID	21.0	47.0	987.0	Yes	14104	Bulletin
LIRO01	I-5 Exit 120	Roseburg	OR	12.0	48.0	576.0	Yes	27618	Bulletin
LNPO01	I-84 Exit 1	Portland	OR	14.0	48.0	672.0	Yes	110667	Bulletin
LSCO05	Hwy 42	Coquille	OR	8.0	25.0	200.0	Yes	5642	Digital
LSEU01	6th Ave.	Eugene	OR	10.0	24.0	240.0	Yes	19942	Poster
LSEU05	Maxwell St.	Eugene	OR	12.0	24.0	288.0	Yes	5707	Poster
LSEU07	W. 11th St.	Eugene	OR	10.0	24.0	240.0	Yes	21910	Digital
LSGO01	Hwy 142	Goldendale	WA	8.0	16.0	128.0	No	1748	Digital
LSKL01	S. 6th St.	Klamath Falls	OR	8.0	24.0	192.0	Yes	11414	Poster
LSSP01	Hwy 126	Springfield	OR	7.0	14.0	98.0	Yes	10037	Junior Poster

RentalID	BillboardID	Facing	Cost	Months	BeginDate	Discount	AdvertiserID
1700200	LHBU01	North	¤ 775.00	3.0	6/5/17	false	BCC76
1700201	LNPO01	East	¤ 1,600.00	4.0	5/2/17	false	CRR15
1700202	LNPO01	West	¤ 1,400.00	8.0	3/14/17	true	SLA77
1700204	LHAR04	North	¤ 2,500.00	1.0	9/4/17	false	KDS21
1700205	LHSA03	North	¤ 800.00	2.0	7/1/17	false	MBT14
1700206	LHSA03	South	¤ 800.00	2.0	7/1/17	false	MBT14
1700208	LHGR02	North	¤ 790.00	2.0	9/12/17	false	GAD74
1700209	LHTW01	North	¤ 350.00	5.0	4/11/17	false	IOA23
1700210	LHDA03	South	¤ 300.00	6.0	6/5/17	true	CDS29
1700211	LHCO05	North	¤ 875.00	4.0	5/12/17	false	KGS04
1700212	LHWE01	South	¤ 900.00	1.0	9/12/17	false	BMS53
1700213	LSGO01	West	¤ 2,875.00	10.0	2/1/17	false	LCA42
1700215	LHMA01	South	¤ 3,000.00	3.0	6/5/17	false	GRI03
1700216	LHCA03	West	¤ 2,750.00	4.0	6/24/17	true	CWW01
1700217	LHCA01	North	¤ 325.00	7.0	3/21/17	false	WSC01
1700219	LHPA03	South	¤ 1,625.00	3.0	8/13/17	false	LFS02
1700220	LSEU01	East	¤ 775.00	2.0	7/1/17	false	HAR01
1700222	LHDD01	East	¤ 825.00	1.0	9/14/17	false	KGS04
1700223	LHKE01	South	¤ 365.00	3.0	8/25/17	false	HCH10
1700224	LHAR04	South	¤ 3,500.00	10.0	2/1/17	false	LCA42
1700225	LHKE01	North	¤ 375.00	3.0	6/12/17	false	RER23
1700226	LHCH05	North	¤ 700.00	4.0	5/25/17	true	IOA23
1700227	LIBO01	West	¤ 1,525.00	4.0	7/1/17	false	BMS53
1700229	LHPA03	North	¤ 1,625.00	3.0	8/14/17	false	LFS02
1700230	LHGR02	North	¤ 790.00	1.0	2/1/17	false	GPM12

State	StateName
CA	California
ID	Idaho
NV	Nevada
OR	Oregon
WA	Washington
WY	Wyoming

SELECT [AdRep].[AdRepNumber], [AdRep].[LastName] FROM AdRep;

SELECT [Advertiser].[AdvertiserID], [Advertiser].[AdvertiserName] FROM Advertiser;

SELECT DISTINCTROW * FROM AdRep;

SELECT [AdReps and Rentals].[AdRepNumber], [AdReps and Rentals].[AdvertiserID], [AdReps and Rentals].[RentalID], [AdReps and Rentals].[Total Cost] FROM [AdReps and Rentals];

SELECT DISTINCTROW * FROM Advertiser;

SELECT DISTINCTROW * FROM Billboard;

SELECT DISTINCTROW * FROM States;

SELECT [Rentals].[AdvertiserID], [Rentals].[RentalID], [Rentals].[BillboardID], [Rentals].[Cost], [Rentals].[Months], [Rentals].[BeginDate] FROM Rentals;

SELECT DISTINCTROW * FROM Rentals;

SELECT DISTINCTROW * FROM States;

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 Billboard.BillboardID, Billboard.Location, Billboard.City, States.StateName, Billboard.Illuminated, Billboard.DEC, Billboard.Type FROM States INNER JOIN Billboard ON States.State = Billboard.State;

UCC final/SC_AC16_8b_me_1.accdb

IfSuccessful_Status	GA_Status_Icon	SAM_Logo
true
false

ID	FirstName	LastName	AssignmentGUID	UserID
false	Wilson	Aguilar-Pulla	{F19409EF-06AA-41F4-93E6-E549926E2A2C}	{F19409EF-06AA-41F4-93E6-E549926E2A2C}

ID	FirstName	LastName	ProjectName	SubmissionNum	MaxScore	Score	EngineVersion

ID	StepNumber	Description	IfSuccessful	StepScore	StepMaxScore	ErrorText	ActionName	StepActionOrder

ClientNumber	ClientName	Address	City	State	PostalCode	Phone	AmountPaid	Balance	TotalAmount	ClientType	ManagerID	Client Notes
CFH01	Chadds Ford HOA	29 Prospect St.	Chadds Ford	PA	19317		¤ 24,752.00	¤ 3,875.25	¤ 28,627.25	Residential	KS02
CHG05	Cherry Hill Group	9 Harrington Ave.	Cherry Hill	NJ	08002	8565552883	¤ 29,837.00	¤ 2,765.30	¤ 32,602.30	Corporate	SL01	Manages condo complexes
CSS01	Claymont Storage Facilities	18 Barton St.	Claymont	DE	19703		¤ 30,841.00	¤ 2,575.50	¤ 33,416.50	Commercial	DH01
DSS02	Delaware Service Stations	103 Hubbard Way	Newark	DE	19702		¤ 27,152.00	¤ 2,875.00	¤ 30,027.00	Commercial	KS02
ECC06	Elkton Community College	134 Lincoln Rd.	Elkton	MD	21921		¤ 19,620.00	¤ 1,875.00	¤ 21,495.00	Corporate	KS01
FBC04	First Bank of Coatesville	290 Brook Mill Rd.	Coatesville	PA	19320		¤ 25,702.00	¤ 3,016.75	¤ 28,718.75	Commercial	AM01
FRR02	Fitzmeyer Rental Properties	35 Washington St.	Elkton	MD	21922		¤ 19,740.00	¤ 2,095.00	¤ 21,835.00	Residential	UG01
GRC01	Gwynedd Retirement Community	302 Great Rd.	Gwynedd	PA	19436		¤ 34,036.00	¤ 580.00	¤ 34,616.00	Residential	MR01
HRG01	Halko Restaurant Group	1294 Monument Ave.	Media	PA	19063		¤ 10,952.00	¤ 0.00	¤ 10,952.00	Commercial	RH01
KRC02	Kennett Retirement Community	7 Lakeview Rd.	Kennett Square	PA	19348		¤ 44,026.00	¤ 3,590.80	¤ 47,616.80	Residential	GZ01
MCC02	Mike's Convenience Stores	17 Briarwood Ln.	Camden	NJ	08101		¤ 0.00	¤ 1,280.75	¤ 1,280.75	Commercial	SL01
MHS01	Mary's Hoagies and Steaks	346 Magee Ave.	Philadelphia	PA	19111		¤ 21,769.00	¤ 2,890.60	¤ 24,659.60	Commercial	RH01
MIG01	MidAtlantic Insurance Group	1702 Hunter Ave.	Pennsauken	NJ	08109		¤ 13,097.00	¤ 2,450.00	¤ 15,547.00	Corporate	BR01
NCA03	Norristown Condominium Association	29 Newtown Circle	Norristown	PA	19401		¤ 22,697.00	¤ 3,480.45	¤ 26,177.45	Residential	ER01
OTR03	Old Town Rentals	682 Hickory Way	Philadelphia	PA	19147		¤ 15,345.00	¤ 2,875.50	¤ 18,220.50	Residential	RH01
RDS03	Roehr Department Stores	913 Main St.	Wilmington	DE	19810		¤ 34,546.00	¤ 0.00	¤ 34,546.00	Commercial	KS01
SCC03	Springfield College System	41 Carver Hill Rd.	Springfield	PA	19064		¤ 2,500.25	¤ 0.00	¤ 2,500.25	Corporate	MR01
SRG01	Starvos Restaurant Group	3 Pleasant St.	Malvern	PA	19355		¤ 42,561.00	¤ 12,040.00	¤ 54,601.00	Commercial	GZ01
VMA01	Villa Maria Apartments	200 Queen Rd.	Malvern	PA	19355		¤ 0.00	¤ 0.00	¤ 0.00	Residential	GZ01
WBT01	Wilmington Bank & Trust	52 Putnam Rd.	Wilmington	DE	19806		¤ 35,640.00	¤ 9,875.40	¤ 45,515.40	Corporate	KS02
WCM01	West Chester Mall	456 Selwyn Ave.	West Chester	PA	19380		¤ 46,540.00	¤ 13,450.00	¤ 59,990.00	Commercial	RH01

TypeID	Description	MinRate
COM	Commercial	¤ 1,000.00
CRP	Corporate	¤ 2,500.00
GVT	Government	¤ 500.00
HOA	Home Owners Assn	¤ 500.00
RES	Residential	¤ 800.00

ManagerID	FirstName	LastName	Address	City	State	PostalCode	PhoneNumber	StartDate	Salary	CPRCert	Skills
AM01	Alex	Middleton	54 Quarry Ave.	Coatesville	PA	19320	610-555-6032	8/1/16	¤ 32,450.90	No	Spanish, carpentry
BR01	Belinda	Rowan	140 Oak Stump Rd.	Pennsauken	NJ	08109	856-555-2134	1/11/16	¤ 36,575.75	No	landscaping, AA degree
DH01	Devyn	Harlier	51 Abby Rd.	Claymont	DE	19703	302-555-7767	8/3/15	¤ 40,320.00	No	licensed electrician
ER01	Eric	Rogers	103 Main St.	Norristown	PA	19401	610-555-8295	1/11/16	¤ 38,767.20	No	general contracting
GZ01	Gregory	Zolda	7 Moose Head Rd.	West Chester	PA	19380	610-555-8765	12/7/15	¤ 42,540.00	No	construction management
KS01	Kevin	Stasner	143 Oak Stump Rd.	Elkton	MD	21921	410-555-1230	4/6/15	¤ 46,575.25	No	carpentry, painting
KS02	Kaitlyn	Stenhope	9 Shennadoah Ave.	Wilmington	DE	19810	302-555-0003	4/18/16	¤ 37,564.20	No	electrician, landscaping
MR01	Max	Riley	24 Spring Rd	Springfield	PA	19064	610-555-2323	2/2/15	¤ 39,875.00	No	landscaping
RH01	Rowan	Horton	12 Heather Rd.	Media	PA	19063	610-555-9009	9/21/15	¤ 41,320.00	No	construction management
SL01	Stephanie	Lorenz	15 River Rd.	Cherry Hill	NJ	08002	856-555-3339	6/6/16	¤ 33,456.75	No	road work
UG01	Ursula	Grayson	342 Pine St.	Fair Hill	MD	21922	410-555-8534	3/7/16	¤ 38,765.30	No	general contracting

PropertyID	Address	City	State	ClientNumber
PCA001	123 Oakley St.	Camden	NJ	MIG01
PCA005	14 Congress St	Camden	NJ	MCC02
PCA006	100 Morgan Ave.	Camden	NJ	MCC02
PCF001	29 Prospect St.	Chadds Ford	PA	CFH01
PCH001	Cherry Hill Mall	Cherry Hill	NJ	CHG05
PCL001	234 Fourth St.	Claymont	DE	CSS01
PCO001	290 Brook Mill Rd.	Coatesville	PA	FBC04
PDA001	230 First St.	Darlington	MD	FRR02
PDW001	123 Major Rd.	Downington	PA	FBC04
PEC001	134 Lincoln Rd.	Elkton	MD	ECC06
PEL001	100 Maryland Ave.	Elkton	MD	FRR02
PKS001	Route 1	Kennett Square	PA	KRC02
PMA001	102 Main St.	Malvern	PA	SRG01
PME001	23 Chester Blvd.	Media	PA	HRG01
PME005	789 Valley St.	Media	PA	MHS01
PNC001	100 Main St.	New Castle	DE	RDS03
PNE001	100 Second St.	Newark	DE	DSS02
PNR001	29 Newton Circle	Norristown	PA	NCA03
PPE001	1702 Hunter Ave.	Pennsauken	NJ	MIG01
PPH001	346 Bingham St.	Philadelphia	PA	MHS01
PPH002	200 Robbins Ave.	Philadelphia	PA	MHS01
PPH003	1234 Rising Sun Ave.	Philadelphia	PA	MHS01
PPH006	100 Elfreth's Alley	Philadelphia	PA	OTR03
PPM001	234 Washington St.	Plymouth Meeting	PA	SRG01
PSP001	Springfield Mall	Springfield	PA	HRG01
PWC001	225 Olympia Rd.	West Chester	PA	RDS03
PWC003	West Chester Mall	West Chester	PA	WCM01
PWI001	102 Townsend St.	Wilmington	DE	DSS02
PWI002	Route 202	Wilmington	DE	DSS02
PWI003	Route 52	Wilmington	DE	DSS02
PWI005	52 Putnam Rd.	Wilmington	DE	WBT01

State	StateName
DE	Delaware
MD	Maryland
NJ	New Jersey
NY	New York
PA	Pennsylvania
VA	Virginia

SELECT [Manager].[ManagerID], [Manager].[FirstName], [Manager].[LastName] FROM Manager ORDER BY [ManagerID];

PARAMETERS __ManagerID Value; SELECT DISTINCTROW * FROM Client AS [Manager Client Data Form] WHERE ([__ManagerID] = ManagerID);

SELECT DISTINCTROW * FROM Client;

SELECT DISTINCTROW * FROM Manager;

SELECT DISTINCTROW * FROM Property;

SELECT DISTINCTROW * FROM State;

SELECT DISTINCTROW * FROM Property;

SELECT DISTINCTROW * FROM Client;

SELECT Property.PropertyID, Property.Address, Property.City, Property.State, Client.ClientName, Client.Phone FROM Client INNER JOIN Property ON Client.ClientNumber = Property.ClientNumber;

SELECT Manager.ManagerID, Manager.FirstName, Manager.LastName, Manager.StartDate, Manager.Skills FROM Manager WHERE (((Manager.StartDate)<#1/1/2016#) AND ((Manager.Skills) Like "*carpentry*"));

SELECT Manager.ManagerID, Manager.FirstName, Manager.LastName, Client.ClientNumber, Client.ClientName, Client.AmountPaid, Client.Balance FROM Manager INNER JOIN Client ON Manager.ManagerID = Client.ManagerID;

SELECT Manager.ManagerID, Manager.FirstName, Manager.LastName, Client.ClientNumber, Client.ClientName, Client.AmountPaid, Client.Balance, Property.PropertyID, Property.Address, Property.City, Property.State FROM (Manager INNER JOIN Client ON Manager.ManagerID = Client.ManagerID) INNER JOIN Property ON Client.ClientNumber = Property.ClientNumber;

SELECT Property.PropertyID, Property.Address, Property.City, State.StateName, Property.Services, Property.ClientNumber, Property.State FROM State INNER JOIN Property ON State.State = Property.State;

UCC final/SC_AC16_9a_me_1.accdb

IfSuccessful_Status	GA_Status_Icon	SAM_Logo
true
false

ID	FirstName	LastName	AssignmentGUID	UserID
false	Wilson	Aguilar-Pulla	{8B59872E-0509-4BDB-9A45-CABF2945DE71}	{8B59872E-0509-4BDB-9A45-CABF2945DE71}

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	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 St.	Salem	OR	97301		¤ 1,090.75	¤ 1,345.60	KS02
PJG34	Patricia Jean Greenhouses	345 Magee Ave.	Seattle	WA	98101		¤ 1,345.00	¤ 2,450.00	UG01
RER23	Rivers Edge Restaurant	125 River Rd.	Kettle Falls	WA	99141		¤ 1,200.00	¤ 1,375.00	GZ01
SLA77	Smarter Law Associates	764 Main St.	Portland	OR	97209		¤ 7,500.00	¤ 0.00	AM01
TVS17	Theon Veterinary Services	346 Austin Ave.	Casper	WY	83114		¤ 2,225.60	¤ 2,500.00	MR01
WEC05	Western Energy Company	12 Polk St.	Kettle Falls	WA	99141		¤ 3,450.80	¤ 0.00	GZ01
WSC01	Wood Sports Complex	578 Central Ave.	Cave Junction	OR	97523		¤ 2,345.50	¤ 3,590.80	KS02

BillboardID	Location	City	State	Height	Width	SqrFt	Illuminated	DEC	Type
LHAR04	Hwy 395	Arden	WA	8.0	24.0	192.0	Yes	4395	Digital
LHBE01	Hwy 97	Beaver Marsh	OR	8.0	24.0	192.0	Yes	3542	Digital
LHBU01	Hwy 395	Burns	OR	10.5	48.0	504.0	Yes	1509	Poster
LHCA01	Hwy 199	Cave Junction	OR	6.0	16.0	96.0	No	2024	Junior Poster
LHCA03	Hwy 20-26	Casper	WY	10.0	32.0	320.0	No	1288	Bulletin
LHCH03	Hwy 97	Chemult	OR	10.0	24.0	240.0	Yes	3411	Digital
LHCH05	Hwy 93	Challis	ID	8.0	24.0	192.0	No	506	Poster
LHCO05	Hwy 89	Cokeville	WY	10.0	20.0	200.0	No	506	Poster
LHDA03	Hwy 197	Dallesport	WA	8.0	20.0	160.0	No	3700	Junior Poster
LHDC07	Hwy 89	Alpine	WY	6.0	8.0	48.0	No	1964	Junior Poster
LHDD01	Hwy 26	Dubois	WY	8.0	24.0	192.0	Yes	1016	Poster
LHGR01	Hwy 18	Grand Ronde	OR	10.0	30.0	300.0	Yes	5182	Bulletin
LHGR02	Hwy 99	Grants Pass	OR	10.0	25.0	250.0	Yes	5838	Poster
LHKE01	Hwy 395	Kettle Falls	WA	6.0	10.0	60.0	No	7400	Junior Poster
LHKL01	Hwy 97	Klamath Falls	OR	10.0	24.0	240.0	Yes	4461	Digital
LHMA01	Hwy 97	Madras	OR	8.0	24.0	192.0	Yes	12070	Digital
LHMP01	Hwy 97	Modoc Point	OR	8.0	24.0	192.0	Yes	5500	Poster
LHPA03	Hwy 95	Payette	ID	12.0	24.0	288.0	No	3777	Bulletin
LHRI01	Hwy 95	Riggins	ID	8.0	24.0	192.0	Yes	1724	Digital
LHRI02	Hwy 20	Rigby	ID	14.0	48.0	672.0	Yes	13406	Bulletin
LHRO01	Hwy 101	Rockaway	OR	5.0	8.0	40.0	No	1702	Junior Poster
LHSA01	Hwy 114	Salem	OR	8.0	24.0	192.0	Yes	10224	Poster
LHSA03	Liberty Road	Salem	OR	9.0	27.0	243.0	Yes	11808	Poster
LHSA05	Hwy 99	Salem	OR	8.0	24.0	192.0	Yes	10224	Poster
LHTW01	Hwy 93	Twin Falls	ID	6.0	12.0	72.0	No	1564	Junior Poster
LHWE01	Hwy 95	Weiser	ID	10.5	24.0	252.0	No	1748	Poster
LHWE04	I-90 Exit 60	Wallace	ID	14.0	48.0	672.0	Yes	6888	Bulletin
LIBO01	I-84 Exit 57	Boise	ID	21.0	47.0	987.0	Yes	14104	Bulletin
LIRO01	I-5 Exit 120	Roseburg	OR	12.0	48.0	576.0	Yes	27618	Bulletin
LNPO01	I-84 Exit 1	Portland	OR	14.0	48.0	672.0	Yes	110667	Bulletin
LSCO05	Hwy 42	Coquille	OR	8.0	25.0	200.0	Yes	5642	Digital
LSEU01	6th Ave.	Eugene	OR	10.0	24.0	240.0	Yes	19942	Poster
LSEU05	Maxwell St.	Eugene	OR	12.0	24.0	288.0	Yes	5707	Poster
LSEU07	W. 11th St.	Eugene	OR	10.0	24.0	240.0	Yes	21910	Digital
LSGO01	Hwy 142	Goldendale	WA	8.0	16.0	128.0	No	1748	Digital
LSKL01	S. 6th St.	Klamath Falls	OR	8.0	24.0	192.0	Yes	11414	Poster
LSSP01	Hwy 126	Springfield	OR	7.0	14.0	98.0	Yes	10037	Junior Poster

RentalID	BillboardID	Facing	Cost	Deposit	Months	BeginDate	Discount	AdvertiserID
1700200	LHBU01	North	¤ 775.00	¤ 0.00	3.0	6/5/17	false	BCC76
1700201	LNPO01	East	¤ 1,600.00	¤ 0.00	4.0	5/2/17	false	CRR15
1700202	LNPO01	West	¤ 1,400.00	¤ 0.00	8.0	3/14/17	true	SLA77
1700204	LHAR04	North	¤ 2,500.00	¤ 0.00	1.0	9/4/17	false	KDS21
1700205	LHSA03	North	¤ 800.00	¤ 0.00	2.0	7/1/17	false	MBT14
1700206	LHSA03	South	¤ 800.00	¤ 0.00	2.0	7/1/17	false	MBT14
1700208	LHGR02	North	¤ 790.00	¤ 0.00	2.0	9/12/17	false	GAD74
1700209	LHTW01	North	¤ 350.00	¤ 0.00	5.0	4/11/17	false	IOA23
1700210	LHDA03	South	¤ 300.00	¤ 0.00	6.0	6/5/17	true	CDS29
1700211	LHCO05	North	¤ 875.00	¤ 0.00	4.0	5/12/17	false	KGS04
1700212	LHWE01	South	¤ 900.00	¤ 0.00	1.0	9/12/17	false	BMS53
1700213	LSGO01	West	¤ 2,875.00	¤ 0.00	10.0	2/1/17	false	LCA42
1700215	LHMA01	South	¤ 3,000.00	¤ 0.00	3.0	6/5/17	false	GRI03
1700216	LHCA03	West	¤ 2,750.00	¤ 0.00	4.0	6/24/17	true	CWW01
1700217	LHCA01	North	¤ 325.00	¤ 0.00	7.0	3/21/17	false	WSC01
1700219	LHPA03	South	¤ 1,625.00	¤ 0.00	3.0	8/13/17	false	LFS02
1700220	LSEU01	East	¤ 775.00	¤ 0.00	2.0	7/1/17	false	HAR01
1700222	LHDD01	East	¤ 825.00	¤ 0.00	1.0	9/14/17	false	KGS04
1700223	LHKE01	South	¤ 365.00	¤ 0.00	3.0	8/25/17	false	HCH10
1700224	LHAR04	South	¤ 3,500.00	¤ 0.00	10.0	2/1/17	false	LCA42
1700225	LHKE01	North	¤ 375.00	¤ 0.00	3.0	6/12/17	false	RER23
1700226	LHCH05	North	¤ 700.00	¤ 0.00	4.0	5/25/17	true	IOA23
1700227	LIBO01	West	¤ 1,525.00	¤ 0.00	4.0	7/1/17	false	BMS53
1700229	LHPA03	North	¤ 1,625.00	¤ 0.00	3.0	8/14/17	false	LFS02
1700230	LHGR02	North	¤ 790.00	¤ 0.00	1.0	2/1/17	false	GPM12

State	StateName
CA	California
ID	Idaho
NV	Nevada
OR	Oregon
WA	Washington
WY	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 DISTINCTROW * FROM AdRep;

SELECT [Billboard].[BillboardID], [Billboard].[City] FROM Billboard;

SELECT DISTINCTROW * FROM AdRep;

SELECT DISTINCTROW * FROM Advertiser;

SELECT DISTINCTROW * FROM Billboard;

SELECT DISTINCTROW * FROM States;

SELECT DISTINCTROW * FROM Rentals;

SELECT DISTINCTROW * FROM States;

SELECT DISTINCTROW * FROM Advertiser;

SELECT DISTINCTROW * FROM Billboard;

SELECT AdRep.AdRepNumber, AdRep.FirstName, AdRep.LastName, AdRep.Address, AdRep.City, AdRep.State, AdRep.PostalCode, AdRep.PhoneNumber, AdRep.Salary, AdRep.DateHired FROM AdRep;

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*"));

UCC final/SC_AC16_CS8-10a_me_1.accdb

IfSuccessful_Status	GA_Status_Icon	SAM_Logo
true
false

ID	FirstName	LastName	AssignmentGUID	UserID
false	Wilson	Aguilar-Pulla	{79416A78-5E34-47AA-989B-4D51AB527916}	{79416A78-5E34-47AA-989B-4D51AB527916}

ID	FirstName	LastName	ProjectName	SubmissionNum	MaxScore	Score	EngineVersion

ID	StepNumber	Description	IfSuccessful	StepScore	StepMaxScore	ErrorText	ActionName	StepActionOrder

ActivityID	Description	Audience	SkillLevel	Fee
A01	Massage	Adult	None	¤ 45.00
A02	Yoga	Adult	Beginning	¤ 6.00
A03	Yoga	Adult	Intermediate	¤ 6.00
A04	Tennis	Both	Beginning	¤ 0.00
A05	Sailing	Both	Beginning	¤ 0.00
A06	Waterskiing	Both	Beginning	¤ 0.00
A07	Waterskiing	Both	Intermediate	¤ 0.00
A08	Fly Fishing	Adult	None	¤ 15.00
A09	Swimming	Child	Beginning	¤ 0.00
A10	Archery	Child	Beginning	¤ 0.00

AdventureID	AdventureName	AdultPrice	ChildPrice
BB01	Bike & Beach	¤ 28.00	¤ 20.00
HR01	Horseback Ride	¤ 30.00	¤ 25.00
WJ01	Windjammer Cruise	¤ 45.00	¤ 35.00
WW01	Whale Watching	¤ 32.00	¤ 26.00

CamperID	LastName	FirstName	Gender	Age	ParentID
10101	Northfold	Leon	M	32	101
10102	Northfold	Jane	F	31	101
10103	Morton	Liz	F	11	101
10104	Northfold	Sam	M	8	101
10201	Ocean	Arnold	M	31	102
10202	Bonds	Bob	M	31	102
10203	Bonds-Ocean	James	M	9	102
10301	Kasuma	Raj	M	40	103
10302	Kasuma	Elena	F	38	103
10303	Kasuma	Eric	M	13	103
10304	Kasuma	Kim	F	11	103
10305	Kasuma	Grant	M	9	103
10401	Goff	Ryan	M	35	104
10402	Heinz	Christen	F	35	104
10403	Goff	James	M	8	104
10501	McLean	Kyle	M	42	105
10502	McLean	Jenny	F	40	105
10503	McLean	Katie	F	11	105
10601	Morontoia	Joseph	M	27	106
10602	Morontoia	Joey	M	10	106
10701	Marchand	Spencer	M	47	107
10702	Marchand	Luke	M	44	107
10703	Marchand	Mark	M	14	107
10704	Marchand	Daniel	M	14	107
10705	Marchand	Andrew	M	12	107
10801	Rulf	Uschi	F	34	108
10802	Lee	Kim	F	33	108
10803	Rulf	Irena	F	9	108
10804	Lee	Katherine	F	8	108
10901	Caron	Jean Luc	M	28	109
10902	Caron	Joan	F	12	109
10903	Caron	John	M	12	109
11001	Bers	Michael	M	36	110
11002	Smythe	Mary	F	37	110
11003	Bers	Peter	M	9	110
11004	Bers	Margie	F	11	110
11201	Jones	Laura	F	39	112
11202	Jones	Katie	F	13	112
11203	Jones	Kristen	F	10	112
11501	Vaccari	Adam	M	41	115
11502	Vaccari	Susan	F	39	115
11503	Vaccari	Pat	F	9	115
11504	Vaccari	Jim	M	7	115
11601	Murakami	Iris	F	33	116
11602	Lawson	Melissa	F	12	116
11901	Chau	Clement	M	43	119
11902	Chau	Annelise	F	43	119
11903	Chau	Laura	F	13	119
11904	Chau	Timothy	M	11	119
12001	Gernowski	Sadie	F	41	120
12002	Gernowski	Teresa	F	7	120
12003	Gernowski	Barbara	F	7	120
12101	Bretton-Borak	Siam	F	35	121
12102	Allen	Mary	F	37	121
12103	Bretton-Borak	Ellie	F	11	121
12104	Allen	Rosemary	F	9	121
12201	Hefferson	Orlagh	M	29	122
12202	Hefferson	Erik	M	7	122
12301	Barnett	Larry	M	47	123
12302	Barnett	Lucy	F	45	123
12303	Barnett	Caitlyn	F	14	123
12304	Barnett	Stephen	M	12	123
12305	Barnett	Mark	M	8	123
12401	Busa	James	M	36	124
12402	Busa	Peter	M	38	124
12403	Busa	Andrew	M	11	124
12404	Busa	Jacob	M	10	124
12501	Peterson	Becca	F	32	125
12502	Peterson	Kirsten	F	8	125
12601	Brown	Peter	M	44	126
12602	Brown	Mary	F	43	126
12603	Brown	Tom	M	13	126
12604	Brown	Bill	M	11	126

CounselorID	LastName	FirstName	Street	City	State	PostalCode	CellPhone	HireDate	Specialty	CPR Certification
AM01	Abrams	Miles	54 Quest Ave.	Williamsburg	MA	01096	617-555-6032	6/3/14	Arts & Crafts	false
BR01	Boyers	Rita	140 Oakton Rd.	Jaffrey	NH	03452	603-555-2134	3/4/14	Water Sports	false
DH01	Devon	Harley	25 Old Ranch Rd.	Sunderland	MA	01375	512-555-1912	1/8/14	Land Sports	false
GZ01	Gregory	Zach	7 Moose Head Rd.	Dummer	NH	03588	215-555-9898	11/4/14	Arts & Crafts	false
KS01	Kiley	Susan	943 Oakton Rd.	Jaffrey	NH	03452	603-555-1230	4/8/15	Arts & Crafts	false
KS02	Kelly	Sam	9 Congaree Ave.	Fraconia	NH	03580	312-555-2232	6/10/15	Water Sports	false
MR01	Marston	Ray	24 Shenandoah Rd.	Springfield	MA	01101	864-555-2323	3/14/17	Land Sports	false
RH01	Rowan	Hal	12 Heather Rd.	Mount Desert	ME	04660	207-555-9009	6/2/16	Nature Studies	false
SL01	Stevens	Lori	15 Riverton Rd.	Coventry	VT	05825	802-555-3339	9/5/16	Water Sports	false
UG01	Unser	Glory	342 Pineview St.	Danbury	CT	06810	203-555-8534	2/2/17	Land Sports	false

CounselorID	SessionID
AM01	1
AM01	4
BR01	2
BR01	3
DH01	1
DH01	2
DH01	4
GZ01	2
GZ01	3
KS01	3
KS01	4
KS02	1
KS02	2
MR01	1
MR01	2
MR01	3
MR01	4
RH01	1
RH01	2
SL01	1
SL01	3
SL01	4
UG01	1
UG01	2
UG01	3
UG01	4

ParentID	LastName	FirstName	Address	City	State	PostalCode	HomePhone	CellPhone	Waiver
101	Northfold	Leon	9 Old Mill Rd.	Londonderry	NH	03053	603-555-7563	603-555-7564	No
102	Ocean	Arnold	2332 South St. Apt 3	Springfield	MA	01101	413-555-3212	512-555-4555	No
103	Kasuma	Raj	132 Main St. #1	East Hartford	CT	06108	860-555-0703	860-555-8008	No
104	Goff	Ryan	164A South Bend Rd.	Lowell	MA	01854	781-555-8423	215-555-2332	No
105	McLean	Kyle	345 Lower Ave.	Wolcott	NY	14590	585-555-5321	585-555-5321	No
106	Morontoia	Joseph	156 Scholar St.	Johnston	RI	02919	401-555-4848	401-555-9988	No
107	Marchand	Spencer	76 Cross Rd.	Bath	NH	03740	603-555-0456	864-555-1223	No
108	Rulf	Uschi	32 Sheep Stop St.	Edinboro	PA	16412	814-555-5521	302-555-3443	No
109	Caron	Jean Luc	10 Greenfield St.	Rome	ME	04963	207-555-9643	207-555-7887	No
110	Bers	Michael	65 Granite St.	York	NY	14592	585-555-0111	585-555-1111	No
112	Jones	Laura	373 Highland Ave.	Somerville	MA	02143	857-555-6258	312-555-7856	No
115	Vaccari	Adam	1282 Ocean Walk	Ocean City	NJ	08226	609-555-5231	609-555-5231	No
116	Murakami	Iris	7 Cherry Blossom St.	Weymouth	MA	02188	617-555-6665	617-555-4554	No
119	Chau	Clement	18 Ark Ledge Ln.	Londonderry	VT	05148	802-555-3096	803-555-3967	No
120	Gernowski	Sadie	24 Stump Rd.	Athens	ME	04912	207-555-4507	207-555-4507	No
121	Bretton-Borak	Siam	10 Old Main St.	Cambridge	VT	05444	802-555-3443	802-555-7890	No
122	Hefferson	Orlagh	132 South St. Apt 27	Manchester	NH	03101	603-555-3476	603-555-3476	No
123	Barnett	Larry	25 Stag Rd.	Fairfield	CT	06824	860-555-9876	704-555-2543	No
124	Busa	James	12 Foster St.	South Windsor	CT	06074	857-555-5532	857-555-5532	No
125	Peterson	Becca	51 Fredrick St.	Albion	NY	14411	585-555-0900	585-555-0901	No
126	Brown	Peter	154 Central St.	Vernon	CT	06066	860-555-3234	864-555-2112	No
128	Sanz	Michael	344 Magee Ave	Philadelphia	PA	19111	610-555-2345	610-555-2345	Yes

ReservationID	AdultTotal	ChildTotal	Lodging	TotalFees	Deposit
1700001	¤ 265.00	¤ 235.00	¤ 0.00	¤ 500.00	¤ 50.00
1700004	¤ 490.00	¤ 215.00	¤ 0.00	¤ 705.00	¤ 125.00
1700008	¤ 490.00	¤ 430.00	¤ 0.00	¤ 920.00	¤ 200.00
1700017	¤ 490.00	¤ 645.00	¤ 0.00	¤ 1,135.00	¤ 250.00
1700018	¤ 490.00	¤ 430.00	¤ 0.00	¤ 920.00	¤ 175.00
1700021	¤ 245.00	¤ 430.00	¤ 90.00	¤ 765.00	¤ 100.00
1700024	¤ 245.00	¤ 215.00	¤ 0.00	¤ 460.00	¤ 75.00

ReservationID	SessionID	Adults	AdultPrice	Children	ChildPrice	LodgingFee	ParentID	AdultTotPrice	ChildTotPrice
1700001	1	1	¤ 245.00	1	¤ 215.00	¤ 0.00	101	¤ 245.00	¤ 215.00
1700002	3	2	¤ 255.00	2	¤ 225.00	¤ 0.00	101	¤ 510.00	¤ 450.00
1700003	2	2	¤ 265.00	3	¤ 235.00	¤ 175.00	103	¤ 530.00	¤ 705.00
1700004	1	2	¤ 245.00	1	¤ 215.00	¤ 0.00	104	¤ 490.00	¤ 215.00
1700005	4	2	¤ 275.00	1	¤ 245.00	¤ 0.00	105	¤ 550.00	¤ 245.00
1700006	3	1	¤ 265.00	1	¤ 235.00	¤ 35.00	106	¤ 265.00	¤ 235.00
1700007	2	2	¤ 250.00	3	¤ 225.00	¤ 175.00	107	¤ 500.00	¤ 675.00
1700008	1	2	¤ 245.00	2	¤ 215.00	¤ 0.00	108	¤ 490.00	¤ 430.00
1700009	4	1	¤ 295.00	2	¤ 235.00	¤ 0.00	109	¤ 295.00	¤ 470.00
1700010	2	2	¤ 265.00	1	¤ 235.00	¤ 105.00	102	¤ 530.00	¤ 235.00
1700011	3	2	¤ 250.00	1	¤ 220.00	¤ 105.00	102	¤ 500.00	¤ 220.00
1700012	3	2	¤ 265.00	2	¤ 235.00	¤ 140.00	115	¤ 530.00	¤ 470.00
1700013	4	1	¤ 265.00	1	¤ 235.00	¤ 0.00	116	¤ 265.00	¤ 235.00
1700014	2	2	¤ 275.00	2	¤ 255.00	¤ 140.00	110	¤ 550.00	¤ 510.00
1700015	3	1	¤ 265.00	2	¤ 235.00	¤ 0.00	120	¤ 265.00	¤ 470.00
1700016	3	2	¤ 240.00	2	¤ 205.00	¤ 175.00	121	¤ 480.00	¤ 410.00
1700017	1	2	¤ 245.00	3	¤ 215.00	¤ 0.00	123	¤ 490.00	¤ 645.00
1700018	1	2	¤ 245.00	2	¤ 215.00	¤ 0.00	126	¤ 490.00	¤ 430.00
1700019	2	2	¤ 275.00	2	¤ 255.00	¤ 0.00	124	¤ 550.00	¤ 510.00
1700020	3	1	¤ 265.00	1	¤ 235.00	¤ 0.00	125	¤ 265.00	¤ 235.00
1700021	1	1	¤ 245.00	2	¤ 215.00	¤ 90.00	112	¤ 245.00	¤ 430.00
1700022	2	2	¤ 275.00	2	¤ 255.00	¤ 0.00	119	¤ 550.00	¤ 510.00
1700024	1	1	¤ 245.00	1	¤ 215.00	¤ 0.00	122	¤ 245.00	¤ 215.00
1700025	4	1	¤ 255.00	1	¤ 225.00	¤ 0.00	125	¤ 255.00	¤ 225.00
1700029	2	2	¤ 255.00	2	¤ 225.00	¤ 175.00	121	¤ 510.00	¤ 450.00
1700030	4	2	¤ 245.00	1	¤ 215.00	¤ 0.00	104	¤ 490.00	¤ 215.00