Computer Science
Seminole State College of Florida Access 2 Model 2019 Project

Seminole State College of Florida

Question Description

I’m trying to learn for my Computer Science class and I’m stuck. Can you help?

Download and work through all instructions and steps for this assignment in SAM to create this project file.

  • Pay close attention to details and make sure your file matches the figures provided in the instructions before submitting
  • You may edit and submit this assignment

Take a look of the picture I add so you can understand my problem with this file.

Unformatted Attachment Preview

Shelly Cashman Access 2019 | Module 2: SAM Project 1a Personal Insurance, Inc. CREATING QUERIES GETTING STARTED • Open the file SC_AC19_2a_FirstLastName_1.accdb, available for download from the SAM website. • Save the file as SC_AC19_2a_FirstLastName_2.accdb by changing the “1” to a “2”. o • To complete this SAM Project, you will also need to download and save the following data files from the SAM website onto your computer: o • 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_2a_UmbrellaData.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 1. Personal Insurance, Inc. is a national company that insures homeowners and renters. It also offers umbrella policies that provide additional coverage. As a regional manager, you need to be able to query the database to help make decisions and to answer questions from other employees. Create a query based on the Customers table in Query Design View with the following options: 2. 3. a. Add the CustomerID, FirstName, LastName, and DateOfBirth fields to the design grid in that order. b. Sort the records in ascending order by LastName. c. Save the query using CustomerLastNameSorted as the name. Open the query in Datasheet View, then close it, saving if necessary. Open the HomeownersPremium query in Design View and make the following changes to the query: a. Delete the CustomerID column from the design grid. b. Add criteria to select only those records where the Premium field value is greater than 2,000. c. Save the changes to the HomeownersPremium query. Open the query in Datasheet View, then close it, saving if necessary. Open the LiabilityOrProperty query in Design View and add criteria to select only those records where the Liability field values equal 75,000 or the PersonalProperty field values equal 75,000. Save the changes to the query. Open the query in Datasheet View, confirm that 3 records appear in the LiabilityOrProperty query results, then close the query, saving if necessary. Shelly Cashman Access 2019 | Module 2: SAM Project 1a 4. Open the ComparisonBirth query in Design View and make the following changes to the query: a. Add the FirstName field to the query design grid. The FirstName field should immediately follow the CustomerID field. b. Add criteria to select only those records where the DateOfBirth field value is less than 1/1/1950. c. Set the format to Medium Date for the DateOfBirth field. d. Set the caption to BirthDate for the DateOfBirth field. e. Save the changes to the ComparisonBirth query. Open the query in Datasheet View, confirm that the results match Figure 1, then close it, saving if necessary. Figure 1: ComparisonBirth query results 5. 6. Open the StateCitySort query in Design View and make the following changes: a. Move the State field to the beginning of the design grid so that the order of the fields in the grid is State, City, FirstName, and LastName. b. Sort the records in descending order by the State field and in ascending order by City. Save the changes to the query. View the query in Datasheet View, then close it, saving if necessary. Open the LiabilityAndProperty query in Design View and add criteria to select only those records where the Liability field values equal 100,000 and the PersonalProperty field values equal 100,000. Save the changes to the query. Open the LiabilityAndProperty query in Datasheet View, confirm that 2 records appear in the query results, then close the query, saving if necessary. Shelly Cashman Access 2019 | Module 2: SAM Project 1a 7. Open the MichiganCustomers query in Design View and add criteria to select only those records where the State field value is MI. Save the changes to the query. Open the query in Datasheet View, confirm that 7 records appear in the query results, then close the query, saving if necessary. 8. Because customers of Personal Insurance reside in many different cities, it is often difficult to know the exact spelling of a city. Open the CustomerCity query in Design View and add criteria to select only those records where the City field value begins with the letters Al. Save the changes to the query. Open the query in Datasheet View, confirm that 2 records appear in the query results, then close the query, saving if necessary. 9. Many queries require data from more than one table. For example, you may want a query to display the customer last name rather than the customer ID for a renter's policy. Create a query in Design View based on the Customers and Renters tables with the following options: 10. a. Add the Customers table and the Renters table to the design window. b. Add the LastName field from the Customers table to the design grid. c. Add the PolicyNumber and Premium field from the Renters table to the design grid. d. Join the Customers table and the Renters table by drawing a line from the CustomerID field in the Customers table to the CustomerID field in the Renters table. (Hint: Because the field names are identical in both tables, the line may already be there.) e. Save the query using Customer-Renter as the name. Open the query in Datasheet View, then close it, saving if necessary. Because customers live in different states, it is often advantageous to create a query where you can change one criteria using a parameter query. Open the StatesParameter query in Design View. a. Add parameter criteria to the State field to replace the current "FL" criteria. The new parameter criteria should prompt the user with Enter desired state as the text. b. Save the changes to the query. c. View the query in Datasheet View. Enter PA when prompted. Confirm that 8 records appear in the query results then close the query, saving if necessary. 11. Open the TopValuesProperty query in Design View. Modify the query to sort the PropertyDamage amounts in descending order. Change the Return value to display only the top 5 records. Open the query in Datasheet view, then close the query, saving if necessary. 12. Open the TotalCoverage query in Design View. Modify the query by creating a calculated field. Enter TotalCoverage: [Liability]+[PropertyDamage] in the Zoom dialog box of the first empty column in the design grid. Save the query. Open the query in Datasheet View, then close the query, saving if necessary. Shelly Cashman Access 2019 | Module 2: SAM Project 1a 13. Open the AveragePremium query in Design View and perform the following tasks: a. Add a Totals row to the design grid. b. Select Avg as the calculation in the Total row. c. Set the caption to Avg Prem for the Premium field. Save the changes to the query. Open the query in Datasheet View and confirm that it matches Figure 2. Close the query, saving if necessary. Figure 2: AveragePremium query results 14. Open the CustomersWithoutHomes query in Design View and perform the following tasks: a. Change the join property for the relationship between the Customers and Homeowners tables to select ALL records from the Customers table and only those records from the Homeowners table where the joined fields are equal. b. Add the Is Null criteria for the PolicyNumber field and add an Ascending sort order on the LastName field. Save the changes to the query. Open the query in Datasheet View and confirm that there are 21 records in the query result. Close the query, saving if necessary. 15. Open the UniqueStates query in Design View. Modify the query to list all states only once. Save the changes to the query. Open the query in Datasheet View and confirm that there are 15 records in the query result. Close the query, saving if necessary. 16. Create a crosstab query based on the MidAtlantic table with the following options: 17. a. Use only data from the MidAtlantic table in the crosstab. b. Use the State field for the row headings. c. Use the City field for the column headings. d. Use a Count of the CustomerID field as the calculated value for each row and column intersection, and include row sums in the crosstab query. e. Save the query using State-City Crosstab as the name. View the query, then save and close it. Export the Renters table as an Excel file (.xlsx) with the name Renters to the same folder as the one that stores your database. Do not export the data with formatting and layout. Save the export steps using Export-Renters as the name. Do not add a description. Save the changes to the table and close it. Shelly Cashman Access 2019 | Module 2: SAM Project 1a 18. Use the Import Spreadsheet Wizard to import the data from the Support_AC19_2a_UmbrellaData.xlsx support file and append it to the Umbrella table. Do not save the Import steps. Open the Umbrella table in Datasheet View. It should contain 14 records. Close the table. 19. Rename the PennsylvaniaOwners query as PennsylvaniaCustomers in the Navigation Pane. 20. Group the objects in the Navigation Pane by Tables and Related Views. 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. PolicyNumber U20011 U21145 U22138 U22269 U20771 CustomerID 11005 11022 11041 11042 11048 Premium 215 226 278 301 315 Coverage 1,000,000 1,000,000 2,000,000 2,000,000 2,000,000 ...
Purchase answer to see full attachment
Student has agreed that all tutoring, explanations, and answers provided by the tutor will be used to help in the learning process and in accordance with Studypool's honor code & terms of service.

Final Answer

Here is the final file for your record

PolicyNumber
CustomerIDLiability
R10223
11004
########
R10334
11017
########
R11234
11010
########
R11345
11015
75,000.00
R11999
11032
########
R12345
11019
50,000.00
R12455
110...

geobalies (1203)
New York University

Anonymous
Top quality work from this tutor! I’ll be back!

Anonymous
Just what I needed… fantastic!

Anonymous
Use Studypool every time I am stuck with an assignment I need guidance.

Studypool
4.7
Trustpilot
4.5
Sitejabber
4.4