User Account

All Pages

01a_ImportCasting.xlsx, Database Management System

Assignment Help:
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.

Related Discussions:- 01a_ImportCasting.xlsx

Query to delete constraint from alter table, A constraint named less_than_2...

A constraint named less_than_20 was defined on the field date_of_birth of table student. Delete this constraint. ALTER TABLE student DROP CONSTRAINT less_than_20;

Discuss how the bi-directional implementations are made, Discuss how the bi...

Discuss how the bi-directional Implementations are made. In bi-directional implementation of the association, a pair of references must implement every link. The declarations a

A table joined with itself is called, A table joined with itself is called ...

A table joined with itself is called ? A table joined with itself is known as self join table. Normal 0 false false false EN-IN X-NONE X-NONE

Explain how you can define an object model of a system, Explain how you can...

Explain how you can define an object model of a system. A list of terms that shall be used by end users to describe state and behaviour of objects in system.   Different use

Relationship between security and integrity, Relationship between Security ...

Relationship between Security and Integrity: Database security usually refers to access, while database integrity refers to avoidance of accidental loss of consistency. But usually

Explain what is aggregation, Explain what is Aggregation? Aggregation:...

Explain what is Aggregation? Aggregation: Aggregation is an significant concept in database design whereas composite objects could be modelled during the design of database a

Describe the basic steps involved in recovery, Question: One of the tas...

Question: One of the tasks a database management system must perform is to ensure that the data is still intact in the face of software and hardware failure. This task is known

Set difference, Set Difference If R1 and R2 are two union compatible re...

Set Difference If R1 and R2 are two union compatible relations or relations then result of R3 =R1- R2 is the relation that have only those tuples that are in R1 but not in R2.

What are called jukebox systems, What are called jukebox systems? Jukeb...

What are called jukebox systems? Jukebox systems have a few drives and numerous disks that can be loaded into single of the drives automatically.

Reasons for the growing popularity of data mining, Question: Data Minin...

Question: Data Mining is the process of extracting knowledge hidden from large volumes of raw data. (a) Using a suitable example, illustrate how Data Mining can be useful t

Write Your Message!

Captcha
Order Assignment

Featured Services

Order Now

Popular Subjects

Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

Submit Assignment

Academics

Major Subjects

Majors

Get In Touch

whatsapp: +91-977-207-8620

Phone: +91-977-207-8620

Email: [email protected]

TERMS & POLICIES

HELP & SUPPORT

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd