Shelly Cashman Access 365 | Module 10: SAM Project B Haverhills Neighborhood Association

GETTING STARTED

1. Save the file SC_AC365_10B_FirstLastName_1.accdb as SC_AC365_10B_FirstLastName_2.accdb

a. Edit the file name by changing “1” to “2”.

b. If you do not see the .accdb file extension, do not type it. The file extension will be added for you automatically.

2. With the file SC_AC365_10B_FirstLastName_2.accdb open, ensure that your first and last name is displayed as the first record in the _GradingInfoTable table.

a. If the table does not display your name, delete the file and download a new copy.

PROJECT STEPS

1. Haverhills Neighborhood Association works with the city of Madison, Wisconsin, to improve the neighborhood for residents. As a volunteer with database skills, you want to use the extended management capabilities available in Structured Query Language (SQL) to retrieve information. Create a new query in SQL View based on the Programs table as follows to list the programs the neighborhood association sponsors:

a. Add all fields from the Programs table to the new query using an asterisk (*) in the SELECT clause.

b. Be sure to end the SQL command with a semicolon (;).

c. Save the query using ProgramList as the query name, run the query to produce the results shown in Figure 1, and then close the query.

Figure 1: ProgramList Query Results

2. Open the RepresentativeList query in SQL View to update the query. Add the LastName field to the SELECT clause after the FirstName field to include a missing detail about the neighborhood representatives. Run the query and confirm that the results match those shown in Figure 2. Save and close the RepresentativeList query.

Figure 2: RepresentativeList Query Results

3. Open the MonthlyDues query in SQL View, which is supposed to show both the annual and monthly dues amounts for each resident. Modify the query as follows:

a. Add a computed field to the SELECT clause after the AnnualDues field that calculates the monthly dues amount by dividing the AnnualDues field by 12.

b. Add an AS clause to use MonthlyDues as the name (alias) for this computed field.

c. Run the query and confirm that the results match those shown in Figure 3, which shows the first 20 records. (Hint: Your records may be in a different order.) Save and close the MonthlyDues query.

Figure 3: MonthlyDues Query Results

4. Open the CommitteeCriteria query in SQL View. Add a WHERE clause to the query to retrieve only those committees where the number of members is greater than 7. Run the query and confirm that the results match those shown in Figure 4, with 6 records meeting the criteria. Save and close the CommitteeCritiera query.

Figure 4: CommitteeCriteria Query Results

5. Open the CommitteeTypes query in SQL View. The query currently selects social committees, but you also want to retrieve committees that work with the city. Add an OR clause to the WHERE clause in the query so that the query retrieves records in which the CommType field is equal to Social or City. Run the query and ensure that the results match those shown in Figure 5. (Hint: Your records may be in a different order.) Save and close the CommitteeTypes query.

Figure 5: CommitteeTypes Query Result

6. Open the ResidentArea query in SQL View. The query currently retrieves records for residents in the North area of Madison, but you want to review records only for North area residents on the City liaison committee (CommitteeID 10). Add an AND clause to the WHERE clause in the query so that the query retrieves only those records in which the Area field is equal to North and the CommitteeID field is equal to 10. Run the query, and then confirm that the results match those shown in Figure 6. Save and close the ResidentArea query.

Figure 6: ResidentArea Query Results

7. Open the AreaCount query in SQL View. The query currently displays the resident IDs for residents in the East area of Madison, but you want to know the number of contacts in West area. Modify the query as follows:

a. Modify the SELECT clause to count the number of residents by resident ID.

b. Add an AS clause to the SELECT clause to set the alias of the function result to WestCount.

c. Modify the WHERE clause to select contacts in the West area.

d. Run the query, resize the column to its best fit, and then confirm that the results match those shown in Figure 7. Save and close the AreaCount query.

Figure 7: AreaCount Query Results

8. Open the ProgramsResidentsJoin query in SQL View. Modify the query as follows to join tables and show one contact for each program in the query results:

a. Add a WHERE clause that joins the Programs table and the Residents table on the ResidentID common field.

b. Qualify the field names in the WHERE clause.

c. Run the query and confirm that the results match those shown in Figure 8. (Hint: Your records may be in a different order.) Save and close the query.

Figure 8: ProgramsResidentsJoin Query Results

9. Open the MarchDues query in SQL View and clarify the contents of a column in the query results as follows:

a. Use an AS clause to add a caption to the Method field in the SELECT clause.

b. Use PaymentType as the caption for the Method field.

c. Run the query, and confirm that the last field in the query is displayed as PaymentType, as shown in Figure 9. Save and close the MarchDues query.

Figure 9: MarchDues Query Results

10. Open the ProgramSort query in SQL View. By default, the query is sorted by the ProgramID field value, but you typically refer to the ProgramDesc field and prefer to sort the query by that field. Modify the query to include an ORDER BY clause that sorts the records in ascending order by the ProgramDesc field. Run the query and confirm that the results match those shown in Figure 10. Save and close the ProgramSort query.

Figure 10: ProgramSort Query Results

11. Open the GroupByArea query in SQL View. The query currently counts the number of residents in the Residents table and displays the column heading as NumberResidents. Modify the query as follows to count the residents by each area:

a. Add the Area field to the SELECT statement before the Count(ResidentID) function.

b. Group the records by the Area field.

c. Order the records by the Area field in ascending order. Run the query and confirm that the results match those shown in Figure 11. Save and close the GroupByArea query.

Figure 11: GroupByArea Query Results

12. Open the RepresentativeSort query in SQL View. The query displays the name, area, and postal code of each representative. Modify the query as follows to organize the records by last name and postal code:

a. Use an ORDER BY clause to sort the records first in descending order by the PostalCode field.

b. Add the LastName field to the ORDER BY clause to sort the results next in ascending order by last name.

c. Run the query, and confirm that the results match those shown in Figure 12. Save and close the RepresentativeSort query.

Figure 12: RepresentativeSort Query Results

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 website to submit your completed project.