Categories: Databases and SQL, Access
The course is designed for students who have some experience working with Access and are able to create simple databases, queries, forms and reports. During the course, they will learn how to create advanced databases using nontrivial techniques, what are good practices in designing complex databases, how to create advanced queries joining multiple tables and subqueries and how to use SQL language. They will be able to create advanced forms with macros, gather data via e-mail and create mail merges with Access data.
Microsoft Certificate: for an additional fee of 20 GBP per person, we provide registration with Microsoft Authorized Training system. Training participant registered in this system receives a certificate recognized and respected anywhere in the world, and additional electronic training materials provided by Microsoft.
Genuine Microsoft certification not only confirms the holder's skills - it can also be a valuable part of a resume. Important: authorization is available for on-demand training courses only.
Duration
2 days
Agenda
- Collecting information from e-mail surveys and populating databases.
- Designing advanced databases
- Anomalies. Normalization.
- Notation for database schemas.
- Design patterns.
- Designing advanced queries. Typical scenarios
- Finding duplicates.
- Finding unmatched elements.
- Counting results by multiple conditions.
- Finding extremes.
- Working with many-to-many relations.
- Working with multiple joins.
- Writing SQL queries.
- Writing simple query – SELECT..FROM.. statement.
- Filtering – WHERE clause.
- Sorting – ORDER BY clause.
- Limiting output – TOP statement.
- Grouping and summarizing data – GROUP BY clause.
- Adding results from multiple queries – UNION statement.
- Working with sub-queries.
- Other type of queries.
- Crosstab queries.
- Changing data using queries – INSERT, UPDATE, DELETE queries.
- Creating table from query – SELECT..INTO..
- Designing custom display formats. Designing custom Input Masks.
- Designing advanced validation rules and calculated default values.
- Setting validation rules for a row. Understanding data types of parameters.
- Linking data from other databases.
- Working with PivotTables and PivotCharts.
- Automating repetitive tasks with macros.
- Adding a button to form to run macro.
- Adding a macro as event handler.
- Introduction to VBA programming.
For open training, the course materials are prepared for MS Office 2010. Other versions (2013, 2007, 2003) are available on request. Contact us for details.
Audience and prerequisites
The course is intended for Access users who can design simple databases, queries, forms, and reports, and want to master more advanced techniques and features of Access.
Before attending this course, students must have:
- Basic computer knowledge.
- Basic file-management skills.
- Intermediate Access skills.
Certificates
Course participants receive completion certificates signed by ALX.