Shelly Cashman Access 365 | Module 10: SAM Project B скачать в хорошем качестве

Shelly Cashman Access 365 | Module 10: SAM Project B

Please contact us if you need this project or any other projects in any subject to be completed. WhatsApp: +1-343-321-3276 Email: essayserviceusa@gmail.com WhatsApp Link: https://wa.me/13433213276 Shelly Cashman Access 365 | Module 10: SAM Project B 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. **More