Office 2013 Myitlab Grader Instructions Go!

Office 2013 – myitlab:grader – Instructions GO! - Access Chapter 2

In this project, you will use a database to answer questions about concerts in the local college area. You will create a relationship between two tables, create a query from an existing query, and create queries using text, numeric, compound, and wildcard criteria based on fields in one or both tables. You will create calculated fields, group data when calculating statistics, create a crosstab query, and create a parameter query.

For grading purposes, perform the following tasks:

  1. Start Access, open the provided database file go_a02_grader_a3_Concerts_Sponsors.accdb, and enable content.
  2. Create a one-to-many relationship between the Sponsors and Concerts tables using Sponsor ID, enforce referential integrity, and enable cascade options. Generate and save a relationship report with default settings. Close all objects.
  3. In the Sponsors table, change the last record's Sponsor ID from SPONSOR-108 to SPONSOR-100. Save and close the table; related records in the Concerts table will update automatically.
  4. Copy the existing Concerts $1000 or More Query to create a new query named Jan-Apr Concerts Query. Redesign it to display Date, Concert Name, Concert Location, and Box Office Receipts in that order, sorted ascending by date, for records between 1/1/18 and 4/30/18. Run, then save and close the query.
  5. Build a new query based on Concerts with fields: Date, Concert Name, Concert Location, and Box Office Receipts, sorted by date ascending. Filter records for location of Georgetown Community Theater or Austin City Center with receipts > 1000. Run, save as GCT OR ACC Over $1000 Query, and close.
  6. Create a query combining Sponsors and Concerts with fields: Sponsor Name, Concert Name, Concert Location, sorted by location. Filter for sponsor names containing "radio" and concert names ending with "festival". Save as Radio Festivals Query, close.
  7. Design a query from Concerts, showing Concert ID, Concert Name, Concert Location, Sponsor ID, Date with records missing the date, and save as Missing Concert Date Query.
  8. Design a combined query from Concerts and Sponsors with fields: Concert ID, Sponsor Name, Box Office Receipts. Sort by Concert ID. Create calculated fields:
    • Sponsor Donation»: 0.5 times Box Office Receipts
    • Total Donation»: sum of Box Office Receipts and Sponsor Donation

    Format the Sponsor Donation as currency with two decimals. Save as Sponsor Donation Query, close.

  9. Create a query summarizing Box Office Receipts by Concert Location, sorted descending by receipts, and summed. Save as Receipts by Location Query.
  10. Use the Query Wizard to create a crosstab based on Concerts, with Sponsor ID as row headers, Concert Location as column headers, and sum of Box Office Receipts. Format currency with 0 decimals. Save as Sponsor and Location Crosstab Query.
  11. Design a query from Concerts with fields: Concert Name, Concert Location, Box Office Receipts, Sponsor ID. Sort by Concert Name. Set a parameter prompt for Enter the Sponsor ID. Run with SPONSOR-101, then hide the Sponsor ID field, save as Sponsor ID Parameter Query.
  12. Ensure all objects are closed, then close Access.

Submit the database as instructed.

Paper For Above instruction

This project involving Microsoft Access 2013 is designed to deepen understanding of relational database concepts, particularly as they relate to managing data about concerts and sponsors. It emphasizes creating relationships, designing various query types, calculating fields, and aggregating data to produce insights suitable for event management and sponsorship analysis.

The initial step involves establishing a relationship between the Sponsors and Concerts tables. Using Sponsor ID as the key, a one-to-many relationship is designed, enforcing referential integrity, ensuring data consistency, and enabling cascading updates. Such relationships are fundamental in relational databases, helping to maintain data integrity and enabling complex queries across related tables. Generating a relationship report visualizes this structure, making it easier to understand the data model.

Adjusting data in the Sponsors table tests understanding of referential integrity management. Changing the Sponsor ID in one record propagates updates to related records in the Concerts table, exemplifying database consistency rules.

Creating several queries illustrates different filtering, sorting, and data presentation techniques. For example, modifying an existing query to focus on concerts from January to April 2018 involves setting date criteria, sorting, and selecting specific fields for clarity. Filtering concerts based on location and revenue demonstrates compound criteria, while combining tables with joins allows for more detailed reports like sponsorship details linked to concerts.

The project also explores data aggregation. Summing box office receipts by location provides revenue insights, while crosstab queries analyze the distribution of receipts across sponsors and locations. Calculated fields like Sponsor Donation and Total Donation illustrate how to perform inline calculations, vital for financial summaries and sponsorship impact analysis. Formatting these calculated fields enhances report readability, aligning with real-world presentation standards.

Utilizing parameters in queries fosters dynamic data retrieval, prompting users to specify criteria at runtime, thus enabling flexible data analysis for varied scenarios. Finally, proper object management and submission procedures are emphasized, ensuring clean project completion and report submission.

References

  • Barrow, D. (2012). Access 2013 For Dummies. John Wiley & Sons.
  • Habin, D. (2013). Microsoft Access 2013: Illustrated Introductory. CENGAGE Learning.
  • Simpson, G. (2013). Mastering Microsoft Access 2013. Sybex.
  • Gaskins, R. (2014). Microsoft Access 2013 Step by Step. Microsoft Press.
  • Murach, J., & Murach, P. (2014). Microsoft Access 2013 Programming by Example. Mike Murach & Associates.
  • Schumacher, R., & DeLorenzo, J. (2014). Access 2013 VBA Programming. CENGAGE Learning.
  • Chapple, M., & White, J. (2013). Microsoft Access 2013 Step by Step. Microsoft Press.
  • Gaskins, R. (2015). Microsoft Access 2013: Inside Out. Microsoft Press.
  • Chapple, M. (2013). Building Corporate Dashboards with Microsoft Dynamics CRM 2013. Packt Publishing.
  • Walkenbach, J. (2013). Excel VBA Programming For Dummies. John Wiley & Sons.

Related Assignments