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

 

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:

  1. Open the partially completed Letter to Members word document in the FIU Wellness Center and save it as Letter to Members Solution so that you can return to the original document if necessary.
  2. Pull down the Tools menu, click Letters and Mailings, then click Mail Merge Wizard to open the task pane.  Select the Letters option.  Click Next.  Select the option to use the current document.  Click Next.
  3. Select the recipients from an existing list.  Choose the FIU Wellness Center Folder, select the FIU Wellness Center database, and select the Members table to display the Mail Merge Recipients dialog Box.
  4. Add the address block, Membership Date and Greeting line as shown in Figure 6.  Click the Preview Your Letters button to toggle between the field names and the actual data.
  5. Complete the merge and specify the first of three records, as shown in Figure 6 (there are more than 60 records in the database). Submit this information by sending an email to your groupl with the subject line CGS3300/Integrated Case Exercise 6 /Your Name.
  6. Save the completed form letter.  Save the merged document.  Close Word.

  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 tabular and portrait.
  4. Next choose corporate.
  5. What title do you want? Exercise 7.
  6. Finish and save.

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. Save.

Figure 8