Your Perfect Assignment is Just a Click Away

Starting at $8.00 per Page

100% Original, Plagiarism Free, Customized to Your instructions!

glass
pen
clip
papers
heaphones

Prince Georges Community College Turrell Resource Events Project

Prince Georges Community College Turrell Resource Events Project

Question Description

Question 1:

YO19_Access_Ch03_PS1 – Turrell Resource Events 1.0

Project 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 2

YO19_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.2

Project 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


"Place your order now for a similar assignment and have exceptional work written by our team of experts, guaranteeing you A results."

Order Solution Now

Our Service Charter


1. Professional & Expert Writers: Eminence Papers only hires the best. Our writers are specially selected and recruited, after which they undergo further training to perfect their skills for specialization purposes. Moreover, our writers are holders of masters and Ph.D. degrees. They have impressive academic records, besides being native English speakers.

2. Top Quality Papers: Our customers are always guaranteed of papers that exceed their expectations. All our writers have +5 years of experience. This implies that all papers are written by individuals who are experts in their fields. In addition, the quality team reviews all the papers before sending them to the customers.

3. Plagiarism-Free Papers: All papers provided by Eminence Papers are written from scratch. Appropriate referencing and citation of key information are followed. Plagiarism checkers are used by the Quality assurance team and our editors just to double-check that there are no instances of plagiarism.

4. Timely Delivery: Time wasted is equivalent to a failed dedication and commitment. Eminence Papers are known for the timely delivery of any pending customer orders. Customers are well informed of the progress of their papers to ensure they keep track of what the writer is providing before the final draft is sent for grading.

5. Affordable Prices: Our prices are fairly structured to fit in all groups. Any customer willing to place their assignments with us can do so at very affordable prices. In addition, our customers enjoy regular discounts and bonuses.

6. 24/7 Customer Support: At Eminence Papers, we have put in place a team of experts who answer all customer inquiries promptly. The best part is the ever-availability of the team. Customers can make inquiries anytime.