Merehall Training Getting People on Course

Microsoft Access 2007 Intermediate Level

WHAT’S NEW

• New features in Access 2007
• Database templates
• Using local templates
• Downloading templates
• Enhanced Datasheet View
• New Layout View
• Improved Interface

SECURITY ISSUES & THE TRUST CENTER

• Security warnings
• Trusted Locations - Trust Centre
• Setting the default folder location within Access

TABLES

MODIFYING FIELDS

• Applying and modifying data types
• Modifying column data types
• Formatting Text data type
• Formatting Memo data type
• Examples of formatting a text box or memo
• Formatting Hyperlink data type
• Formatting Currency data type
• Formatting Date/Time data type
• Database object naming conventions
• Data types
• Converting between data types
• Changing a field to a different data type

LOOKUP FIELDS

• Creating and editing a lookup in a field or column
• Creating a lookup from values in a table or query
• Creating a lookup from values you type in

VALIDATION RULES

• Creating a validation rule within a field

INPUT MASKS

• Creating an input mask in a field

DEFAULT VALUES

• Applying a default value to a field

MANDATORY FIELDS

• Setting a required field

CREATING A TABLE

• Creating a new table

RELATIONSHIPS

BASICS OF RELATIONSHIPS

• Issues relating to creating valid relationships
• Identifying a related table
• Creating a relationship
• Deleting a relationship
• Creating a primary key and a composite key

TYPES OF JOINS IN A RELATIONSHIP

• One-to-one
• One-to-many
• Many-to-many

CREATING RELATIONSHIPS

• Creating a one-to-one relationship
• Modifying one-to-one relationship
• Creating a one-to-many relationship
• Modifying one-to-many relationship
• Creating many-to-many relationships

APPLYING INNER AND OUTER JOINS

• Applying an inner join
• Applying an outer join

APPLYING AND USING REFERENTIAL INTEGRITY

• Setting referential integrity

APPLYING AUTOMATIC DELETION OF RELATED RECORDS

• Setting cascading options

QUERIES

QUERY TYPES

• Enabling the content
• Creating and using a query to update data in a table
• Populating data in the tables
• Creating an Update Query
• Creating and using a query to delete data within a table
• Creating a Delete Query
• Creating and using a query to save the selected information as a table
• Creating a Make-Table Query
• Appending records to a table using query
• Creating an Append Query

GROUPING INFORMATION IN A QUERY

• Using Aggregate Functions
• Counting column values using the COUNT function
• Summing column values using the SUM function
• Averaging column values using the AVG function
• Finding minimum column values using the MIN function
• Finding maximum column values using the MAX function
• Using a crosstab query
• Creating a crosstab query
• Creating a crosstab query using Crosstab Wizard
• Using wildcards as parameters

USING ARITHMETIC OR LOGICAL EXPRESSIONS IN A QUERY

• Creating an arithmetic calculation within a query
• Creating a logical (condition based) expression within a query

REFINING A QUERY

• Finding duplicates using the Find Duplicates Query Wizard
• Finding unmatched values using the Find Unmatched Query Wizard
• Finding the lowest range of values within a query
• Finding the highest range of values within a query
• Creating a parameter query
• Finding Null values
• Finding NOT values

FORMS

CREATING BOUND AND UNBOUND CONTROLS

• Creating an unbound control
• Creating a bound control

CREATING A COMBO BOX, LIST BOX, CHECK BOX & OPTION GROUPS

• Creating a combo box
• Creating a list box
• Creating a check box
• Creating an option button
• Creating an option group

CREATING ARITHMETIC / LOGICAL EXPRESSION CONTROLS

• Creating an expression control
• Creating an arithmetic expression control
• Creating a logical expression control

CREATING A FORM USING THE FORM WIZARD

• Creating a form with Form Wizard

SETTING SEQUENTIAL ORDER OF CONTROLS ON A FORM

• Setting tab index for controls on a form

CREATING FORM HEADERS OR FOOTERS

• Creating a form header and footer area
• Numbering a form page

CREATING A SUBFORM AND LINKING TO THE MAIN FORM

• Using a subform
• Adding a subform to a form

MODIFYING THE SUBFORM TO CHANGE RECORDS DISPLAYED

• Bringing a subtotal from a subform to a main form

REPORTS

CREATING ARITHMETIC / LOGICAL CALCULATION CONTROLS

• Placing a control on a report
• Creating an arithmetic expression control
• Creating a logical expression control

USING FORMULAS AND EXPRESSIONS

• Using common functions in a report
• Using count and sum calculations within a report
• Using sum min, max and average calculations within a report
• Concatenating (combining) values within an expression

CREATING RUNNING SUMMARIES

• Creating a report with running summaries

PRESENTATION

• Creating page headers and footers
• Creating report headers and footers
• Grouping records on a report

FORCING PAGE BREAKS FOR GROUPS ON REPORTS

• Forcing page breaks after each group of records

USING MACROS

MACROS

• Creating a new macro
• Running a macro
• Using the single step method to run a macro
• Attaching a macro to a form control
• Attaching a macro to a report control

IMPORTING, EXPORTING AND LINKING DATA

IMPORTING DATA

• Importing from a text file
• Importing from an Excel spreadsheet
• Importing from a dBase file
• Importing from a Paradox file

EXPORTING DATA

• Exporting data to a text file
• Exporting data as an Excel spreadsheet file
• Exporting data to a dBase file
• Exporting data to a Paradox file
• Linking external data to a database
• Linking to an external text file
• Linking to an Excel worksheet