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

GETTING STARTED

1. Save the file SC_AC365_EOM2-1_FirstLastName_1.accdb as SC_AC365_EOM2-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_EOM2-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, and other media. As a projects coordinator, you are using the company database to answer questions about subscriptions, members, and services for new subscribers. Create a query for the Members table that selects the MemberID, FirstName, LastName, and State fields for all members living in Nevada (NV). Save the query using NVMembers as the query name.

2. Open the MemberFees query and add a calculated field that multiples the MonthlyFee field values by 12 to calculate the annual fees. Assign the alias AnnualFees to the calculated field. Save the query.

3. Create a parameter query for the Members table that allows users to enter a different city each time they run the query. Include all fields in the query results. Use Enter desired city as the input prompt. Save the query using MemberCities as the query name.

4. Create a query that joins the Members table and the Subscriptions table to list subscriptions by members. Include the SubDescription field from the Subscriptions table and the FirstName, LastName, and State fields from the Members table, in that order, in the query results. Sort the records in ascending order by SubDescription. Add a criterion to select records with LastName values that start with M followed by any other letters. Save the query using SubsByMembers as the query name.

5. Open the MemberList query and select all members who have a SubID of 4 and reside in the state of Washington (WA) to list members in Washington with video subscriptions. Save the query.

6. Open the MembersByMedia query and add the State field from the Members table so it appears as the last field in the query grid. Sort the query in ascending order by MediaName and then by State to make it easier to find member information. Save the query.

7. Open the ProducersByMedia query and move the MediaName field so it appears as the first field in the query grid. Select all producers who have a MediaID of M01 or M02 to list producers of TV shows and movies. Sort the records in ascending order by MediaName and then by LastName to organize the producer information. Save the query.

8. Create a query for the Members and Subscriptions tables that includes the State field from the Members table and the RegistrationFee and MonthlyFee fields from the Subscriptions table, in that order. Add a Total row to the query grid. Group the records by the State field, and then sum the records by the RegistrationFee and the MonthlyFee fields. Save the query using SubFees as the query name.

9. Create a report based on the MemberFees query to list members and the fees they pay. Include all the query fields in the report. Do not add any grouping levels. Sort the records in ascending order based on the LastName field. Use a Tabular layout and a Portrait orientation. Use FeeList as the name of the report.

10. Create a crosstab query based on the MembersByMedia query to list the number of members in each city by media type. Use the City field values as the row headings and use the MediaName field values as the column headings. Count the values in the MemberID field for each city and media type and include row sums. Use MediaCrosstab as the name of the query.

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.