Access 2016 capstone project ac 2

Illustrated Access 2016 | Modules 5–8: SAM Capstone Project 1a

C:\Users\akellerbee\Documents\SAM Development\Design\Pictures\g11731.png Illustrated Access 2016 | Modules 5–8: SAM Capstone Project 1a

Seminar Database

Enhancing tables, queries, forms, and reports

GETTING STARTED

· Open the file IL_AC16_CS5-8a_FirstLastName_1.accdb, available for download from the SAM website.

· Save the file as IL_AC16_CS5-8a_FirstLastName_2.accdb by changing the “1” to a “2”.

· Hint: If you do not see the .accdb file extension in the Save As dialog box, do not type it. The program will add the file extension for you automatically.

· Open the _GradingInfoTable table and ensure that your first and last name is displayed as the first record in the table. If the table does not contain your name, delete the file and download a new copy from the SAM website.

PROJECT STEPS

View the relationships for the database, then create a Relationship Report. Save the report with the name Relationships then close it. Close the Relationships window.

Open the Attendees table in Design View. Use the Input Mask Wizard to add a Phone Number input mask to the CellPhone field. Use the default input mask options with one exception: Change the placeholder character to an asterisk (*). (Hint: Make sure that your data is stored without symbols in the mask.) Save but do not close the Attendees table.

With the Attendees table still open in Design View, specify the HomeCity field as a required field. Save but do not close the Attendees table, then click Yes when prompted to test the existing data.

With the Attendees table still open in Design View, add a new field after the AttendeeEmail field with the name DateOfBirth. Specify the Date/Time data type and the Medium Date (e.g., 12-Nov-17) Format property for this field. Save and close the Attendees table. (Hint: If a dialog box appears warning that some data may be lost, click Yes.)

Open the Registration table in Design View and update the RegistrationFee field properties as described below:

a. Create a Validation Rule that specifies that all entries in the RegistrationFee field must be greater than or equal to 10.

b. Set the Validation Text for the RegistrationFee field to The minimum registration fee is $10. (including the period).

c. Save the Registration table, click Yes when prompted to test the existing data, then close the table.

Open the Companies table in Design View. Below the WebSite field, add a new field named State. Use the Lookup Wizard to set the Lookup properties for the State field as described below:

d. Select the option to type in the values for the lookup field.

e. Enter Kansas and Missouri as the two possible values for the lookup field.

f. Use the default label State for the field label.

g. Limit entries to the list, and do not allow multiple values.

Save and close the Companies table.

Open the Registration table in Datasheet View, then update the RegistrationDate field values for the following records:

h. For the record with the RegistrationID field value 79, update the RegistrationDate field value to 3/1/2017.

i. For the record with the RegistrationID field value 80, update the RegistrationDate field value to 3/6/2017.

j. For the record with the RegistrationID field value 81, update the RegistrationDate field value to 3/13/2017.

Close the Registration table, saving it if prompted to do so.

Create a new query in Design View based on the Attendees table by completing the following tasks:

k. Add the HomeCity, AttendeeLastName, and AttendeeFirstName fields (in that order) to the query.

l. Add the OR criterion to the HomeCity field, so that only records with a HomeCity field value of Fontanelle or Greenfield are returned by the query.

m. Add an ascending sort order to the HomeCity, AttendeeLastName, and AttendeeFirstName fields.

n. Save the query with the name FontanelleGreenfield. (Hint: Do not include the period.)

Run the query to view it in Datasheet View, then close the query. (Hint: If you entered the criteria correctly, the query should return four records.)

Create a new query in Design View based on the Attendees, Registration, and Events tables with the following options:

o. Add the AttendeeLastName and HomeCity fields from the Attendees table, add the RegistrationDate and RegistrationFee fields from the Registration table, and add the EventName field from the Events table (in that order) to the query.

p. Add the AND criterion to the query, so that only records that contain Ames in the HomeCity field and 10 in the RegistrationFee field are returned by the query.

q. Add an ascending sort order to the AttendeeLastName field.

r. Save the query with the name Ames10.

Run the query to view it in Datasheet View, then close the query. (Hint: If you entered your criteria correctly, the query should return two records.)

Open the EventRevenue query in Datasheet View. Add a Total row to the query that totals the values in the RegistrationFee field using the SUM function. Save and close the query.

Open the StateAnalysis query in Design View. Turn the StateAnalysis query into a Crosstab query as described below:

s. Use the Crosstab button to add the Total row and the Crosstab row to the query.

t. Select the EventName field as a row heading field.

u. Select the HomeState field as the column heading field.

v. Sum the value of the RegistrationFee field.

w. Sum the TotalFees field, and select it as a row heading.

Save and run the StateAnalysis query. Compare the query to Figure 1 below, then close the query.

Figure 1: StateAnalysis Query in Datasheet View

Open the Registrations query in Design View and complete the following tasks:

x. Add the AttendeeLastName field from the Attendees table to the query.

y. Add the Total row to the query grid. (Hint: The records will be grouped by the EventName field by default.)

z. Using the Total row, sum the values of the RegistrationFee field.

aa. Using the Total row, count the number of records for each group using the AttendeeLastName field.

Save and run the Registrations query. Compare the query to Figure 2 on the next page, then close the query.

Figure 2: Registrations Query in Datasheet View

Open the Attendee Entry form in Design View, apply the Office theme to the database, then save the Attendee Entry form.

With the Attendee Entry form still open in Design View, complete the following tasks:

ab. Set the Record Source property of the form to the Attendees table. Save but do not close the form.

ac. Add the AttendeeEmail field just below the CellPhone control in the approximate location shown in Figure 3 on the next page.

ad. Select the Cell Phone and AttendeeEmail labels (but not the text boxes), then align their right edges using the Right option.

Save and view the Attendee Entry form in Form View, then close the form.

Figure 3: Attendee Entry Form in Design View

Open the Event Entry form in Design View. Make sure that the Control Wizards feature is turned on. Using the Subform Wizard, add a subform to the form as described below:

ae. The subform should be located in the approximate location shown in Figure 4 on the next page.

af. Use an existing table as the data for your subform.

ag. From the Registration table, add the EventID, RegistrationDate, and RegistrationFee fields (in that order) to the subform.

ah. Show records in the subform for each record in the Events table using the common EventID field.

ai. Use Registrations as the name of the subform.

Save and view the Event Entry form in Form View, then close it.

Figure 4: Event Entry Form in Design View

Open the Company Entry form in Design View, then delete the control containing the date expression from the Form Footer section. Save the Company Entry form, but do not close it.

With the Company Entry form still open in Design View, complete the following tasks:

aj. Select the CompanyName, Industry, and WebSite controls, then align the left edge of the controls using the Left option.

ak. Select the CompanyID label and the CompanyID control, then align them using the Top option.

Save but do not close the Company Entry form.

With the Company Entry form still open in Design View, confirm that the Control Wizards feature is turned on. Using the Combo Box Wizard, add a combo box to the form as described below:

al. Add the combo box to the Form Header section at approximately the 4" mark on the horizontal ruler as shown in Figure 5 on the next page.

am. Specify that the combo box is used to find a record in the form.

an. Select the CompanyID and CompanyName fields (in that order) to display in the combo box.

ao. Do not hide the key column.

ap. Enter Find Company as the label for the combo box.

Save but do not close the Company Entry form.

Figure 5: Company Entry Form in Design View

With the Company Entry form still open in Design View, add a command button to the form as described below:

aq. The command button should be located in the Detail section below the WebSite text box in the approximate location shown in Figure 5.

ar. The command button should use a Form Operation to close the form.

as. The command button should display the text Close Form. (Hint: Do not type the period.)

at. Use CloseForm as the meaningful name for the command button.

au. Resize and reposition the command button as needed to match Figure 5.

Save the Company Entry form and compare it to Figure 5. Close the form and save it if prompted to do so.

Open the Main Menu form in Design View. Add a tab control to the form at approximately the 1" mark on both the horizontal and vertical rulers. Complete the following steps:

av. Use Reports as the name of the first tab.

aw. Use Entry Forms as the name of the second tab.

Save and close the Main Menu form.

Open the Event Master List report in Design View. Open the Property Sheet for the report, then set its Record Source property to the Events table. Save but do not close the Event Master List report.

With the Event Master List report still open in Design View, open the Field List and complete the following steps:

ax. Add the AvailableSpaces field to the Detail section of the report at approximately the 6" mark on the horizontal ruler, as shown in Figure 6 below.

ay. Cut the AvailableSpaces label and paste it into the Page Header section at approximately the 6" mark on the horizontal ruler, as shown in Figure 6.

Compare your report to Figure 6, then save and close the Event Master List report.

Figure 6: Event Master List Report in Design View

Open the Event Registration report in Design View, apply a conditional formatting rule to the RegistrationFee control in the Detail section. The rule should highlight any record with a RegistrationFee field value greater than 100 using bold text and a yellow background. Save but do not close the Event Registration report.

With the Event Registration report still open in Design View, use the Format Painter to copy the formatting from the Event Name label in the Page Header section to the Location and Attendee Name labels in the Page Header section. Save but do not close the Event Registration report.

With the Event Registration report still open in Design View, draw a straight line that spans the report’s width across the bottom of the EventID Footer section, as shown in Figure 7 below. Save but do not close the Event Registration report.

With the Event Registration report still open in Design View, modify the properties of the report sections as described below:

az. Close (but do not remove) the Report Header section.

ba. For the EventID Footer section, update the Force New Page property to force a new page after each section using the After Section option.

Save the Event Registration report, compare it to Figure 7, then close it.

Figure 7: Event Registration Report in Design View