01a_ImportCasting.xlsx, 01a_Broadway_LastFirst_yyyy-mm-dd Capstone Exercises You work as, Database Management System

01a_ImportCasting.xlsx, Database Management System

01a_Broadway_LastFirst_yyyy-mm-dd
Capstone Exercises

You work as a production assistant in the office of the Green Man Theatre. One of your duties is to maintain the company database. You will create and edit tables, queries, forms, and reports as well as maintain and back up the database. A database has already been created, so you decide to explore it.
Navigate among the Objects in an Access Database

You will begin by opening the Broadway database and examining the tables and other objects. You will also make one change in the Actors table.

Open the database 01a_Broadway and save the database as 01a_Broadway_LastFirst.

Open each table and examine the data.

Close all the tables except the Actors table.

Sort the Actors table by Last Name in ascending order.

Find Stacie Henderson’s record.

Replace the last name Henderson with Tandbell.

Remove the Sort.

Save and close the Actors table.

Open the Actors from NY query.

Switch to Design view. Modify the State criterion so the query displays all the actors who are not from NY.

Run and save the query (use Save Object As) and rename as Actors Not from NY. Close the query.

Create a Casting Table

The database contains tables for theaters, shows, and actors. However, there is no efficient way to keep track of which actors performed in which shows. You will create a Casting table that will contain the show, the actor, and the weekly pay.

Create a table using Table Design view.

Add field names and data types as listed below.
Field Name Data Type
ShowID Number
ActorID Number
WeeklyPay Currency

Set ShowID and the ActorID together as the primary key.

Save the table as Casting and close the table.

Import Excel Data into the Casting Table

The theater has been storing casting data in an Excel spreadsheet. You will import this spreadsheet data directly into the Casting table.

Append the data in Sheet1 of the 01a_ImportCasting Excel file into the Casting table. If an error message displays, click Yes.

Open the Casting table and examine the data.

Verify the primary key is set correctly by changing the last record’s ActorID from 12 to 9. Click the record directly above and Access will display the message The changes you requested to the table were not successful because they would create duplicate values… Undo the change to ActorID.

Close the table.

Establish Table Relationships

Table relationships help users extract data more efficiently. Referential integrity will help eliminate common data entry mistakes. You will set two new relationships and enforce referential integrity.

Open the Relationships window and add the Casting and Actors tables.

Set a relationship between the Shows and Casting tables and set Enforce Referential Integrity.

Set a relationship between the Actors and Casting tables and set Enforce Referential Integrity.

Make sure all table data and join lines are visible.

Save and close the Relationships window.

Create a Casting Query

You need to post the casting roll for each new show. You will create a casting roll for the latest show using a query.

Create a query based on the Casting table. Include only the ShowID and ActorID fields.

Add criteria to display all the actors who performed in ShowID 3, which is Jersey Boys.

Run the query.

Save the query as Jersey Boys Cast.

Close the query.

Copy and Modify an Existing Query

The previous query does not show the name of the show or the name of the actor. You will copy the previous query and revise it to add the missing data.

Copy the Jersey Boys Cast query and paste it using the name Jersey Boys Cast Enhanced. Open the Jersey Boys Cast Enhanced query in Design view.

Add the Actors and Shows tables into the new query.

Modify the query so that the ShowName displays as the first column and the ShowID field does not display in the query results.

Replace the ActorID field with the LastName and FirstName fields.

Run the query. Verify the accuracy of the data.

Save and close the query.

Use a Calculated Field to Forecast Daily Revenue

The producers asked you for the daily estimated revenue for each show, based on a sold-out house. You will create a query and use a calculated field to provide this information.

Create a new query based on the Shows and Theaters tables.

Include the ShowName, TheaterName, SeatingCapacity, and TicketPrice fields.

Save the query as Estimated Daily Revenue.

Use the Expression Builder to create the DailyRevenue calculated field in the fifth column. Multiply the field SeatingCapacity by TicketPrice to create the calculated field.

Close the Expression Builder, run the query, and then manually verify whether the calculated field values are correct.

Save and close the query.

Use Functions in a Query

The producers ask you to modify and improve the Estimated Daily Revenue query. You will copy the existing query, rename it, and then add the required changes. The new query will require a Totals query so you can look up the total actors’ salaries for each show.

Create a Totals query from the Casting table that can be used to look up the total actors’ salaries for each show. Add the ShowID and WeeklyPay fields. Use the Total row to group by ShowID and sum the WeeklyPay. Run the query and save the query as Show Salaries. Close the query.

Copy the Estimated Daily Revenue query and paste it using the name Estimated Daily Revenue and Expenses.

Modify the Estimated Daily Revenue and Expenses query to add a Daily Rent calculated field to the sixth column using DailyRent: [WeekRentalFee]/7. Change the format property to Currency. Run the query and verify whether the calculated field is working.

Switch to Design view and add a Daily Salaries calculated field to the seventh column using the following expression:

DailySalaries: DLookUp(“SumOfWeeklyPay”,“Show Salaries”,“ShowID= ” & [ShowID])/7

The DLookup() function directs Access to look up a value in the Show Salaries query you created in Step a. Change the format property to Currency.

Run the query and manually verify whether the calculated field values are correct. Save and close the query.

Copy the Estimated Daily Revenue and Expenses query and paste it using the name Estimated Net Income. To the new query, add another calculated field for DailyNet. Daily Net is defined as Revenue minus Expenses (DailyRent and DailySalaries). Add another calculated field for TotalNet. Total Net is defined as Daily Net times the number of days the show runs. Total Net requires the DateDiff() function, as shown: TotalNet: [DailyNet]*DateDiff(“d”,[StartDate],[EndDate]).

Run the query and verify the calculated fields are correct.

Save and close the query.

Create a Form Using a Form Tool

You need to create a form to make it easier to add new actors. The same form will function as a maintenance screen for existing actors.

Create a form based on the Actors table using the Form tool.

Resize the width of the text box controls so they are about half the original size.

Remove the subform.

Change the title label to Enter and Edit Actor Data.

Save the form as Main Actors.

Add yourself as an actor using the new form. Include your real name and make up the rest of the data.

Close the form.

Create a Report Using the Report Wizard

You need to create a report that lists all the actors grouped by show. The report should also show the weekly pay for each actor and a sum for each show, as well as display a sum for the weekly pay overall.

Create a simple query that will be used to create a report. Include the Casting, Shows, and Actors tables. Add the necessary fields based on the description above.

Name the query Casting Summary. Close the query.

Create a report based on the Casting Summary query using the Report Wizard. Answer the Report Wizard’s questions using the description above. Sort by Last Name and name the report Casting by Show.

Switch to Layout view and delete the summary for the ShowName box displayed in the ShowName footer. Preview the report.

Switch to Design view and move the Sum label and Sum box up to the top of the ShowName footer. Reduce the height of the ShowName footer by one-half. Increase the width of the Grand total field to display the value including the $ sign. Preview the report.

Save the report. Close the report.

Add yourself as ActorID 20 to the Jersey Boys show with a weekly pay of 1250 in the Casting table and preview the report again.

Close the report.

Back Up the Database

An Access database needs to be maintained regularly. As a part of your daily routine, you will back up the database.

Back up the database in the location where you save your student files. When naming the file, include today’s date.

Save and close all open database objects. Based on your instructor’s directions, submit 01a_Broadway_LastFirst.

Write Your Message!

Name

Email id

Message

Verfication Code

User Account

All Pages

01a_ImportCasting.xlsx, Database Management System

Related Discussions:- 01a_ImportCasting.xlsx

View serialiabilty-labelled precedence graph, how to construct labelled pre...

File Management Principles, What are the requirements to ensure the objecti...

Types of indexes, Types Of Indexes One of the words used during the fil...

Economics, Economics: It is now usually accepted that it costs less to make...

DB TECHNOLOGIES, WHAT IS DATABASE TECHNOLOGIES

Advantages to copying records from one table to another, What kind of advan...

State the objectives-data source, You will be analysing a set of financial ...

Structure of the ldb, Though all the ABAP/4 Dictionary Structures that exis...

What is a candidate key, What is a candidate key? Minimal super keys ar...

Difference between operating system and database security, Difference betwe...

Write Your Message!

Featured Services

Online Tutoring

Project Development

Exam Preparation

Course Help

Assignment Help

Popular Subjects

Assured A++ Grade

Academics

Major Subjects

Majors

Get In Touch

TERMS & POLICIES

HELP & SUPPORT