Description
User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.
Explanation & Answer
Review
Review
Anonymous
Really helpful material, saved me a great deal of time.
Studypool
4.7
Trustpilot
4.5
Sitejabber
4.4
24/7 Homework Help
Stuck on a homework question? Our verified tutors can answer all questions, from basic math to advanced rocket science!
Most Popular Content
4 pages
Ntopicsix
After Looking at the Patent Literature, What Speculation can you Make about VoiceActivated Technologies in the Near Future ...
Ntopicsix
After Looking at the Patent Literature, What Speculation can you Make about VoiceActivated Technologies in the Near Future? Voice activation, commonly ...
Prince Georges Community College Turrell Resource Events Project
Question 1:YO19_Access_Ch03_PS1 - Turrell Resource Events 1.0Project Description:Beth Turrell runs a business that organiz ...
Prince Georges Community College Turrell Resource Events Project
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
10 pages
Secure Videoconferencing. Update
Videoconferencing is becoming an essential entity for business growth; as much as every organization would wish to have vi ...
Secure Videoconferencing. Update
Videoconferencing is becoming an essential entity for business growth; as much as every organization would wish to have video conferencing units put ...
CSIA 459 University of Maryland Technology Evaluation Study Plan Paper
Read your chosen Technology Selection paper to learn more about the selected technology. Next, consider what type of forma ...
CSIA 459 University of Maryland Technology Evaluation Study Plan Paper
Read your chosen Technology Selection paper to learn more about the selected technology. Next, consider what type of formal evaluation study could be used to learn more about this technology and how it is likely to interact with people, processes, and technologies. Then, design a formal evaluation study which could be used to obtain more information about one or more of the following:
7 pages
Wireless Security Essay Questions
Courtesy of the advancement of technology, modern organizations recognize the need to connect their devices and appliances ...
Wireless Security Essay Questions
Courtesy of the advancement of technology, modern organizations recognize the need to connect their devices and appliances to ensure effective and ...
Similar Content
New England College Biometric Techniques Paper
Some common biometric techniques include:Fingerprint recognitionSignature dynamicsIris scanningRetina scanningVoice prints...
University of Arkansas at Little Rock Data Mining Discussion Questions
answer the following questions in an essay format:How does data and classifying data impact data mining?What is associatio...
CU Computer Science Digital Forensic Profession Discussion
A digital forensics professional must know basic IT skills, understand computer architecture and networking, and have anal...
Tasks of theoretical computer science/ Main source Sipser's book
All tasks are in files. Introduction to the Theory of ComputationIs the following statement true or false? Then, provide a...
Java Question
Please read the instructions and requirement attached!...
JWU Week 4 Pseudocode and Flowchart of Simple Pay Calculation Project
InstructionsWeek 5 Assignment: Pseudocode and Flowchart Solution Instructions:Create a FLOWCHART and a PSEUDOCODE for each...
Information Assurance00
Culture is influential and known for playing a significant role in ensuring that the organization operates in the most mea...
Plagiarism Report 2
...
Drp
Official copies of the document are available at the following locations: Official copies of the document are available at...
Related Tags
Book Guides
The Russian Hoax
by Gregg Jarrett
The Unwinding of the Miracle
by Julie Yip-Williams
Harry Potter and the Sorcerers Stone
by J. K. Rowling
Black Beauty
by Anna Sewell
The Great Gatsby
by Francis Scott Key Fitzgerald
Othello
by Wiliam Shakespeare
Twilight
by Stephenie Meyer
Narrative of the Life of Frederick Douglass
by Frederick Douglass
Tess of the DUrbervilles
by Thomas Hardy
Get 24/7
Homework help
Our tutors provide high quality explanations & answers.
Post question
Most Popular Content
4 pages
Ntopicsix
After Looking at the Patent Literature, What Speculation can you Make about VoiceActivated Technologies in the Near Future ...
Ntopicsix
After Looking at the Patent Literature, What Speculation can you Make about VoiceActivated Technologies in the Near Future? Voice activation, commonly ...
Prince Georges Community College Turrell Resource Events Project
Question 1:YO19_Access_Ch03_PS1 - Turrell Resource Events 1.0Project Description:Beth Turrell runs a business that organiz ...
Prince Georges Community College Turrell Resource Events Project
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
10 pages
Secure Videoconferencing. Update
Videoconferencing is becoming an essential entity for business growth; as much as every organization would wish to have vi ...
Secure Videoconferencing. Update
Videoconferencing is becoming an essential entity for business growth; as much as every organization would wish to have video conferencing units put ...
CSIA 459 University of Maryland Technology Evaluation Study Plan Paper
Read your chosen Technology Selection paper to learn more about the selected technology. Next, consider what type of forma ...
CSIA 459 University of Maryland Technology Evaluation Study Plan Paper
Read your chosen Technology Selection paper to learn more about the selected technology. Next, consider what type of formal evaluation study could be used to learn more about this technology and how it is likely to interact with people, processes, and technologies. Then, design a formal evaluation study which could be used to obtain more information about one or more of the following:
7 pages
Wireless Security Essay Questions
Courtesy of the advancement of technology, modern organizations recognize the need to connect their devices and appliances ...
Wireless Security Essay Questions
Courtesy of the advancement of technology, modern organizations recognize the need to connect their devices and appliances to ensure effective and ...
Earn money selling
your Study Documents