MS Access SQL select MAX record of each group

Posted on July 19, 2016 by Wyman W

This MS Access tutorial explains how to use Access SQL to select MAX record (MAX row) of each group. Access SQL select MAX record In Microsoft Access, you can select fields you need to Group and then use MAX to get the max record of each Group. However, the result only returns the grouped fields but not all the fields in the original table. In this tutorial I will show two methods to select all fields. Example - Select Max data Table1 below stores the performance rating of each employee in d...

Access SQL SELECT DISTINCT Clause

Posted on May 16, 2016 by Wyman W

This Microsoft Access tutorial explains how to use SELECT DISTINCT Clause in Access Query. Microsoft Access SQL SELECT DISTINCT Clause SELECT DISTINCT can be used in Microsoft Access SQL to select unique values in a field. Note that you cannot use this keyword in Query Design View, you can only use it directly in SQL View. The outcome of DISTINCT is exactly the same as GROUP BY if you only try to find the unique values, but GROUP BY can be used together with other Aggregate Functions such a...

Microsoft Access UNION and UNION ALL

Posted on January 6, 2016October 26, 2016 by Wyman W

This Access tutorial explains how to use UNION and UNION ALL in Access Query. You may also want to read: Excel VBA Union Method UNION and UNION ALL in Access UNION and UNION ALL are used to combine two Query results vertically into one Query, therefore both Queries require to have the same number of fields and same data types. When two Queries are combined, there could be rows of record that are exactly the same (duplicates). UNION removes all duplicates while UNION does not remove an...

Access SQL WHERE Clause

Posted on July 6, 2015 by Wyman W

This Access tutorial explains how to use SQL WHERE clause to add criteria using AND OR keywords. You may also want to read: Access SQL SELECT, SELECT TOP, SELECT INTO Access SQL WHERE Clause In my previous post, I explained how to use SELECT statement to select desired fields. After selecting fields, the next step is to filter data. If no record in the table can match the criteria, no result will return. Access SQL WHERE Clause with "AND" Criteria Syntax For single criteria SELEC...

Access SQL SELECT, SELECT TOP, SELECT INTO

Posted on July 5, 2015July 5, 2015 by Wyman W

This Access tutorial explains how to use SQL SELECT, SELECT TOP and SELECT INTO statement. Access SQL Select Statement SELECT is the most fundamental syntax in SQL. SELECT Statement defines what fields we want to retrieve in the Query. In this tutorial, I will demonstrate how to use SELECT, SELECT TOP and SELECT INTO statement. Access SQL SELECT Syntax of Select and Where SELECT expressions FROM tables WHERE criteria Select specific fields Assume that we have created the below ...