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

GETTING STARTED

1. Save the file SC_AC365_2B_FirstLastName_1.accdb as SC_AC365_2B_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. To complete this Project, you will also need the following files:

Support_AC365_2B_Dues.xlsx

3. With the file SC_AC365_2B_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, you are querying the database to help make decisions and to answer questions from other members of the association. Create a query based on the Residents table in Query Design View with the following options to list residents and their birthdates:

a. Add the ResidentID, 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 ResidentLastNameSorted as the name. Open the query in Datasheet View, then close it, saving if necessary.

2. Open the QuarterlyDues query in Design View and make the following changes to the query to select records with dues less than $150:

a. Delete the ResidentID column from the design grid.

b. Add criteria to select only those records where the DuesAmount field value is less than 150.

c. Save the changes to the QuarterlyDues query. Open the query in Datasheet View, then close it, saving if necessary.

3. Open the HighBudgetOrDonations query in Design View and add criteria to select only those records where the Budget field values are greater than 400 or the Donations field values are greater than 300. Save the changes to the query. Open the query in Datasheet View, confirm that six records appear in the HighBudgetOrDonations query results, then close the query, saving if necessary.

4. Open the ResidentDOB query in Design View and make the following changes to the query to list residents with birthdates after December 31, 1989:

a. Add the FirstName field to the query design grid. The FirstName field should immediately follow the ResidentID field.

b. Add criteria to select only those records where the DateOfBirth field value is greater than 12/31/1989.

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 ResidentDOB query. Open the query in Datasheet View, confirm that the results match Figure 1, then close it, saving if necessary.

Figure 1: ResidentDOB Query Results

5. Open the AreaResidentSort query in Design View and make the following changes to organize residents first by area and then by last name within each area:

a. Move the Area field to the beginning of the design grid so that the order of the fields in the grid is Area, LastName, and FirstName.

b. Sort the records in Descending order by the Area field and in Ascending order by LastName. Save the changes to the query. View the query in Datasheet View, then close it, saving if necessary.

6. Open the HighBudgetAndDonations query in Design View and add criteria to select only those records where the Budget field values are greater than 400 and the Donations field values are greater than 300. Save the changes to the query. Open the HighBudgetAndDonations query in Datasheet View, confirm that two records appear in the query results, then close the query, saving if necessary.

7. Open the NorthSide query in Design View and change the criteria to select only those records where the Area field value is North. Save the changes to the query. Open the query in Datasheet View, confirm that 11 records appear in the query results, then close the query, saving if necessary.

8. You need to find a resident, but you know only that the resident's last name begins with "Ba". Open the ResidentNames query in Design View and add criteria to select only those records where the LastName field value begins with the letters Ba. Save the changes to the query. Open the query in Datasheet View, confirm that five records appear in the query results, then close the query, saving if necessary.

9. You want a query that displays the resident name rather than the resident ID for each dues record. Create a query in Design View based on the Residents and Dues tables with the following options:

a. Add the Residents table and the Dues table to the design window.

b. Add the LastName field from the Residents table to the design grid.

c. Add the DuesID and DuesAmount field from the Dues table to the design grid.

d. If necessary, join the Residents table and the Dues table by drawing a line from the ResidentID field in the Residents table to the ResidentID field in the Dues table. (Hint: Because the field names are identical in both tables, the join line may already appear in Design View. Do not attempt to recreate the line if it is already present.)

e. Save the query using ResidentDues as the name. Open the query in Datasheet View, then close it, saving if necessary.

10. You often want to list dues according to the payment method, which varies depending on the information you need. Open the DuesParameter query in Design View and then modify it as a parameter query as follows:

a. Add parameter criteria to the DuesMethod field to replace the current "Credit Card" criteria. The new parameter criteria should prompt the user with Enter desired dues method as the text.

b. Save the changes to the query.

c. View the query in Datasheet View. Enter Check when prompted. Confirm that six records appear in the query results, then close the query, saving if necessary.

11. Open the TopValuesPrograms query in Design View. You want to list the programs with the three highest budgets. Modify the query to sort the Budget amounts in Descending order. Change the Return value to display only the top 3 records. Save the query. Open the query in Datasheet View, then close it, saving if necessary.

12. Open the AnnualDues query in Design View. Modify the query by creating a calculated field to determine the annual dues for each resident. Enter AnnualDues: [DuesAmount]*4 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 it, saving if necessary.

13. Open the AvgQuarterlyDues query in Design View and perform the following tasks to list the average dues for residents:

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 Dues for the DuesAmount 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: AvgQuarterlyDues Query Results

14. Open the ProgramsWithoutResidents query in Design View and perform the following tasks to identify programs without resident directors:

a. Change the join property for the relationship between the Programs and Residents tables to select ALL records from the Programs table and only those records from the Residents table where the joined fields are equal.

b. Add the Is Null criterion for the ResidentID field and add an Ascending sort order on the ProgramDesc field. Save the changes to the query. Open the query in Datasheet View and confirm that two records appear in the query results. Close the query, saving if necessary.

15. Open the UniqueDuesMethods query in Design View. Modify the query to list all dues methods only once. Save the changes to the query. Open the query in Datasheet View and confirm that four records appear in the query results. Close the query, saving if necessary.

16. Create a crosstab query based on the Committees table with the following options to list the total number of committees by committee and director type:

a. Use only data from the Committees table in the crosstab.

b. Use the CommitteeType field for the row headings.

c. Use the DirectorType field for the column headings.

d. Use a Count of the CommitteeID field as the calculated value for each row and column intersection, and include row sums in the crosstab query. Save the query using Committees_Crosstab (the default) as the name. Close the query, saving if necessary.

17. Export the Programs table as an Excel file (.xlsx) with the name Programs.xlsx 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-Programs as the name. Do not add a description. Save the changes to the export and close it.

18. Use the Import Spreadsheet Wizard to import the data from the Support_AC365_2B_Dues.xlsx support file and append it to the Dues table to add new dues information to the database. Do not save the import steps. Open the Dues table in Datasheet View. It should contain 34 records. Close the table.

19. Rename the EastSideResidents query as WestSideResidents in the Navigation Pane so that the query name reflects the results.

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