Microsoft Access Semester Project



Microsoft Access Semester Project

Nest Egg Investment Club

Fall 2013

Overview

This project is based on Case 1 from the Adamski textbook. The project has been adapted and expanded by the instructor to meet the course objectives.

Barbara and Neal Hennessey and some friends recently formed an investment club. Researching investment clubs on the Web, Barbara found the National Association of Investors Corporation (NAIC) site. Established in 1951, NAIC is a nonprofit organization founded to educate investment clubs and individual investors. Following guidelines recommended by NAIC, the club members chose Nest Egg as their club name, prepared and approved a partnership agreement and a set of bylaws, registered their club, obtained the club’s tax ID, and established an online brokerage account to handle the club’s investments.

The club decided to meet monthly. Each club member each will receive one vote for all club matters. Each of the members can contribute $200 monthly (a participation level of 1) or $400 monthly (a participation level of 2).

The online brokerage account will track the club’s investments, but Felicia Rodriquez, the club treasurer, has been working on a database to handle club accounting for monthly dues and for any future withdrawals. Felicia has enlisted your help to design this database and the objects for the database.

Below you will find a list of requirements for this database. Some requirements are very specific while others require you to apply your expertise, judgment, and critical thinking.

Project Objectives

▪ Plan, create and edit tables (20 points)

▪ Create Relationships (2 points)

▪ Create and format entry forms (15 points)

▪ Interact with a database by entering data (3 points)

▪ Interact with other Office applications by importing & exporting data (10)

▪ Create queries (16 points)

▪ Create and format reports (20) points)

▪ Create a switchboard (10) points)

▪ Print selected records and reports (4 points)

Due Date/Submission

This completed project is due by 11:00 p.m. on Tuesday, Dec 10, 2013. A 10 point bonus can be earned by submitting the project by 11:00 p.m., Tuesday, December 4. Turn in your project by uploading your Access file to the module labeled Semester Project under Assignments in NS Online. You will also be asked to print out hard copies of certain documents throughout this project. Your printouts should be collected in a three-prong folder and turned in to your instructor by the specified due date and time. Include a title page with the following information centered on the page. Place all printouts in the order printed behind the title page.

Title Page Layout:

Your Name

Database Applications

Fall 2013

Current Date

Grading

This project is worth 10% of your overall course grade. The project is worth a total of 100 points broken out as shown under Project Objectives above. Each project element will be graded based on completeness, accuracy, application of database concepts, and presentation (including user interface).

Project Requirements/Instructions

Database

Download and save the [Your Last Name] Nest Egg F13 database emailed to you by your instructor.

Tables

Overall Table Requirements

• Create fields that efficiently and effectively store the data for each table.

• Define descriptive field names and apply appropriate properties for the fields in each table.

1. Design and create a table in Design View named Members to store member information.

a) Create appropriate field names and properties to store member data.

b) Current member data is found in the spreadsheet labeled Nest Egg Members. Use the current Nest Egg members list as a basis for the information to be stored in this table.

c) There will only be one record per member in the Members Table.

d) Most of the club members reside in California; therefore, the default value for the state field should be CA.

e) The level field indicates the participation level of each member and is used in calculating member fees. There are only two participation levels. Set a validation rule so that the only two acceptable entries for this field are 1 or 2.

f) Add an attachment field named InvestorProfile. This field will be used to store a survey on investor traits and objectives obtained from the NAIC.

2. Design and create a table in Design View named InvstProgram to store information about the investments offered by the club.

a) A list of investments offered by Nest Egg is attached. Use the list of investments as a basis for designing this table.

b) Create appropriate field names and properties.

c) Each investment is unique.

3. The Contributions table has already been created and populated with data. This table is used to store information about who invested in the program, when the investment was made, and what funds were invested in.

a) Adjust the ProgramID field to include a combo box with a value list displaying each program ID. See the Investments spreadsheet for a list of ProgramIDs.

b) Test your value list. When you go to Datasheet View, you should get a drop-down menu in the ProgramID field.

c) The primary key for this table was omitted in design. The PaymentID field should be set as the primary key.

Import Data

1. Import (or copy) the data from the Nest Egg Members Excel spreadsheet to the Members Table in Access. The Excel spreadsheet is found under the Project link on the Lessons page.

2. Import (or copy) the data from the Investments Excel spreadsheet to the InvstProgram table in Access. The Excel spreadsheet is found under the Project link on the Lessons page..

Relationships

1. Close all open objects.

2. Define and save relationships between all tables in the database. Since there may be multiple contributions to multiple investments, you will not be able to enforce referential integrity between the

Forms

Overall Form Requirements

• Use a pleasing format (font style, size, color, bold, etc.).

• Remove all rectangles around textboxes and remove alternate background colors.

• Include explanatory text if necessary.

1. Design and create a custom entry form for member data with a subform for contributions data.

a) Name the form MemberContributionsFrm.

b) The layout of the form should match that shown in Exhibit A.

c) Close the main form and open the subform in datasheet view. Autosize the subform columns. Close the subform and open the main form.

d) Add a calculated field on the main form that totals the investments shown on the subform (HINT: You will have to add a calculated field to the subform to total the contributions – For more information, review Tutorial 6, pages 337-343). Make sure the label for the calculated field is appropriate.

e) Add the following formatting to your form. The formatting is not shown in the example. Use your own judgment and style.

i. A form header with an appropriate title.

ii. A background color for the detail section of the main form with a coordinating background color for the form header.

iii. Change the text color of the labels to a color coordinating with the background.

iv. Add a two-point dividing line somewhere on the form.

v. Format the calculated field for Currency with no decimal places.

f) The tab order for the form should be as follows:

Member ID, Join Date, Level, First Name, Last Name, Address, City, State, Zip, Phone, Subform. Skip the Investor Profile field, but allow users to select it. Skip the calculated field and do not allow users to select it.

Enter Data

1. Using the MemberContributions Form, enter yourself as a member of Nest Egg Investment Club.

a) Your Member ID is I-990.

b) The join date is April 25, 2013.

c) Your level is 1.

d) Be sure to use your own name but all other data may be fictitious.

2. Using the Find command, locate the record for Maureen Macy.

3. Using the MemberContributions Form, enter the contributions for April in the contributions subform. The April contribution data is found in Exhibit B.

4. Using the Find command, locate the record for Maureen Macy.

a) In the Attachment field, add the spreadsheet called Investor-007 to the record for Maureen Macy. This spreadsheet contains the investor profile for this member and can be found in the Project link.

b) Print the record for Maureen Macy.

Queries

Design queries to help Barbara generate the information described below. Some queries will be used as “reports” themselves. Other queries are the basis for reports to be designed in the Reports object.

1. Barbara wants to be able to retrieve investment data on specific investment types at any time by specifying the investment she wants to see.

a. Using the Contributions table, create a query called Investment Parameter.

b. Include the program ID, paid date, and amount in the results of this query.

c. Be sure to use a query feature that lets Barbara specify the investment type she wants to view when the query is run.

2. Barbara needs information to prepare member investment statements.

a. Design a query called StatementDataQry based on all three tables.

b. Include the member ID, member name, complete address, payment date, program description, and investment amount in the query results. A report will be based on this query.

3. Barbara wants a listing of all investments by investor.

a. Design a query called MonthlyInvestmentsQry based on all three tables.

b. Include the member name, investment description, investment amount, and paid date.

c. Create a parameter criteria for the date field that allows the user to specify the beginning and ending dates for the report. HINT: Use two parameters within the BETWEEN/AND operators.

d. Test your query by running the query with the following dates: Beginning Date – 3/1/2013; Ending Date – 3/31/2013.

e. In Datasheet view, turn on the Sum function and sum the investment amount field. Check figure: $3,000.

4. Barbara wants to see the total of each month’s investments by city and month.

a. First create a query called CrosstabData based on the Contribution and Member tables. Include the City, PaidDate, and InvestmentAmt fields in the query results.

b. Using the Crosstab Query Wizard, Design a query called InvestmentsByMonthQry based on the CrosstabData query. The City will be the row heading and the Date (by month) will be the column heading. Sum the investment amount.

c. Save your changes and print the crosstab query results.

5. Felicia, the club treasurer, wants to send a welcome letter to new members. A report will be based off of this query.

a. Design a query called NewMembersQry based on the Member table. Include the member ID, name, address, level, and date joined fields in the query results.

b. The criteria for the JoinDate field should be BETWEEN 4/1/2013 AND 4/30/2013. This criteria is set so that only members joining in the latest month, April, will be selected.

c. Add a field in which you enter an IIF expression. The IIF expression will return a value of 200 if the membership level is 1 or 400 if the membership level is 2.

d. Change the name that displays for the expression field to “Contribution Amount”.

Generate Information – Reports

Overall Report Requirements

• Use a pleasing format (font style, size, color, bold, etc.).

• Remove all rectangles around textboxes and remove alternate background colors.

• Make the page width is correct and that each section length is correct.

1. Statement report. Design a custom report that will print a statement for each club member. Design your statement to match that created by Barbara shown in Exhibit C.

a. Base the report on the Statement Data query.

b. Add a MemberID Header and Footer (use the Sorting and Grouping tool.)

c. In the MemberID Header, concatenate the member first and last name so that the full name displays in one textbox.

d. Add the member address to the MemberID Header. Concatenate the city, state, and zip code.

e. Place the PaidDate, Description, and InvestmentAmt fields in the Detail Section of the report.

f. Add an unbound Text Box in the MemberID Footer. Place a formula to total the InvestmentAmt for each member. In the Properties box, change the name of this field to TotalInvestment.

g. Make each statement a separate page by placing a page break in the MemberID Footer section of the report.

h. Apply appropriate design and layout for this report.

i. Print the first statement.

2. New Member Report.

a. Design a report based on the NewMemberQry.

b. Add a group header and footer for the MemberID. Place the MemberID in the upper left corner of the Group Header.

c. Place an address label in the Group Header. The address label should include concatenated fields for the member name and city, state, zip. HINT: You can copy these fields from the Statement Report.

d. Make sure that the page width for the report is 6.5”.

e. Add a label control that starts at the 1” position and stretches to the 6” position on the horizontal ruler.

f. Enter all of the following text in the label box. HINT: to make returns in a label box, press SHIFT + ENTER.

Welcome to the Nest Egg Investment Club. Please make note of your Member ID, shown above, and include this ID on all correspondence and investments.

Below is information on your club level and monthly contribution amount. Contributions should be received by the Club no later than the 20th of each month. Club meetings are held on the fourth Thursday of each month.

We hope this will be a productive and exciting experience for you.

g. Place the level field approximately 2 gridrows below the letter text.

h. Place the contribution amount field below the Level field.

i. Make sure the labels for the level and contribution amount fields are appropriate.

j. Place another label box underneath the two text boxes. Start this label at the 3.5” position on the horizontal ruler and stretch it to the 6” position.

k. Enter the following text in the second label.

Sincerely,

Felicia Rodriguez, Club Treasurer

l. Place a page break in the Group Footer.

m. In addition to the overall report requirements, use your own design and style to create an attractive and effective report.

Export Data

Barbara has asked that you send her the information on the monthly investments.

1. Using Word, create a short memo to Barbara indicating you are sending the requested information. Use proper formatting, grammar and sentence structure.

2. Export the InvestmentsByMonthQry results to this document. Print the memo.

Switchboard

1. Develop a switchboard to operate this database.

a. Design appropriate macros to run the switchboard.

b. Include only the objects that Barbara will need to run (HINT: if a query is used only as the basis for a report, do not include the query in the Switchboard.)

Exhibit A – Layout of MemberContributionsForm

[pic]

Exhibit B - Nest Egg Investment Club April Contributions

|Trans |Date |Investor ID |Investment |Program ID |

|No. | | |Amount | |

|  |April |  |  |  |

|22 |1 |I-005 |$200 |MutFundG |

|23 |1 |I-014 |$200 |BondT |

|24 |5 |I-001 |$400 |MutFundA |

|25 |6 |I-009 |$200 |BondI |

|26 |10 |I-008 |$400 |MutFundG |

|27 |10 |I-012 |$200 |MutFundG |

|28 |14 |I-003 |$200 |MutFundI |

|29 |15 |I-004 |$200 |BondT |

|30 |22 |I-011 |$400 |MutFundI |

Exhibit C - Example of the Layout for the Member Statement

Note—this report does not necessarily represent a check figure but is provided to give you a basis for your design.

|Nest Egg Investment Club |

|201 River Drive |

|Oakland, CA 94618 |

| | | | | |

|Statement of Activity | | | |

| | |Send To: | |

| |Maureen Macy | |

| | |252 Whetstone Rd | |

| | |Salem, OR 97303 | |

|Member Number: |I-007 | | | |

| | | | | |

|Date |Program ID |Investment Amount | | |

|2/17/2013 |BondI |$200 | | |

|3/17/2013 |MutualFundA |$200 | | |

| | | | | |

| | | | | |

| |Total Investment |$400 | | |

| | | | | |

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download