Shelly Cashman Access 365 | Module 2: SAM Project A Connect Marketing Group

GETTING STARTED

1. Save the file SC_AC365_2A_FirstLastName_1.accdb as SC_AC365_2A_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_2A_AccountData.xlsx

3. With the file SC_AC365_2A_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. 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, you need to query the database to help make decisions and to answer questions from other employees. Create a query based on the Contacts table in Query Design View with the following options to list contacts and their birthdates:

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

2. Open the AccountBillings query in Design View and make the following changes to the query to select accounts with monthly billings over $500:

a. Delete the ContactID column from the design grid.

b. Add criteria to select only those records where the MonthlyBill field value is greater than 500.

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

3. Open the HighBillOrContract query in Design View and add criteria to select only those records where the MonthlyBill field values are greater than 1000 or the ContractTotal field values are greater than 2000000. Save the changes to the query. Open the query in Datasheet View, confirm that 6 records appear in the HighBillOrContract query results, then close the query, saving if necessary.

4. Open the ContactBirth query in Design View and make the following changes to the query to list contacts with birthdates before January 1, 1990:

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

b. Add criteria to select only those records where the DateOfBirth field value is less than 1/1/1990.

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

Figure 1: ContactBirth Query Results

5. Open the StateCitySort query in Design View and make the following changes to organize contacts first by state and then by cities within each state:

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

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

6. Open the HighBillAndContract query in Design View and add criteria to select only those records where the MonthlyBill field values are greater than 1000 and the ContractTotal field values are greater than 2000000. Save the changes to the query. Open the HighBillAndContract query in Datasheet View, confirm that 1 record appears in the query results, then close the query, saving if necessary.

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

8. You need to find a contact from a particular city, but you know only that the city name begins with "Ba". Open the ContactCity query in Design View and add criteria to select only those records where the City field value begins with the letters Ba. Save the changes to the query. Open the query in Datasheet View, confirm that 3 records appear in the query results, then close the query, saving if necessary.

9. You want a query that displays the contact name rather than the contact ID for each account. Create a query in Design View based on the Contacts and Accounts tables with the following options:

a. Add the Contacts table and the Accounts table to the design window.

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

c. Add the AccountNumber and ContractTotal field from the Accounts table to the design grid.

d. If necessary, join the Contacts table and the Accounts table by drawing a line from the ContactID field in the Contacts table to the ContactID field in the Accounts 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 Contact-Account as the name. Open the query in Datasheet View, then close it, saving if necessary.

10. You often want to list contacts from a particular state, which varies depending on the information you need. Open the StatesParameter query in Design View and then modify it as a parameter query as follows:

a. Add parameter criteria to the State field to replace the current "CA" criterion. The new parameter criteria should prompt the user with Enter desired state as the text.

b. Save the changes to the query.

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

11. Open the TopValuesBillings query in Design View. You want to list the accounts with the five highest monthly billings. Modify the query to sort the MonthlyBill amounts in Descending order. Change the Return value to display only the top 5 records. Save the query. Open the query in Datasheet View, then close the query, saving if necessary.

12. Open the AnnualPayments query in Design View. Modify the query by creating a calculated field to determine the annual payment from each account. Enter AnnualPayment: [MonthlyBill]*12 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 the query, saving if necessary.

13. Open the AverageBillings query in Design View and perform the following tasks to list the average monthly bill for all active accounts:

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 Billing for the MonthlyBill 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: AverageBillings Query Results

14. Open the ContactsWithoutAccounts query in Design View and perform the following tasks to identify contacts who are not assigned to accounts:

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

b. Add the Is Null criterion for the AccountNumber field and add an Ascending sort order on the LastName field. Save the changes to the query. Open the query in Datasheet View and confirm that there are 23 records in the query result. Close the query, saving if necessary.

15. Open the UniqueStates query in Design View. Modify the query to list all states only once. Save the changes to the query. Open the query in Datasheet View and confirm that there are 24 records in the query result. Close the query, saving if necessary.

16. Create a crosstab query based on the WestCoast table with the following options to list the total number of contacts in each city on the west coast:

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

b. Use the City field for the row headings.

c. Use the State field for the column headings.

d. Use a Count of the ContactID field as the calculated value for each row and column intersection, and include row sums in the crosstab query.

e. Save the query using WestCoast_Crosstab (the default) as the name. View the query, then close it, saving if necessary.

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

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

19. Rename the WashingtonContacts query as CaliforniaContacts 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.