Homework answers / question archive / Shelly CashmanAccess 2016 | Module 3: SAM Project 1a West Coast Outdoor Advertisers CREATING AND MODIFYING TABLES AND OTHER DATABASE OBJECTS GETTING STARTED ?Open the fleSC_AC16_3a_FirstLastName_1

Shelly CashmanAccess 2016 | Module 3: SAM Project 1a West Coast Outdoor Advertisers CREATING AND MODIFYING TABLES AND OTHER DATABASE OBJECTS GETTING STARTED ?Open the fleSC_AC16_3a_FirstLastName_1

MS Access

Share With

---

Shelly CashmanAccess 2016 | Module 3: SAM Project 1a

West Coast Outdoor Advertisers

CREATING AND MODIFYING TABLES AND OTHER DATABASE OBJECTS

GETTING STARTED

?Open the fleSC_AC16_3a_FirstLastName_1.accdb, available ±or download±rom the SAM website.

?Save the fle asSC_AC16_3a_FirstLastName_2.accdbby changing the “1” toa “2”.oHint: I± you do not see the.accdbfle extension in the Save As dialog box,do not type it. Access will add the fle extension ±or you automatically.

?Open the_GradingInfoTabletable and ensure that your frst and last name isdisplayed as the frst record in the table. I± the table does not contain yourname, delete the fle and download a new copy ±rom the SAM website.

PROJECT STEPS

1.Open the Relationships window and add theBillboardtable to it. Create a one-to-many relationship between theBillboardIDfeld in theBillboardtable and theBillboardIDfeld in theRentalstable. Make the relationship en±orce re±erentialintegrity and cascade update related felds. Do notmake the relationshipcascade delete related records. Save the relationship.

2.With the Relationships window still open, modi±y the relationship between theAdRepandAdvertisertables to cascade update related felds. Do notmake therelationship cascade delete related records. Save the relationships and close thewindow.

3.Open theBillboardtablein Design View. Use the Lookup Wizard to change theTypefeld to a Lookup feld. Type in the ±ollowing ±our values (in the ordershown) as the list o± possible values ±or the feld:Digital,Bulletin,Poster, andJunior Poster. Limit the feld values to only the items in the list, and do notallow multiple values ±or the feld.

4.With theBillboardtable still open in Design View, delete theFacingfeld.

5.With theBillboardtable still open in Design View, add a new feld namedSqrFta±ter theWidthfeld. This feld is a calculated feld. Use the expressionHeight *Widthto calculate the feld values. Save and close theBillboardtable.

6.Open theAdReptable in Design View and move thePhoneNumberfeld so thatit appears a±ter thePostalCodefeld. Save and close theAdReptable.

7.Open theRentalstable in Design View and make the ±ollowing updates:

a.Change theBillboardIDfeld to a required feld.

b.Change the Field Size property ±or theFacingfeld to6.

c.Change the data type of theCostFeld toCurrency, and change theDecimal Places property to0.

d.Change the default value of theMonthsFeld to1.Save the changes to theRentalstable. (Hint: Because there was a change to aFeld size, the “Some data may be lost” warning message will appear. Continuesaving the table. The data Fts within the valid ranges, so ignore this warningand continue saving the table.)

8.Switch to viewing theRentalstable in Datasheet View, and then add the Totalrow to the table. The Total row should average the values in theCostFeld. Sortthe records in ascending order by theAdvertiserIDFeld. Save the changes tothe table, and then close the datasheet.

9.Create an Update query to update the value of theDiscountFeld in theRentalstable for all records toNo. Run the query, and then save it asDiscount Query.(Hint: 24 records will be updated by this query.)

10.Open theAdReptable in Datasheet View, and then open the subdatasheet forAbraham Miller (who has the AdRepNumber Feld value of AM01). In thesubdatasheet, update the Germann Inn record (which has the AdvertiserID Feldvalue of GRI03) by changing theCurrent DueFeld value to2900.50. Close thesubdatasheet and then close theAdReptable.

11.Open theAdvertisertable in Datasheet View. ±ind the record for Goldendale City±oundation (which has the AdvertiserID Feld value of GC±56) and delete thisrecord. Close theAdvertisertable.

12.Open theBillboardtable in Datasheet View, and then apply a ±ilter by ±orm toFnd all records where theStateFeld is equal to ID and theTypeFeld is equal toDigital. Toggle the Flter and, for the record returned, change theDECFeld valueto1724. Toggle the Flter, and then save and close the table.

13.Create a Split ±orm based on theBillboardtable. Save the form asBillboardSplit Form, and then close it.

14.Create a Delete query for theRentalstable. Delete all rentals where theBillboardIDFeld value is LSEU05 and theFacingFeld value is West. Save thequery asDelete Rentals Query, run the query (Hint: Only one record will bedeleted.), and then close the query.

15.Create an Append query for theMore Statestable. Append theStateand theStateNameFeld values in theMore Statestable to theStatestable in thecurrent database. Save the query asAppend States Query, run the query(Hint: Two records will be appended.), and then close the query.

16.Open theInsurance Queryin Design View, and then modify it by adding theInsuranceFeld to the Design grid.Insuranceis a multivalued Feld, and eachoption should appear on a separate row. Run the query, save it, and then closethe query.

17.Open theRentals Update Formin ±orm View, and then navigate to the lastrecord (with theRentalIDFeld value 1700227). Change theMonthsFeld valueto4. Navigate to the previous record in the form (with theRentalIDFeld value1700226). Change theDiscountFeld value toYes.

18.Use theRentals Update Formto add a new record to theRentalstable with thevalues shown in ±igure 1 below.

Figure 1: Rentals Update Form

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