sam capstone project 1a access modules 1 3

View SAM Project Answer Keys

View the correct answers for SAM projects.

• Click SAM assignments .

• Click the file under Solution File to download it to your computer.
• Open the solution file.

New User? Register

Fill This Form To Receive Instant Help

Homework answers / question archive / Shelly Cashman Access 2019 | Modules 1-3: SAM Capstone Project 1a Economic Development Conference Creating Tables, Queries, Forms, and Reports in a Database     GETTING STARTED Open the file SC_AC19_CS1-3a_FirstLastName_1

Shelly Cashman Access 2019 | Modules 1-3: SAM Capstone Project 1a Economic Development Conference Creating Tables, Queries, Forms, and Reports in a Database     GETTING STARTED Open the file SC_AC19_CS1-3a_FirstLastName_1

Share With -->

Shelly Cashman Access 2019 | Modules 1-3: SAM Capstone Project 1a

Economic Development Conference

Creating Tables, Queries, Forms, and Reports in a Database

• Open the file SC_AC19_CS1-3a_ FirstLastName _1.accdb , available for download from the SAM website.
• 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.
• Support_AC19_CS1-3a_Advertisers.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
• Rename the default primary key ID field to AdvertiserID and change the data type to Short Text . ( Hint: AdvertiserID should remain the primary key.)
• Add a new field with the name AdvertiserName and the Short Text data type.
• Add a third field to the table with the name ContactLName and the Short Text data type.
• Add a fourth field to the table with the name ContactFName and the Short Text data type.
• Add a fifth field to the table with the name Cost and the Currency data type. Save the table with the name Advertisers and close the table.
• Use the Import Spreadsheet Wizard to import the data from the Support_AC19_CS1-3a_Advertisers.xlsx file and append to the Advertisers table. Do not save the import steps.
• Add a field with the name SponsorID and the Short Text data type. Change the field size to 4 .
• Set SponsorID as the primary key for the table.
• Add a field with the name SponsorName and the Short Text data type.
• Add a third field with the name Amount and the Currency data type. Save the table with the name Sponsors and switch to Datasheet View.
• With the Sponsors table open in Datasheet View, add the record shown in Table 1. Resize the SponsorName field so that the field value is completely visible. Save the changes and close the Sponsors table.
• Open the Attendees table in Datasheet View and delete the record for the attendee with an AttendeeID of TL001 .
• Change the name of the Street field using Address as the new field name.
• Add a new field to the end of the table with the name CellPhone and the Short Text data type. Save the changes and close the table.
• Include the AttendeeID , FirstName , LastName , and CellPhone fields in that order.
• Save the query using AttendeeContactInfo as the query name. View the query results and then close the query.
• Include the AdvertiserID , AdvertiserName , and Cost fields.
• Select the Columnar layout.
• Assign the title AdvertiserCostData to the form. View the form and then close it.
• Include the LastName , FirstName , City , and State fields.
• Do not add any grouping levels.
• Sort the records in ascending order by LastName .
• Select the Tabular layout and the Portrait orientation.
• Assign the title AttendeeLocations to the report. View the report and then close it.
• Include the LastName , FirstName , City , and State fields in the query design grid.
• Add criteria to select only those records where Philadelphia is the City.
• Save the query using PhiladelphiaAttendees as the query name. Open the query in Datasheet View and then close it.
• Add the RegistrationFee field to the query design grid immediately following the AttendeeID field.
• Add criteria to select only those records where the GuestFee field value equal 0 and the WorkshopFee equal 0 . Open the query in Datasheet View and confirm that 16 records appear in the GuestAndWorkshop query results. Close the query, saving if necessary.
• Add the Attendees table and the Registration table to the Design window. The tables should be automatically joined by the common field AttendeeID .
• Add the FirstName and LastName fields from the Attendees table to the query design grid
• Add the RegistrationID , RegistrationFee , and WorkshopFee fields from the Registration table.
• Save the query using AttendeeRegistration as the query name. Open the query in Datasheet View and then close it.
• Move the State field so that it is the first field in the query design grid.
• Add criteria to select only those records where the value in the State field is NY or NJ .
• Sort the records in ascending order by State and then by LastName . Open the query in Datasheet View and confirm that it matches Figure 1. Close the query, saving if necessary.
• Use the State field for the row headings.
• Use the City field for the column headings.
• Use a Count of AttendeeID as the calculated value for each row and column intersection.
• Save the query using State_CityCrosstab as the query name. View the query and then close it.
• Open the AttendeeFirstName query in Design View and add criteria to select only those records where the FirstName field value begins with Lau followed by any other letters. Save the changes to the query. Open the query in Datasheet View, confirm that two records appear in the query results, and then close it.
• Open the StateAttendees query in Design View. Add parameter criteria to the State field to replace the current "PA" criteria. The new parameter criteria should prompt the user with Enter desired state as the text. Save the query, then view it in Datasheet View. Enter IL when prompted. Confirm that three records appear in the query results and then close it.
• Open the TotalFees query in Design View. Modify the query by creating a calculated field that sums the total of RegistrationFee , GuestFee , and WorkshopFee . Use TotalFees as the name of the calculated field. Save the query. Open the query in Datasheet View and then close it.
• Open the ConferenceFees query in Design View. Modify the query by sorting the records in descending order by the calculated field ConferenceFees and display only the top 5% of the records. Save the query. Open the query in Datasheet View and then close it.
• Open the SortedStates query in Design View. Modify the query to sort the State field in ascending order. Each state should appear only once. Save the query. Open the query in Datasheet View and then close it.
• Add the RegistrationFee , GuestFee , and WorkshopFee fields to the query design grid.
• Add the Total row to the query design grid.
• Calculate the sum of each of the three fields.
• Save the query using AggregateFees as the query name. Open the query in Datasheet View, confirm that it matches Figure 2, and then close it.
• Make ExhibitorID the primary key of the table.
• Change the field size of the ExhibitorID field to 4 .
• Change the data type for the BoothCost field to Currency with 0 decimal places.
• Add the text Basic cost to rent a 10 x 10 booth as the description for the BoothCost field. Save the changes to the table and close it. (Hint: Because a field size was reduced, a warning message appears asking if you want to test the data, The data is valid, so ignore this message and continue saving the table.)
• Add a field with the name Status to the end of the table.
• Select LookupWizard at the data type.
• Select the option to type in your own values.
• Type in the following three values (in the order shown) as the list of possible values for the field: Member , NonMember , and Student . Limit the field values to only the items in the list and do not allow multiple values for the field. Save the changes to the table and close it.
• Most of the attendees are members of the Economic Development Professional Organization. Use an Update query to update the values in the Status field of the Attendees table to Member for all records. Run the query and save it using UpdateStatus as the name of the query.  
• Open the Attendees table in Datasheet View and change the Status value for the attendee with an AttendeeID of CB001 (Cynthia Brown) to Student . Add the caption PostalCode to the ZIP field. Close the table.
• Create a split form for the Registration table. Save the form using Registration Split Form as the form name. Close the form.
• Select all the fields from the PotentialDonors table in the same order that they are listed in the field list.
• Select Sponsors as the destination table in the Append dialog box.
• Save the query using AppendPotentialDonors as the name, run it, and then close it. The query should append 10 records.
• Open the Relationships window and add the Attendees table and the Registration table to the Relationships window. Create a relationship between the AttendeeID field in the Attendees table and the AttendeeID field in the Registration table. Make the relationship enforce referential integrity . Save the relationship and close the window.
• Select the Attendees table as the table to display in the query results.
• Select the Registration table as the related table.
• Select the AttendeeID as the common field in both tables.
• Display all fields in the query result.
• Save the query using UnmatchedRegistration as the query name. View the query results, confirm there is one record, and then close it.
• Add criteria to select only those records where the RegistrationFee field value equals 175 .
• In the Make Table dialog box, assign the name EarlyRegistration to the new table.
• Save the query using MakeEarlyRegistration as the query name, run it, and then close the query. The new EarlyRegistration table should contain 13 records.
• Enter a validation rule for the RegistrationFee field to ensure that values in the field are greater than or equal to 100 .
• Enter the text, Must be greater than or equal to $100 in the Validation Text property box. Save the changes to the Registration table and close it. ( Hint: Because a validation rule was added, a warning message appears asking if you want to test the data. The data is valid, so ignore this message and continue saving the table.)
• Select the SponsorName field from the Sponsors table.
• Add criteria to delete only those records where Comor is the SponsorName.
• Save the query using DeleteSponsor as the query name, run it, and then close it. The query should delete one record.
• Sort the records in ascending order by SponsorName .
• Add the Total row to the datasheet and calculate the sum for the Amount field. Save the changes to the table and close it.

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.

Fill The Details Below To Receive Instant

Homework help, low cost option, download this past answer in few clicks, purchase solution.

Already member? Sign In

Custom new solution created by our subject matter experts

Get a quote.

GET ANSWER TO THIS QUESTION

100% UNIQUE SOLUTION WITHIN FEW HOURS

rated 5 stars

Purchased 39 times

View Answer

Sitejabber (5.0)

Trustpilot (4.9)

Google (5.0)

Related questions.

• Shelly Cashman Access 2019 Modules 13 SAM Capstone Project 1b The Preserve Creating Tables Queries Forms and Reports in a Database GETTING STARTED Open the file SCAC19CS13bFirstLastName1
• New Perspectives Access 2019 Module 6 End of Module Project 1 Homestead Partners Working with forms and reports GETTING STARTED Open the file NPAC19EOM61FirstLastName1
• New Perspectives Access 2019 Module 1 Sam Project 1a Global Human Resources Consultants CREATING AND MODIFYING TABLES AND OTHER DATABASE OBJECTS GETTING STARTED Open the file NPAC191aFirstLastName1
• The Prestige Hotel chain caters to upscale business travellers and provides stateoftheart conference meeting and reception facilities
• New Perspectives Access2019 Module 1 Textbook Project New Perspectives Access2019 Module 2 Textbook Project Here both the textbook projects have been answered as per textbook information
• New Perspectives Access 2019 Module 3 End of Module Project 1 Homestead Partners Creating and Improving Queries GETTING STARTED Open the file NPAC19EOM31FirstLastName1
• New Perspectives Access 2019 Module 7 SAM Critical Thinking Project 1c Global Human Resources Consultants Modifying Advanced Reports GETTING STARTED Open the file NPAC19CT7cFirstLastName1
• Microsoft Access Database questions If you received a sales report of sales by particular insurance agents 1
• Shelly Cashman Access 2019 Module 8 SAM Project 1b City Tours Ltd
• New Perspectives Access 2019 Module 2 End of Module Project 1 New Perspectives Access 2019 Module 2 End of Module Project 1 Homestead Partners Improving an Access Database GETTING STARTED Open the file NPAC19EOM21FirstLastName1

Help in Homework

• Don't have an account. Register Now
• Forgot Password?. Reset Password

Please enter your email address and we'll send you instructions on how to reset your password

• Already have an account? Log In

Modules 1-4 - SAM Capstone Project 1a

Description

Topics covered in this video:

• Carmelo Miraglia is an executive consultant at Tallridge Regional Medical Center. Carmelo is building a workbook that summarizes income and expenses for the organization. Change the theme of the workbook to Office.
• On the Departments worksheet, change the width of columns C through H to 12.00 characters.
• Change the height of row 1 to 21 points.
• Merge and center the contents of the range A2:J2.
• Format the merged range A2:J2 as described below: a. Apply the 40% - Accent 5 cell style. b. Apply bold formatting. c. Change the font size to 14.
• Enter the values shown in Table 1 into the corresponding cells in the range A5:E5. Table 1: Data for the Range A5:E5

A B C D E 5 Abbreviation Department MD RN Support

• Format the range A5:J5 as described below: a. Center cell contents. b. Change the font size to 11 pt. c. Change the background color to Blue, Accent 5, Lighter 40% (9th column, 4th row of the Theme Colors palette). d. Apply Wrap Text to the text in each cell.
• Select the range A6:J14 and then add a White, Background 1 border to all sides of each cell.
• Select the range A14:J14 and then add a thin top border to each cell using the Automatic color.
• Select the range H6:H13 and then format the range as described below: a. Format the range with the Percentage number format with zero decimal places. b. Add a Highlight Cells conditional formatting rule that formats cells that are greater than 0.24 as light red fill with dark red text.
• Select the range I6:I13 and then use conditional formatting to add solid orange data bars.
• Select the range J6:J13 and then add top/bottom conditional formatting rules to format the top 10% of values as green fill with dark green text and the bottom 10% of values as light red fill with dark red text.
• Enter a formula in cell M7 using the VLOOKUP function to find an exact match for the department abbreviation. Look up the department abbreviation (cell M6) using an absolute reference, search the staff table data (the range A6:J13) using an absolute reference, and return the department name (the 2nd column).
• Copy the formula in cell M7 to the range M8:M15, and edit the copied formulas to return the value from the column indicated by the label in column L.
• In cell C17, enter a formula using the TODAY function that displays the current date.
• Delete column O.
• Hide row 18.
• On the Budget worksheet, create a 2-D pie chart based on the non-adjacent range A5:A12 and F5:F12. Modify the chart as described below: a. Resize and reposition the chart so that the upper-left corner is located within cell H4 and the lower-right corner is located within cell O22. b. Apply Chart Style 3 to the chart. c. Enter 2022 Budget by Department as the chart title.
• In the 2018 Budget by Department 2-D pie chart (located in the range H23:O43), make the following changes: a. Change the data labels to display only the percentage and a label position of Center. (Hint: Select Reset Label Text to remove category names) b. Reposition the legend on the right side of the chart.
• Update the Departmental Budget Comparison 2018-2022 combo chart in the range A15:G36 as follows: a. Change the minimum bound of the right vertical axis to 40,000,000. b. Add axis titles to the chart. Use Department Budgets as the left vertical title, use Total Budget as the right vertical title, and remove the horizontal axis title. c. Apply a shape fill to the chart using the Blue, Accent 5, Lighter 80% fill color.
• Delete the Hiring worksheet.

Support 💖 Buy me a coffee: https://www.buymeacoffee.com/taandar Join Patreon: https://www.patreon.com/Taandar

#cengage #solutions #sam #excel

Disclaimer: Our videos are intended solely for educational purposes, with the goal of assisting students in understanding how to solve problems in various courses. We want to emphasize that any attempt to copy or replicate the solutions shown in these videos may be regarded as a breach of academic integrity policies at your college or university. We strongly recommend that you use these videos as a guide and as a tool to support your learning, but we urge you to complete your assignments independently and refrain from cheating or plagiarism. Please be aware that we are not liable for any consequences resulting from the misuse of these videos in violation of academic integrity policies.

1 00:00:00,000 --> 00:00:04,980 so this is Capstone project 1A and step

2 00:00:03,179 --> 00:00:06,720 1 it is saying

This is member-only video. Contact [email protected] if you are confused.

Recommended Books

Reading books is a great way to learn. Here are some of the books we recommend.

Word Module 3 SAM Project 1a Part 1

New Perspectives Access 2019 | Modules 5-8: SAM Capstone Project 1a

• February 24, 2024
• Do my Online Course

Midwest Executive Professionals

IMPROVING TABLES, QUERIES, FORMS, AND REPORTS

Open the file  NP_AC19_CS5-8a_ FirstLastName _1.accdb , available for download from the SAM website.

Save the file as  NP_AC19_CS5-8a_ 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_CS5-8a_StateProvs.csv

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 this project, you will improve an existing database by modifying tables, queries, forms, and reports. Open the  JobSeekers  table in Design View and complete the following:

After the existing  Phone  field add a new field named  Email  with a Hyperlink data type.

Add a second new field named  Comments  with a Long Text data type. Save and close the  JobSeekers  table.

Open the  Jobs  table in Design View and complete the following:

Add  Salary  as the caption property to the  StartingSalary  field.

Add  >=Date()  as the Validation Rule property for the  PostingDate  field.

Add  Date must be on or after today  as the Field Validation Text property for the  PostingDate  field. Save and close the  Jobs  table. If prompted to test data integrity click  Yes .

Open the  Companies  table in Design View and complete the following:

Use the Lookup Wizard to lookup values for the  Industry  field.

The lookup field should get its values from the  Industries  table.

Select the  Industry  field and sort the records in ascending order by the  Industry  field.

Widen the  Industry  field to get the best fit for all of the values in the list.

Enable data integrity on the relationship and use  Industry  as the label for the lookup field.

Save the table if prompted.

Switch to Datasheet View then choose  Investment Banking & Asset Management  for the  Industry  field for  CompanyID 2 (Des Moines Financial Group).  Close the  Companies  table.

Open the  ApplicationListing  query in Design View and complete the following:

Add  >=[Enter minimum salary]  as parameter criteria to the  StartingSalary  field.

Run the query using  60000  when prompted.  Save and close the  ApplicationListing  query.

Open the  IndustryAnalysis  query in Design View and complete the following:

Add the Total row to the query grid.

Group By the  Industry  field, Count the  CompanyName  field, and Avg (average) the  StartingSalary  field.

Switch to Datasheet View and widen each column to see all data and field names as shown in Figure 1 with the following measurements: Change the  Industry  column field width to  35 , the  CountOfCompanyName  to  22 , and the  AvgOfStartingSalary  to  19 . Save and close the  IndustryAnalysis  query.

Figure 1: Final IndustryAnalysis Query in Datasheet View

Start a new query in Design View and complete the following:

Add the  Companies  and  Jobs  tables.

Add the  Industry  field from the  Companies  table and the  JobTitle  and  StartingSalary  field from the  Jobs  table to the query grid.

Change the query from a Select query to a Crosstab query.

Put the  Industry  field in the Column Heading area and the  JobTitle  field in the Row Heading area.

In the Value area, Avg (Average) the  StartingSalary  field.

Use  JobCrosstab  to name and save the query.

Switch to Datasheet View and widen each column to see all data and field names as shown in Figure 2.  Save and close the  JobCrosstab  query.

Figure 2: Final JobCrosstab Query in Datasheet View

Open the  JobSeekerEntry  form in Design View and complete the following:

Add a combo box to the Form Header section at about the 4″ mark on the horizontal ruler using the Combo Box Wizard.

The combo box should find a record.

Select the  LastName  and then  FirstName  fields for the combo box list.

Use  FIND  as the label for the new combo box.

Save the form and open it in Form View.

Use the new combo box to find the  Thompson, Christina  record. Close the  JobSeekerEntry  form.

Open the  JobsEntry  form in Design View and complete the following:

Add a subform control below the  Posting Date  label using the Subform Wizard.

The subform should contain all the fields from the  ApplicationDates  query.

Show  ApplicationDates  for each record in  Jobs  using  JobID .

Use the default name for the subform. Save the  JobsEntry  form, open it in Form View, and then close it.

Open the  CompanyEntry  form in Design View and complete the following:

Right-align the right edges of the labels in the Detail section.

Right-align the content within the labels in the Detail section. Save the  CompanyEntry  form.

With the  CompanyEntry  form still open in Design View, change the Record Source to the  CompaniesSortedByName  query.

With the  CompanyEntry  form still open in Design View, change the tab order in the Detail section so that  CompanyID  is the first item in the list. Save the form and switch to Form View as shown in Figure 3, and then close the  CompanyEntry  form.

Figure 3: Final CompanyEntry Form in Form View

Open the  JobListing  report in Design View and add a horizontal line above the text box in the Report Footer section that sums the  StartingSalary . The line should be just above and approximately the same width as the text box. The line will be positioned between the Report Footer section bar and the text box and be approximately 1.7″ wide that is the width of the text box. Save the  JobListing  report.

With the  JobListing  report still open in Design View complete the following:

Add the  PostingDate  field to the left side of the Detail section.

Delete the  PostingDate  label.

Position the left edge of the  PostingDate  text box at about the 1″ mark on the horizontal ruler.

Add a label to the Report Footer section at about the 1″ mark on the horizontal ruler.

Use  Internal Use Only  as the caption for the label. Save the  JobListing  report.

With the  JobListing  report still open in Design View, add a sort order under the existing  CompanyName  grouping field to sort the records in ascending order on the  JobTitle  field. Save the report and display the  JobListing  report in Print Preview as shown in Figure 4, then close the  JobListing  report.

Figure 4: Final JobListing Report in Print Preview

Open the  JobSeekerListing  report in Design View and complete the following:

Expand the Report Footer section by about 0.5″.

Add a text box to the Report Footer section at about the 1″ mark on the horizontal ruler and use the expression of  =Count([LastName])  as the Control Source for the text box.

Delete the new label in the Report Footer section. Save the  JobSeekerListing  report, preview it, then close it.

Copy the  CompanyListing  report and paste it as the  CompanyInfo  report. 

Open the  CompanyInfo  report in Design View and change the Record Source to the  CompaniesSortedByName  query. Save the  CompanyInfo  report.

With the  CompanyInfo  report still open in Design View, add page numbers to the report in an N of M format to the center of the Page Footer. Save the  CompanyInfo  report, preview it, then close it.

Open the  JobSalaries  report in Design View and use the Chart Wizard to create a chart at about the 0.5″ mark on the horizontal ruler in the Report Footer section with the following information:

Base the chart on the  JobSalaryIndustry  query.

Include the  StartingSalary  and  CompanyName  fields.

Select a Bar Chart.

Use the  CompanyName  in the Axis area and the sum of the  StartingSalary  field in the Data area.

Do not link the report and the chart.

Use  Salary Total By Industry  as the title for the chart and do not display a legend.

Resize the chart to be about 2.75″ tall and 7″ wide. Save the  JobSalaries  report, preview it as shown in Figure 5, and close it.

Figure 5: Final JobSalaries Report in Print Preview

Export the  Companies  table as an HTML Document to the default folder with the default file name with the following information:

Do not include formatting and layout.

Save the export steps using  Export-Companies to HTML  for the name.

Do not include an export Description.

Export the  CompanyListing  report as an HTML Document to the default folder with the default file name with the following information:

Do not select an HTML template.

Use default encoding.

Save the export steps as  Export-CompanyListing to HTML  for the name.

Import the  Support_AC19_CS5-8a_StateProvs.csv  file as a new table with the following information:

The file is in a comma-delimited format.

The first row contains field names.

Do not modify the fields.

The  StateAbbrev  field should be the primary key.

Use  StatesProvs  as the new table name.

Do not save the import steps.

Export the  Jobs  table in an XML format to the default folder with the default file name with the following information:

Export both the Data XML and Schema XSD files.

Save the export steps as  Export-Jobs to XML  for the name.

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.

Related Services

Cengage SAM Capstone Project Help