Excel 2007 Presentation

Document Sample
Excel 2007 Presentation Powered By Docstoc
					Microsoft Access 2007




                                   Brian Cotter
                       Unit Computing Specialist
                 Student Affairs Computing Group
                        bcotter@camden.rutgers.edu
                                                                               Microsoft Access 2007

Course Goals
  •   Identify the attributes and needs for using a relational database structure as compared to a flat
      database structure. (Excel vs. Access)
  •   Review common database terms and features.
  •   Review new features of Access 2007



  Hands On!
  •   Create a database for a Detailed Contact/Address Book.
  •   Create a form to be used to populate the sample database
  •   Create queries and reports based on information gathered in the database
  •   Export data from Access to PDF and Excel




  Student Affairs Computing Group
                                                                            Microsoft Access 2007


Excel or Access? What should I use?

  Flat Data Structure vs. Relational Data Structure

  A flat data structure:
  •    Is a simple list that isn’t related to any other data
  •    Is easy to create and maintain – as long as you don’t have too much information
  •    Example: Food Shopping List – unique items (no repitition)

  A relational data structure:
  •   Divides your information into logical pieces, and places each piece in a separate table. (Each
      table is a “flat table” – however, there is a relationship shared between tables)
  •   Lets you manage data easier, because each table holds just part of your information.
  •   Helps keep your information accurate, because you can prevent users from entering data in the
      wrong table.
  •   Ideal use: Tracking actions or events, use with a lot of repeating data.




  Student Affairs Computing Group
                                                                                Microsoft Access 2007

Benefits Using Excel
      •    Storing Numbers
      •    PivotTable reports – reports that allow you to rotate rows and columns to see different summaries
           of your data.
      •    Charts, conditional formatting, color bars, other visual data displays.
      •    Greater familiarity – more people use Excel than Access.




Benefits Using Access

  •       Data Entry – use of forms
  •       Reports that users can run at any time
  •       Multiple users can work on data at the same time (Access locks a record only when a user
          changes data in it. Excel would lock the entire file)
  •       Connecting to external databases and editing data in those databases




  Student Affairs Computing Group
                                                                            Microsoft Access 2007

Access 2007 - Common Database Terms
  Table
        • Lists of rows and columns (similar to an Excel Spreadsheet)
  Record
        • Each row in a table. It is a meaningful and consistent way to combine information about
          something.
  Field
        • Each column (or “heading”) of a table
  Primary Key
        • A primary key is a field in your table that provides a unique ID for every row (record). You
          use table relationships and primary keys to tell Access how to “connect” data in a meaningful
          way.
  Query
        •  request for some pieces of data found in one or more tables
  Report
        • Output of a query, formatted for presentation




  Student Affairs Computing Group
                                    Microsoft Access 2007

Open Access 2007




  Student Affairs Computing Group
                                             Microsoft Access 2007

Create New Database – Choose File Location




  Student Affairs Computing Group
                                    Microsoft Access 2007

Save “Contacts.accdb” to Desktop




  Student Affairs Computing Group
                                          Microsoft Access 2007

Introducing the Microsoft Office Button




  Student Affairs Computing Group
                                                                      Microsoft Access 2007

Introducing the Office 2007 Ribbon




                Office 2007 introduces Tabs, Groups, and Commands with the Ribbon




  Student Affairs Computing Group
                                                  Microsoft Access 2007



Change the Table View – Design View




                                      Choose a name for the Table




  Student Affairs Computing Group
                                    Microsoft Access 2007

Create Fields for the New Table




  Student Affairs Computing Group
                                                Microsoft Access 2007

Switch to DataSheet View – Populate Table




 Create a New Table – “Addresses” – Populate with Data




  Student Affairs Computing Group
                                       Microsoft Access 2007

Create a Relationship between Tables




  Student Affairs Computing Group
                                    Microsoft Access 2007

Creating Relationships – Cont’d




  Student Affairs Computing Group
                                                  Microsoft Access 2007

Additional Example for Tables and Relationships




  Student Affairs Computing Group
                                            Microsoft Access 2007

Create a Form – Allow users to Enter Data




  Student Affairs Computing Group
                                            Microsoft Access 2007

Create a Form – Use Data from Both Tables




  Student Affairs Computing Group
                                    Microsoft Access 2007

Create a Form – “Form View”




  Student Affairs Computing Group
                                    Microsoft Access 2007

Forms – Create a New Entry




  Student Affairs Computing Group
                                                  Microsoft Access 2007

Creating a Query – Friend’s and Email Addresses




  Student Affairs Computing Group
                                    Microsoft Access 2007

Creating a Query – Cont’d




  Student Affairs Computing Group
                                    Microsoft Access 2007

Edit the Query – Design View




  Student Affairs Computing Group
                                                Microsoft Access 2007

Reports – Using the Microsoft Access Report Wizard




  Student Affairs Computing Group
                                     Microsoft Access 2007

Reports – Based on Query or Tables




  Student Affairs Computing Group
                                    Microsoft Access 2007

Reports – Cont’d




  Student Affairs Computing Group
                                    Microsoft Access 2007

Reports – Cont’d




  Student Affairs Computing Group
                                    Microsoft Access 2007

Viewing the Final Report




  Student Affairs Computing Group
                                                                          Microsoft Access 2007

Reports – Grouping
  •   Grouping allows you to separate groups of records visually and to display introductory and
      summary data for each group. For example, the following report groups sales by date shipped and
      calculates the total amount of sales for each day.




  Student Affairs Computing Group
                                    Microsoft Access 2007

Editing a Report – Design View




  Student Affairs Computing Group
                                    Microsoft Access 2007

Exporting Data - Reports




  Student Affairs Computing Group
                                    Microsoft Access 2007

Exporting Data - Tables




  Student Affairs Computing Group
                                                                Microsoft Access 2007

Additional Resources & Links

  Basic Information / Examples from Class
  • http://studentaffairs.camden.rutgers.edu/SAC/office2007/index.html

  Online Tutorials
  • http://office.microsoft.com/en-us/training/FX100565001033.aspx




  Comments / Questions?




  Student Affairs Computing Group

				
DOCUMENT INFO
Description: Excel 2007 Presentation document sample