Question 1:YO19_Access_Ch03_PS1 - Turrell Resource Events 1.0Project Description:Beth Turrell runs a business that organizes events for local manufacturing companies to introduce various industry standards, new technologies, networking opportunities and more. She has hired you to add queries to make the database more useful. You have been given a small database with representative data.Steps to Perform: Step Instructions Points Possible 1 Start Access. Download and open the file named Access_Ch03_PS1_ResourceEvents.accdb. Grader has automatically added your last name to the beginning of the filename. Save the file to the location where you are storing your files. 0 2 Changing the appearance of a table in Datasheet view can make it easier to view the data. Open tblContact, increase the font size to 14 and change the alternate row color to Orange, Accent 2, Lighter 60%. AutoFit the columns as necessary. 6 3 Filters can be used to easily locate records that meet specific criteria in Datasheet view of a table. Create a filter to show only the contacts who have a State of OH. Save and close the table. 7 4 Beth Turrell would like to know if there are any dates that have multiple events booked. Using the Query Wizard, create a Find Duplicates query that will show all events on the same date. Show all the fields in the query results. Name the query qryDupDates. Adjust the column widths as necessary. Save and close the query. 10 5 Beth would like a list of contacts who have never attended an event. Using the Query Wizard, create a Find Unmatched query that will display the ContactID, Firstname, Lastname, Address, City, State, and ZipCode, in that order, of anyone who is listed as a contact in tblContact but does has not any matching record in the tblContactInEvent table. Sort the query in ascending order by Lastname and then by Firstname. Name the query qryContactNoEvent and adjust the column widths as necessary. Save and close the query. 10 6 Using Query Design, create a query that will return the contact’s Lastname, Firstname, TotalGuests, EventName, and EventDate. Sort the query in descending order by EventDate and then in ascending order by Lastname. Name the query qryEventDetails run the query, and add a total row in Datasheet view to show the total guests. Adjust the column widths as necessary. Save and close the query. 12 7 Open the query qryNumOfEventsByType. This query is supposed to show each event type only once along with the number of events for that type. Switch to Design view, find the mistake, and correct it. Run the query, adjust the column widths as necessary. Save and close the query. 10 8 Using Query Design, create a query that will calculate the total number of paid guests for each EventType. Use the Sum aggregate function on the TotalGuests field and name it Total Number of Guests. Be sure your query only displays those contacts that have paid the entry fee. The results should show EventType and Total Number of Guests, in that order. Sort in descending order by Total Number of Guests. Name the query qryTotalPaidGuestsByType and adjust the column widths as necessary. Save and close the query. 15 9 Using Query Design, create a query to calculate the total amount of entry fees that have been collected for each event that takes/took place in the year 2022. Include EventName, EventDate, EventType, EntryFeePerGuest, TotalGuests, and Paid fields. Name the query qryTotalPaidEntryFees2022 Calculate Total Entry Fees as EntryFeePerGuest * TotalGuests. You will need to specify the necessary criteria for the Paid and EventDate fields. Do not show the Paid field in the results. Sort in ascending order by EventName. Adjust the column widths as necessary. Save and close the query. 15 10 Using Query Design, create a query that will return the contact’s ContactID, Firstname, LastName, and State, as well as the EventName, EventDate, EventType, and Status. Apply the necessary criteria so that only contacts from Ohio (OH) who have registered for an event with a Plant Tour event type or any contact from any state other than Ohio (OH) who has registered for an event with an Other event type are returned. Sort the records in ascending order by Lastname and then in ascending order by Firstname. Name the query qrySelectRegistrants and adjust the column widths as necessary. Save and close the query. 15 11 Save and close Access_Ch03_PS1_ResourceEvents.accdb. Exit Access. Submit the file as directed. 0 Total Points 100 Question 2YO19_Access_Ch05_PS1 - Events 1.0 Project Description: From weddings to conferences, the resort is a popular destination. The resort has several facilities that can accommodate groups from 30 to 600 people. Packages and prices vary by size of group, room, and other services, such as catering. The Event Planning & Catering team works closely with clients to ensure the clients have everything they need for their event. The resort stocks several choices of decorations, table arrangements, and centerpieces. These include professional, simple, themed, and luxurious. Steps to Perform: Step Instructions Points Possible 1 Start Access. Download and open the file named YO19_Access_Ch05_PS1_Events.accdb. Grader has automatically added your last name to the beginning of the filename.Enable the content, if necessary. 0 2 Create a new table with the following attributes: Field Name Data Type EventDetailID AutoNumber EventID Number ClientID Number DecorID Number Theme Short Text Add the following descriptions for each of the fields: EventDetailID: Surrogate key for each selected item (primary key) EventID: This is the scheduled event ClientID: Client ID (foreign key) from the tblClients table DecorID: What the client wants to have at the event Theme: Can be professional, simple, themed, or luxurious Save the new table as tblEventDetails. 15 3 Create the following field properties in the tblEventDetails table. EventDetailID field – to make sure the ID number is clearly for the detail: • Under Field Properties, add the following custom format: "DETAIL"0. • Ensure that the New Values field property is set to Increment. EventID field – to identify which event the detail pertains to: • Create a lookup field by changing the data type to Lookup Wizard. Select your data from the existing tblEvents table, and then click Next. Select EventID, EventName, and EventDate. Click Next. Sort in Descending order by EventDate. Click Next. Hide the Key Column. Click Next two times. Click Enable Data Integrity, and ensure that Restrict Delete is selected. Click Finish. Save the table when prompted. • Under Field Properties, enter Event as the caption. • Under Field Properties, change the Required property to Yes. 15 4 Continue creating the following field properties in the tblEventIDetails table. ClientID field – to identify the client associated with this event detail: • Create a lookup field by changing the data type to Lookup Wizard. Select your data from the existing tblClients table, and then click Next. Select ClientID, LastName, and FirstName. Click Next. Sort in Ascending order by LastName and FirstName. Click Next. Keep the Key Column hidden. Click Next. Click Enable Data Integrity, and ensure that Restrict Delete is selected. Click Finish. Save the table when prompted. • Under Field Properties, type Client as the caption. • Under Field Properties, change the Required property to Yes. DecorID field – to identify the decoration or item associated with this event detail: • Create a lookup field by changing the data type to Lookup Wizard. Select your data from the existing tblDecorations table, and then click Next. Select DecorID, DecorItem, and Color. Click Next. Sort in Ascending order by DecorItem and Color. Click Next. Keep the Key Column hidden. Click Next. Click Enable Data Integrity. Click Finish. Save the table when prompted. • Under Field Properties, enter Item as the caption. • Under Field Properties, change the Required property to Yes. 15 5 Continue creating the following field properties in the tblEventDetails table. Theme field – to restrict the data entered to the only four options available: Create a lookup field by changing the data type to Lookup Wizard. Click I will type in the values that I want. Click Next. Enter the following options. Row 1: Professional Row 2: Simple Row 3: Themed Row 4: Luxurious Click Next, click Limit to List, and then click Finish. Under Field Properties, change the Field Size property to 12. 11 6 Save your changes, and then switch to Datasheet view. Add the following records to test the field properties in your new table. Event Client Item Theme Wedding Reception 11/23/2021 Bennett Tablecloth White Luxurious Wedding Reception 11/23/2021 Bennett Candles with Flowers Silver Luxurious Wedding Reception 11/23/2021 Bennett Fountain (large) Luxurious Close the tblEventDetails table. 11 7 The Eldorado room is the most popular room. Users frequently need to filter to just see details associated with the Eldorado room. Open the tblEvents table in Datasheet view to create a query from a filter. • Click the Location field, and then, on the Home tab, in the Sort & Filter group, click Selection. • Select all events being held in the Eldorado Room. Save this filter as a query named qryEldorado by using the Save Object As option. • Close qryEldorado. 11 8 Evening events are very popular. Users frequently answer phone calls inquiring about evening events and need to quickly filter to evening events. On the Home tab, in the Sort & Filter group, click Advanced, click Clear All Filters, and then click Advanced. • Select Advanced Filter/Sort. The tblEventsFilter1 pane opens. • Select the following fields, and then enter the following criteria in the tblEventsFilter1 grid. Field Field Name Criteria 1 EventName 2 EventDate 3 StartTime 4 EndTime >6pm • On the Home tab, in the Sort & Filter group, click Toggle Filter. Notice that the results of the Advanced Filter are now displayed in the tblEvents table. • Save this filter as a query named qryEveningEvents by using the Save Object As option. • Close qryEveningEvents. • Close tblEvents and tblEventsFilter1. Do not save the changes when prompted. 11 9 The tblEvents may not be as normalized as it could be. Use the Analyze Table to see how Access would recommend breaking up the tblEvents into multiple tables. On the Database Tools tab, in the Analyze group, click Analyze Table. The Table Analyzer Wizard opens to the first introduction screen. • Click Next two times, and then select tblEvents if necessary. • Click Next, and then click Yes, let the wizard decide. • Click Next. Access will create three new tables: Table1, Table2, and Table3. • Double-click the current table names, and rename them as follows. Table1: tblEventData Table2: tblEventName Table3: tblEventLocation • Click Next two times. Access asks whether you want to create a query that resembles your old table. Because your old table will be saved automatically, you will not need a query with the same data. Click No, don’t create the query. • Click Finish. All three tables you just created will open. Close the tblEventData, tblEventName, and tblEventLocation tables. Click Yes if Access prompts you to save any changes. 11 10 Exit Access, and then submit your file as directed by your instructor. 0 Total Points 100 Question 3:YO19_Access_Ch06_PS1 - BOOK CORNER 1.2Project Description:The Book Corner is a small, local bookstore that sells to both recreational readers and the local college student population. The manager uses Access to track inventory, customers, and transactions. You have been asked to perform advanced queries that will help the management team make important business decisions. To keep the file small while you work with the database, the store manager removed most of the data and left only some sample data. Once the store manager accepts your changes, she will load all the data and implement the new database.Steps to Perform: Step Instructions Points Possible 1 Start Access. Download and open the file named YO19_Access_Ch06_PS1_BookCorner.accdb. Grader has automatically added your last name to the beginning of the filename. Enable the content, if necessary. 0 2 Create a query in Design view based on the tblShipments and tblTransaction tables that displays the TransID and a calculated field titled DaysToShip that calculates the number of days it took to ship the items from the date purchased using the DateDiff function. Sort the results in Descending order by DaysToShip. Run the query. Save the query as qryDaysToShip and then close the query. 12 3 Create a query in Design view that is based on the tblInventory table. Display InventoryID, Price, and a new calculated field called NewPrice. The NewPrice should be 30% more if the book is OutofPrint and 10% more if not. Both values should be rounded to the nearest dollar. Format the NewPrice field as Currency. Run the query. Save the query as qryNewPrice and then close the query. 13 4 Create a query in Design view that is based on the tblAuthor and tblInventory tables. Display a new calculated field called Description. The Description field should display the following for all books in the database: Author: FirstName LastName -Title: BookTitle -Genre: BookGenre. Be sure to allow a blank space after each colon, between FirstName and LastName, and before and after each dash. Run the query. Save the query as qryBookDescription and then close the query. 13 5 Create a query in Design view that is based on the tblCustomer, tblTransaction, tblTransactionDetails, and tblInventory tables that will return the top 25% of customers based on a calculated field called Revenue. Display CustomerID and Revenue. Calculate Revenue by multiplying the price by the quantity. Format Revenue as Currency and sort the results in Descending order of Revenue. Run the query. Save the query as qryTop25%Customers and then close the query. 12 6 Create a query in Design view based on the tblInventory table to help determine when to order additional copies based on the NumberInStock. Display InventoryID, BookTitle, and a calculated field called OrderStatus. If there are seven or more copies in stock, then display None. If there are between four and six copies in stock, then display Critical. For those with fewer than four in stock, display Urgent. Limit the results to only those books that are not OutofPrint. Do not display the OutofPrint field in the query results. Run the query. Save the query as qryOrderStatus and then close the query. 13 7 Create a query in Design view based on the tblInventory table to help determine which books in the fiction and literary genres were written during the 19th century (between the years 1800 and 1899). Display BookTitle, AuthorID, YearPublished, BookGenre, and Condition. Run the query. Save the query as qry19thCentury and then close the query. 12 8 Create a query in Design view based on the tblInventory, tblTransaction, and tblTransactionDetails tables to help determine which Your Office books have been shipped within a specific 90-day period. Display CustomerID, BookTitle, TransactionDate, and Qty. Use a parameter and the Between…And function that prompts you to enter a start date and end date when you run the query. The first prompt should display the text Enter Start Date and the second prompt should display Enter End Date (no period). Use a wildcard and the Like function to ensure that only books with Your Office in the title are included in the results. Test your query by using 7/1/2020 as your start date and 9/30/2020 as your end date. Save the query as qryNinetyDays and then close the query. 13 9 Create a query in Design view based on the tblTransaction table to help determine the date that a purchase should be shipped. Display TransID, CustomerID, TransactionDate, Shipped, and a calculated field titled ShipByDate that calculates the date by which the order should be shipped using the DateAdd function. Orders should be shipped within seven days from the date that the order was placed. Sort the results in Ascending order by ShipByDate. Run the query. Save the query as qryShipByDate and then close the query. 12 10 Close all database objects. Close the database and then exit Access. Submit the database as directed. 0 Total Points 100

Questions:1. Write a program to count the nodes in a linked list with the first node pointed to by first.2. Write a program to determine the average of a linked list of real numbers with the first node pointed to by first.3. Determine the computing times of the algorithms in question 1 and 2.4. Write a program to insert a new node into a linked list with the first node pointed to by first after the nth node in this list for a given integer n.5. Write a program to delete the nth node in a linked list with the first node pointed to by first, where n is a given integer.You are required to write a completed program for each question except Question 3.Requirements:The followings are the requirements for this homework.You can either write separate programming for each question except question #3 or build a complex program to implement all the functions that requested by every questionYou have to make sure your code works before submitting your homework. If you cannot make your code work, please write a short description of the problem you had, I would like to read or fix your code to give you partial credits. The screenshot(s) of the program's output is also required to be submitted along with your source codeYou should find the example code from the lecture slides, directly coping from online code will result in a grade "0" for HW2, or even an "F" at the end of the Fall semester. Your submission will be checked by the plagiarism checking system