The KPOP Tours Company sets up tours in the United States for KPOP music groups and sells tickets to the concerts

Subject:ManagementPrice: Bought3

Share With

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.


Open test4versionb1.accdb and save it as test4-yourfirstnameyourlastname Enable the content if necessary.
Examine the database. Be sure to look at the relationships so that you understand how the tables are related. Form the relationships you need, and then create a relationship report accepting the default name.
3. Create the following select queries. Make sure that you replace lastname with your lastname on each of the queries (I will not mark any that have not been updated with your lastname. (30 marks)

Create a query that lists all customers’ names and phone numbers. Only display customers who live in maine and their phone numbers’ area code begins with either 3 or 8. Sort in Ascending order by last name, and then Save the query as qry1AreaCodes-Lastname. Hint: check your data!


Create a query to determine the total cost of tickets (multiple quantity times ticket cost). Create a second calculated field and deduct 13% to account for taxes. Format the calculated field as Currency with one decimal. Save the query as qry2totalticketcost-lastname.


Create an aggregate query from tblTicketSales, tblConcerts, and tblVenue. Show the average TicketTotal, minimum TicketTotal, maximum TicketTotal, and sum of TicketTotal for each VenueName. Change the field titles to average, minimum, maximum. Only show appropriate fields. Save the query as qry3aggregateQuantities-lastname
Create an aggregate query that will return the number of venues in each state. Only show appropriate fields to show the results. Name this query qry4venuesCountBystate-lastname
Create a query named qry5Selectedvenues-lastname to find all records in the venue and concert tables in which the venuename contains an “y” and the concert is in May. Only show appropriate fields to show the results. Save and close the query.
Create a query named qry6theaterorauditorium-lastname. If the totalcost of the ticket is greater than $150 and the venue name contains the word theater or auditorium, show appropriate fields. Sort venuename in descending order.
Create a form showing the 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.
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 160 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 rpt_5Lastnamet
CLOSE your database file and close Microsoft Access.