Participation Project #16 - Access Queries I

Step 3a

To create a query in Microsoft Access that displays information about each school, including the School Name, County, and whether it is Closed, follow these steps:

Creating Query 3a to View School Information

1. Open Microsoft Access
   1. Ensure your database is open.
2. Go to the Query Design View
   1. Click on the Create
   2. Select Query Design.
3. Add the Schools Table
   1. In the Show Table window, select Schools.
   2. Click Add, then Close.
4. Select the Fields to Include
   1. From the Schools table, double-click the following fields to add them to the query grid:
      1. School Name
      2. County
      3. Closed as of 2020-2021

5. Run the Query
   1. Click the Run button (red exclamation mark) in the toolbar.
   2. The results will show each school’s name, county, and whether it is closed.
6. Save the Query as Query 3a
   1. Click File > Save As.
   2. Name the query Query 3a.
   3. Click OK.

Now, the query Query 3a will be stored in your database and can be accessed whenever needed. Let me know if you need any adjustments!

Step 3b

Creating Query 3b to View Annual District Statistics

1. Open Microsoft Access
   1. Ensure your database is open.
2. Go to the Query Design View
   1. Click on the Create
   2. Select Query Design.
3. Add the District Statistics Table
   1. In the Show Table window, select District Statistics.
   2. Click Add, then Close.
4. Select the Fields to Include
   1. From the District Statistics table, double-click the following fields to add them to the query grid:
      1. School Year
      2. District
      3. Dropout Rate

5. Sort by School Year
   1. In the query design grid, locate the School Year
   2. Under the Sort row, select Ascending.
6. Run the Query
   1. Click the Run button (red exclamation mark) in the toolbar.
   2. The results will show each school year, district, and dropout rate, sorted by School Year in ascending order.
7. Save the Query as Query 3b
   1. Click File > Save As.
   2. Name the query Query 3b.
   3. Click OK.

Now, Query 3b will be stored in your database and can be accessed whenever needed. Let me know if you need any modifications!

Step 3c

Creating Query 3c to View School Proficiency Rates

1. Open Microsoft Access
   1. Ensure your database is open.
2. Go to the Query Design View
   1. Click on the Create
   2. Select Query Design.
3. Add the Necessary Tables
   1. In the Show Table window, select the following tables:
      1. Schools
      2. Districts
      3. School Statistics
   2. Click Add, then Close.

4. Select the Fields to Include
   1. From the Schools table, double-click the following field:
      1. School Name
   2. From the Districts table, double-click the following field:
      1. Region
   3. From the School Statistics table, double-click the following fields:
      1. School Year
      2. Students Tested
      3. Math Proficiency
      4. Reading Proficiency

5. Sort the Query Results
   1. In the query design grid, locate the School Name
   2. Under the Sort row, select Ascending.
   3. Locate the School Year
   4. Under the Sort row, select Ascending.
6. Run the Query
   1. Click the Run button (red exclamation mark) in the toolbar.
   2. The results will show each school's name, region, school year, number of students tested, math proficiency rate, and reading proficiency rate, sorted by School Name and then by School Year in ascending order.
7. Save the Query as Query 3c
   1. Click File > Save As.
   2. Name the query Query 3c.
   3. Click OK.

Now, Query 3c will be stored in your database and can be accessed whenever needed. 

Step 3d

Creating Query 3d Using SQL in Microsoft Access

To create Query 3d, you will need to use SQL (Structured Query Language) instead of the Query Design view. The SQL statement is provided in the assignment PDF, and you must follow these steps to copy and execute it in Microsoft Access:

Steps to Create Query 3d

1. Open Microsoft Access
   1. Ensure your database is open.
2. Go to the SQL View
   1. Click on the Create
   2. Select Query Design.
   3. Close the Show Table window (since we are writing SQL directly).
   4. Click on the SQL View button in the toolbar.
3. Copy the SQL Statement from the Assignment PDF
   1. Open the assignment PDF provided to you.
   2. Locate the SQL statement for Query 3d.
   3. Highlight the entire SQL statement and copy it (Ctrl + C).
4. Paste the SQL Statement into Access
   1. In SQL View, click inside the query editor and paste the copied SQL statement (Ctrl + V).
5. Run the Query
   1. Click the Run button (red exclamation mark) in the toolbar.
   2. If the SQL statement is correct, the query will execute and display results.
6. Save the Query as Query 3d
   1. Click File > Save As.
   2. Name the query Query 3d.
   3. Click OK.

Step 3e

Creating Query 3e Using SQL in Microsoft Access

To create Query 3e, you will need to use SQL (Structured Query Language) instead of the Query Design view. The SQL statement is provided in the assignment PDF, and you must follow these steps to copy, modify, and execute it in Microsoft Access:

Steps to Create Query 3e

1. Open Microsoft Access
   1. Ensure your database is open.
2. Go to the SQL View
   1. Click on the Create
   2. Select Query Design.
   3. Close the Show Table window (since we are writing SQL directly).
   4. Click on the SQL View button in the toolbar.
3. Copy the SQL Statement from the Assignment PDF
   1. Open the assignment PDF provided to you.
   2. Locate the SQL statement for Query 3e.
   3. Highlight the entire SQL statement and copy it (Ctrl + C).
4. Paste the SQL Statement into Access
   1. In SQL View, click inside the query editor and paste the copied SQL statement (Ctrl + V).
5. Run the Query
   1. Click the Run button (red exclamation mark) in the toolbar.
   2. You will receive a prompt for a missing value.
   3. This occurs because the provided SQL statement references an invalid field name.
6. Identify and Fix the Invalid Field Name
   1. Examine the SQL statement carefully to find any incorrect field names.
   2. Compare the field names used in the SQL statement with the actual field names in your tables.
   3. Modify the incorrect field name to match the correct field name in your database.
7. Run the Corrected Query
   1. After making the necessary modifications, click Run
   2. The query should now execute correctly without prompting for a missing value.
8. Save the Query as Query 3e
   1. Click File > Save As.
   2. Name the query Query 3e.
   3. Click OK.

Now, Query 3e is created using SQL and saved in your database. If you still encounter issues, review the field names carefully and ensure they match those in your database structure.

Let me know if you need further clarification!