Skip to main content

Read our latest COVID-19 (coronavirus) updates.

PayReportApply
PayReportApply
Read aloud icon Read aloud Accessibility icon Accessibility
IT training

Microsoft Access 2016 (Advanced)

  • Duration

    Two days: 9.30am - 4.30pm

  • Description

    The Access Advanced course introduces many of the more advanced tools available in Microsoft Access. It has been designed for users who are proficient in the functionality of Microsoft Access to help them realise the full potential of the database tool.

    The course covers various new topics including the use of macros, advanced queries, formulas in reports, linking tables, forms and sub forms. It also covers some of the functions available within Access.

  • Prerequisites

    Attendance on Access course or extensive knowledge of Access.

    Check points:

    • Can you create a table, form, query and report?
    • Can you create relationships?
    • Have you knowledge of using formulas in reports and queries?

  • Agenda

    Exercise Details
    Exercise 1 Modifying data types
    • change field properties
    • add hyperlinks
    • lookup lists
    • set mandatory fields.
    Exercise 2  Modifying data types
    • creating validation rules and text,
    • default values
    • input masks.
    Exercise 3  Linking tables
    • Linking tables
    • amending links
    • understanding referential integrity.
    Exercise 4  Select queries
    • creating a simple query to filter information.
    Exercise 5 

    Select queries

    • creating a query to emphasize the importance of linking tables.
    Exercise 6 

    Creating subtotals within queries

    • using Sum and Count functions within queries to sub total information.
    Exercise 7 

    Identify key values using queries

    • Find the highest and lowest range of values in a query.
    Exercise 8

    Using the Crosstab Query

    • using the Crosstab Query to cross analyse data.
    Exercise 9 

    Using wildcards within queries.
    ​Exercise 10 

    Using parameters within queries.
    Exercise 11 Combining wildcards and parameters within the same query.
    Exercise 12  Using Null in queries
    • Using the keyword ‘Null’ to find empty fields in a table.
    Exercise 13  Using Not in queries
    • Using the keyword ‘Not’ to find values that do not match the specified criteria in a query.
    Exercise 14  Using Not or Null in queries
    • Using the keywords ‘Not’ or ‘Null’ to find values in a query.
    Exercise 15  Using Arithmetic Expressions within queries
    • Using arithmetic expressions within queries to create new information.
    Exercise 16 
         		 Find duplicates query
    • using the find duplicates query to find duplicate records in a table.
    Exercise 17 
    Show Unmatched Values Query
    • using the Show Unmatched query to find values in one table that do not have a match in a linked table.
    Exercise 18  Update Query
    • using the Update Query to update values in a table.
    Exercise 19  Delete Query
    • using the Delete Query to delete values that match certain criteria within a table.
    Exercise 20  Make Table Query
    • using the Make Table Query to create a new table listing all orders that have not been paid.
    Exercise 21  Make Table Query
    • Using the Make Table Query to create a new table listing all Purchasing Officers.
    Exercise 22  Append Query
    • Using the Append Query to add records that meet a certain criteria to a table.
    Exercise 23  Join Properties
    • Create a new query and amend the join type between the tables.
    Exercise 24  Join Properties
    • Open an existing query and modify the join type between the tables.
    Exercise 25  Join Properties
    • Opening a second query and again modify the join type between the tables.
    Exercise 26  Creating Forms
    • creating a basic input form.
    Exercise 27  Changing controls within forms
    Modifying controls within a form.
    Exercise 28  Adding controls within a form
    • Adding a combo box to a form.
    Exercise 29  Adding Controls within a form
    • adding combo boxes, check boxes and option groups to a form.
    Exercise 30 Using Formulas in forms
    • using the multiplication operator to update information on a form.
    Exercise 31  Using formulas in forms
    • using the subtraction operator formula to update information on a form.
    Exercise 32 
         		 Creating a subform
    • Creating a subform using the Form Wizard.
    Exercise 33 
         		 Embedding a Subform
    • Adding a subform to a form that has already been created.
    Exercise 34 
         		 Tab Order on Forms
    • Changing the sequential order of tabs within a form.
    Exercise 35 
         		 Creating a Report
    • Creating a grouped report using the Report Wizard.
    Exercise 36 
         		 Using Arithmetic Formulas in reports
    • using the multiplication formula in reports.
    Exercise 37 
         		 Using Formulas in Reports
    • using formulas to calculate new costs in a report.
    Exercise 38 
         		 Using Functions in Reports
    • using the Sum function in a report.
    Exercise 39 
         		 Using percentages in reports
    • creating a formula to calculate percentage based information in a report.
    Exercise 40 
         		 Using Functions in reports
    • using the Count function in a report.
    Exercise 41 
         		 Using Running Totals in reports
    • using running totals in reports.
    Exercise 42 
         		 Formulas in Reports
    • more formulas within reports and using a formula to concatenate fields within a report.
    Exercise 43 
         		 Creating an opening screen
    • creating an opening screen for the database.
    Exercise 44 
         		 Creating macros
    • Creating macros and attaching them to a form.
    Exercise 45 
         		 Using macros in forms
    • Creating command buttons on the opening screen form and assigning macros to them.
    Exercise 46  Using macros in forms
    • More macros within forms. Creating command buttons on individual forms and assigning a macro to them.
    Exercise 47 
         		 Creating buttons using the wizard
    • Creating buttons on a form via the command button wizard.
    Exercise 48  Using the maximize macro in a form
    • Creating a maximize macro and assigning it to a specific form.
    Exercise 49  Exporting and importing data
    • Exporting data from Access to Excel and importing an Excel spreadsheet into Access as a table.

  • Goals and objectives


    Goals

    1. To introduce the user to many of the advanced features within Microsoft Access.
    2. To cover the syllabus for Advanced European Computer Driving Licence (ECDL) for databases.
       

    Objectives

    After reading this manual you will be able to:

    1. Apply and modify data types within table design.
    2. Create validation rules and input masks within Access.
    3. Understand the reasons for creating relationships between tables within Access.
    4. Modify relationship types.
    5. Understand and use referential integrity when linking tables.
    6. Create queries to group information.
    7. Use functions such as sum, count, average, etc. within queries.
    8. Show the highest and lowest range of values in a query.
    9. Create queries to update or delete values in a table.
    10. Create queries to save data to another table or append records within a table.
    11. Create forms based on tables.
    12. Create subforms.
    13. Change controls within forms.
    14. Use arithmetic expressions within a form.
    15. Create reports based on tables or queries.
    16. Use formulas and arithmetic expressions within a report.
    17. Create a macro and assign it to a button on a form.
    18. Export and import data from or to an Access database.

    You will complete all of these using Microsoft Access.

  • Costs

    External attendees: £170 plus VAT per candidate.

Read aloud icon Read aloud