Databases with Microsoft ACCESS by ems33260

VIEWS: 0 PAGES: 20

									 Databases with Microsoft ACCESS
                  (Written for Troy City Schools as an introduction to Access)


       Da•ta•base: n. A collection of data arranged for ease and speed of search &
                   retrieval. An organized body of related information.


As the definition states, Access databases can be used in every conceivable aspect where you
have data that you want store, retrieve, and analyze. You are only limited by your imagination
and your willingness to learn.


Examples of How TCS Uses Databases:
    Store every last bit of info about students. You’ll find databases for student computer
     accounts, grades, attendance, clinic visits, and class lists.
    Store information about staff, including both home and school information. This info is
     used to generate a staff directory, assign paychecks, track staff development, and report
     statistics to the state.
    Track enrolment numbers and funding for students and find discrepancies.
    Store schedule information for both daily school activities and sporting events that are
     accessible to all on our website.


Why an Access Database over Excel Spreadsheet:
   Security
   Reliability
   Ease of sorting and analyzing large amounts of data.
   Ability to safely share common data with multiple parties.
   Multiple users can work with one database at the same time.
   Easily import or export data to/from other sources.
   Mail-merge with Word or print labels/forms.
   Interface with webpages.


What Can You Store in a Database?
   Text & memos
   Numbers
   Dates/Times
   Web or E-mail address.
   Pictures or other such files.
   “Yes/No” values.




                                               1
Is Access for Everyone?
Yes and No. While there is pretty much an application in everyone’s life where Access could be
applied, many cases may be too small to justify the time spent setting up and retrieving data. It
is really on a case by case basis, and many times Access may be too big for the job.

Also, Access has a very high learning curve, so you need to have the time to stick with it. But
once you get over that curve, it is very difficult to stop using Access. It is also much easier to
use databases if you think logically and plan ahead. Since database applications are about the
rawest form of computing, you must rely on your own brain to do a lot of the planning.

In a world where getting the information quickly is key, databases are going to be everywhere.
Understanding them will help you stay ahead of the curve. Knowing how to use them will make
you invaluable.


DATABASE PARTS/DEFINITIONS
Database: One file that is a complete package of data (usually stored in tables) and
          retrieval/analysis components. Like data and/or objects are stores in the same
          database regardless of how may tables or objects you have.

Table:        A collection of like data. Tables are usually viewed in columns (fields) and rows
              (records).

Field:        A container for holding one like type of data that is common to each record. A
              field will have a label and associated type, and may also contain other variables or
              default values. Field types include: text, numbers, date/time, memos, OLE objects
              (like pictures), check boxes, or record ID numbers.

Record:       A unit of data that you intend to track having values that correspond to your fields.
              A record is a separate “event” that you wish to track and analyze, such as a person,
              activity, book, or product.

Query:        A utility that helps you sort, filter, merge, separate, or redefine data from a table.
              This is the most power feature of a database which makes retrieval/analysis easier
              for the users. Queries do not contain any data themselves, but rather redisplay data
              from a table in a form that better to a certain result.

Form:         Forms are usually used to help users enter data. While you can enter data directly
              into a table, forms will provide and graphical interface that may be easier to users
              to understand. Forms can also be used to assist in advanced data entry, in printing,
              and in navigation menus.

Report:       Used to export data and analysis in a format that is best understood by users.
              Reports are generally designed to be printed and can be designed in almost any
              layout to better reach your audience. Reports can be in a table and form format, as
              labels, or a standard document.

Macro:        Macros are “small programs” that help the user by doing repetitive tasks on call.
              For instance, you can have a macro print several reports at one time, or run
              multiple queries at the click of a button. Basic macros are straight forward and do
              not require any program.
                                                  2
Expressions:   These are lines of logical code that allow you to manipulate the data. They can
               be as simple as “=10” when assigning criteria, to replacing values if a statement
               equals a value in another table. Access provides help with expressions, but it is
               up to you to understand the logic of what you are asking for.

Datasheet:     About every function of Access includes a “Datasheet” view, which is pretty
               much what the default table format (or what Excel would look like). For
               instance, you may be entering data into a Form and decide that you want to print
               the data you entered with one record per line. You can switch to datasheet view,
               print, and switch back to you normal view without messing any data up.

Relationships: You can link two tables or queries together so as to combine data into one query
               or compare multiple sets of data.


PLAN YOUR DATABASE

The biggest part of a successful database is the planning before you get started. This will make
thing go much easier, both in the setup and future expansion/usage. While you can always go
back and make changes, you can often waste your own time and will kick yourself for not
making it more flexible.

Here are a few things that you should keep in mind when planning your database:
    Write down what kind of info you want to collect. This may be names, dates, numbers,
       comments, or images.
    Do you need to subdivide your info? For instance, you can have one field for full name,
       or do you need two fields for first and last names. You can always combine field in a
       query, but less columns make for a cleaner database.
    Be careful giving your fields names. Make sure that it communicates what the field is.
       Once you set a name, it should never change. Also, make sure that the name is not also a
       reserved value (see page 8). While it still may work, it can cause problems later. Such
       names may include: “Name”, “Count”, “Sum”, “Value”, “Date”. My trick for getting
       around this is to add an “X” when there is a questionable name.
    Do NOT make names date sensitive. For instance, do not label a table “0809”. Next year
       when you want to make a new table you have to change all your queries and forms to
       match. Instead, make it something like “Current”. At the end of the year when you want
       to start a new table, simply rename the old one with a year code and make a new one
       named “Current”. Your queries will accept the new table.
    How do you want to retrieve or analyze data? Make sure that your fields are clearly
       designed to sort or look up info. For instance, if you want to always sort by city or ZIP
       code, make sure they are separate.
    Do you want to tie two or more tables together? For instance, if you have a list of staff
       and the buildings they belong to, you can make a second table that contains just building
       info. This way, you only have to type the building info once and make changes once.
       Later, you can merge this info together in a query.
    Will others be using your database or sharing your info? If so, you may want to plan
       field names and table layouts to accommodate their use. Also, you may need to setup
       special security measures.


                                               3
STARTING A NEW DATABASE:

    1) Open Microsoft Access (can be done from [Start] > Programs or the Shortcut Bar).

    2) A screen will come up like the one to the right.

        To start a new database, choose Blank Access
        database and [OK].

        If you already have a database created, then
        choose Open an existing file and either choose
        from the list of recent files listed or click [OK] to
        browse.

    3) When creating a new file, it will prompt you right
       away to save the file. Make sure to choose the
       proper location (like your U:\ drive) and then give the file a name.

    4) Once you have created a new file, Access will open to the main database window, which
       is titled “{filename}: Database”. You are now ready to work.

Take a few minute to familiarize
yourself with this window as
you will be using it a lot.
                                            C       D           E
(A) Objects – Jump between the
    functions of the database.
                                       A
(B) List of objects that you have
    created.                                                        B

(C) Open – Opens the
    highlighted object, like a
    table, query, or report.

(D) Design – Edit the design of
    the highlighted object like a
    table, query, or report.

(E) New – start a new object,
    from scratch or with a
    wizard.




                                                   4
START A NEW TABLE:

When working with a database, the first thing you always want to do is setup a table. Without a
table for data, none of the other functions of a database will work. To setup a new table:
    1) From the main database window, make sure you are on the Tables tab under Objects.
    2) Click on New.
    3) Choose Design View.
    4) In the first blank under Field Name, type in the name for your first field.
    5) Under Data Type, choose an option from the drop down menu that corresponds with the
        field.
    6) If you like, enter a Description for the field to help remind yourself what it is in the
        future. Description is optional.
    7) Set additional options on the General and Lookup tabs below the list. (See Page 6.)
    8) Repeat steps 4-7 as necessary until you have entered all your fields.
    9) Save the table (either click the disk icon or go to File > Save). Give it a name that makes
        sense.
        TIP: Never use punctuation in an object’s name. These characters are allowed: ~_ space
    10) To start entering data, click the “View” icon (right below the menu bar word “File”).




As you enter data into a table, the data entered is saved every time you leave a record. This
means that you never have to remember to save your database. However, if you loose the
network connection to your database, the database can not be used until the connection is
restored.

To get back to the design view, click the “View” icon again.
                                                5
“General” Tab Value Descriptions:
Since each field type has different values you can set, reference the values associated with the
field type you chose.

TEXT/MEMO:
Field Size: Set the maximum number of characters you will use in this field. Can be set at
        1 to 255. You should always choose the smallest number in order to keep the size of
        your database small. (Memos are virtually unlimited in length.)
Format: Can force a certain format like all uppercase. Press F1 in the field for complete options.
Input Mask: Specify the layout of text entered, such as phone numbers or Social Security
        numbers. You can use the wizard (press the “…” button to the right) or program it
        yourself. Press F1 for more help.
Caption: Alternative label for the field when using forms.
Default Value: Enter a value that you want to appear if no data is entered. For instance, if you
        know that “Troy” is going to be entered 95% of the time, you can save yourself retyping
        it by entering it as a default value. You can always change it for new records at any time.
Validation Rule/Text: Access can make sure that the data is valid. Press F1 for help with
        programming.
Required: If set to YES, then access forces you to enter a value before the record can be saved.
Allow Zero Length: About the same as Required, but makes sure a “Null” value is not entered.
Indexed: If you are always looking up data by this field, then indexing the field helps querys run
        faster. If you want to index the field, then you choose whether you want to allow
        duplicate entries in this field, or whether you want every record to be unique.

NUMBER/CURRENCY:
Field Size: Choose the type of number that is the smallest to meet your need…
        Byte = numbers from 0 to 255 (no fractions), no decimal, smallest size.
        Integer = numbers from –32,768 to 32,767 (no fractions), no decimal, med-small size.
        Long Integer (Default) = numbers from –2,147,483,648 to 2,147,483,647 (no fractions), no
                decimal, medium size.
        Single = numbers from –3.40e38 to 3.40e38, includes decimals to 7 places, medium size.
        Double = numbers from –1.79e308 to 1.79e308, includes decimals to 15 places, med-
                large size.
        Decimal = numbers from –10^28–1 to 10^28–1, includes decimals to 28 places, large size.
Format: Show the number in a format, like currency or percentage. Note: the data is always
        stored as a plain number, and only formatted when displayed in datasheet, form, or report
        views. Exporting the field will not carry the formatting with it.
Decimal Places: Set the number of decimal palaces to be displayed.
>> For other fields, see “TEXT”

DATE/TIME:
Format: Choose the data and/or time formatting.
>> For other fields, see “TEXT”




                                                6
AUTONUMBER:
>>      This option automatically numbers your records and will not allow you to change the
        value. Every record will have a different value which makes it great for identify records
        when other values may be alike.
Field Size: Choose “Long Integer” to have the filed use a standard number. “Replication ID” is
        a complex random ID used in larger databases.
New Values: “Increment” will number in order. “Random” is as it says; but the values will be
        random ONLY if this field is added after you already have a set of records.

YES/NO:
>>     This will display check boxes in datasheet view.
Format: While the format of the check box will not change, the value associated with a check
       can change. Set the values to: “Yes/No”, “True/False”, or “On/Off”.

OLE OBJECT:
>>   This allows you to include external files, such as images, sounds, or documents. There is
     really no values associated with this as the objects are attached on a case by case basis.

HYPERLINK:
>> Text entered becomes a link that you can click on to jump to a web page or E-mail address.
>> Values are the same as TEXT.


TIPS FOR ENTER DATA INTO A TABLE:
    Data is entered across the table (from left to
      right). Once you get to the end, Access
      jumps to the start of the next line.
    Use the TAB key to move from field to
      field. To back up a field, use SHIFT+TAB.
    As you tab field to field, you enter data for
      the whole field. While this does not cause
      problems for new records, if you go to a
      field to edit it, by default it will replace the
      entire field value with what you type.
    To edit data already in a field, use your
      mouse to double click on the field. Once you get a text curser, you can begin editing.
    Careful on the arrow keys. You can use your arrow keys to navigate table fields. You
      can only use arrow to go back and forth in a field if you double click to edit it. If you are
      editing a field and use an arrow key to go beyond the text, it will move to the next field.
    Need to repeat data from the row above? CTRL+ " to copy the field value directly
      above you current position.
    Need to add a new record? Go to the very last blank line and start typing. Simple as that.
    Need to insert a record? Sorry, databases do not work that way. Records are just
      attached to the end and are always saved in that order. However, you can reorder the
      view by using sorting tools.
    Need to put records in order? Place your curser in the field you want to order by and
      click the sort buttons in the toolbar (A/Z with arrow). If you close the table and save
      changes, your table will always display in that order.
                                                 7
ACCESS 97, XP, 2003, & 2007 USERS!!!!

      1) Access 2000 is not backwards compatible with Access 97! If you need to share with an
         Access 97 computer, special procedures will need to be followed.
      2) Access XP and Access 2003 is fully compatible with these different versions so long as
         you do not do anything on purpose to save the root file as a higher version. In other
         words, don’t worry about it.
      3) Access 2007 users, TCS has not tested to make sure of file compatibility. You’re on your
         own.


HOW DO I SAVE MY DATABASE ELSEWHERE?
Unlike other Microsoft products, you can not do a “Save As” to save your database to another
location or make a copy. Instead, you must browse to the file through My Computer and
copy/move it to another location manually. Make sure you choose the file with the .MDB
extension, and do not try to move/copy while the file is open!


WHAT NAMES CAN I NOT USE FOR FIELDS OR OBJECTS?
-A--------------------   -D---------------------   -H--------------------   -N--------------------   -S--------------------
  Add                      Database                   Having                  Name                     Screen
  All                      Date                    -I--------------------     Newpassword              Section
  Alphanumeric             Datetime                   Idle                    No                       Select
  Alter                    Delete                     If                      Not                      Set
  And                      Desc                       Ignore                  Null                     Short
  Any                      Description                Imp                     Number, Numeric          Single
  Application              Disallow                   In, In                -O--------------------     Smallint
  As                       Distinct                   Index                   Object                   Some
  Asc                      Distinctrow                Index, Indexes          Oleobject                Sql
  Assistant                Document                   Inner                   Off                      Stdev
  Avg                      Double                     Insert                  On                       String
-B--------------------     Drop                       Inserttext              Option                   Sum
  Between                -E---------------------      Int                     Or                     -T--------------------
  Binary                   Echo                       Integer                 Order                    Table
  Bit                      Else                       Into                    Outer                    Tabledef
  Boolean                  End                        Is                    -P--------------------     Tableid
  By                       Eqv                     -J--------------------     Parameter                Text
  Byte                     Error                      Join                    Parameters               Time, Timestamp
-C--------------------     Exists                  -K--------------------     Partial                  Top
  Char, Character          Exit                       Key                     Percent                  Transform
  Column                 -F--------------------    -L--------------------     Pivot                    True
  Constraint               False                      Lastmodified            Primary                  Type
  Container                Field, Fields              Left                    Procedure              -U--------------------
  Count                    Fillcache                  Level                   Property                 Union
  Counter                  Float                      Like                  -Q--------------------     Unique
  Create                   Foreign                    Logical, Logical1       Queries                  Update
  Createdatabase           Form, Forms                Long                    Query                  -V--------------------
  Createfield              From                       Longtext                Quit                     Value
  Creategroup              Full                    -M--------------------   -R--------------------     Values
  Createindex              Function                   Macro                   Real                     Var
  Createobject           -G--------------------       Match                   Recalc                 -W--------------------
  Createproperty           General                    Max, Min, Mod           Recordset                Where
  Createrelation           Getobject                  Memo                    References               With
  Createtabledef           Getoption                  Module                  Refresh                  Workspace
  Createuser               Gotopage                   Money                   Relation               -X--------------------
  Createworkspace          Group                      Move                    Repaint                  Xor
  Currency                 Group By                                           Report                 -Y--------------------
  Currentuser              Guid                                               Reports                  Year
                                                                              Requery                  Yes
                                                                              Right                    Yes

                                                               8
START A NEW QUERY:

Once you have at least one table setup, you can start building queries. You don’t actually need
to have data entered in the table when you create a query, but you query results will be empty.

There are many types of queries. For this section, we will focus on a simple query using the one
table you have created. To start this query:
    1) From the main database window, make sure you are on the Queries tab under Objects.
    2) Click on New.
    3) Choose Design View.
    4) You will get a pop-up window called “Show Table”. From this window, highlight the
        table that you want to work with and click [Add]. Then click [Close].
    5) You should now see the query design window with your table (and corresponding fields)
        listed in the top half, and a spreadsheet layout in the bottom half. The bottom half is
        where you will setup and define your query.
    6) In the first column of the query setup, choose the first Field you want to show or work
        with from the drop down list.
        TIP: You can also add fields to the table by dragging and dropping them from the table
        window or double-clicking them in the table window.
        TIP: You can include all fields from a table by using the asterisk (*) field. However,
        you can not customize individual fields with this option.
    7) When you choose a standard field from a table, the name should appear for Table.
    8) If you would like the query to display the records sorted in order by this field, then
        choose a Sort option from the drop-down list. Otherwise, leave it blank.
    9) If you would like the field to show in the query display, then make sure the Show box is
        checked.
        TIP: Uncheck this box if you are filtering records by this field but do not need to see it.
    10) Set any Criteria for
        this field. See the next
        page for information.
    11) Repeat steps 6-10 for
        each field you want to
        display.
    12) When done with the
        setup of your query,
        make sure to save it
        and give it a unique
        name.
    13) To view the results,
        click on the “View”
        icon to go to the
        datasheet view.




                                                9
Additional Query Considerations:
    When placing your fields, the order in which they are arranged in “Design View” will be
       the order in which they appear in “Datasheet View”.
    To insert a field between two existing columns, drag the field from the table window and
       drop it on the column in which you want it to appear. The columns will then shift to the
       right accordingly. You can also move columns once they have been placed by clicking and
       dragging on the field name.
    When sorting, priority is given to the first field from the left. If you want to sort by “Name”
       then “Age”, you have to make sure the “Name” field appears before the “Age” field.
    To remove a field from the design, select the field and click the [Delete] key on the
       keyboard.



QUERY CRITERIA:
The nice thing about queries is you can set criteria for fields in order to filter out records and only
show the info you want. Criteria can come in many forms and how you enter it is very important.

AND/OR: In the design view, you will see several “cells” under each field. Every cell represents
     a different criterion you can specify. Also, each row represents a different combination of
     criteria. This concept is referred to as AND & OR statements. Expressions placed on the
     same row (even though in different fields) are AND, which mean that criteria for each field
     in the row must ALL match in order for the record to show. Placing expressions on
     different rows is OR, which means that it will display records that match either the first row
     or the second row. Actual AND/OR statements can also be used within a single criteria
     expression.

        Let’s say you want to find all the boys who live in Troy. You would want to use
        expressions to limit the “Gender” field to boys and the “City” field to Troy, and you would
        want to make sure the expressions are on the same line. This will give you only records of
        boys in Troy and will NOT show you girls who live in Troy or boys who live in Tipp.

        Now let’s say you want to find all students who live in Troy and all students that live in
        Tipp. Under “City” you would limit records to Troy and then in the row below “Troy”, you
        would also include Tipp. Because multiple rows is an “OR”, you will now see all boys and
        girls who live in Troy or Tipp.

        If you were to take these two examples and combine them, let’s say the first line has boys
        for “Gender” and Troy for “City” and the second line just has Tipp for “City”, then you will
        get all the boys who live in Troy plus all boys and girls who live in Tipp. In order to get
        just boys for Tipp in this example, you would need to duplicate your expression for
        “Gender” to the second line so that boys and Tipp appear in the same row.




                                                  10
SIMPLE VALUE: If you just want to filter out records that have a simple value like one letter,
     word, or number, then you can enter that value in the criteria field. For example to find all
     male students in the “Gender” fields where you only use “M” or “F”, type “M” into the
     criteria cell. (You do not have to type the quotes, but Access will still put them in for you.
     When you run your query, you will only see records where M is the value of the Gender
     fields. While the value is not case sensitive, if there is anything else used in the field with
     “M”, the records will not show. It will only find exact matches. When specifying values
     for a number field, do NOT use quotes.

LOGICAL VALUES: If you want to look for all student over the age of 10, then you can enter a
     logical value of “>10” (w/o quotes). This show all records where the “Age” value is
     greater than 10, but not including 10. To include 10 in this example, use the value “>=10”.

       Here are some sample expressions. NOTE: Use quotes around X if field is text based, but
       no quotes if numeric based.
              >X — Values greater than X
              <X — Values less than X
              >=X —Values greater than or equal to X
              <=X — Values less than or equal to X
              Between X and Y — Values Between X and Y including X and Y
              Not X — Any value that does not equal X
              Like “X*” — Any value that starts with X
              Like “*X*” — Any value that contains X
              Is Null — Any record that does not have a value. (also “”)
              Is Not Null — Any record that does have a value.

From here, criteria can get more and more complex. For more on criteria, reference F1 Help.

If you enter a criteria wrong, you will get three results:
    1) Invalid Syntax means that you used an unknown character and you need to retype the
        expression;
    2) Data Type Mismatch means that the expression may contain valid syntax but not for the
        type of field you are using it on, so retype; or
    3) no error messages, but you do not see any records or get the results you expect, which
        means that the expressions are valid but do not match the logic you are wanting, so retype.

Another nice thing about most queries (not all), is that you can change data in the query without
going back to the table. For instance, if every student should have a homeroom, you can make a
query that filters out all the records where “Homeroom” “Is Null” (meaning you forgot to fill
something in). When you view the query in Datasheet View, you can now see what students you
missed and fill in the appropriate info. However, once you change the data, the records will not get
refiltered until you close and open the query again.


We will cover more advanced queries later in the notes.




                                                11
Reports:
Reports allow you to print your data in a format that makes sense to others. There are many
different types of reports you can create, including labels and mail merges with Word. For this
class, we will start with a simple report using the wizard. The wizard is a good way to setup
your report until you get real good at it. You can always customize your report after using a
wizard.


START A NEW REPORT:

   1)  Click on the Reports tab.
   2)  Click New at the top.
   3)  From the “New Report” window, highlight Report Wizard.
   4)  From the drop-down box below for “Choose a Table…”, select the table or query you
       want to work with for your report. Click [OK].
   5) From the “Available Fields” list, highlight the each field that you want to use in your
       report and click the right arrow [ > ] to copy it to “Selected Fields”. To move all fields,
       click on the double arrow [ >> ]. Click [Next].
   6) If you want to group any records, highlight those fields and click the single arrow [ > ].
       It helps to copy them in order from the major group to minor group. Click [Next].
   7) The next screen will allow you to sort records in order, overwriting any previous order.
       If you want to overwrite, choose a field name and order to sort by. If you are using
       numbers that you want to calculate, then click on the [Summary Options] button and set
       the options you desire. Click [Next].
   8) Choose a layout that best suits your data or desired method of display. Click [Next].
   9) Choose a style for your report. Click [Next].
   10) Give your report a name. If you want to preview the report, make sure the option is
       checked. Otherwise, you can go directly to making design changes by choose Modify
       Design. Click [Finish].




                                                12
                                   REPORT TOOLBARS




                  Label Tool: Used to display text that will not change.

                  Text Box Tool: Used to display text from a field. Will dynamically change
                                 based on records.




                  Image Tool: Places an unchanging graphic on your report.

                  Unbound Object Tool: Displays an OLE object (embedded file) from a
                                       database record.


                  Line Tool: Draws a line.

                  Rectangle Tool: Draws a rectangle.




                                                                 Expressions Builder Tool:
                                                                 Helps build formulas or command
                                                          Properties: Shows the properties
                                                                      of an object.
                      Sorting/Grouping: Configure sort/group options and toggle Header/Footers.
            Toolbox: See toolbox command above.
     Field List: Opens a window showing all fields. Click and drag the fields from this window
                 to add additional fields to your report.


Common Expressions
Total Column (Sum): =Sum([fieldname])
Count:              =Count([fieldname])
Percentage:         =Count([fieldname])/[total_count_fieldname]
Average:            =Avg([fieldname])
Text with Field:    =“Text Here ” & [fieldname] & “. More Text Here.”




                                              13
FORMS
Forms are designed to allow users to quickly and easily input data in a database. They can be setup
in many different ways so as to make more sense to a user. Forms can also be further customized
to automatically fill in info, calculate, or double-check data. One can also make menus out of
forms so that other users can navigate your database by clicking buttons.


START A NEW FORM:

   1)   Click on the Forms tab.
   2)   Click New at the top.
   3)   From the “New Form” window, highlight Form Wizard.
   4)   From the drop-down box below for “Choose a Table…”, select the table or query you want
        to work with for your form. Click [OK].
   5)   From the Available Fields list, highlight the each field that you want to use in your form
        and click the right arrow [ > ] to copy it to “Selected Fields”. To move all fields, click on
        the double arrow [ >> ]. Click [Next].
   6)   Select the layout you would like for your form. Click [Next].
            a. Columnar: Will list fields down form, then start a new column at the top.
            b. Tabular: Will list field across, similar to a table. You will likely have to scroll left
                and right, but will see multiple records, like a table.
            c. Datasheet: Exactly like a table. No matter what form you choose, you can always
                jump back to datasheet view.
            d. Justified: Will list fields across in rows. You will not have to scroll and has the
                same feel as normal paper forms. This is my preferred option.
   7)   Choose a style for your form. Click [Next].
   8)   Give your form a name. If you want to preview the form, make sure the option is checked.
        Otherwise, you can go directly to making design changes by choose Modify Design. Click
        [Finish].




                                                  14
                                   REPORT TOOLBARS




                  Label Tool: Used to display text that will not change.

                  Text Box Tool: Used to display text from a field. Will dynamically change
                                 based on records.

                  *List Box: Choose from a predefined group/list.

                  *Command Button: Press a button to run a macro or other function.

                  Image Tool: Places an unchanging graphic on your report.

                  Unbound Object Tool: Displays an OLE object from a database record.

                  *Tab Control: When making menus, have tabs for different form options.

                  Line Tool: Draws a line.

                  Rectangle Tool: Draws a rectangle.




                                                               Expressions Builder Tool:
                                                               Helps build formulas or command
                                                        Properties: Shows the properties
                                                                    of an object.
            Toolbox: See toolbox command above.
     Field List: Opens a window showing all fields. Click and drag the fields from this window
                 to add additional fields to your report.



PRINT LABELS
  1) Click on the Reports tab.
  2) Click New at the top.
  3) From the “New Report” window, highlight Label Wizard.
  4) From the drop-down box below for “Choose a Table…”, select the table or query you want
     to work with for your labels. Click [OK].


                                              15
   5) From “Label Wizard” window, choose the Avery product number that corresponds to the
      labels you plan on using (can be found on front of Avery boxes). If you are not using
      Avery, change to the corresponding Manufacturer. Or if you need a custom size, click the
      [Customize] button, and click [New] to setup your own sizes. Click [Next].
   6) Choose the font type, size, and color you want to use on your label. Click [Next].
   7) Create a prototype label by highlighting the each field that you want to use from the
      Available Fields list and click the right arrow [ > ] to copy it to the layout window. Copy
      the field in the order you want them to appear. If you need a carriage return, spaces, or text,
      type it in normally. Field names in brackets { } will be replaced with data when the report is
      ran. Click [Next].
   8) The next screen will allow you to sort records in order, overwriting any previous order. If
      you want to overwrite, choose a field name and order to sort by. Click [Next].
   9) Give your label report a name. If you want to preview the report, make sure the option is
      checked. Otherwise, you can go directly to making design changes by choose Modify
      Design. Click [Finish].



MACROS
A macro is a set of one or more actions that each performs a particular operation, such as opening a
form or printing a report. Macros can help you to automate common tasks. For example, you can
run a macro that prints a report when a user clicks a command button.

START A NEW MACRO:
  1) Click on the Macros tab.
  2) Click New at the top.
  3) In the Macro window, click the first empty row in the Action column.
  4) In the Action column, click the arrow to display the action list.
  5) Click the action you want to use.
  6) In the lower part of the window, specify arguments for the action, if any are required.
  7) Type a comment for the action. Comments are optional.
  8) To add more actions to the macro, move to another action row and repeat steps 3-7.
  9) Save your macro and give it a name that makes sense to you.

To run your macro directly, do one of the following:
    From the Macro window: click Run            on the toolbar.
    From the Database window: click Macros            and then double-click a macro name.
    From anywhere in the database: on the Tools menu, point to Macro, click Run Macro, and
       then select the macro from the Macro Name list.

You can also have a macro run by assigning it to an event in a Form, such as a Command Button,
adding a new record, or leaving a field. Use a macro to print a copy automatically of student info
for a new student you just entered or changed.

TIP: Macros can be run from a shortcut key. For instance, press [CTRL] + [X] to open a specific
report from anywhere in your database. Ask for advanced instruction on doing this.


                                                16
MORE ON TABLES…

VALUE LISTS:
You can easily fill in field values from a predefined list. To do this, first define your list:
   1) In your table’s design view, select the field you
      want to add a lookup field to.
   2) Click on the Lookup tab at the bottom of the
      screen.
   3) Change Display Control to LIST BOX.
   4) Change Row Source Type to VALUE LIST.
   5) In the Row Source field, type your predefined
      values. Make sure each value is enclosed in
      double quotes and each value is separated by a
      semi-colon. As for order, it does not matter, but
      it works better for you if the items you will use
      the most are listed first. You can always come
      back to this list as edit your values.


                                  To test the value list, go back to Datasheet View. For that field,
                                  you can now do 3 things:
                                     1) Click the down arrow on the right side of the field and
                                         choose an item with your mouse.
                                     2) Type the first few letters of the value and move on
                                         (tab/enter) when the correct value comes up.
                                     3) Type your own custom text not listed in the list.

When you setup this field in a table before creating any other forms or reports, the list items will
automatically get configured into these items. If you do this after you have created a form, you
may have to do the same list setup again in the form’s design view.



TABLE/QUERY LISTS:
Like the Value List, this type of list will provide a drop down list to choose from, but rather than
set values as typed, it will reference values from a table or query. By using a 2nd table that already
has values you want to use, you do not have to double your work, plus it makes it easy to update
values. To do this:
    1) In your table’s design view, select the field
        you want to add a lookup field to.
    2) Click on the Lookup tab at the bottom of the
        screen.
    3) Change Display Control to LIST BOX.
    4) Change Row Source Type to
        TABLE/QUERY.




                                                   17
   5) In the Row Source field, enter the following expressions substituting your lookup table and
      field name (keep brackets):      SELECT [fieldname] FROM [tablename]
   6) Keep Bound Column and Column Count set at 1.

Go back to Datasheet View to see the list in action. To change values, simply change them in the
table or query you linked to.


MAKE A LINKED TABLE:
You can use data from many outside sources, such as other databases, Excel, or text files. To do
this, simple “link” a table to one of these source, and now you can use data from the outside source
as though it were in your own database. You can even change the data in the outside source while
in your own database (depending on permissions). To link to an outside source:
    1) In the Table section of your database, go to File > Get External Data > Link Tables >.
    2) Select your source type by choosing the corresponding Files of type at the bottom of the
        window. For an Access database, keep on “Access”. For Excel, choose “MS Excel”. For
        text files, choose “Text Files”.
    3) Double click on the file that contains the data that you want to link to (for ODBC, choose
        your source location).
    4) Follow the wizard prompts to linking to the data. For databases, simply choose the table or
        query you want to link to.
    5) Once you have fully link to your source, you will see a table with an icon that includes an
        arrow. This means that the table is linked to an outside source. Double click on the table to
        open it and see the data.

The biggest thing with linking table is making sure the file location does not change or gets deleted.
If the link is broken, then the table will have an error and will not work.


IMPORT DATA TO A TABLE:
Access can import data from almost any source. If you would like to take something that is given
to you in Excel, you can use a few easy steps to pull the data in as a new table or add it to an
existing one (fields should match). To do this:
    1) In the Table section of your database, go to File > Get External Data > Import >.
    2) Select your source type by choosing the corresponding Files of type at the bottom of the
        window. For an Access database, keep on “Access”. For Excel, choose “MS Excel”. For
        text files, choose “Text Files”.
    3) Double click on the file that contains the data that you want to import.
    4) Follow the wizard prompts to import the data. For databases, simply choose the table or
        query (make sure to check “import as table”) under Options.
    5) The imported table is now its own entity, completely separate from the source you imported
        it from.




                                                 18
RELATIONSHIPS:
In Access, you can setup relationships between tables/queries where certain fields contain common
data. For instance, if you have one table that contains a class list and second table that contains
grades, you can set a relationship between grades and homeroom bases on a common field like a
name or ID.

Relationships are most commonly setup in queries where data can easily merge. To setup and use a
relationship in a query, do the following:
1) Open a new or existing query.
2) Make sure you have all the tables/queries you want to
    work with added to the “working area” of your query.
3) Drag the common field from the first table and drop it
    on top of the common field in the second table. You
    should now notice a line with dots appear between
    these two fields. Note: The field names do NOT need
    to match.
4) Double click on the line, in the middle of the line, to
    open up the Join (relationship) Properties.
5) Set the join type to one of the 3 selections based on
    what records to be returned when the query is ran:
        1. Records where there is matching data in each
            table. Records that do not have a matching
            record will be “hidden”.
        2. Includes all the records from the first table, but
            only matching data from the second table will
            be displayed.
        3. Includes all the records from the second table,
            but only matching data from the first table will
            be displayed.
6) Click [OK]. The line between the tables will change based on your option above.

You can setup more than one relationship. If you want two matching fields between two table (such
as first and last name), do the same thing for each field. You can also have three tables where
student name is link between the three; however it is best to have a primary table that acts as a hub
for the links.

To now merge data from two tables with relationships,
simply drag the fields you want to use from any of the
joined tables. You will notice that the Table name will
correspond to the table the filed is from.

NOTE: If you do NOT have unique values in each table
for the joined fields (like 1 student is listed twice), then
you may get multiple returns in your query as it tries to
match up all possible combinations.

Doing relationships from within a query design view will mean that they are only setup for this
query. To setup relationships for all queries, use Tools > Relationships.
                                                  19
MORE ON QUERIES…

GROUP BY / TOTALS:
In a query, you can have like records grouped together, either to give you a list of possible values
or for calculations. For instance, you can quickly calculate how many students are in a class or get
a list of homeroom names.

You can access this feature with the Totals option in your design view. To show this field, go to
View > Totals >. A new line should appear between Table and Sort.

By default, each field name you use will be “Grouped By”. This means that it will find unique
values based on the field, or combination of fields, you have chosen to group by.

You can than calculate based on these groups. To do this, change Total for a particular items to the
type of calculation you wish to perform, such as “Count” to count the number of student in a group.
Here is what the Total “calculations” represent:
             SUM: The total of all values in this field (numeric only).
             AVG: The average of values in this field (numeric only, not counting nulls).
             MIN: The lowest value (text or number).
             MAX: The highest value (text or number).
             COUNT: The total number of records in the field (that are not null).
             STDEV: The standard deviation of values.
             VAR: Variance of values.
             FIRST/LAST: The first or last records of this grouping (based off of the
               chronological order in which the records were entered).


OTHER TYPES OF QUERIES:
There are many other types of queries in access that are not just for showing filtered or sorted data.
Here are the other types, what they do, and what you may use them for. Reference F1 Help for
more info.

Delete Queries – Deletes a group of records from a table. For example, you could use a Delete
Query to remove students when they graduate. With Delete Queries, you always delete entire
records, not just selected fields within records.
Update Queries – Makes global changes to a group of records in one or more tables. For example,
you can promote all students one grade or remove values associated only for that year. You can
also use criteria to limit what records get changed.
Append Queries – Adds a group of records to the end of a table. If you a have a separate table that
you want to add to your main table, you can use this.
Make-Table Queries – Creates a new table from the data returned in the query. Make-table
queries are helpful for creating a table to export to other Microsoft Access databases or a history
table that contains old records.
Union Queries – (SQL) Combines 2 or more like-tables together into one source. You can use a
union query to combine student info from all 9 schools into one source.


                                                 20

								
To top