Sort Data in Access Report

Posted on January 9, 2017September 21, 2017 by Wyman W

This Microsoft Access tutorial explains how to sort data in Access Report, including sort data in custom order. You may also want to read: Add Auto number in Access Query Add sorting in Access Table field Sort Data in Access Report In Access Report Design View, you can sort data at the Group, Sort, and Total section, in this tutorial I will explain how to sort data in Detail and sort Grouped data. Example Suppose we have the below table that contains employee data. employee_tbl ...

Create Matrix Report in Access Report

Posted on January 3, 2017May 1, 2019 by Wyman W

This Microsoft Access tutorial explains how to create Matrix report in Access Report using Crosstab Query and Expression. You may also want to read: Access Crosstab Query Excel VBA Convert Text in Columns and Rows into Matrix Table Access Matrix Report Matrix Report is like an Excel Pivot Table, where you can place fields in row and column, and then apply Aggregate Functions on a field value such as Count, Sum, Max. In newer version of Microsoft Access, Pivot Table function has been...

Access Crosstab Query

Posted on December 29, 2016May 1, 2019 by Wyman W

This Microsoft Access tutorial explains how to create Crosstab Query and demonstrate how to add grand total. You may also want to read: Create Matrix Report in Access Report Access replace Crosstab Query with Expression Excel VBA Convert Text in Columns and Rows into Matrix Table Access Crosstab Query Crosstab Query is a Matrix Report, the most commonly known Matrix Report is Excel Pivot Table, where you can place fields in row and column, and then apply Aggregate Functions on a fi...

Access Update Table Records

Posted on December 27, 2016 by Wyman W

This Microsoft Access tutorial explains how to update table records from another table or query. Access update table records You can always update table records manually by viewing the table and then enter the value. With Update Query, you can update multiple table records that meet defined criteria, or update table records from another table. Note that you may update data in an Access table, but you cannot update data in linked table. For example, if your Access table is linked to Excel sp...

Access VBA change Report Record Source

Posted on December 18, 2016December 18, 2016 by Wyman W

This Access tutorial explains how to change Access Report Record Source using VBA in order to modify Report criteria automatically. Change Report Record Source When you create an Access Report, you can specify the data source of the report in Report Record Source, which can be a Query or Table. Type the Table / Query name in the Record Source as below. To add criteria to the Report, there are three ways Method 1 - Write SQL Type the full SQL statement directly in Record S...

Access delete table records

Posted on November 5, 2016 by Wyman W

This Access tutorial explains how to delete table records and delete records that exist in another table in Microsoft Access. Access delete table records You can always modify or delete table data manually by viewing the table and then press the delete button in keyboard. With Query, you can delete specific rows of record that meet the criteria. Note that you may delete data in an Access table, but you cannot delete data in linked table. For example, if your Access table is linked to Excel spr...

Access Instr Function

Posted on October 6, 2016 by Wyman W

This Access tutorial explains how to use Access INSTR Function in Expression and VBA, and explain difference among vbUseCompareOption, vbBinaryCompare and vbTextCompare. Access INSTR Function Access INSTR function is used to search a substring (part of the string) within a string (string is a text) and return the position of the first occurrence. For example, in the string “FinanceDepartment”, the substring “Department” can be found at 8th position within “FinanceDepartment”, the function w...

Access Mid Function to extract substring

Posted on September 26, 2016 by Wyman W

This Access tutorial explains how to use Access MID Function to extract substring from a string. Access MID Function MID Function is quite similar to LEFT Function and RIGHT Function, where RIGHT Function extracts a substring on the right, LEFT Function extracts a substring on the left, while MID extracts a substring in the middle of the string. Other than Access, Mid Function can also be used in Excel worksheet, Excel / Access VBA. Access MID Function is commonly used with Instr Function (...

MS Word VBA add footer to all word documents in folder

Posted on September 25, 2016September 21, 2017 by Wyman W

This MS Word VBA tutorial explains how to add footer to all word documents in a folder automatically. MS Word VBA add footer to all word documents in a folder A couple of years ago, my manager asked me to add the keyword Public / Confidential / Restricted to all Word documents footers to meet compliance requirement (this is a common practice), I painfully did it manually for hundreds of documents. In fact it could have been done automatically. In some of my previous posts, I have demonstrate...

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...