Microsoft Access Training
Toronto, Mississauga, GTA, SW Ontario
By working with your own database (or similar database) learn how Access can create customized reports for you.
We customize the training to your needs and the first question is: Do you have an existing Access Database OR do you need to create a new Database? (if an existing database then we can make rapid progress – if a new database more time may be needed)
Below is a suggested list of topics but these can be modified to suit your needs.
Get information or a quote on Access Training
Send us an email Call 905.271.1119
AGENDA
Microsoft Access – Level 1 Fundamentals
Topic 1: Overview of Microsoft Access 2016, 2013, 2010 & 2007
- Database Concepts, terms & conventions
- Basics concept & objects in Access
- Initial Access database planning and design
Topic 2: Microsoft Access tables – the basic building blocks
- A typical Access table (records & fields)
- Creating a table with the wizard
- Creating a table in design view
- Adding the Access Primary key (this is used to keep unique records)
Topic 3: Working with Microsoft Access tables
- Modifying table fields in Design view
- Searching for records
- Applying filters & sorts to records
Topic 4: Creating Select Queries to extract select information
- Adding an Access Select Query for specific fields
- Adding criteria to a Select Query see only some records
- Expression (formulas in queries) to add calculations
- Having multiple tables in a query
- Select, Make Table, Append, Delete & Crosstab Queries
Topic 5: Creating Forms for easy data entry
- Creating a new form using the Form Wizard
- Using the form to modify data
- Switch to the Form Design view
- Using a form to locate information
- Creating Multiple-Table Forms
Topic 6: Creating Access Reports for Data Output
- Creating reports with the Report Wizard
- Adding calculations (SUM, COUNT etc.) to reports
- Editing the report header & footer
Topic 7: Maintaining, Repairing & Compacting a Database
- Creating an Access database from a template
- Managing a database and its objects
- Database maintenance options
Microsoft Access Excel Training Level 2 – Advanced
Topic1: Principles of Database Table Design
- Why Normalize Data
- Normalizing Data for First, Second and Third Normal Forms
Topic 2: Table Relationships in a Relational Database
- Creating Table relationships (one to many, one to one etc)
- Primary keys and foreign keys
- Using the Access Tools to see table relationships
- Referential Integrity with table relationships
Topic 3: Access Table Design Techniques
- Formatting fields
- Applying Data Validation
- Adding an Index
- Input masks & default values
Topic 4: Advanced Select Queries
- Calculated Fields in Access Queries
- Creating Multiple-Table Queries
- Creating Parameter queries
Topic 5: Form Design Customization
- Customizing the form layout (to enhance usage)
- Adding Calculations to a form
- Adding Combo Boxes for form navigation
- Adding labels etc.
Topic 6: Exporting Access Data to Excel, PDFs etc
- Export data to Excel and using Pivot tables
- Import data from Excel
Topic 7: Customizing Advanced Reports
- Customizing a Report Created by the Report Wizard
- Summary Options in reports
- Creating grouping in Access reports
Some Access tips
Conditional Formats
Apply Conditional Formatting on forms to highlight important information
Default Values
Connect to different data sources (databases, text files Excel files etc.)
Layout View
Use Layout view to quickly change the width of columns or rows – no need to use the Design view
Object Dependencies
Use the Object Dependencies tool to see what a query or form depends on. Also shows what depends on the query
Link Excel files
Link Excel files to MS Access to create more detailed and comprehensive reports. The data stays in Excel but the report is created in Access
What are
MS Access
Relational
Databases?
You may have heard that Microsoft Access is a relational database. But what is a relational database?
A Microsoft Access relational database is a number of tables containing data fitted into categories. Tables are composed of Row (Records) and Columns (Fields). All of the tables are connected with links called relationships.
For example a simple database of maintenance of trucks could have 2 tables:
Table A. Lists all the trucks and their specifications such as engine size, weight etc.
Table B. Lists all of the maintenance on the trucks such as oil changes, brakes repairs etc.
To produce a report of trucks AND their maintenance the 2 tables have to be related by a field. In this case it would most likely be a “TruckID” field. The TruckID would only listed on one row in table A (Truck table) but would be listed on many rows on Table B (Maintenance table – all the oil changes etc.) The relationship links Table A to Table B so a report containing data from both tables is possible. This is why this type of database is called an Access Relationship Database.
When creating a relational database, you can restrict the possible values in a data column. For example, purchase year of the truck would have to be greater than 1970 and number of wheels has to be greater that 4. Also there can be default values for fields such as Province which could be set to “Ontario”.
Creating an
MS Access
Database
When you start to create a database with MS Access, you cannot just input data. You need to create a relational database design, which involves splitting your data into one or more tables. Each table will only contain data about one aspect (Trucks would be listed in one table). Tables will then be connected using relational joins, where a field in one table matches to (relates to) a field in another.
Steps in
creating an
MS Access
database
Identify your data
Make a list of the typical fields (columns) needed. Note which fields are text, numeric, date, yes/no etc. Split the data into the smallest possible field for example First Name and Last Name (2 fields). Do not store the same data in more than one place. Note that calculations can be carried out by queries afterwards.
Organize the fields into relevant tables
Group your fields into tables according to what they describe. So a table for Trucks would list all the specifications of a truck.
Add Extra tables for codes and abbreviations
Add a table for provincial codes two-letter codes and anything else that can be standardized. These tables will create dropdown lists – speeds up data entry and reduces errors.
Choose the primary key for each table
A primary key is the field that uniquely identifies each row or record in the table. These primary keys can be numeric or alphabetic.
Link related tables
Which tables contain fields that match fields in other tables? So a TruckID in the asset table will match a TruckID in the maintenance table. Most relationships are one-to-many – one truck relates to may oil changes. Note that this step is usually the most difficult step for people to learn.
About our Training
Delivery
We provide onsite fully customized training targeting your specific needs – not just a pre-set fixed agenda. The training can be with your own Access database and this means you are productive even on the training day, you will create your own queries, forms and reports. We work with you and build your training agenda based on your training objectives and goals. In Microsoft Access there are many functions and while most people will create queries to show stock or sales etc. you may need queries with more complex criteria. In fact the queries can also be combined so that one query depends on another one. Also a query can be connected to a form.
Location
We provide onsite training in the Toronto, GTA, Mississauga, Oakville, Burlington, Hamilton, Niagara, Kitchener, Waterloo, Guelph, Concord, Markham, Pickering, areas. We come to your location so you and your team do not have to drive across town – no wasted time. We offer customized MS Excel PowerPivot training for employees, managers, executives involved in Financial, Accounting, Marketing, Sales etc. in Toronto, Mississauga, Brampton, Ontario, Kitchener, Waterloo, Hamilton, and Cambridge, Canada. Also onsite training allows employees who cannot attend for the entire day to attend for the hours they have available.
Courses
We provide Training courses on Microsoft Excel, PowerPivot, Access, Microsoft Project training, Office 365 & SharePoint End-User. In MS PowerPivot courses Toronto the trainer covers data analysis, PowerPivot pivot tables, graphs, sorting and filtering, Microsoft Excel training also includes MS Excel formulas, Microsoft Excel VLOOKUP Function for data automation, Microsoft Excel HLOOKUP Function for data automation. Office 365 onsite training Analysis and creating MS Excel IF functions formula. We also offer onsite training in other Microsoft Office software: PowerPivot, MS Project, MS PowerPoint customized training, MS Word, MS Outlook, MS Access, Office 365 and SharePoint end-user training Toronto