Learning Microsoft Access 2007 - Introduction

Document Sample
Learning Microsoft Access 2007 - Introduction Powered By Docstoc
					Guided Computer Tutorials



 Learning
                       ®

                   ®

       2007
  By Greg Bowden
PUBLISHED BY

GUIDED COMPUTER TUTORIALS
PO Box 311
Belmont, Victoria, 3216, Australia

www.gct.com.au


© Greg Bowden


This product is available in Single or Multi User versions.

Single-user versions are for single person use at any particular
time, just as a single text book would be used. If you intend to use
the notes with multiple students the single user version should be
upgraded to the multi-user version.

Multi-user versions allow the school or institution to print as many
copies as required, or to place the PDF files on the school network,
intranet and staff laptops. A certificate of authentication is provided
with multi-user versions. Bookmarks provide links to all headings and
sub-headings, and individual chapters are provided.




First published 2008


ISBN: 1 921217 52 9 (Module 1)
      1 921217 53 7 (Module 2)
      PDF document on CD-ROM




Every effort has been made to ensure that images used in this
publication are free of copyright, but there may be instances where
this has not been possible. Guided Computer Tutorials would
welcome any information that would redress this situation.
Learning Microsoft Access 2007


Module 1 Contents

Chapter 1: Introduction to Microsoft Access
        Getting Started With Microsoft Access ................................................1-2
        Starting a New Blank Database .............................................................1-3
        The Database Screen ...............................................................................1-4
        Creating a Table .......................................................................................1-4
              Entering a Text Field ......................................................................1-5
              Setting the Field Properties...........................................................1-7
              Entering More Text Fields.............................................................1-7
              Entering a Date Field .....................................................................1-8
              Inserting Number Fields ...............................................................1-9
              Saving the Table ............................................................................1-10
        Entering a Record ..................................................................................1-11
        Adjusting the Columns.........................................................................1-12
        Adding Extra Records...........................................................................1-14
        Changing the Data ................................................................................1-15
              Changing the Contents of a Field ..............................................1-15
              Adding New Records ...................................................................1-16
              Deleting a Record .........................................................................1-16
        Printing the Database ...........................................................................1-17
              Previewing the Database .............................................................1-17
              The Page Setup..............................................................................1-18
        The Record Navigation Buttons...........................................................1-19
        Finishing a Microsoft Access Session .................................................1-20
        Databases Assignment 1 ............................................................. 1-22




                                   © Guided Computer Tutorials, 2008                                                  C-1
      Learning Microsoft Access 2007


      Chapter 2: Processing Data
              Loading the Sample File .........................................................................2-1
              Saving the Database ................................................................................2-2
              File Security..............................................................................................2-2
              Sorting Data .............................................................................................2-3
                    Alphabetical Sorting ......................................................................2-3
                    Numerical Sorting..........................................................................2-5
                    Chronological Sorting ...................................................................2-5
                    Unsorting ........................................................................................2-6
                    Sorting on More Than One Column ...........................................2-7
              Filtering Information ..............................................................................2-8
                    Filter By Selection ..........................................................................2-8
                    Practice Exercise 2.1 ......................................................................2-9
                    Multiple Selection Searches ........................................................2-10
                    One Condition or Another Searches .........................................2-11
              Omitting Records ..................................................................................2-13
              Practice Exercise 2-1 .............................................................................2-14
              Common Filters.....................................................................................2-14
              Finding Data ..........................................................................................2-16
              Using Find Wildcards ...........................................................................2-18
              Databases Assignment 2 ............................................................. 2-21
              Databases Assignment 2 Question Sheet ...........................................2-22


      Chapter 3: Introduction to Queries
              Loading the Sample File .........................................................................3-1
              Looking at the Tables ..............................................................................3-2
              Creating a Query .....................................................................................3-2
                   Linking the Tables ..........................................................................3-4
                   Building the Query ........................................................................3-4
                   Looking at the Query.....................................................................3-6
                   Adding a Calculation Field ...........................................................3-6
                   Sorting the Query ..........................................................................3-8
                   Filtering a Query ..........................................................................3-10
                   Saving the Query ..........................................................................3-11



C-2                                       © Guided Computer Tutorials, 2008
                                                                                                                    Contents


       Opening the Queries Pane ...................................................................3-11
       Creating a Second Query .....................................................................3-12
            Using the Query Wizard .............................................................3-12
            Adjusting the Query ....................................................................3-15
            Formatting Fields in a Query .....................................................3-16
            Combining Text Fields ................................................................3-17
            Sorting the Query ........................................................................3-19
       Interactive Queries ................................................................................3-22
            Duplicating a Query ....................................................................3-22
            Setting an Interactive Dialogue Box ..........................................3-23
            Setting Multiple Interactive Dialogue Boxes ............................3-24
       Deleting Queries....................................................................................3-26
       Creating Relationships Between Tables ..............................................3-27
       Databases Assignment 3 ............................................................. 3-29


Chapter 4: Simple Forms
       Loading the Database .............................................................................4-1
       Creating a Form.......................................................................................4-2
       Layout and Form View ...........................................................................4-3
       Saving the Form.......................................................................................4-3
       Changing the Form Title ........................................................................4-4
       Reducing the Field Width ......................................................................4-5
       Changing the Field Formats ..................................................................4-6
       Adding a Logo .........................................................................................4-8
       Using the Form ........................................................................................4-9
       Tabular Forms ........................................................................................4-11
            Duplicating the Form ..................................................................4-11
            Setting the Tabular Form ............................................................4-12
       Databases Assignment 4 ............................................................. 4-14


Chapter 5: Creating More Detailed Forms
       Starting a New Database.........................................................................5-1
       Defining the Fields ..................................................................................5-2
       Deleting the First Table ..........................................................................5-3


                                 © Guided Computer Tutorials 20068                                                         C-3
      Learning Microsoft Access 2007


              Starting the Form ....................................................................................5-4
                    Opening the Form in Design View..............................................5-4
                    Linking the Form to the Data Table ............................................5-5
                    Setting the Grid ..............................................................................5-6
              Inserting Fields ........................................................................................5-6
              Understanding the Handles ...................................................................5-8
              Saving the Form.....................................................................................5-10
              Looking at the Form .............................................................................5-10
              Adding the Remaining Fields ..............................................................5-11
              Aligning the Fields and Labels ............................................................5-14
              Adding Rectangles ................................................................................5-17
              Copying the Rectangle..........................................................................5-19
              Changing the Background Colour ......................................................5-21
              Changing Text Colours.........................................................................5-22
              Adding a Company Logo .....................................................................5-23
                    Inserting the Image ......................................................................5-23
                    Entering the Logo Text ................................................................5-24
              Entering the Data ..................................................................................5-27
              Inserting an Image into the Photo Field.............................................5-28
              Adjusting the Form ...............................................................................5-30
              Adding Another Record .......................................................................5-31
              The Value of Forms ...............................................................................5-31
              Creating a Pop-up List ..........................................................................5-32
                    Creating a Table to Store the Menu Items .................................5-32
                    Copying the Form ........................................................................5-34
                    Setting the Menu List...................................................................5-34
                    Formatting the Menu List ...........................................................5-36
                    Entering a Record Using the Menu List ....................................5-38
                    Adjusting the Tab Order ............................................................5-39
                    Entering the Rest of the Record .................................................5-39
              Exercise 5.1 .............................................................................................5-40
              Printing the Field Definitions ..............................................................5-41
              Databases Assignment 5 ............................................................. 5-42




C-4                                       © Guided Computer Tutorials, 2008
                                                                                                                        Contents


Chapter 6: Quick Reports
       Loading the Sample File .........................................................................6-1
       Using the Report Wizard........................................................................6-2
            Tabular Reports ..............................................................................6-2
            Looking at the Report ....................................................................6-4
            Columnar Reports .........................................................................6-6
            Setting the Report Into Columns .................................................6-9
       The Report Icon .....................................................................................6-11
            Creating the Report .....................................................................6-11
            Saving the Report .........................................................................6-12
            Totals ..............................................................................................6-12
            Autoformats ..................................................................................6-13
            Sorting the Data ...........................................................................6-14
       Databases Assignment 6 ............................................................. 6-16


Chapter 7: Creating More Detailed Reports
       Loading the Database .............................................................................7-1
       Starting a New Report ............................................................................7-1
             Reducing the Height of the Detail Section .................................7-2
             Displaying the Grid Increments ...................................................7-3
             Inserting Fields into the Detail Section.......................................7-3
             Looking at the Report ....................................................................7-7
             Widening the Grid .........................................................................7-8
             Adding a Calculation Field ...........................................................7-9
             Saving the Report .........................................................................7-13
       Formatting the Report ..........................................................................7-13
             Formatting the Fields...................................................................7-13
             Formatting the Labels ....................................................................... 7-14
             Adding a Main Heading ..............................................................7-16
             Adding the Sub-Heading ............................................................7-17
             Inserting the Current Date..........................................................7-18
             Adding Page Numbers to the Page Footer ................................7-19




                                  © Guided Computer Tutorials 20068                                                            C-5
      Learning Microsoft Access 2007


              Some Finishing Touches.......................................................................7-20
                  Adding a Rectangle ......................................................................7-20
                  Inserting a Line.............................................................................7-22
              Databases Assignment 7 ............................................................. 7-24


      Chapter 8: Creating User-Friendly Databases
              Opening the Sample File ........................................................................8-1
              Opening the File as an Exclusive file ....................................................8-1
              The Sections of the Database .................................................................8-2
                    Tables ...............................................................................................8-2
                    Queries ............................................................................................8-2
                    Forms ...............................................................................................8-2
                    Reports ............................................................................................8-2
              The Structure of the Database................................................................8-3
              Completing the Data Entry Form .........................................................8-3
                    Inserting a New Record Button ....................................................8-4
                    Inserting a Find Record Button....................................................8-5
                    Inserting a Delete Record Button.................................................8-7
                    Testing the Buttons ......................................................................8-10
              Creating a Main Switchboard Form ...................................................8-12
                    Setting the Grid ............................................................................8-13
                    Setting the Background Colour..................................................8-14
                    Inserting a Graphic ......................................................................8-14
                    Adding a Button to Open the Data Entry Form ......................8-16
                    Inserting Buttons to Print Reports.............................................8-18
                    Adding an Exit Button .................................................................8-21
                    Adding a Rectangle ......................................................................8-23
                    Adding the Headings ...................................................................8-24
                    Testing the Buttons ......................................................................8-25
              Setting the Opening Form ...................................................................8-28
              Setting a Password .................................................................................8-29
              Printing the Form ..................................................................................8-30
              Databases Assignment 8 ............................................................. 8-32

      Microsoft Access Project

C-6                                       © Guided Computer Tutorials, 2008
                                                                                                                Contents


Module 2 Contents

Chapter 9: Using Macros
       Loading the Sample File .........................................................................9-1
       Looking at the Database .........................................................................9-1
       Creating the Properties Macros.............................................................9-2
             Creating the Rental Properties Macro.........................................9-2
             Saving the Macro ............................................................................9-5
             Running the Macro ........................................................................9-5
             Creating the Sale Properties Macro .............................................9-7
             Creating the Auction Properties Macro ......................................9-9
             Testing the Macros .......................................................................9-11
       Macros to Open Forms .........................................................................9-12
             Opening the Data Entry Form ...................................................9-12
             Opening the Main Switchboard Form ......................................9-14
             Saving the Macro Group .............................................................9-14
             Testing the Macros .......................................................................9-15
       Assigning Buttons to the Macros ........................................................9-15
             Switching to the Data Entry Form .............................................9-16
             Switching to the Main Switchboard Form ................................9-18
       Buttons to Display the Report Macros ...............................................9-20
             The Auction Properties Report Button .....................................9-20
             The Sale Properties Report Button ............................................9-22
             The Rental Properties Report Button ........................................9-22
             The Exit Application Button .......................................................9-23
       Setting an Autoexec Button..................................................................9-25
       Printing the Macro Definitions ...........................................................9-26
       Databases Assignment 9 ............................................................. 9-28




                                © Guided Computer Tutorials 20068                                                      C-7
      Learning Microsoft Access 2007


      Chapter 10: Reports That Total Items
              Loading the Sample File .......................................................................10-1
              Creating a Daily Sales Report ..............................................................10-1
              Adjusting the Report.............................................................................10-5
                   Adjusting the Report Header......................................................10-6
                   Adjusting the Date Header and Detail Sections.......................10-8
                   Reducing the Field Widths .........................................................10-9
                   Adjusting the Group Footer......................................................10-10
                   Adjusting the Report Footer .....................................................10-14
                   Some Final Adjustments ...........................................................10-16
              Creating a Second Report ..................................................................10-20
                   Copying the Report....................................................................10-20
                   Altering the Report Header ......................................................10-21
                   Changing the Grouping ............................................................10-21
              Practice Exercise 10.1 .........................................................................10-24
              Databases Assignment 10 ......................................................... 10-25


      Chapter 11: Applying Relational Database Features
              Creating a Relational Database System ..............................................11-1
              Loading the Sample File .......................................................................11-2
              Defining the Relationship ....................................................................11-2
              Completing the Suppliers Form ..........................................................11-6
                   Setting the Sub-Form Frame ......................................................11-6
                   Setting the Wizard Options ........................................................11-7
                   Adjusting the Sub-Form ..............................................................11-8
                   Looking at the Form ....................................................................11-9
              Using the Main Form ..........................................................................11-11
                   Deleting Records ........................................................................11-11
                   Allowing for Cascade Deletes ...................................................11-12
                   Deleting a Supplier.....................................................................11-14
                   Deleting a Product .....................................................................11-15
                   Adding a New Supplier .............................................................11-16
              Adjusting the Sub-Form .....................................................................11-18
              Advantages of Relational Database Systems ....................................11-20
              Databases Assignment 11 ......................................................... 11-21

C-8                                    © Guided Computer Tutorials, 2008
                                                                                                                     Contents


Chapter 12: Setting up an Invoice System
        Loading the Sample File .......................................................................12-3
             Looking at the Tables ...................................................................12-3
             Looking at the Forms...................................................................12-4
        Creating the Invoice Table....................................................................12-5
        Saving the Table .....................................................................................12-7
        Creating the Relationships ...................................................................12-8
             Relating the Customer Details and Invoice Details Tables .....12-9
             Relating the Invoice Details and Line Items Tables ...............12-11
             Relating the Line Items and Product Details Tables ..............12-12
        Calculating the Amount Sold ............................................................12-14
        Completing the Invoice Form............................................................12-17
             Setting the Table .........................................................................12-17
             Setting the Default Settings.......................................................12-17
             Inserting the Invoice Fields.......................................................12-18
             Inserting the Customers Sub-Form into the Invoice .............12-22
             Creating the Line Items Sub-Form ..........................................12-25
             Inserting the Line Items Sub-Form into the Invoice .............12-28
             Adding a New Invoice ...............................................................12-31
        Adding Calculations to the Invoice ..................................................12-32
             Adding a Calculation Control in the Line Items Sub-Form .12-32
             Placing the Calculation in the Main Form .............................12-34
        Databases Assignment 12 ......................................................... 12-38


Chapter 13: Formatting Invoice Systems
        Loading the File .....................................................................................13-1
        Setting Lookup Values ..........................................................................13-1
              Deleting the Customer Relationships ........................................13-1
              Setting the Customer Lookup Values ........................................13-2
              Looking at the Customer Lookup Settings ...............................13-5
              Setting the Products Popup List .................................................13-6
              Resetting the Customers Relationship ......................................13-8
              Adjusting the Invoice .................................................................13-10
              Testing the Invoice .....................................................................13-14


                                  © Guided Computer Tutorials 20068                                                         C-9
       Learning Microsoft Access 2007


               Aligning the Invoice Elements ..........................................................13-16
               Adding a Border to the Invoice .........................................................13-18
               Adjusting the Sub-forms ....................................................................13-19
               Adding a Page Break ...........................................................................13-20
               Setting the Tab Order..........................................................................13-21
               Previewing the Form...........................................................................13-21
               Using the Invoice .................................................................................13-23
               Databases Assignment 13 ......................................................... 13-26


       Chapter 14: Reporting From Invoice Systems
               Loading the Sample File .......................................................................14-1
               Creating a Total Items Sold Report .....................................................14-1
                    Starting the Report Wizard .........................................................14-1
                    Making the Total Items Sold Report More Concise ................14-4
                    Formatting the Product Name Footer .......................................14-8
                    Inserting Field Labels ..................................................................14-9
                    Separating the Sections .............................................................14-12
                    Renaming the Report ................................................................14-15
               Creating the Monthly Sales Report ...................................................14-16
                    Creating the Monthly Sales Query...........................................14-16
                    Copying the Items Sold Report ................................................14-18
                    Adjusting the Grouping.............................................................14-19
                    Adjusting the Report .................................................................14-20
               Creating Mailing Labels .....................................................................14-24
               Looking at the Mailing Labels ...........................................................14-26
               Databases Assignment 14 ......................................................... 14-27




C-10                                     © Guided Computer Tutorials, 2008
                                                                                                                      Contents


Chapter 15: Enhancing Invoice Systems
        Loading the Sample File .......................................................................15-1
        Creating a Customers Data Entry Form ............................................15-1
              Creating the Form ........................................................................15-1
              Adding Buttons to the Form .......................................................15-4
              Testing the Buttons ......................................................................15-7
        Creating a Products Data Entry Form................................................15-8
        Creating a Main Switchboard ............................................................15-11
        Creating a Reports Switchboard ........................................................15-13
        Creating the Macros ...........................................................................15-14
              Macros to Switch to the Different Forms ................................15-14
              Macros to Preview the Reports ................................................15-16
              An AutoExec Macro ..................................................................15-17
        Setting the Buttons ..............................................................................15-18
              Adding Buttons to the Main Switchboard Form....................15-18
              Adding Buttons to the Invoice Form .......................................15-21
              Adding Buttons to the Customers and Products Forms .......15-23
              Adding Buttons to the Reports Switchboard Form ...............15-24
        Databases Assignment 15 ......................................................... 15-28


Chapter 16: Useful Tools
        Loading the Sample File .......................................................................16-1
        Enabling Content ..................................................................................16-1
        Help Features .........................................................................................16-2
        Creating Form Tabs...............................................................................16-4
             Creating the Form ........................................................................16-4
             Creating the Tab Controls ...........................................................16-5
             Placing Fields in the Form Tabs .................................................16-8
             Looking at the Form Tabs .........................................................16-10
             Adjusting the Form Tabs ...........................................................16-11
             Changing the order of the Form Tabs .....................................16-13
             Adding Another Form Tab .......................................................16-14
        The Calendar Control .........................................................................16-15
        Changing the Form Heading .............................................................16-17


                                   © Guided Computer Tutorials 20068                                                        C-11
       Learning Microsoft Access 2007


               Checking for Duplicate Records .......................................................16-18
                    Creating a Duplicates Query ....................................................16-18
                    Using the Duplicates Query......................................................16-20
                    Setting a Button to Run the Query ..........................................16-21
               Trusted Locations ................................................................................16-24
               Database Templates .............................................................................16-25




C-12                                     © Guided Computer Tutorials, 2008
                                                                                        Chapter

                                                                                          1
Introduction to Microsoft Access
Data is simply a collection of characters (that is, letters, numbers and symbols)
which, on their own, have no particular meaning. When data about a particular
topic is stored it is said to be a database. A database allows data to be processed
into information, something that can be communicated and understood.
There are two different types of databases:
	 •	   those	that	already	contain	data	and	just	allow	you	to	obtain	information	from	
       them. You are not able to make any changes to the data. These are called
       CLOSED DATABASES.
	 •	   those	that	allow	you	to	enter	and	change	the	data	and	process	it.		These	are	
       called OPEN DATABASES.
Microsoft Access is an OPEN DATABASE.
Some databases that we use in everyday life are: the telephone book, a dictionary,
an atlas, a bus timetable, etc. Data within a database is usually divided into
categories or sections, called FIELDS. The fields that the telephone book is divided
into are:
       Surname, Initials, Street Number, Street Name, Suburb, Telephone Number
These FIELDS can vary in length depending on how much data (characters) needs
to be placed into them. For example, an Initials field does not need as much space
as a Surname field.
One complete set of fields is termed a RECORD. For example, each subscriber’s
details in the phone book is a record. There are over 1 000 000 records in the
Melbourne telephone book.
A	group	of	records	on	a	particular	subject	is	called	a	FILE.		For	example,	the	phone	
book is divided into two FILES, white pages and yellow pages.




                                © Guided Computer Tutorials 2008                         1-1
Learning Microsoft Access 2007


Getting Started With Microsoft Access
 1    Load Microsoft Access and you should receive the GETTING STARTED WITH
      MICROSOFT OFFICE ACCESS startup screen.




                                  New Database
                                  Button

          Templates Pane                                Recent Database
                                                        Pane


                                              Sample Database
                                              Templates


 2    The RECENT DATABASE panel at the right of the window allows you to quickly
      open recently used files.




                                              3 Click on LOCAL TEMPLATES in the
                                                TEMPLATES CATEGORIES pane and
                                                a range of preset database types are
                                                provided.




  NOTE:      Once you are familiar with creating Access databases you can use
             these templates to quickly setup the type of database you require.
             You can also find templates online using the FROM MICROSOFT
             OFFICE ONLINE section.



1-2                          © Guided Computer Tutorials 2008
                                              Introduction to Microsoft Access       1

Starting a New Blank Database
To learn how to create your own database a database for a bank will be created.




                                              1 Return the TEMPLATES CATEGORIES
                                                to FEATURING and click on the BLANK
                                                DATABASE icon in the NEW BLANK
                                                DATABASE section.




                                              2 The BLANK DATABASE pane will be
                                                opened, enter the file name:
                                                                Bank Records
                                                 and click on the BROWSE icon.




                                              3 Microsoft Access automatically saves
                                                your work as you enter data so the
                                                program needs to know where to save
                                                the file before you create it. Locate
                                                your STORAGE folder and click on OK.




                                              4 Click on CREATE to start the database.




                             © Guided Computer Tutorials 2008                            1-3
Learning Microsoft Access 2007


The Database Screen
The database screen has a number of sections which are labelled in the following
diagram.

        Office Button          Quick Access Toolbar          The Ribbon




                    The Navigation Pane                           The Work Pane



An Access database is made up of TABLES where you enter the data, QUERIES where
you ask questions of the data, FORMS where you make the data easier to view and edit,
and REPORTS where you print the data. These sections will be covered in the next few
chapters.


Creating a Table
Tables (or lists) are the main way of displaying data in Microsoft Access. All data is stored
in tables. The one database can have many different tables, but each table should
concentrate	on	the	one	subject.		For	example,	the	products	that	a	company	sells	or	the	
customers that the company has. The ROWS in the table represent the RECORDS of the
database. The COLUMNS represent the FIELDS.
Let’s create a database for a bank that records the names, addresses and annual incomes
for clients taking out personal loans. The first step in creating a database is to enter the
FIELDS (the categories or sections into which the data is divided).
You can type the field names directly into the Work pane area like you would in a
spreadsheet. However, usually you will want to set the field types and formats so it is
better to enter the table in DESIGN VIEW.



1-4                            © Guided Computer Tutorials 2008
                                         Introduction to Microsoft Access          1



                                         1 In the DATASHEET tab of the RIBBON
                                           click on the arrow at the base of the
                                           VIEW button and select DESIGN VIEW.




                                         2 You will be asked to save the table,
                                           enter the Table Name:
                                                           Current Loans
                                            and select OK.



A   Entering a Text Field

                                         1 In the DESIGN VIEW screen the table
                                           name is displayed in the NAVIGATION
                                           pane.




                                         2 In the first FIELD NAME box (or cell)
                                           enter the field name:
                                                            First Name
                                            and press the TAB key to move the
                                            cursor to the DATA TYPE column.


NOTE:    DESIGN VIEW allows you to enter the field names, their data type
         (for example, text, number, date/time, etc.) and to make comments
         to remind users about what the field stores.


                        © Guided Computer Tutorials 2008                           1-5
Learning Microsoft Access 2007


 3    In the DATA TYPE column you will be provided with the AUTONUMBER TYPE as the
      program is expecting a record number or code number field. In this case we are
      creating a simple database so the field will need to be set to TEXT.


                                              4 Notice that there is a down arrow
                                                added to the box (or cell). This
                                                indicates that there is a drop-down
                                                menu available containing a list of
                                                other data types to select from.




                                              5 Click on the down arrow and select
                                                TEXT.



                                              6 Press the TAB key to The DESCRIPTION
                                                column, which allows you to make
                                                comments about the field.




  NOTE:      The comments entered in the DESCRIPTION column are displayed
             in the status bar at the bottom of the screen when the field is
             selected. This section of the field definitions is optional and we will
             leave it out for this first database. You will use this feature in later
             databases that you create.




1-6                          © Guided Computer Tutorials 2008
                                                Introduction to Microsoft Access           1
B    Setting the Field Properties
At the bottom of the TABLE window there is a section called FIELD PROPERTIES. This
allows you to enter detailed settings about a field. If the field size is set to 50 characters
it means that the field can store 50 letters, numbers, punctuations, spaces, etc.




                                                  For a person’s FIRST NAME field 20
                                                  characters will be more than enough so
                                                  highlight the 255 and replace it with 20.




C    Entering More Text Fields
TEXT fields will be needed for each person’s Last Name, Sex and Address.


                                                 1 Click in the second FIELD NAME cell
                                                   (just	under	the	FIRST	NAME	field)	and	
                                                   enter:
                                                                  Last Name
                                                   press the TAB key to move to the DATA
                                                   TYPE cell and set it to TEXT.



                                                 2 Set the FIELD SIZE box in the FIELD
                                                   PROPERTIES to 20 characters.




                               © Guided Computer Tutorials 2008                             1-7
Learning Microsoft Access 2007




                                               3 Repeat steps 1 and 2 to enter the
                                                 following fields:
                                                        Sex          (field size: 5)
                                                        Address      (field size: 50)




  NOTE:        You can use the arrow keys on the keyboard to move the cursor
               from cell to cell):




D     Entering a Date Field
Fields that contain dates should be set to the DATE/TIME field type.


                                              1 In the fifth FIELD NAME cell enter:
                                                                 Loan Date
                                                 TAB to the DATA TYPE cell and set the
                                                 DATA TYPE to DATE/TIME from the drop
                                                 -down menu.




NOTE:      i    You can press the first letter of a DATA TYPE to insert it. For
                example, pressing d will insert DATE/TIME.
          ii    Access does not like reserved words to be used as field names. So
                using just DATE as the field name is not recommended.


1-8                           © Guided Computer Tutorials 2008
                                             Introduction to Microsoft Access         1

                                             2 In the FIELD PROPERTIES section, click
                                               in the FORMAT box to display its down
                                               arrow. Click on the arrow to display the
                                               drop-down menu and a series of date
                                               formats should be displayed. Select
                                               MEDIUM DATE 19-June-07.




E    Inserting Number Fields
Fields that will contain numbers can be set to a number format such as NUMBER or
CURRENCY. Calculations can be carried out on the data in number fields.



                                             1 Click in the sixth FIELD NAME cell and
                                               enter:
                                                                Income
                                                TAB to the DATA TYPE cell and set it to
                                                CURRENCY.




                            © Guided Computer Tutorials 2008                            1-9
Learning Microsoft Access 2007




                                              2 In the FIELD PROPERTIES section, click
                                                in the DECIMAL PLACES box, click on
                                                the down arrow to display the drop-
                                                down menu and select 2.




                                              3 Insert a field called:
                                                                  Loan
                                                in the seventh FIELD NAME cell.




                                               4 Set its DATA TYPE set to CURRENCY
                                                 with 2 decimal places.




F      Saving the Table
Microsoft Access automatically saves any records that you enter, however, it does not
save modifications to tables, queries, forms, reports or macros. These sections must be
saved individually.


                                               Click on the SAVE button in the QUICK
                                               ACCESS TOOLBAR to update the table.




1-10                         © Guided Computer Tutorials 2008
                                              Introduction to Microsoft Access         1

Entering a Record
Once the FIELDS have been defined you are ready to start entering the data. At the
moment the screen is in DESIGN VIEW. To enter data the screen must be changed to
DATASHEET VIEW.


                                              1 Click on the arrow at the base of the
                                                VIEW button in the DESIGN tab of the
                                                RIBBON and select DATASHEET VIEW.




                                              2 In the first cell under FIRST NAME enter:
                                                                 Carol
                                                 then press the TAB key to move the
                                                 cursor (or insertion marker) to the next
                                                 field.



NOTE:      i   The box to the left of ‘Carol’ contains the pencil    symbol to
               indicate that you are writing data into the database. Whilst the
               pencil symbol is displayed, the data in that record is not saved.
          ii   The asterisk symbol      at the start of the next row indicates a
               new record can be entered. Microsoft Access automatically adds
               a blank record to the end of the table as you add data.
         iii   Pressing the TAB key moves the cursor to the next field. The
               <enter> key can also be pressed or you can use the arrow keys on
               the keyboard.
         iv    Holding down the SHIFT key and pressing the TAB key moves
               the cursor to the previous field. The left arrow key can also be
               pressed.
          v    If you make a mistake when entering data, use the I-Beam to
               highlight the mistake and type in the correction.


                             © Guided Computer Tutorials 2008                          1-11
Learning Microsoft Access 2007




                                               3 Enter the rest of the record:
                                                 Robinson                         <tab>
                                                 F                                <tab>
                                                 142 High Street Richmond 3121    <tab>
                                                 3 Sep 2008                       <tab>
                                                 25500                            <tab>
                                                 2550                             <tab>



   NOTE:      The symbols at the left of the table should have changed to just
              an asterisk marker. This indicates that RECORD 2 is selected. The
              pencil symbol has been removed from RECORD 1 indicating that its
              data has been saved.




Adjusting the Columns
If	you	look	at	the	data	you	have	just	entered	you	should	see	that	not	all	the	data	is	
displayed.		The	width	of	the	columns	can	be	adjusted	to	display	all	the	data.
 1     Move the pointer over the intersection between the FIRST NAME and LAST NAME
       field headings. The cursor should change to a cross-hair with horizontal arrows
       indicating that it can be only dragged to the left or right.




                                               2 Hold down the mouse button and
                                                 drag the mouse to the left by about a
                                                 centimetre	so	that	the	first	column	just	
                                                 encloses the FIRST NAME heading.



1-12                          © Guided Computer Tutorials 2008
                                            Introduction to Microsoft Access             1
3   Reduce the width of the SURNAME field by about half a centimetre.

                                            4 Move the pointer over the intersection
                                              between the SEX and ADDRESS fields
                                              and double click the mouse button.
                                              This will instruct Access to choose the
                                              best fit based on the largest piece of
                                              data (in this case the width will be
                                              based on the FIELD NAME itself ).




                                            5 Increase the width of the ADDRESS
                                              field so that all of the address data is
                                              visible.




                                            6 Reduce the width of the LOAN DATE,
                                              INCOME and LOAN columns.


7   You have made changes to the appearance of the table. The data has been saved,
    but	not	the	adjustments	to	the	column	widths.		Click	on	the	SAVE	button	in	the	
    QUICK ACCESS TOOLBAR to save the changes you have made to the table.




                           © Guided Computer Tutorials 2008                              1-13
Learning Microsoft Access 2007


Adding Extra Records




                                                 1 In the second row of the table, enter:
                                                    Michael                         <tab>
                                                    Smith                           <tab>
                                                    M                               <tab>
                                                    79 Charles Court Fitzroy 3065   <tab>
                                                    3 Sep 2008                      <tab>
                                                    28000                           <tab>
                                                    2800                            <tab>



   NOTE:       Once you press the TAB key after the LOAN data, the program has
               automatically saved the second record.



 2     Enter the following data using the TAB key to move to each field:
       Gary Kane       M   8 Newell Road Surrey Hills 3127    4 Sep 2008    32000   3200
       Paula Douglas   F   216 Main Road Clayton 3168         6 Sep 2008    35000   3500



                                                 3 The two extra records should be
                                                   inserted and saved.




   NOTE:       You can adjust the width of the columns to best fit to ensure that all
               the data is displayed.


1-14                           © Guided Computer Tutorials 2008
                                              Introduction to Microsoft Access          1

Changing the Data
Now that you have entered some data it can be changed in a number of different ways.
Individual FIELDS can be altered, RECORDS can be deleted, new RECORDS can be added,
etc.


A    Changing the Contents of a Field
Let’s	assume	that	Paula	Douglas	has	just	married	and	she	wishes	to	take	her	spouse’s	
name. You need to change her last name.

                                              1 Position the I-Beam at the start of the
                                                ‘D’ in the last name: DOUGLAS. Hold
                                                down the mouse button and drag the
                                                mouse to the right to highlight the
                                                entire name.




  NOTE:     Double clicking the mouse button with the pointer or I-Beam over
            the name will also select it.




                                              2 Enter: Mason and ‘Douglas’ will be
                                                replaced with ‘Mason’.




                             © Guided Computer Tutorials 2008                           1-15
Learning Microsoft Access 2007


                                              3 Gary Kane has changed his address.
                                                Click the pointer at the start of
                                                his address (this is another way to
                                                highlight a field’s contents). Alter his
                                                ADDRESS field to:
                                                    33 Rogers Court Malvern 3144
                                                and press the <enter> key to accept the
                                                change.


B      Adding New Records
New records can be added at any time. Microsoft Access automatically adds a blank
record to the end of a table as you enter data.


                                              In the fifth row enter:
                                                   Allan Smith M
                                                   156 Alsop Street Werribee 3030
                                                   14 Sep 2008 45000          4500




C      Deleting a Record
Paula Mason has been able to pay back her loan so her details can be removed from the
database.




                                              1 Click on the shaded box to the left of
                                                Paula Mason’s record to highlight the
                                                record then click on the DELETE icon in
                                                the HOME tab of the RIBBON.



1-16                         © Guided Computer Tutorials 2008
                                               Introduction to Microsoft Access       1

                                               2 You will receive the DELETE RECORD
                                                 warning dialogue box telling you that
                                                 you cannot reverse the delete record
                                                 process. Click on the YES button
                                                 and the record will be permanently
                                                 removed from the database.




  NOTE:      Be careful with deleting records. Once a record has been deleted it
             cannot be retrieved. If you delete a record by mistake it will need
             to be re-entered into the database.




Printing the Database
Tables are not normally printed. Reports are created to professionally display the data
on paper. However, you can print tables so that you have a record of what you have
done.

A    Previewing the Database
You can look at a screen view of your table before printing.


                                               1 Click on the OFFICE BUTTON, highlight
                                                 PRINT and select PRINT PREVIEW.




                              © Guided Computer Tutorials 2008                        1-17
Learning Microsoft Access 2007


                                                2 Click on the ZOOM icon in the RIBBON
                                                  to increase the screen view.




                                                3 Notice that the program has inserted
                                                  the name of the table as a header,
                                                  included the date next to it and at the
                                                  bottom of the screen included page
                                                  numbers.



B      The Page Setup

                                                1 Click on the LANDSCAPE icon in the
                                                  RIBBON to so that the table prints
                                                  across the longer side of the paper.




                                                2 Click on the SIZE icon in the RIBBON
                                                  and select the PAPER SIZE you will be
                                                  printing to. It is probably A4,




 3     Print a copy of the table if you wish or click on the CLOSE PRINT PREVIEW icon in
       the RIBBON to close the preview window.




1-18                           © Guided Computer Tutorials 2008
                                               Introduction to Microsoft Access     1

The Record Navigation Buttons
When databases become larger, all the records of those databases will not be visible on
the screen. The RECORD NAVIGATION BUTTONS at the bottom left of the screen allow
you to scroll through the records.

                   Previous    Next       New
                   Record      Record     Record



                 First    Current    Last                  Quick
                 Record   Record     Record                Search




                                               1 Try clicking on the FIRST RECORD,
                                                 NEXT RECORD, PREVIOUS RECORD and
                                                 LAST RECORD buttons in turn and the
                                                 RECORD HIGHLIGHT should move each
                                                 time.




                                               2 Enter: 2 in the CURRENT RECORD box
                                                 and press <enter>. RECORD 2 will be
                                                 highlighted.




                              © Guided Computer Tutorials 2008                      1-19
Learning Microsoft Access 2007




                                               3 Enter: Carol in the SEARCH BOX and
                                                 Carol should be highlighted in the data.




  NOTE:     i   The PAGE UP key on the keyboard sets the cursor to the
                beginning of a column (field).
           ii   The PAGE DOWN key sets the cursor to the end of a column.
          iii   The HOME key sets the cursor to the beginning of a row (record).
          iv    The END key sets the cursor to the end of a row (record).




Finishing a Microsoft Access Session
Complete this section when you need to finish a session or when your teacher asks you
to finish.




                                               1 Click on the OFFICE BUTTON and select
                                                 EXIT ACCESS.




1-20                          © Guided Computer Tutorials 2008
                                            Introduction to Microsoft Access      1
                                            2 If changes have been made to the table
                                              since your last save, you will be asked
                                              whether to save the changes. Select
                                              YES.


3   You will be returned to the Windows desktop.


NOTE:      CTRL+F4 can be pressed to close a table then ALT+F4 can be pressed
           to close the program.




                           © Guided Computer Tutorials 2008                       1-21
Learning Microsoft Access 2007



Databases Assignment 1
You are required to set up a wages database for a clothing factory. You will be required
to create a table, define fields, enter some data, make changes to the data and print the
table.
 1     Start a new BLANK DATABASE, called ASSIGNMENT 1. Save the table as PAY DETAILS
       and enter the following fields with the indicated DATA TYPE and DESCRIPTION.

         Employee             Text                       Enter the employee’s name
	 	      Position	            Text	             	        Enter	the	employee’s	job	type	
         Weekly Hours         Number                     Enter the hours worked per week
         Pay Rate             Currency                   Enter the rate of pay per hour
         Gross Pay            Currency                   Enter the employee’s total pay

 2     For the TEXT fields you will need to decide on appropriate FIELD SIZES. For the
       NUMBER and CURRENCY fields you will need to decide on appropriate FORMATS
       and DECIMAL PLACES.
 3     Enter the following data:
         S. Jones         Manager                   40      $25.00     $1000.00
         W. Anderson      Secretary                 35      $18.00      $630.00
         W. Grace         Receptionist              35      $13.50      $472.50
         G. Seller        Machinist (Snr)           38      $17.50      $665.00
         J. Cope          Machinist                 38      $14.50      $551.00
         R. Justin        Machinist (App)           38       $7.50      $285.00
         W. Drapper       Weaver                    38      $13.50      $513.00
         W. Freeman       Weaver                    38      $13.50      $513.00
         H. Allison       Weaver                    38      $13.50      $513.00


Printing
	 1	   Adjust	the	column	widths,	check	a	preview	of	the	table	thoroughly,	then	print	a	
       copy.
	 2	   Check	the	printout	and	make	any	necessary	adjustments.
	 3	   Make	two	adjustments	to	the	data.		W.	Anderson	has	resigned	and	has	been	
       replaced with R. Nixon. Two employees have had to be laid off due to tough
       economic times. Delete R. Justin and W. Freeman.
 4     Reprint the table and hand in your two printouts for marking.


1-22                               © Guided Computer Tutorials 2008

				
DOCUMENT INFO
Shared By:
Tags:
Stats:
views:70
posted:6/14/2012
language:English
pages:36
Description: Sample pages from Chapter 1 of Learning Microsoft Access 2007 by Greg Bowden which introduces the basics of setting up a Microsoft Access database, including setting a table, defining field types, entering data and editing data,