Intermediate  Skills in Microsoft Access

Once you have a database it is critical to be able to easily extract useful data from it.

In this 2 day course we look at examples of Many-to-Many relationships as well as dig deeper into Access query engine. In the Introduction we got familiar with simple Select queries and basic Summary queries. In this segment we explore techniques to specify criteria. Also we learn about Action queries to manipulate data. Also we introduce forms containing a subform as well as reports containing a subreport.

At the end of this workshop, the participants will master the various techniques for selecting data using the query tools available in Access. We will discuss how these queries integrate in an application. For instance how to print only the record currently visible on a form or records falling between two dates appearing on a form.

Detail

• Getting familiar with the sample databases.
• Recognizing a Many-to-Many relationship between datasets and why this is so important.
• Practical exercise on paper creating two databases using various types of relationships.
• Using more complex expressions in Select queries. Using the Immediate Window to create expressions using iif(), Left(), Mid(), etc.
• Using Summary queries (called Total queries) to group data and summarize a field per group. Expressions used are Sum, Count, Avg, Min, Max, First, Last and Expression. Discussion of the Where keyword to specify a criterion on which the summary is done.
• Actions queries are discussed and discussion of how they may be used in some business process manipulating data.
• Crosstab queries. Two dimensional datasets are a very efficient way of looking at data based on two variables. We see how to create this type of query and link the resulting data to a report.
• Printing only one record. Access report by nature prints all records in a query. In this section we learn how to print the record currently displayed in a form. Classic example of this is printing an invoice.

Note

To benefit fully from this 2 day course it is highly recommended to have taken the Introduction level or have 6 to 8 months experience in creating and administrating an Access database. It is recommended to bring a memory stick to make a personal copy all the files.