Shelly Cashman Access 365 | Module 3: End of Module Project 1 TopStream, Inc.

GETTING STARTED

1. Save the file SC_AC365_EOM3-1_FirstLastName_1.accdb as SC_AC365_EOM3-1_FirstLastName_2.accdb

a. Edit the file name by changing “1” to “2”.

b. If you do not see the .accdb file extension, do not type it. The file extension will be added for you automatically.

2. With the file SC_AC365_EOM3-1_FirstLastName_2.accdb open, ensure that your first and last name is displayed as the first record in the _GradingInfoTable table.

a. If the table does not display your name, delete the file and download a new copy.

PROJECT STEPS

1. TopStream, Inc. is a new online streaming platform for TV shows, movies, podcasts, music, and videos. As the new projects coordinator, you are in charge of introducing TopStream's services in the Pacific Northwest. You are using an Access database to track and store company data for that region. Create a split form for the Producers table so you can easily add records while displaying the datasheet for the table. Save the form, using ProducersSplit as the name.

2. With the ProducersSplit form still open, navigate to the record for Artie Sellars (ProducerID 121) and delete the record since he is no longer working for the company. Change the FirstName field value for ProducerID 102 to Elena from Eliza to correct an error.

3. Open the Media table in Datasheet View and add a missing record using M05 as the MediaID and Videos as the MediaName.

4. Using an update query, update records in the Subscriptions table to change the RegistrationFee for SubID 5 to 17, the new registration fee for any two subscription services. Save the query using UpdateSubscriptions as the query name, and then run the query.

5. Open the Producers table in Design View and move the Specialty field so that it immediately follows the LastName field, which is a more logical location for the field. Change the Specialty field to a Lookup field to make it easier to enter correct values. Enter the following three values: Original content, Royalties, Licenses for the Lookup field. Limit the values to only those items on the list and do not allow multiple values for the field. Save the table but do not close it.

6. With the Producers table still open in Design View, change the Caption property to First Name for the FirstName field and change the Caption property to Last Name for the LastName field.

7. With the Producers table still open, switch to Datasheet View and add Licenses as the Specialty field value for Mariam Akbar (who has a ProducerID of 125). Sort the records in the table in ascending order by LastName to list the producers in alphabetic order. Save and close the table.

8. Open the Subscriptions table in Datasheet View and add a Total row to the table to display the results of two calculations. Use the Sum function to total the amounts in the RegistrationFee field. Use the Average function to average the amounts in the MonthlyFee field.

9. With the Subscriptions table still open, switch to Design View. Change the field size for the SubDescription field to 20 to reduce the required storage space. Add the description Member initial payment to the RegistrationFee field. Add a calculated field named AnnualFee to the end of the table. The field is the result of multiplying the MonthlyFee field value by 12. Save the changes, click Yes when notified that some data might be lost, and then close the table.

10. Because Seattle and Portland have the most TopStream members, it would be useful to store their data in a separate table. Create a Make Table query that selects all fields from the Members table in the same order that they are listed in the field list. Add criteria to select only those records with Seattle or Portland City field values. Assign the name CityMembers to the table, save the query using MakeCityMembers as the query name, and then run the query.

11. Open the TotalRegistrationFees query in Design View. Use the Expression Builder to create a calculated field the calculates the sum of the RegistrationFee and MonthlyFee field values. Assign the caption StartFees to the calculated field. Save and run the query.

12. Create a one-to-many relationship between the Media table and the Members table. MediaID is the matching field in each table. Make the relationship enforce referential integrity. Save the relationship.

13. Open the Media table in Datasheet View. Display the subdatasheet for MediaID M01, TV shows, and then change the Street field value for MemberID 21003 (Kyle Griffith) to 2911 Prospect St. from 29 Prospect St.

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 website to submit your completed project.