Illustrated Access 2019 | Modules 1-4: SAM Capstone Project 1a

Midwest Executive Professionals

Illustrated Access 2019 | Modules 1-4: SAM Capstone Project 1a

Improve a database by creating and modifying tables, queries, forms, and reports

GETTING STARTED

Open the file IL_AC19_CS1-4a_FirstLastName_1.accdb, available for download from the SAM website.

Save the file as IL_AC19_CS1-4a_FirstLastName_2.accdb by changing the “1” to a “2”.

If you do not see the .accdb file extension in the Save As dialog box, do not type it. The program will add the file extension for you automatically.

To complete this SAM Project, you will also need to download and save the following data files from the SAM website onto your computer:

Support_AC19_CS-1-4a_Industries.xlsx

Open the _GradingInfoTable table and ensure that your first and last name is displayed as the first record in the table. If the table does not contain your name, delete the file and download a new copy from the SAM website.

PROJECT STEPS

Midwest Executive Professionals is a firm that connects professional job seekers with job openings. You work in the Research Department of Midwest Executive Professionals as a research analyst. You are developing an Access database application to help manage and analyze companies, jobs, applicants, and job placements facilitated by your company. In Design View of the Applications table, modify the table with the following instructions:

Add a new field after the ApplicationDate field named FollowupDate with a Date/Time data type. 

Use ApplicationID to rename the ID field. Save and close the Applications table.

In Datasheet View of the JobSeekers table, complete the following instructions:

Delete the record for Richard Marlay (JobSeekerID 14).

Edit John to be Jonathan for JobSeekerID 10.

Enter a new record into the table using the information in Table 1. Close the JobSeekers table.

Table 1: New Record for JobSeekers Table

JobSeekerID

(AutoNumber will automatically number this field)

FirstName

Leonardo

LastName

Eagan

Phone

5551118887

In the Navigation Pane, make Industries the new name for the Categories table.

Use the import wizard to append the records from the Support_AC19_CS-1-4a_Industries.xlsx to the Industries table. Do not save the import steps. (Hint: If a warning message appears, Click Open.)

Create a new table in Table Design View with the following specifications:

Enter Employees as the name of the new table.

Add a new field named EmployeeID with the Numberdata type. Set it as the Primary key field.

Add two new fields named EmployeeFirst and EmployeeLast in the order shown. They should have the Short Text data type and field size properties of 20.

Add a new field named Email with the Hyperlink data type. Set the Required property to Yes.

Add a new field named Salary with the Currency data type. Set the Default Value property to 45000.

Add a new field named Extension with the Short Textdata type. Update the Input Mask property using the Input Mask Wizard. Select the Extension input mask and update the field to 9999, so that 9999;;_ is the final input. Accept the default in the last window. Save the table and then close it.

In Datasheet View of the JobSeekers table, complete the following options:

Delete the Comments field. (Hint: It currently does not have any data.) 

Move the Phone field to be the last field in the datasheet.

Sort the records in ascending order by LastName. Save and close the JobSeekers table.

Create a new query in Design View using the Companies and Jobs tables with the following options:

Select the CompanyName and Industry fields from the Companies table and the JobTitle and StartingSalaryfields from the Jobs table.

Join the tables using the CompanyID field from the Companies table and the Company field from the Jobstable.

Save the query, using HighSalaries as the name.

With the HighSalaries query still open in Design View, add criteria to select only those records where the StartingSalary is greater than or equal to 50000, save the query, switch to Datasheet View as shown in Figure 1, and then close it.

Figure 1: HighSalaries Query in Datasheet View

Use the Simple Query Wizard to create a new query with the following options:

Use the JobSeekers and Applications tables. 

Select all four fields from the JobSeekers table and the ApplicationDate field from the Applications table. 

Create a detail query.

Use ApplicationDates as the title for the query. Save and close the query.

In Design View of the ApplicationListing query, modify the query with the following options:

Add criteria to select all records with Administrative Assistant or User Support Director as the JobTitle. 

Sort the records in ascending order by LastName, and then by FirstName. Run the query to display it in Datasheet View as shown in Figure 2, and then save and close it.

Figure 2: ApplicationListing Query in Datasheet View

In Design View of the DirectorSeptember query, add criteria using wildcard characters to select all records with Director anywhere in the JobTitle field and an ApplicationDate greater than or equal to 9/1/2018. Run the query to display it in Datasheet View as shown in Figure 3, and then save and close it.

Figure 3: DirectorSeptember Query in Datasheet View

Use the Form Wizard to create a new form with the following options:

Select all of the fields from the Jobs table. 

Choose a Columnar layout.

Use JobsEntry as the title. Save and close the JobsEntry form.

Use the Report Wizard to create a new report with the following options:

Select all of the fields from the Companies table. 

Group the records by Industry.

Sort the records in ascending order by CompanyName.

Use a Stepped layout and a Portrait orientation.

Use CompanyListing as the title. Preview, save, and close the report.

Open the JobSeekerEntry form in Form View, enter Jacob as the FirstName value for the first record to replace Jack, and then add a new record in the main form with the information in Table 2.

Table 2: New Record for JobSeekerEntry Form

JobSeekerID

(AutoNumber field will automatically increment)

FirstName

Camilla

LastName

Escobar

Open the JobSeekerEntry form in Layout View and modify it with the following options:

Right-align the text in the JobSeekerID, FirstName, and LastName labels. 

Edit the FirstName label to read First Name and use Last Name to rename the LastName label. 

Delete the Close command button in the Form Footer section.

With the JobSeekerEntry form still open in Layout View, modify it with the following options:

Add the Phone field to the form and position it just below the Last Name label. 

Change the font color for the Phone label to Automatic (black). 

Apply a quick style, Colored Outline – Blue, Accent 1(first 1st row second 2nd column of Theme Styles gallery) to the Close command button in the Form Header section. Display the form in Form View as shown in Figure 4, and then save and close it. 

Figure 4: JobSeekerEntry Form in Form View

Open the JobListing report in Layout View and add conditional formatting so that any StartingSalary value greater than or equal to $70,000 is formatted using Green 3 background color (7th column, 4th row in the Standard Colors pallete). Save the JobListing report.

With the JobListing report still open in Layout View, modify it with the following options:

Change the background color of the Starting Salary label in the Page Header section to the same color, Green 3 (7th column 4th row in the Standard Colors palette). 

Group the two labels in the Report Header section together in a tabular layout. Save and display the report in Report View as shown in Figure 5, and then close it. 

Figure 5: JobListing Report in Report View

In Layout View of the JobSeekerListing report, modify it with the following options:

Change the Theme Colors to Green .

Change the Theme Fonts to Franklin Gothic. Display the report in Report View as shown in Figure 6, and then save and close it.

Figure 6: JobSeekerListing Report in Report View

Save and close any open objects in your database. Compact and repair your database, close it, and then exit Access. Follow the directions on the SAM website to submit your completed project.

APA FORMATE

ADD REFERENCES      AT END 

Barbara just received the good news: She was assigned as the project manager for a project that her company won as part of competitive bidding. Whenever a request for proposal (RFP) comes into Barbara’s company, a committee composed mainly of senior managers reviews the RFP. If the decision is made to bid on the job, the RFP is turned over to the Proposal Department. Part of the Proposal Department is an estimating group that is responsible for estimating all work. If the estimating group has no previous history concerning some of the deliverables or work packages and is unsure about the time and cost for the work, the estimating team will then ask the functional managers for assistance with estimating. 

Project managers like Barbara do not often participate in the bidding process. Usually, their first knowledge about the project comes after the contract is awarded to their company and they are assigned as the project manager. Some project managers are highly optimistic and trust the estimates that were submitted in the bid implicitly unless, of course, a significant span of time has elapsed between the date of submittal of the proposal and the final contract award date. Barbara, however, is somewhat pessimistic. She believes that accepting the estimates as they were submitted in the proposal is like playing Russian roulette. As such, Barbara prefers to review the estimates. 

One of the most critical work packages in the project was estimated at twelve weeks using one grade 7 employee full time. Barbara had performed this task on previous projects and it required one person full time for fourteen weeks. Barbara asked the estimating group how they arrived at this estimate. The estimating group responded that they used the three-point estimate where the optimistic time was four weeks, the most likely time was thirteen weeks, and the pes- simistic time was sixteen weeks. 

Barbara believed that the three-point estimate was way off of the mark. The only way that this work package could ever be completed in four weeks would be for a very small project nowhere near the complexity of Barbara’s project. Therefore, the estimating group was not con- sidering any complexity factors when using the three-point estimate. Had the estimating group used the triangular distribution where each of the three estimates had an equal likelihood of occurrence, the final estimate would have been thirteen weeks. This was closer to the fourteen weeks that Barbara thought the work package would take. While a difference of 1 week seems small, it could have a serious impact on Barbara’s project and incur penalties for late delivery. 

1. ©2010 by Harold Kerzner. Reproduced by permission. All rights reserved. 

Case Study 735 

Barbara was now still confused and decided to talk to Peter, the employee that was assigned to do this task. Barbara had worked with Peter on previous projects. Peter was a grade 9 employee and considered to be an expert in this work package. As part of the discussions with Barbara, Peter made the following comments: 

I have seen estimating data bases that include this type of work package and they all esti- mate the work package at about 14 weeks. I do not understand why our estimating group prefers to use the three point estimate. 

“Does the typical data base account for project complexity when considering the esti- mates?” asked Barbara. Peter responded: 

Some data bases have techniques for considering complexity, but mostly they just assume an average complexity level. When complexity is important, as it is in our project, analogy estimating would be better. Using analogy estimating and comparing the complexity of the work package on this project to the similar works packages I have completed, I would say that 16–17 weeks is closer to reality, and let’s hope I do not get removed from the project to put out a fire somewhere else in the company. That would be terrible. It is impossible for me to get it done in 12 weeks. And adding more people to this work package will not shorten the schedule. It may even make it worse. 

Barbara then asked Peter one more question: 

Peter, you are a grade 9 and considered as the subject matter expert. If a grade 7 had been assigned, as the estimating group had said, how long would it have taken the grade 7 to do the job? 

QUESTION 1

1. The ways that HLOOKUP formula works is searching down instead of across data. True
   False

0.9 points   

QUESTION 2

1. The AND formula takes a list of Booleans and returns TRUE if all of them are true, and FALSE otherwise. True
   False

0.9 points   

QUESTION 3

1. How would Excel evaluate the following formula? =OR(7<5, A2=6,NOT(“zebra”<=”tree”))? Assume that the formula is in cell D4, the workbook contains no circular references, and outside of cell D4 the workbook contains no errors.TRUEFALSEIt would return an errorThere is insufficient information to answer this question

1.4 points   

QUESTION 4

1. One of the benefits of using the Table feature is that you’ll be able to see the column titles at every moment. True
   False

0.9 points   

QUESTION 5

1. Use the brewery.xlsx file to answer the following question: How many total transactions took place at Beerland Amherst?3527425844258542194315939

0.9 points   

QUESTION 6

1. Use the file Code_Enforcement_-_Building_and_Property_Violations.xlsx for this question.
   What is the total dollar amount of penalties for closed cases in Charlestown due to residents’ improper storage of trash (improper storage trash:res)?

1.4 points   

QUESTION 7

1. Use the Brewery.xlsx file to answer the following question: What s the name of the beer with the highest Alcohol by Volume (ABV)?Half LordGear ShiftSheep MojoRed Salty HoserGnu Breath

0.9 points   

QUESTION 8

1. The PivotTable functionality is located in the Data tab. True
   False

0.9 points   

QUESTION 9

1. Use the Brewery.xlsx file to answer the following question: How many beers product names have order quantities between 3000 and 12000?75689

1.4 points   

QUESTION 10

1. Which of the following functions returns a range of cells?OFFSETCOUNTAMATCHREDEFINENone of the given choices

0.9 points   

QUESTION 11

1. A named range can be usedwhen creating chartsin formulasin functionsnone of the given choicesall of the given choices

0.9 points   

QUESTION 12

1. Which of the following statements is correct if new data will be attached as a new row to the existing table?OFFSET( base cell, 0, 0, 1, COUNTA (larger range in row where potential data may appear) )OFFSET( base cell, 0, 0, COUNTA (larger range in row where potential data may appear),1)OFFSET( base cell, COUNTA (larger range in row where potential data may appear), 0, 0,1)OFFSET( base cell, 0, COUNTA (larger range in row where potential data may appear), 0,1)None of the given choices

1.4 points   

QUESTION 13

1. One of the primarily characteristics of Solver is the mathematical relationship between the objective, the constraints, and the decision variables. True
   False

0.9 points   

QUESTION 14

1. The Solver and Analysis Toolpak Add-ins are not built in to Excel. True
   False

0.9 points   

QUESTION 15

1. Use solver to answer the following question: A manufacturing concern wishes to minimize its shipping costs between its 4 factories and 3 warehouses. Factory 1 supplies 1000 units per week and costs $5, $3, and $4 to ship each unit to Warehouses 1, 2, and 3 respectively. Factory 2 supplies 1200 units each week and costs $4, $3, and $3 to ship to Warehouses 1, 2, and 3. Factory 3 supplies 1500 units and costs $6, $2, and $5 to ship to the three warehouses. Factory 4 supplies 1800 units and costs $6, $2, and $4.
   If Warehouse 1 requires 3000 units per week, Warehouse 2 demands 1000, and Warehouse 3 demands 1500, what is the minimum it would cost them in shipping to fulfill each warehouse’s demand?

Lab 4

Complete the following in Chapter 6 Problems: #31 , #34  from textbook (Page #282, #283). 

Excel data has been attached below.

Work on the problems and clearly explain answering the questions in word document.Provide the worked on excel files

perform the below instructions to the po2c1women_lastfirst  powerpoint file the other two files will be needed to complete the assignment

Outline and Modifications

Based on the storyboard you created after researching women in STEM on the Internet, you type the outline of your presentation. As you create the outline, you also modify the outline structure.

• a.Open Outline view.
• b.Type Name 3 women in STEM as the title for Slide 2.
• c.Type each of the following as Level 1 bullets for Slide 2: My biology teacher, My computer applications teacher, My math teacher.
• d.Type Think on a bigger scale as the title for Slide 3. Enter each of the following as Level 1 bullets for Slide 3: National names?, and International names?
• e.Add this speaker note to Slide 3: These may be hard questions to answer quickly because there are relatively few women in these fields.
• f.Type Here are some names to get you started as the title for Slide 4.
• g.Type each of the following as Level 1 bullets for Slide 4: Sally Ride, Christa McAuliffe.
• h.Add this speaker note to Slide 4: For different reasons, both of these women were important in the development of the aerospace industry.

Imported Outline

You have an outline on women in STEM that was created in Microsoft Word and also a slide show on that topic. You reuse this content to build your slide show.

• a.Position the insertion point at the end of the outline after Slide 4.
• b.Use the Slides from Outline option to insert the p02c1Stem outline.
• c.Delete Slide 5 and any blank slides.
• d.Demote the last two bullets on the new Slide 5.
• e.Click the first bullet on Slide 6. Cut and paste the text after the name and date from the bullet point to the Notes pane. Replace She with Hypatia. Repeat for the remaining two bullets.
• f.Delete all text after physics for the first bullet of Slide 7. Replace the comma with a period.
• g.Position the insertion point at the end of the outline.
• h.Reuse Slides 2 and 3, using the same order, from p02c1Work to add two slides to the end of the presentation.
• i.Modify the outline structure by reversing slides 8 and 9.

Design

The content of some of the imported slides does not fit well and the font colors are not uniform across all of the slides. You want to adjust the layout and font color to create a well-designed presentation. Then you decide to view a slide show to verify your changes.

• a.Switch to Normal view. Change the layout of Slide 9 to Blank.
• b.Check Slides 5–7 to ensure the title placeholder font is Arial Black (Heading) with the color set to Black, Text 2. Check the subtitle font to Arial Body with the color set to Grey 80%, Text 1.
• c.Use the spelling checker and proofread the presentation.
• d.View a slide show from the beginning.
• e.Move Slide 5 to just before Slide 8.

Sections

To facilitate organization of the presentation and moving between the slides, you create sections.

• a.Add a section before Slide 2 and rename it Quiz.
• b.Add a section before Slide 5 and rename it History.
• c.Add a section before Slide 7 and rename it Reasoning.
• d.Print the outline as directed by your instructor.
• e.Save and close the file. Based on your instructor’s directions, submit p02c1Women_LastFirst.