In this project, you will create a database which will allow the foundation to more easily track events, attendees, and other data.

INSTRUCTIONS TO CANDIDATES

ANSWER ALL QUESTIONS

GETTING STARTED

• Open the file IL_AC16_CS1-4a_FirstLastNameaccdb, available for download from the SAM website.

• Save the file as IL_AC16_CS1-4a_FirstLastNameaccdb by changing the “1” to a “2”.

• 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 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

1. You are an employee at Evenstar Financial Planning, a nonprofit foundation located in Omaha, Nebraska which organizes events and seminars on financial topics for community members. In this project, you will create a database which will allow the foundation to more easily track events, attendees, and other data.

In Design View, create and save a new table with four fields as follows:

1. Add a field with the name CompanyID and the AutoNumber data type.

2. Add a field with the name CompanyName and the Short Text data type.

3. Add a field with the name Industry and the Short Text data type.

4. Add a field with the name WebSite and the Hyperlink data type.

5. Set the CompanyID field as the table’s primary key.

Save the table with the name Companies, but do not close it.

2. With the Companies table still open in Design View, change the field size of the CompanyName and Industry fields to 50. Save and close the Companies

3. Open the Attendees table in Design View. Add a field to the Attendees table with the field name AttendeeEmail and the Hyperlink data type. Save and close the Attendees

4. Open the Events table in Datasheet View, then enter the two records shown in Table 1 below. (Hint: The EventID field is an AutoNumber field.) After entering the records, save and close the Events

Table 1: Two New Records for the Events Table

EventID	EventName	Location	EventDate	AvailableSpaces
8	Living Off Grid	Destin Town Hall	6/1/2017	200
9	Solar Power Solutions	Niceville Arena	6/2/2017	250

5. Open the Relationships window and add the Companies Link the Companies table to the Attendees table using the common CompanyID field in a one-to-many relationship, and be sure to enforce referential integrity on the relationship as shown in Figure 1 below. (Hint: Resize the field lists to see all fields.)

6. With the Relationships window still open, add the Events Link the Events table to the Registration table using the common EventID field in a one-to-many relationship, and be sure to enforce referential integrity on the relationship as shown in Figure 1 below. Save and close the Relationships window. (Hint: Resize the field lists to see all fields.)

 

7. You decide that a query showing attendees of large events occurring in the first quarter of the year would be useful for the nonprofit’s board. Create a new query in Design View using the Events, Registration, and Attendees tables with the following options:

   1. Select the following fields for the query in the order listed: the EventName field from the Events table, the AttendeeLastName and AttendeeFirstName fields from the Attendees table, and the RegistrationDate and RegistrationFee fields from the Registration

   2. Apply an ascending sort order to the EventName, AttendeeLastName, and AttendeeFirstName

   3. Add criteria so that only those records with a RegistrationDate field value less than 4/1/2017 and a RegistrationFee field value greater than 100 are selected. (Hint: The records returned by the query will fulfill both criteria.)

   4. Save the query with the name LargeFirstQuarterEvents. (Hint: Do not type the period.)

Run the query and check it against Figure 2 below, then close the LargeFirstQuarterEvents query.

 

8. Create a new query in Design View using the Events, Registration, and Attendees tables with the following options:

   1. Select the following fields for the query in the order listed: the EventName field from the Events table, the RegistrationDate and RegistrationFee fields from the Registration table, and the AttendeeLastName field from the Attendees

   2. Apply an ascending sort order to the EventName field and a descending sort order to the RegistrationDate

   3. Add criteria so that only those records in which the EventName field contains Planning a Will or Estate Planning are selected. (Hint: The records returned by the query will fulfill either criterion.)

   4. Save the query with the name PlanningWillOrEstate. (Hint: Do not type the period.)

Run the query and check it against Figure 3 below, then close it.

 

9. View the PlanningWillOrEstate query in Datasheet View. Find the text “Planning a Will” in any record in the EventName field and change the value to Planning a Simple Will. (Hint: Do not type the period.) Save and close the PlanningWillOrEstate

10. You decide that a form would be the best tool to allow users of the database to quickly and easily create records for new events. Use the Form Tool to create a new form based on the Events Save the form with the name Event Entry and open it in Form View.

11. Navigate to the record for EventID 5 (having EventName Wealth Management), then change the contents of the EventName field from “Wealth Management” to Grow Rich Slowly. Change the contents of the Location field from “Millen Civic Center” to Mercy Auditorium. (Hint: Do not type the period at the end of event name and location.)

 

12. Enter a new record in the Event Entry form as shown in Figure 4 below, then save and close the form. (Hint: The EventID field is an AutoNumber field.)

 

13. Your supervisor likes the Event Entry form, so you decide to also create a form to allow entry of attendee information. Use the Form Wizard to create a new form with the following options:

    1. Select all fields in the Attendees table to include in the form.

    2. Use the Columnar layout for the form.

    3. Enter Attendee Entry as the title of the form.

14. View the Attendee Entry form in Layout View, then modify the labels in the first column so that there is a space between the words as shown in Figure 5 on the following page. (Hint: You will modify 10 labels in the form.)

15. View the Attendee Entry form in Form View, then enter the new record shown in Figure 5 on the following page. (Hint: The AttendeeID field is an AutoNumber field, and there is no entry in the CompanyID)

 

16. Navigate to the first record in the Attendee Entry form (Attendee ID 1), then change the Home Address from “111 Maple Street” to 700 Wedd Street. (Hint: Do not type the period.) Save and close the Attendee Entry

17. You decide to create a report to allow users to generate an attractive list of upcoming events. Use the Report Wizard to create a new report with the following options:

    1. Select the following fields for the report in the order listed: the EventName and EventDate fields from the Events table, the AttendeeFirstName and AttendeeLastName fields from the Attendees table, and the RegistrationFee field from the Registration

    2. View the data by Events and do not add any additional grouping levels.

    3. Sort the report in ascending order first by AttendeeLastName, then by AttendeeFirstName.

    4. Use the Stepped layout and Portrait

    5. Title the report Event Roster, then preview the report.

18. View the Event Roster report in Layout View, then resize the AttendeeLastName column so that its left edge is moved to the right as shown in Figure 6 below. (Hint: Figure 6 shows only a portion of the first page of the report.)

 

Attachments:

Related Questions

. Introgramming & Unix Fall 2018, CRN 44882, Oakland University Homework Assignment 6 - Using Arrays and Functions in C

DescriptionIn this final assignment, the students will demonstrate their ability to apply two ma

. The standard path finding involves finding the (shortest) path from an origin to a destination, typically on a map. This is an

Path finding involves finding a path from A to B. Typically we want the path to have certain properties,such as being the shortest or to avoid going t

. Develop a program to emulate a purchase transaction at a retail store. This program will have two classes, a LineItem class and a Transaction class. The LineItem class will represent an individual

Develop a program to emulate a purchase transaction at a retail store. Thisprogram will have two classes, a LineItem class and a Transaction class. Th

. SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of Sea Ports. Here are the classes and their instance variables we wish to define:

1 Project 1 Introduction - the SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of

. Project 2 Introduction - the SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of Sea Ports. Here are the classes and their instance variables we wish to define:

1 Project 2 Introduction - the SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of