Homework answers / question archive / ISMG 2050 E01 Concert Ticket Sales Database The KPOP Tours Company sets up tours in the United States for KPOP music groups and sells tickets to the concerts

ISMG 2050 E01 Concert Ticket Sales Database The KPOP Tours Company sets up tours in the United States for KPOP music groups and sells tickets to the concerts

MS Access

Share With

ISMG 2050 E01

Concert Ticket Sales Database

The KPOP Tours Company sets up tours in the United States for KPOP music groups and sells tickets to the concerts. Management has created an Access database to track pre-sales of concert tickets, and they need help normalizing the data, and creating forms and reports.

1. Open Exam_3_bu_data and save it as Exam_3_bu_LastFirst Enable the content if necessary. (5 Marks)
2. In tblTicketSales, change font to Arial with a size of 12 point. Format with alternating row colors of Standard Colors Light Blue 2 (10 Marks)
3. Create a query that shows sales of tickets for VenueID 2 only. Show the VenueID, VenueName, Quantity, ConcertDate, ConcertTime, FirstName, and LastName. Sort in ascending order by LastName. Save the query as qryNokiaTheatreSales (10 Marks)
4. Create an aggregate query from tblTicketSales, tblConcerts, and tblVenue. Show the average TicketTotal, minimum TicketTotal, maximum TicketTotal, and sum of TicketTotal for each VenueName. Save the query as qryQuantities In Datasheet view, add a Total row and then average the MinOfTicketTotal field, average the MaxOfTicketTotal field, and sum the SumOfTicketTotal fiel (10 Marks)
5. Create a Find Unmatched query that will return the last name, first name, and email of any customer in tblCustomer who has not purchased tickets to a venu Save the query as qryUnmatched (10 Marks)
6. Create a split form to add a customer to the tblCustomers table. Name the form frmCustomers In Layout view, resize the form label controls so the entire label is visible. Move the bottom half of the form up to remove the white space. Apply the Slice theme to the entire database. Change the form title to Customers Bold the form labels. Change label font color to Aqua Blue 5. In Form View, add the following new customer: (10 Marks)

7. Create a form showing venue name and the FirstName and LastName of customers who attended a concert at each venue in Datasheet layout. Name the main form frmVenue and the subform frmCustomersSubform AutoFit the columns on the subform and widen the subform to show all fields. Change the title to Customers per Venue Increase the width of the title label to include all text on one line. Delete the subform label. Move the subform to the left and align with the left side of the tblVenue labels. (10 Marks)
8. Use Report Wizard to create a report named rptAverageTotals that shows GroupName and the average TicketTotal. Use Sum and Avg on TicketTotal. Delete the label containing Summary for ‘GroupID’. Use conditional formatting to highlight the text color of all averages that are greater than 150 in Green and bold. Change the report title to Average Ticket Totals Resize all field labels as needed so all the data displays. Change the Avg label to Average Ticket Total Add a label in the report footer with your first and last name and left-align it with the Grand Total text box. Save the report as a PDF named a04_exam_bu_report_LastFirst (10 Marks)
1. Use Report Wizard to create a report in landscape orientation that shows VenueName, GroupName, Quantity, and TicketTotal. View the report by GroupName. Change the orientation to portrait.  Add a subtotal with a sum of Quantity and TicketTotal. Save the report as rptTicketSales (10 Marks)
10. Customize the rptTicketSales report. Resize the field labels and move the labels to the left as necessary so all the data displays on the page. Move the page number below the TicketTotal column. Delete the Summary for ‘GroupID’ label. Delete the Summary for ‘ConcertID’ label. Change the report title to Ticket Sales by Group Name Add a label in the report footer with your first and last name. Move it below the Grand Total label. (10 Marks)
11. Save the report and close Access. Submit your work in the Canvas. Make sure that the submitted file is an Access file with the extension as .accdb. (5 Marks)