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

Shelly Cashman Access 365 | Module 10: SAM Critical Thinking Project C

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 Critical Thinking Project C Connect Marketing Group USING SQL PROJECT STEPS 1. Connect Marketing Group is a national company that provides marketing services such as contact research, brand building, and merchandising to retail stores. As a sales analyst working with the company database, you want to use the extended management capabilities available in Structured Query Language (SQL) to retrieve information. Create a new query in SQL View as follows to list the services that Connect Marketing provides: a. Add all fields from the Services table to the new query using an asterisk (*) in the appropriate clause and ending the SQL command with a semicolon (;). b. Save the query using ServiceList as the query name, run the query to produce the results shown in Figure 1, and then close the query. Figure 1: ServiceList Query Results 2. Update the ProgramContactList query in SQL View so that the LastName field appears after the FirstName field in the query results. Run the query and confirm that the results match those shown in Figure 2. Save and close the ProgramContactList query. Figure 2: ProgramContactList Query Results 3. Open the AnnualBill query in SQL View, which is supposed to show both the monthly and annual bill amounts for each account. Modify the query as follows: a. Add a computed field after the MonthlyBill field that calculates the total annual bill amount by multiplying the MonthlyBill field by 12. b. Add an AS clause that uses AnnualBill as the alias for the computed field. c. Run the query and confirm that the results match those shown in Figure 3. (Hint: Your records may be in a different order.) Save and close the AnnualBill query. Figure 3: AnnualBill Query Results 4. Open the CompanyCriteria query in SQL View to include criteria. Add a WHERE clause to retrieve only those companies with more than 25 retail stores. Run the query and confirm that the results match those shown in Figure 4, with 11 records meeting the criteria. Save and close the CompanyCritiera query. Figure 4: CompanyCriteria Query Results 5. Open the CompanyRegions query in SQL View to expand the selection criteria. Add an OR clause to the WHERE clause to select companies in the Midwest or the Northwest. Run the query and confirm that the results match those shown in Figure 5. (Hint: Your records may be in a different order.) Save and close the CompanyRegions query. Figure 5: CompanyRegions Query Results 6. Open the ContactCityState query in SQL View to narrow the selection criteria. Add an AND clause to the WHERE clause to retrieve only those records for contacts in the state of CA and the city of San Jose. Run the query, and then confirm that the results match those shown in Figure 6. Save and close the ContactCityState query. Figure 6: ContactCityState Query Results 7. Open the NewYorkCount query in SQL View. The query currently displays the contact IDs for contacts in New York state, but you want to know the number of contacts in California. Modify the query as follows: a. Modify the SELECT clause to count the number of contacts by contact ID. b. Add an AS clause that uses ContactCount as the alias of the function result. c. Modify the WHERE clause to select contacts in CA. 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 NewYorkCount query. Figure 7: NewYorkCount Query Results 8. Open the ProgramsContactsJoin 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 ProgramContacts table on the ContactID 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: ProgramsContactsJoin Query Results 9. Open the ProgramLocations query in SQL View and clarify the contents of a column in the query results as follows: a. Use an AS clause to use AllStates as the caption for the National field. b. Run the query, and confirm that the last field in the query is displayed as AllStates, as shown in Figure 9. Save and close the ProgramLocations query. Figure 9: ProgramLocations Query Results