INTEGRATED CASE STUDY: THE FIU WELLNESS CENTER

The purpose of this integrated case is to develop 8 solutions in MS Office that relate to the FIU Wellness Center.  Each exercise describes a specific task for the FIU Wellness Center and combines Word and Excel, PowerPoint and Excel, Excel and Access.

Your assignment is to follow the directions of solutions 5-8.

5. Importing Data (Access and Excel): The FIU Wellness Center database contains two tables: one for members and one for the various membership plans that the club to its members. The Plan table already exists within the Access Database. The member information, however, is in an Excel spreadsheet. Your first task is to import the Excel worksheet into the Access database, when it will be converted to an Access table called Members.

1. Download the following spreadsheet 3300FIUWellnessMembers.xls to your FIU Wellness Center Folder on your desktop as with the previous solutions 1-4.
2. Download also 3300FIUWellnessPlanid.xls
3. Start Access. Click on file then new, choose blank database. Under filename name it 3300FIUWellnessCenter.
4. Pull down the File menu, click (or point to) the Get External Data command, and then click the import dialog box.
5. Change the file type to Microsoft Excel.
6. Look on your desktop FIU Wellness Folder for 3300FIUWellnessMembers and select the workbook to start the Import Spreadsheet Wizard.
7. Check the box that indicates the first row contains column headings as shown in Figure 5.
8. Click Next. Select the option button to store the data in a new table. Click Next.
9. You do not need information about the individual fields. Click Next.
10. Select the option to choose your own primary key. Select memberid if it is not already selected. Click next.
11. Click the Finish button, and then click OK when the wizard indicates that it has imported the table. The Members table appears in the Database window. Double Click on Members table to ensure that the data has been imported.
12. Repeat this process and import 3300FIUWellnessPlanid
13. But let access choose the primary key this time.
14. Check again that the data has been imported correctly.
15. Save the completed document as ICEx5. You will need to burn a CD when you have finished all your solutions. 

Figure 5

6. Mail Merge (Word and Access): This exercise uses the Mail Merge utility in Microsoft Word in conjunction with an Access Database.  You will be asked to create a form letter which will be sent to a different recipients.  We will start from Word.  Proceed as follows:

You should have already downloaded the 3300FIUWellnessMembers.xls  and 3300FIUWellnessPlanid.xls  documents to you Wellness Center folder and have EX. # 5 completed.

  Steps_

1.      Open the Letters to Members document and save it as Letters to Members Solution.

2.       Pull down the Tools menu, click Letters and Mailings then click Mail Merge Wizard. Under Select Recipients, select use an existing list. Then click on Next: Write your letter.

 3.      On the Select Data Source window, click on Open. 

4.      On the Data Connection Wizard click ODBC DSN. Then under OBDC data source, select MS Access Database.

 5.      On the Select Database window, select the drive where your file is located. Next click on the appropriate file.

 6.      On the Data Connection Wizard – choose data window, select members and click next then click finish.

 7.      On the Mail Merge Recipients window, make sure that all recipients are selected and click Ok

 8.      Click on Next: Write your letter at the bottom right of the screen.

 9.      Click under the date on your document,  DATE \@ "MMMM d, yyyy" July 19, 2006, and click Address block from the right.

 10.  To add Membership Date, click on (More items) on the right. On the Insert Merge Field window, select Membership Dade and click insert. Afterwards, press enter twice to insert the greeting line.

 11.  Click on (Greeting line) on the right. On the greeting line window click OK. 

12.  Click the View Merged Data on the button on the Merge Mail tool bar. This is the <<ABC>> button to the right of the Insert Word Field. Toggle between the letter, and specify the first of the three records, which is Abraham Albertson.

13. Save the completed document as ICEx 6. You will need to burn a CD when you have finished all your solutions.

  Figure 6

7. Access Objects:  Reports  (Ex 7): Open the FIU Wellness database in the FIU Wellness database and create the additional objects as described below.

1. Create an Access report using Wizard, which shows the member addresses in alphabetical order by member last name. The report should be based on the Member table and should contain the following fields: Member ID, Membership Date, Plan ID, Last Name and First Name.
2. Click Next to display the wizard’s second page which is sort. This page allows you to organize it in any logical manner that you might fill is appropriate. Click Next.
3. The next page asks you to choose layout - choose columnar and portrait.
4. Next choose corporate.
5. What title do you want? Exercise 7.
6. Save the completed document ICEx7. You will need to burn a CD when you have finished all your solutions.

Figure 7

8. Access Objects:  Queries (Ex 8): Open the FIU Wellness database in the FIU Wellness database and create the additional objects as described below.

1. On the objects bar, Click Queries and then double click Create query in Design view.
2. In the Show Table dialog box click on Members, then click add
3. Close the Show Table dialog box.
4. Click on the Sum button on the Tool bar this will create a line which will say Total.
5.   The field cell should read Member ID, the table cell should read Members, and the total cell should read count.
6. If the total cell does not read count, click on it and an arrow will appear press it add select Count.
7. Run the query by pressing the red Exclamation mark on the tool bar.
8. The query should resemble Figure 8.
9. Save the completed document ICEx8. Now it is time to burn your CD and clearly mark it with your name and Panther ID. Hand it in in class.

Figure 8