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

Determine the programming language structures, What is the most significant...

What is the most significant feature that does not directly map into programming language structures? Why? Association, since there are complex types of association, like as qu

Write the motivations led to development of oo-dbms, Write the motivations ...

Write the motivations led to development of OO-DBMS? 1.  Programmer frustration with RDBMSs. 2.  Requirement for special features.

What are the advantages of having an index structure, What are the advantag...

What are the advantages of having an index structure?      Ans: The index structures commonly provide secondary access paths, which offer alternative ways of accessing the reco

Query processor in database management system, Query Processor The qu...

Query Processor The query language processor is responsible for taking query language statements or shifting them from the English-like syntax of the query language to a form

Query, Use a Set operator to create a list of academics who have written or...

Use a Set operator to create a list of academics who have written or co-written less than 5 papers and also have greater than 3 interests. List their academic number in the output.

What is a super key, What is a super key? A super key is a set of one o...

What is a super key? A super key is a set of one or more attributes that collectively permits us to recognize uniquely an entity in the entity set.

Traffic data management, This report identifies and outlines my CIS 499 pro...

This report identifies and outlines my CIS 499 project named Traffic Data Management  Systems (TDMS) .   My goal is to design a replica of all traffic issues to be implemented i

Create a database model, Submit the table creation statements (including co...

Submit the table creation statements (including constraints) for the Database Model. Submit them all in a single script file. Also submit a document explaining what test data yo

Cascading rollback, explain cascading rollback

explain cascading rollback

Implementation of database, The ER diagram clearly showing the additional t...

The ER diagram clearly showing the additional tables you have implemented. SQL table creation scripts for the tables you have set up. SQL scripts showing the sample data you

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