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

GETTING STARTED

1. Save the file SC_AC365_5A_FirstLastName_1.accdb as SC_AC365_5A_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_5A_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 working with the company database, you need to modify tables and create multiple-table forms. Open the Contacts table in Design View. Use the Input Mask Wizard to create an input mask for the Phone field to make it easier to enter phone numbers. Store the data without symbols in the mask. Save the change to the Contacts table without closing it.

2. Display the Contacts table in Datasheet View, and then add a new field named ContactNotes at the end of the table to record comments about company contacts. Use Long Text as the data type for the field. Save the change to the table.

3. With the Contacts table still open in Datasheet View, update the record for Contact Ruben Hernandez (ContactID 20001) as follows to provide a phone number and contact note:

a. Using the input mask, enter 615-555-2133 in the Phone field.

b. Enter the text Interested in being a program rep in the ContactNotes field.

4. Display the Contacts table in Design View, and then add a new field with the name ContactPhoto following the ContactNotes field. Use Attachment as the data type for the field, and use Photo as the caption property for the field. Save the change to the table, and then close it.

5. Modify the ContactEntry form as follows to add a missing field:

a. Open the ContactEntry form in Design View.

b. Open the field list for the form.

c. Add the Phone field to the form directly below the State label and text box. Position the left edge of the Phone text box at the 2" mark on the horizontal ruler and position the top of the label and control at approximately the 2" mark on the vertical ruler.

d. Align the left edge of the Phone label with the left edge of the State label. Save the changes to the form, confirm that it matches Figure 1, and then close the ContactEntry form.

Figure 1: ContactEntry Form in Design View

6. Create a new form in Design View based on the Accounts table to store account information, and then update the form as follows:

a. Add the AccountNumber, CompanyID, MonthlyBill, and ContractTotal fields to the Detail section of the form. Position the left edges of the text box controls at the 2" mark on the horizontal ruler.

b. Save the form with the name AccountLookup but do not close the form.

c. Add a title to the form, and then insert a space so that the title is "Account Lookup". Save the form, confirm that it matches Figure 2, and close the AccountLookup form.

Figure 2: AccountLookup Form in Design View

7. Open the CompanyAccounts form in Design View, and ensure that the Use Control Wizards button is selected. Use the Subform/Subreport Wizard to add a subform as follows to show account information on the form:

a. Use the Accounts table for the subform.

b. Select the AccountNumber, MonthlyBill, and AnnualBill fields from the Accounts table to add to the form.

c. Accept the default link (Show Accounts for each record in Companies using CompanyID) to link the main form to the subform.

d. Save the subform as Accounts subform (which is the default name).

e. Delete the label for the subform.

f. If necessary, reposition the subform so it is below the Stores label and RetailStores text box and its left edge is aligned with the left edge of the Stores label.

g. Resize the subform so its right edge is at the 5" mark on the horizontal ruler, as shown in Figure 3. Save and close the form.

Figure 3: CompanyAccounts Form in Design View

8. Open the ServicesAndAccounts form in Design View, and then modify it by resizing the width of the subform so that the right edge is at approximately 7.5" on the horizontal ruler to match the width of the other form controls. (Hint: Your subform might show only a TableAccounts label and no fields as in Figure 4.) Save but do not close the form.

9. With the ServicesAndAccounts form still open in Design View, format the form title as follows to make it more distinctive:

a. Change the font color of the title to Dark Red (1st column, 7th row in the Standard Colors palette).

b. Apply a 22-point font size to the title.

c. Resize the title control to display all of its text.

d. Change the width of the form to 8". Save the form, confirm that it matches Figure 4, and close the ServicesAndAccounts form.

Figure 4: ServicesAndAccounts Form in Design View

10. Open the ContactBirthdate form in Design View, and then modify the form as follows to make the form easier to use:

a. Delete the ProgramTitle control and the accompanying label.

b. Change the label for the DateOfBirth control using Birthdate as the new label text. Save and close the form.

11. Open the AccountEntry form in Design View, and then modify the form as follows to rearrange the controls to match Figure 5:

a. Align the AccountNumber, CompanyID, and CompanyName controls and labels along their top edges.

b. Align the left edge of the Annual Bill label with the left edge of the Contract Total label.

c. Align the left edge of the AnnualBill text box with the left edge of the ContractTotal text box.

d. Adjust the spacing of the AccountNumber, ContactID, ServiceID, MonthlyBill, ContractTotal, and AnnualBill controls to have equal vertical spacing. Save, but do not close the form.

Figure 5: AccountEntry Form with Aligned Controls in Design View

12. With the AccountEntry form still open in Design View, modify the tab properties to make the form easier to use:

a. Change the tab order so that users tab to the LastName field immediately after the ContactID field.

b. Change the Tab Stop property to No for the CompanyName field. Save, but do not close the form.

13. With the AccountEntry form still open in Design View, modify the form as follows to identify the form and make it more visually appealing:

a. Add a title to the form using AccountEntry as the name. (Hint: This is the default name.) Save the form, confirm that it matches Figure 6, and then close the AccountEntry form.

Figure 6: Final AccountEntry Form in Design View

14. Open the ProgramList query in Design View, and then modify the query to find all national programs started in September 2029. The query should return two records. Run the query, and then save and close it.

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.