Your Federal Quarterly Tax Payments are due April 15th Get Help Now >>

Microsoft Access - Get as PowerPoint by 3K50Av1m

VIEWS: 4 PAGES: 19

									 Microsoft Access

Database Creation and Management
    Basic Database Definitions
• Table
  – All the data relating to a particular subject.

• Record
  – An individual entry in a table.
  – Like a file folder.


• Field
  – Piece of information in a record.
  – Last Name, First Name, Address, City
     Access Database Objects
• Table Object
  – Data Table: Basic area where your data is
    stored and manipulated.

• Query Object
  – Results of a question or condition you outline.
  – All students at MSU who belong to the COB.
     Access Database Objects
• Form Object
  – Data entry screen that you can customize .
  – Used to add new records.

• Report Object
  – Used to print and emphasize specified
    information.
 The first step in creating a
database table is to create its
          structure.



    You begin by specifying the fields
    you want to include and their
    characteristics.
• Primary Key:
  – A field that uniquely identifies each record in a
    table.
  – Used to sort or index a database.
  – No two records can have the same primary key.

• Input Mask
  – Allows you to simply data entry for fields that
    have the same format.
  – You create the standard format (the mask)

               Access
     Modifying the Data Table
• Widen out or narrow down columns to
  improve appearance. DO NOT CUT OFF
  WORDS.

• Add new records.

• Add new structure items.
             Database Views
• Design View
  – Lets you see the structure of the database and
    create its properties.

• Datasheet View
  – Lets you see the actual data.
       Manipulating a Database
• Find: Searches the entire database for
  specified criteria.

• Sort records into a particular order.
  – Sort columns in ascending or descending order
  – Can also sort adjacent columns. Access sorts
    adjacent columns from left-to-right.
  – If columns are not in the correct order for
    sorting, you must rearrange the columns.
                                                        Access

           Manipulating a Database
• Filter: Displays only a subset of the entire table
  that matches your criteria. Every single field shown
  – Filter By Selection
     • Select information you want to see: filter finds match
  – Filter By Form
     • Allows you to specify multiple criteria
• Query Design
  – Specify the exact criteria you wish to view.
  – View only the fields you wish to see: All other fields not
    shown.
      Common Query Operators
•   Text Criteria       “Fred Jones”
•   Greater than >         >1000
•   Less than        <     <1000
•   Equal to         =     =1000
•   Greater than or equal to
             >=           > =1000
    Less than or equal to
             <=           < =1000
      Creating Relationships
• Relationships are used to link tables
  together.

• Relationships are based upon common
  fields in both tables.

• Drag the name from one table over to the
  next table and a connecting line will appear.
                                          Access
                    Reports
                                             Access
• Reports are used to “Dress Up” or
  summarize your data.

• Reports can be made from a Table or
  Query.

• You can use a Wizard to create your reports
  or you can customize them to fit your needs.
  – You can specify sorting orders and you can also
    group data.
                   Forms                  Access

• Forms are used to customize your data entry
  screen. You see only the relevant items in
  the location where you want them.

• Forms are created for the convenience of
  the user.

• You can change locations of fields and
  colors very easily in the Design screen.

• Forms are used to update Data Tables.
CoachID   First Name   Last Name   Phone Number     Address               City    State   Zip Code   Status   Nickname     Colors
   C01    Steve        Zion        (954) 753-3553   6754 Lakeview Dr.     Coral   FL      33071      1        Rockets      Red/White
   C02    Michael      Moldof      (954) 753-6696   100 Oak Lane          Coral   FL      33071      2
   C03    Neil         Goodman     (954) 752-0279   3800 Westview Dr.     Coral   FL      33071      2        Rockets      Red/White
   C04    David        Stone       (954) 753-1019   550 Sample Road       Coral   FL      33067      1        Tornadoes    Blue/Gold
   C05    Frank        Barber      (954) 752-1817   4545 Westview Dr.     Coral   FL      33071      2
   C06    Curtis       Kimble      (954) 344-9979   1101 Ramblewood Rd.   Coral   FL      33071      1        Seminoles    Maroon/Gold
   C07    Caren        Zarinsky    (954) 753-2265   4532 Maplewood Dr.    Coral   FL      33065      2
   C08    Derek        Anderson    (954) 344-8734   1345 University Dr.   Coral   FL      33071      2        Bulldogs     Red/Black
   C09    Karen        Kinzer      (954) 752-0098   922 Sample Road       Coral   FL      33067      1        Gators       Orange/Blue
   C10    Alex         Fraser      (954) 344-8665   4566 Parkside Dr.     Coral   FL      33071      1        Hurricanes   Orange/Green
   C11    Lorenzo      Pearson     (954) 752-6543   2450 Maplewood Dr.    Coral   FL      33065      1        Bulldogs     Red/Black
   C12    Shira        Citron      (954) 753-9110   500 Oak Lane          Coral   FL      33071      2        Seminoles    Marron/Gold
                                       Coaches

CoachID   First Name   Last Name   Phone Number     Address               City            State   Zip Code   Status   Team ID
   C01    Steve        Zion        (954) 753-3553   6754 Lakeview Dr.     Coral Sprigns   FL      33071      1           T01
   C02    Michael      Moldof      (954) 753-6696   100 Oak Lane          Coral Springs   FL      33071      2
   C03    Neil         Goodman     (954) 752-0279   3800 Westview Dr.     Coral Springs   FL      33071      2           T01
   C04    David        Stone       (954) 753-1019   550 Sample Road       Coral Springs   FL      33067      1           T05
   C05    Frank        Barber      (954) 752-1817   4545 Westview Dr.     Coral Springs   FL      33071      2
   C06    Curtis       Kimble      (954) 344-9979   1101 Ramblewood Rd.   Coral Springs   FL      33071      1           T07
   C07    Caren        Zarinsky    (954) 753-2265   4532 Maplewood Dr.    Coral Springs   FL      33065      2
   C08    Derek        Anderson    (954) 344-8734   1345 University Dr.   Coral Springs   FL      33071      2           T03
   C09    Karen        Kinzer      (954) 752-0098   922 Sample Road       Coral Springs   FL      33067      1           T06
   C10    Alex         Fraser      (954) 344-8665   4566 Parkside Dr.     Coral Springs   FL      33071      1           T04
   C11    Lorenzo      Pearson     (954) 752-6543   2450 Maplewood Dr.    Coral Springs   FL      33065      1           T03
   C12    Shira        Citron      (954) 753-9110   500 Oak Lane          Coral Springs   FL      33071      2           T06
Teams
TeamID   Nickname     Colors
   T01   Rockets      Red/White
   T02   Comets       Blue/White
   T03   Bulldogs     Red/Black
   T04   Hurricanes   Orange/Green
   T05   Tornadoes    Blue/Gold
   T06   Gators       Orange/Blue
   T07   Seminoles    Maroon/Gold
PlayerID   First Name   Last Name   Address                  City            State   Zip Code   Phone Number     BirthDate    Rating   Team ID
    P01    Julie        Zion        6754 Lakeview Dr.        Coral Sprigns   FL      33071      (954) 753-3553     8/8/1982   B           T01
    P02    Britta       Zarinsky    4532 Maplewood Dr.       Coral Springs   FL      33065      (954) 753-2265     9/9/1992   B           T07
    P03    Jenna        Moldof      100 Oak Lane             Coral Springs   FL      33071      (954) 753-6696   12/26/1983   B           T05
    P04    Melissa      Kimble      1101 Ramblewood Rd.      Coral Springs   FL      33071      (954) 344-9979    5/31/1982   C           T07
    P05    Jessica      Anderson    1345 University Dr.      Coral Springs   FL      33071      (954) 344-8734    3/16/1983   A           T03
    P06    Leslie       Ericson     8922 Coral Ridge Dr.     Coral Springs   FL      33069      (954) 753-2000   12/21/1983   B           T03
    P07    Jessica      Goodman     3800 Westview Dr.        Coral Springs   FL      33071      (954) 752-0279    3/16/1983   A           T01
    P08    Marci        Barber      4545 Westview Dr.        Coral Springs   FL      33071      (954) 752-1817    5/15/1982   A           T04
    P09    Nancy        Dorman      900 Ramblewood Rd        Coral Springs   FL      33071      (954) 344-8977   12/12/1982   A           T02
    P10    Julie        Fraser      4566 Parkside Dr.        Coral Springs   FL      33071      (954) 344-8665     4/4/1982   C           T04
    P11    Kelly        James       800 Coral Springs Dr.    Coral Springs   FL      33169      (954) 753-4455     7/1/1982   A           T03
    P12    Sherrie      Green       6600 Main Street         Coral Springs   FL      33065      (954) 753-1554    8/22/1982   A           T04
    P13    Priscilla    Pearson     2450 Maplewood Dr.       Coral Springs   FL      33065      (954) 752-6543     2/3/1983   B           T03
    P14    Kerri        Stone       550 Sample Road          Coral Springs   FL      33067      (954) 753-1019     4/4/1983   C           T05
    P15    Toni         Moldof      522 Coral Springs Dr.    Coral Springs   FL      33069      (954) 752-5633   11/11/1983   B           T05
    P16    Shelly       Parker      1001 Coral Ridge Dr.     Coral Springs   FL      33069      (954) 753-0345   11/24/1982   C           T02
    P17    Amy          Kinzer      922 Sample Road          Coral Springs   FL      33067      (954) 752-0098     5/5/1982   C           T06
    P18    Mary         Citron      500 Oak Lane             Coral Springs   FL      33071      (954) 753-9110    3/16/1983   A           T06
    P19    Jessica      Grauer      1881 Vestal Road         Coral Springs   FL      33071      (954) 732-1725    3/16/1983   A           T01
    P20    Sonja        Freed       2210 Main Street         Coral Springs   FL      33065      (954) 753-9982     7/1/1982   B           T06
    P21    Lori         Pryor       2001 Parkside Dr.        Coral Springs   FL      33071      (954) 344-5567    6/18/1983   C
    P22    Lauren       Howard      1520 University Dr.      Coral Springs   FL      33071      (954) 752-4536    11/1/1982   A
    P23    Katie        Remmen      2200 Cedarwood Dr.       Coral Springs   FL      33067      (954) 753-4577    4/18/1982   A
    P24    Katherine    Akong       1705 Ramblewood Dr.      Coral Springs   FL      33071      (954) 344-5578     2/2/1982   C
    P25    Lilly        Taboas      1025 Coral Springs Dr.   Coral Springs   FL      33069      (954) 752-3487    5/22/1983   B
          Operators for Queries
Operator           Meaning                   Example
 =                equal to                     =“kim”
 <                less than                    <#1/1/99#
 <=               less than or equal to       <=100
 >                greater than                >”C400”
 >=               greater than or equal to    >=18.75
 <>               not equal to               <>”Hall”
 Between… And..    Between two values         Between 50 And 325
 In()              in a list of values        In (“Hall”,”Seeger”)
 Like              matches a pattern that     Like”706*”
                      includes wildcards

								
To top