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

What are audit trails, What are audit trails? An audit trail is a log o...

What are audit trails? An audit trail is a log of all alters to the database along with information like which user performed the change and when the alter was performed.

How to insert data in your table from another table, How to Insert data in ...

How to Insert data in your table from another table? insert into e.g, To insert tuples from employee into N_emp created above, use following statement Insert into N_emp

Relational operations, Let us now talk about the relational operations: ...

Let us now talk about the relational operations: SELECT The select operation is used to select few specific record from the database based on different criteria, This is a un

Explain differance between primary and secondary storage, Explain differanc...

Explain differance between Primary and secondary storage ? Primary and secondary storage Computer storage is classified within primary(main) memory and secondary(per

Database techknowlagies, limitations and benefits in database tecknowlagy

limitations and benefits in database tecknowlagy

Create a subroutine, 1. Create a subroutine that does the remote blast on t...

1. Create a subroutine that does the remote blast on the file protein.fa. 2. Pass that returned files from the blast to another subroutine that parses the output and sends it to

Execute sql queries, Consider the relations described below: PHYSICIAN (re...

Consider the relations described below: PHYSICIAN (regno, name, telno, city) PATIENT (pname, street, city) VISIT (pname, regno, date_of_visit, fee) Where the regno and pname ide

Explain the project-join normal form, Explain the Project-Join normal form ...

Explain the Project-Join normal form A relation R is in PJNF along with respect to a set of functional dependencies D if for all join dependencies of the outline *(R 1 , R 2 ,

Relational algebra operators, Question 1 Draw a Data Flow Diagram for a...

Question 1 Draw a Data Flow Diagram for a University scenario Question 2 Discuss the following with suitable examples Relational Algebra Operators Database Pla

Define the concept of aggregation, Define the concept of aggregation.  Give...

Define the concept of aggregation.  Give two examples where this concept is useful. Ans: Aggregation transforms a relationship among the objects into a higher-level object. A

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