Learning Microsoft Access 2007 - Introduction

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

Introduction to Microsoft Access

1

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:

1-2

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.

© 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:

1-6

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.

© 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:

NOTE:

Sex Address

(field size: 5) (field size: 50)

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 dropdown 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

NOTE:

F 142 High Street Richmond 3121 3 Sep 2008 25500 2550

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 3

1

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



NOTE:

2

Smith M 79 Charles Court Fitzroy 3065 3 Sep 2008 28000 2800



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

Enter the following data using the TAB key to move to each field: Gary Kane Paula Douglas

M F

8 Newell Road Surrey Hills 3127 216 Main Road Clayton 3168

4 Sep 2008 6 Sep 2008

32000 35000

3200 3500

3 The two extra records should be inserted and saved.

NOTE:

1-14

You can adjust the width of the columns to best fit to ensure that all the data is displayed. © 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

1-18

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.

© 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 Record

First Record

Next Record

Current Record

New Record

Last Record

Quick 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 Position Weekly Hours Pay Rate Gross Pay

Text Text Number Currency Currency

Enter the employee’s name Enter the employee’s job type Enter the hours worked per week Enter the rate of pay per hour 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 W. Anderson W. Grace G. Seller J. Cope R. Justin W. Drapper W. Freeman H. Allison

Manager Secretary Receptionist Machinist (Snr) Machinist Machinist (App) Weaver Weaver Weaver

40 35 35 38 38 38 38 38 38

$25.00 $18.00 $13.50 $17.50 $14.50 $7.50 $13.50 $13.50 $13.50

$1000.00 $630.00 $472.50 $665.00 $551.00 $285.00 $513.00 $513.00 $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