Microsoft Access training
We understand that Microsoft Access isn't like the other Microsoft Office tools - it's not so simple to just pick up and use.
But, whether you're taking over with an existing database, or have to create a new one from scratch, we'll show you what you need to know, using real world examples or even your own data so that you really understand the concepts.
Microsoft Access - Working with an existing database
Do you have a database that was designed by someone else who's now left your organisation? Or have you set it up yourself and got so far, but now you need some more guidance? This course is designed to help you make the most of your database.
We'll help you understand how the different parts of Access fit together, and how you use each of them to work with the information that you've got recorded.
Key skills learned:
- The different types of object in an Access database, and what they're used for, covering:
- What makes a relational database such as Access different from a simple data list such as Excel?
- Working with Tables in Access
- What you can (and can't) do in a table in Access
- Finding, sorting and working with tables
- Understanding common data types and their uses
- Finding data in linked tables
- How to use forms in Access
- Finding data using a form
- Using a form to add data in Access
- Editing data with an Access form
- Deleting data using a form
- Making changes to forms
- Preventing users from editing or deleting data in a form
- Changing the appearance of MS Access forms
- Adding buttons to forms in Access
- Using queries in Access
- Finding your data by using queries
- Bringing together data from multiple tables with queries
- Using wildcards to perform powerful searches
- Using queries to find and fix common problems - blanks and null values, missing data, zero values, incorrect data
- Doing calculations with queries - totalling across rows and columns, finding earliest and latest dates, highest and lowest totals and more
- Creating reports in Access
- Why create a report in Access?
- Report-building essentials
- Formatting reports to look attractive
- Working with multi-page reports
- Adding calculations to reports
- Importing and exporting data
- Sending your data out to Excel
- Importing data from Excel
- Linking Access to Excel so that you can write queries on your spreadsheets
- Exporting reports to PDF or to Word
Unlike most training companies, we don't operate to fixed schedules.
We always run our courses in our clients' offices, so your staff will not have to travel or stay away from home.
We run courses on demand, tailoring each course to the needs of the client. So, for example, if you want a quick recap on the basics to fill in any gaps, but then want to move on to specific issues that you're having with your database, that's fine.
Or if you to focus for a day on fixing data problems, or creating flexible queries - that's all good too; whatever works for you is fine by us.
As to when - whenever you need it. Because the course is just for you, we'll work with you to find a date to suit you.
Costs vary, but typically start at £795 + VAT per day. Remember, this is the total cost - not the cost per person.
Need to create a new database?
Although the topics covered above would definitely be needed by anyone planning to create their own database, there's also a lot more required if you're going to be setting up a new system from scratch.
A database is not like an Excel spreadsheet or a Word document - you can't easily just open Access and go. And that's where most courses fall down - they'll tell you where the buttons are, and what they do, but they don't give you the in-depth background understanding of how a relational database is structured, and what the difference is between a database that really works and one that'll give you endless trouble.
We spend our lives building databases for clients like The Environment Agency, the Freight Transport Association, The Royal Marsden Hospital and many more - and so this is an experience-driven, real-world, hands-on course designed to give you a real, practical understanding of the issues you'll face.
What's more - we'll use your database project along the way, so that by the time the course is over, you're well along the road toward having your working database.
One final thing - we've split this course into three parts to reflect the stages you're likely to go through both in your development process and also in your learning:
- Planning and creating a new database
- Creating the user interface for a database
- Writing VBA code to automate your database - this is a course all on its own! (See below for details)
Part 1: Planning and creating a new database
Key areas covered:
- An overview - what's the process?
- Determining the scope of your database
- What are the key things your database needs to track?
- What do you need to know about each of those things?
- What are the key processes your database must support?
- What are the key outputs your database needs to produce?
- Does the database need need to support multiple users, or different users with different permission levels?
- Do you already have the data for the database? If so where?
- Planning the database
- What are the key objects ("Entities" in database-speak) that your database needs to store information about?
- What information do you need to store about each of these things (these are known as the "attributes")
- Understanding and creating Primary Keys and Foreign Keys
- Checking your design - the process of normalization
- How do your entities link together? Planning relationships
- Constructing the database
- Creating tables
- Adding columns and determining data types
- Setting basic field properties - Captions, Default Values, Nulls and Zero Length Strings (and yes - we'll go through what that lot actually means!)
- Setting up Indexes - when you need them and when you don't
- Creating relationships between your tables
- Advanced field and table properties
- Validation rules
- Input masks
- Display Control options
- Indexes based on multiple columns
- Setting table and column descriptions
- BONUS: Code that will automate away much of the drudgery (and we give you the code for free!)
- Simple SELECT queries to find, sort and filter your data
- Getting unique values from a query
- Joining multiple tables
- Changing the join types - INNER vs OUTER joins
- Creating joins on non-key fields - how and why
- Joining non-related tables
- Creating new columns within a query
- Using MS Access functions in a query
- Action queries
- Update queries
- Append queries
- Make-table queries
- Delete queries
- Getting data from elsewhere
- Linking to an Excel file
- Importing data from Excel
- Using a query to import data from Excel to a table
- Determining which tables to import first
- Creating primary keys for imported values
Part 2: Creating the user interface for a database
- Creating a plan for your database interface
- Which processes do you need to support?
- What lists of information do you need to see?
- How will you create new items (products, customers, widgets or whatever else you're tracking) or edit existing ones?
- What other tools will your users need?
- Design options - switchboards, toolbars, subforms, process maps
- Creating forms
- Specifying a data source
- Adding controls
- A guide to the available controls
- Working with ComboBoxes and ListBoxes
- Using Option Groups
- Displaying images
- Working with related data
- Using multi-table queries to show data from multiple sources
- Creating forms with subforms
- Using tab controls to show multiple sets of related data
- Using buttons to link forms together
- Displaying related data in ListBoxes
- Creating the main user interface
- Creating switchboard forms
- Creating process map forms
- Using forms with subforms as a user interface- including an introduction to writing code
Working with Access VBA to create a full user interface
You really can't build a fully functional user interface in Access without getting your sleeves rolled up and learning some code. VBA (Visual Basic for Applications) is the code used in MS Access, and the good news is that the more you learn, the more powerful, flexible and user-friendly your database can be.
This course is both the third part of the course on building a database (as detailed above) and a course in its own right.
Key areas covered:
- Getting started - an overview
- Why write your own code?
- What about Macros in Access?
- Where do I write code in Access? Standard modules and class modules
- A guided tour of the VBA Editor - which toolbars and windows do you need?
- Programming Microsoft Access objects
- What is an object?
- What are the major objects in Access?
- Properties, Methods and Events
- Collections of related objects
- The basics of writing a procedure
- The structure of a procedure
- Interacting with forms in your procedures
- Responding to events in your procedures
- Calling one procedure from another
- Using parameters in your procedures
- Function procedures
- What's the difference between a Sub and a Function?
- How to create a function
- How to call a function
- Calling built-in Access and VBA functions
- What is a variable?
- Creating variables
- The Scope and Lifetime of variables
- Variable data types
- Object variables
- Enforcing variable declarations
- Using Constants
- Making your code more powerful
- Creating loops
- For... Next loops
- Do... While loops
- Do... Until loops
- For Each... Next loops
- Decision-making in your code
- If... Then... Else structures
- SELECT CASE statements in VBA
- Creating loops
- Working with data in code
- The ADO recordset object
- Key Properties and Methods of recordsets
- Looping through data
- An introduction to SQL
- Basics of getting data with SQL
- Aggregate calculations in SQL
- Integrating SQL with your VBA code
- Debugging your code
- The Debug toolbar and the Debug menu
- Break points
- The Debug object and its methods - Debug.Print and Debug.Assert
- Stepping through, over and out of your code
- Error handling
- What is error handing?
- Error handling options
- Creating a robust error handling structure
- What happens after you handle an error?
- Connecting Access to Excel
- Connecting your database to Excel through code
- An introduction to Excel's key objects
- Exporting data to Excel
- Basic formatting of your Excel output
We'd love to work with you if we can!
Let us know how we can help - whether it's giving you a general understanding of Excel or fixing issue with spreadsheets you've already got - just get fill in the form below and we'll get right back to you.