Alamance Community College MS Access Supplemental Products Worksheet all the steps are in the instructions document as well with the start up document and

Alamance Community College MS Access Supplemental Products Worksheet all the steps are in the instructions document as well with the start up document and there is one more document that study pool does not allowed me to upload it, I do not way, and is necessary for the completion of his project is an MS Access document. Yogaland
Sales
Yogaland
Products
Yogaland
Customers
Final Cumulative Assignment Instructions
PART 1
You will be responsible for tailoring the MS Excel file entitled: MS EXCEL START FILE – Final Cumulative Assignment
You will modify the current worksheets in the workbook to reflect the following changes below:
?
Apply a Theme (something else besides the “Office” theme) to the workbook. – 5pts
?
For the Sales worksheet, import a new query from the MS ACCESS SUPPLEMENTAL FILE – Final Cumulative
Assignment database for the corresponding “Sales” table and place the table in cell A3. – 2pts
?
For the Products worksheet, import a new query from the MS ACCESS SUPPLEMENTAL FILE – Final Cumulative
Assignment database for the corresponding “Products” table and place the table in cell A3. – 2pts
?
For the Customers worksheet, import a new query from the MS ACCESS SUPPLEMENTAL FILE – Final Cumulative
Assignment database for the corresponding “Customers” table and place the table in cell A3. – 2pts
?
Change the default table style for each of the tables on each worksheet you just imported to the “Table Style
Medium 4.” – 3 pts
?
Take the title of each worksheet (located in cells A1) and Merge & Center it over the entire corresponding table
(which varies the size due to each table having a different amount of columns). – 3pts
?
Take the subtitle of each worksheet (located in cells A2) and Merge & Center it over the entire corresponding
table (which varies the size due to each table having a different amount of columns). – 3pts
?
Change the defined name (hint: Name Manager) of the table located on the Products worksheet to
“Products_4_U” instead of the defaulted name. – 2pts
?
Create a Pivot Table based on the Products_4_U table and place it on a new worksheet. – 5pts
o The Pivot Table should depict the Category in the rows area and the Average Price in the values area 4pts
o Change the Number Format of the Prices to currency with the $ sign and two (2) decimal places – 2pts
o Create a slicer based on the Product ID – 4pts
o Change the name of the Pivot Table worksheet to Avg_Prod_Price – 1pt
o Place this worksheet directly after (directly to the right of) the Products worksheet – 1pt
?
On the Customers worksheet, create a custom multi-level sort. Sort the Customers table by Customer Last Name
in ascending order and then by the Customer First Name in ascending order. – 5pts
?
Create a new worksheet called “Misc” (no quotes) and move it directly to the left of the Customers worksheet. 2pts
o Type the following data in the specified cells below (no quotes):
? B3 = “Customer ID#:” – 1pt
? B4 = “Customer First Name:” – 1pt
Page 1 of 5
? C3 = “C041” – 1pt
o In cell C4, create a NESTED function using the IFERROR and VLOOKUP functions.
? This NESTED function will refer to the Customers worksheet to place the corresponding
Customer’s First Name in cell C4 based off the value entered in cell C3 for the Customer ID#. If
the end user types an invalid Customer ID#, they will receive the follow error message, “Please
enter a valid Customer ID#.” – 6pts
(Note: make sure to test your functions several different ways to ensure they are working properly.)
?
On the Misc worksheet:
o Type the following data in the specified cells below (no quotes):
? B6 = “Product ID#:” – 1pt
? B7 = “Product Name:” – 1pt
? C6 = “P089” – 1pt
o In cell C7, create a NESTED function using the IFERROR and VLOOKUP functions.
? This function will refer to the Products worksheet to place the corresponding Product’s Name in
cell C7 based off the value entered in cell C6 for the Product ID#. If the end user types an invalid
Product ID#, they will receive the follow error message, “Please enter a valid Product ID#.” – 5pts
o Adjust the B and C columns’ width so all the information shows within its own column (does not cross
over another column. – 1pt
(Note: make sure to test your functions several different ways to ensure they are working properly.)
?
On the Misc worksheet, apply a cell style of your choice ONLY to the labels in the B column (do not select the
entire columns nor the blank cells in-between the labels). – 2pts
?
On the Misc worksheet, in cell B12, create a NESTED IF function that will determine if the Customer ID# on this
worksheet is equal to C040. – 10pts total
o If it is true, then it will display “Please enter a different Customer ID#.” (without the quotes being
displayed)
o If it isn’t true, then it will check to see if the Product ID# on this worksheet is equal to P089 OR it will
check to see if the Product ID# on this worksheet is equal to P011.
? If it is, it will display “Excellent product selection.” (without the quotes being displayed)
? If it isn’t, it will display “Did you purchase the right item?” (without the quotes being displayed)
(Note: make sure to test your NESTED IF functions several different ways to ensure they are working properly.)
?
On the Misc worksheet, create a button displaying the words “Click Me” (no quotes) on it. – 3pts
o Sync the button to a macro called “Happy_Face” (no quotes) and with the Shortcut key “Ctrl + Shift + H”
for this workbook only. – 5pts
o When the Click Me button is clicked, the Happy_Face macro will run and place an actual happy face of
your choice in cell C15 or this will occur when you press the keyboard shortcut, Ctrl + Shift + H. – 2pts
o Resize and position the button in the range E4:F5. – 1pt
?
On the Misc worksheet, complete the following:
o In cell B17, type “Verification Date:” (no quotes) – 1pt
o In cell C17, create a Data Validation that will only allow dates. – 1pt
? Make sure that the dates are either equal to or after 6/1/2020. – 2pts
Page 2 of 5
?
A Stop message will display with a title and description of your choice if they enter a date before
this date. – 2pts
?
Create a line chart based off the Products table located on the Products worksheet that will depict the Product
IDs and their Price. – 1pt
o Move the chart on its own worksheet (it should be its own CHART SHEET, not a new worksheet where
you copy and pasted the actual chart) and name the worksheet “Products by Price” (no quotes) – 4pts
o Change the Chart Title to “Product Prices” (no quotes) – 1pt
?
On the Sales worksheet, apply Conditional Formatting to the Quantity column of the table that will flag,
highlight, or whatever you like to values greater than 2. – 4pts
?
Filter the Sales worksheet table by Customer ID to show only C004, C006 and C015. – 5pts
?
On the Misc worksheet, only lock cells C4, C7, and B12 properly so that no one can change the functions in those
cells or cannot even select those cells after they are created once the password protection is turned on. – 4pts
o Password protect the Misc worksheet with the password “def” (without the quotes AND lowercase only)
– make sure to specify what can be selected and or changed before protecting the worksheet. – 1pts
?
Lastly, save your MS Excel file with macros-enabled (- 1pt – NOTE: if you do not do this, majority of your macro
work is basically lost), with the following name, and upload this to Moodle under the MS EXCEL FILE UPLOAD –
Final Cumulative Assignment link:
FCA – Your Initials
(Hint: “Your Initials” is where I want you to literally put your initials so I know this is your file) – 1pt
***Ask questions if you have any confusion toward anything please***
PART 2
With your group/partner, you will now create either a MS PowerPoint (PPT) highlighting the major things you just
completed with a minimum of 5 PPT slides and a maximum of 8 PPT slides -7pts
-ORa Screencast-O-Matic video that highlights the major things you just completed with a maximum of 10 minutes – 7pts
Your group/partner will be listed in the Final Cumulative Assignment week of Moodle under your Group #. Please ONLY
use your individual discussion forums’ area to talk to your teammates/partner. If you need to schedule a one-on-one(s)
BlackBoard Collaborate session with your teammates/partner, please email me to let me know and I can schedule that
for the date and time specified and email you both the link to ensure even if I am not present you can all share your
work virtually live with one another. These sessions MUST be recorded (someone will have to manually turn the
recording on). These along with the discussion forums will help me to truly see who worked on what for this second part
of the Final Cumulative Assignment. Also, your group work will be evaluated with feedback surveys as well for all
teammates/partner to help make the grading fair (see Part 3 below).
Page 3 of 5
Do not over think this here. You all just need to create slides or a video that teaches the major things you all just created
AND how they FUNCTION/WORK/OPERATE to TEACH ME (the instructor) what those items do. For instance, explain
how your Conditional Formatting worked in this Workbook, explain how the IF functions conditions/parameters worked,
tell us what your chart is showing and what that means visually when we look at it, or how your macro button actual
works/what does it do. Teaching me the material is what I am looking for more than anything else. Please keep this in
mind the whole time for this portion. -15pts
This is not a MS PowerPoint class, nor is it a Screencast-O-Matic course. I am aware of this, but I would like you all to see
how other software can be incorporated with one another. You all can try to impress me with what you all may
remember from MS PowerPoint, or you can impress me with learning the new software on Screencast-O-Matic. Either
way, you all must advise me of which software you all are going to use for this section by shooting me a Moodle
message or Access email and it just needs to say Your Group’s # and “We are doing an MS PPT for the Cumulative
Assignment” or “We are doing a Screencast-O-Matic video for the Final Cumulative Assignment.” (Hint: “#” is your
actual group’s number) -1pt
***Here are some BAD examples of what I DO NOT want and will result in lost points:
“I placed imported table from MS Access on the Products worksheet.”
“I used a nested IF function to display a particular message.”
“I applied a theme by going to the Page Layout tab and then in the Themes group with the Themes button options.”
If anything like this is reflected on your presentation, points will be deducted. TEACH ME what you have LEARNED.
The KEY thing to remember here is that I want you all to be creative. Show me that you learned something besides just
mimicking what the system or myself wants you all to do. In other words, this is a way for you all to teach me/show me
what you all have learned in this class.
If your group selects the MS PPT option (1 submission per group), you will need to submit the MS PPT presentation via
Moodle under the MSPPT/Screencast-O-Matic UPLOAD – Final Cumulative Assignment link and save your file as: – 1pt
FCA – Group # – PPT
(Hint: “#” is where I want you to literally put your group number so I know this is your group’s file)) – 1pt
If your group selects the Screencast-O-Matic option (1 submission per group), please contact me if you need help getting
to the website, using the software, creating your video, saving your video for publishing, and etc. You will need to copy
your link for your video and paste it into an MSWord document, and then submit that file via Moodle under the MSPPT/
Screencast-O-Matic UPLOAD – Final Cumulative Assignment link and save your file as: -1pt
FCA – Group # – SCOM
(Hint: “#” is where I want you to literally put your group number so I know this is your group’s file) – 1pt
***Ask questions if you have any confusion toward anything please***
Part 3
Lastly, complete the Group Survey (labelled as such) on Moodle to help make the grading process for the Part 2 portion
of this assignment as fair as possible. There are only 5 questions, and this is imperative to help me, as the instructor,
know who all pulled their weight on Part 2 of this assignment. – 10pts
Page 4 of 5
(Note: You can receive up to 150pts in total for this entire assignment.)
Page 5 of 5

Purchase answer to see full
attachment