Goals
- Learn to design a database and tables
- Improve the ergonomics of a database
- Learn about VBA and SQL languages
Program
Presentation of Access and the other tools of the Office suite
Examples of implementation with Access
The vocabulary to use
Description of the interface (Ribbon, Quick Access Toolbar, etc.)
Structure of a file (database, tables, fields and records)
Create a database
The different editing modes of ‘Access
Create a table
Define the primary key of the new table
Integrate fields and apply a data type to them
Configure field properties
Index a table
field Calculated fields
Creation of the relational model
Create a relation manually
Define a type of relation
The different joins
Check the object dependencies
Automatic relation thanks to the drop-down lists on a field
Workshop: Creation of a complete database with tables and relations
Query creation
Gather fields from several tables
Filter the data to display
Introduce regular expressions
Use query parameters
Use functions (sum, average, concatenation, etc.) in queries
Group data
Identify duplicates in a table
Perform a crosstab
Find non-matches on records
Workshop: Data extraction in order to produce statistics
Create a form via a table or a query
Use the wizard for form creation
The different areas (header, details and footer)
Modify the visual of a form
Adapt the properties
Insert action buttons
Integrate sub -forms
Workshop: Improve the user interface of your database
Create an independent form
Display the form when opening the database
Integrate various controls (text zone, drop-down list, checkbox
Workshop: Create a navigation system within your Access database
Create a report via a table or a query
Use the report creation wizard
Modify the visual of a report
Adapt the properties
Perform groupings and sorts
Open a report via a form
Integrate a filter on a report via a form
Create labels using Access data
Integrate a table or query for a Word mail merge
Import an Excel table into an Access table
Import a CSV file
Export data to Excel, Word or in PDF format
Save all import / export steps
Bulk add data
Apply a change to an entire field
Quickly delete a large amount of data
Passing of the certification (if foreseen in the financing)
Duration
5 days
Price
£ 2174
Audience
Everyone
Prerequisites
Knowledge of office software such as Word and Excel
Reference
BAS1102-F
Analyze the performance of the database or of a table
Split the database
Compact and repair the database
Encrypt its database with a password
The interest of macros
Principle of operation of a macro
Concept of event
Macros on command button
Start a macro when opening the database
Condition Notions
Error Handling
User Interface
Macros Data Macros
Objectives of the VBA language
Presentation of the VBA
Use of the VBA
Principle of operation of a macro
See and modify the code of a macro
Language syntax
Variables
Constants
Operators
Integrating a VBA function in an Access macro
The DoCmd
object The Form object
Operating principles
Condition structures (If, Select Case)
Multiple conditions (And / Or)
Presentation of loops
Looping according to a counter (For Next)
Looping according to a condition (Do Loop)
Discover the basics of SQL language
Retrieve the SQL code of an existing query
Integrate VBA objects from Excel
Use the ADO method
Transfer data to Excel
Retrieve data from Excel
Revisions on Access
Questions / Answers
Passing the PCIE certification
Passing of the certification (if foreseen in the financing)
Sessions
Contact us for more informations about session date
