-
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- To introduce the user to many of the advanced features within Microsoft Access.
- To cover the syllabus for Advanced European Computer Driving Licence (ECDL) for databases.
Objectives
After reading this manual you will be able to:
- Apply and modify data types within table design.
- Create validation rules and input masks within Access.
- Understand the reasons for creating relationships between tables within Access.
- Modify relationship types.
- Understand and use referential integrity when linking tables.
- Create queries to group information.
- Use functions such as sum, count, average, etc. within queries.
- Show the highest and lowest range of values in a query.
- Create queries to update or delete values in a table.
- Create queries to save data to another table or append records within a table.
- Create forms based on tables.
- Create subforms.
- Change controls within forms.
- Use arithmetic expressions within a form.
- Create reports based on tables or queries.
- Use formulas and arithmetic expressions within a report.
- Create a macro and assign it to a button on a form.
- 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.