Microsoft Access Query Examples

You have landed on our Microsoft Access Query Tutorial and SQL Tips home page.  This page provides an introduction and summary of each of our Access query programming examples.  Access SQL examples range from simple query commands such as insert, select & delete to more advanced concepts such as unions and dynamic code substitution.

On the left side menu you'll find links to the specific Access query examples.  Below you'll find example topic summaries and excerpts from the more popular examples.

Our goal is to give you the information needed to create superior Access databases through use of solid SQL query coding.  At the same time we hope that you'll consider our services for your next project or you might hire us for hourly consulting to help you overcome difficult programming tasks on your current project.

A Little SQL Background:  Structured Query Language is a programming language developed within IBM corporation during the 1970's.  Also known as SEQUEL (Structured English Query Language), this language is designed to provide retrieval and update of information stored in Relational Database Management Systems (RDBMS).

Oracle corporation has had great influence on expanding the functionality of SQL during the 1980's through the 1990's.  SQL differs from other programming languages such as Basic & Fortran in that a user will identify which data is to be retrieve/updated by field name and table name and it is up to a SQL database engine to determine how to find information based on the table relationships originally defined by a database designer.

Microsoft Access query language is a fairly robust subset of the full SQL language.  Access query examples contained in this section of our website will help the non-professional programmer to hurdle stumbling blocks often encountered when creating a query.  These MS Access query examples will provide 'how to' programming for several situations when  you may need to get into the Access SQL script editor to customize your query.

Access Query Examples/Tutorial Table of Contents

 

Use the right-side navigation links to see complete programming example.
SQL Example	Access Query Examples Description
SQL Aggregate Function Query	Min, Max, Count, etc are aggregate functions used to perform calculations in an SQL queries.  Learn the syntax of these aggregations and additional information.
SQL Child/Parent Table Query	Retrieve master (parent) records dependent on the values in a detail (child) table.  An example of this feature would be to identify product order records where on or more of the line item details has a certain status such as out of stock, not shipped, etc.
SQL Choose Statement	The choose function is the big brother of the immediate if statement.  The functionality is similar to the decode function or Oracles SQL programming.  Almost all the components of an SQL statement can be made dynamic using this powerful feature.
SQL Choose Function Advanced	In these examples we explore more complex choose command usage.  We assume you have review the basic choose  statement tutorial above.
Crosstab Query	The Access crosstab (pivot) query wizard which is a good place to start when creating crosstab.  A few runs with the wizard will demonstrate the components which are laid out in these examples.
SQL Crosstab Query Advanced	Here we show modified crosstab query examples giving you ideas about further customization of these types of queries.
SQL Date Time &  Date Part Query	Access date/time query example is the topic of this discussion. Many types of data change over time some examples include work pay, hourly consulting rates, part costs, etc.
Delete Query Example	The delete statement is a data manipulation language (DML) command which will remove one or more rows of data from a relational database table.  The number of rows deleted is dependent upon the criteria used within the where clause of the delete query.
External Link In Clause	Linking to tables outside of the main Access database increases the flexibility of Access databases.  Here we demonstration the used of the 'IN' clause for dynamic table linking   If you put this query in a visual basic module then the user will not know from where the data is coming.
SQL Filter Reports	Filter report records using a form field variable  Many users and developers would use filters on the report to accomplish this task.
Group By Query	The SQL group by clause is a DML command used to combine data based on the values in the fields of a table.  The Group By clause is often used in together with one or more aggregate functions.
Having Query Clause	The having clause is a DML statement used in combination with the Group By clause to limit the records retrieved based on a criteria applied to the aggregate function values.
SQL Histogram Query	In this example we demonstrate a Histogram query example - you'll be surprised at how easy it is to do the calculations for a seemingly complex statistical measure using a single Access query.  In this example we have customers who purchased items from a store.
Insert Into Query Example	The insert into SQL command is use to append or add new records to a table. The new records can be added as Values or the insert into command can be combined with a select statement to append records which already exist in other tables.
Master/Detail SQL Updates	In this example we assume that we have two tables in a master/detail relationship.  The main table contains Tasks which much be completed.  The code demonstrates how to update the master records based on the completion statuses of the task in the detail table.
Order By SQL Example	Create a dynamic order by clause using an immediate if statement to change the 'Order By' clause of a query dependent upon user input.  Based on user input you can sort a form or report on different fields depending on a user's input. This feature is often combined with using Labels as Links.
Query Parameters	Parameter query example is an extension of the bottom-up query example.  The form field is used in the query to determine how far to look back in the M_Attendance table for missed work days.
Predicate In SQL	Predicates in SQL are fairly easy to implement but are important to understand for full use of the query programming language. Listed below are the 5 predicate functions: All, Distinct, Distinctrow, Top, and Top Percent.
Update Same Table Field	In this Update Query example we want to update each employee's salary by 10%.  There are at least two ways to do this query.
Scalar Queries	The scalar query or scalar subquery substantially simplifies what would otherwise be a complex set of queries.  This function allows retrieval of single values from a table, usually aggregate functions, from within the from clause.
Select Query	Select statement begins your basic understanding of SQL DML (data manipulation language) in a relational database.  While the select statement starts out simple there are several optional arguments which can lead to a truly complex SQL command which can span several screens.  The purpose of the select command is to return zero or more rows (records or tuples) from one or more tables or views.
Select Top Query	Have you ever had the need to get the 3rd record from a table using a query only?
Union Query	Access union query example shows how to create a combo box All  choice item. This technique is often used for selecting specific (or All) records for a report.
Update Query	The update statement is the DML (data manipulation language) command to use to modify the data in your relational database tables.