Create Cartesian product with Excel Query

This Excel tutorial explains how to create Cartesian product with Excel Query. You may also want to read: Microsoft Access create Cartesian product with Cross Join Create Excel Query and update Query Create Cartesian product with Excel Query In SQL, there are several types of Table Join: Left Join, Right Join, Inner Join and Cross Join. Cross Join is to produce all possible combination of records in joined Tables which are unrelated (without common key), the result is known as Cart...
More

Create Excel Query and update Query

This Microsoft Excel tutorial explains how to create Excel Query, create Join Table, update Query, add Query criteria. Microsoft Excel create Query Similar to Microsoft Access Query, Excel allows users to create Query through graphical user interface, which means you don't need to have technical skills to write any SQL statement. Although Microsoft Excel has the capability to do that, Access undeniably provides a much easier way to build Query because You can create Expression and apply c...
More

Microsoft Access produce Cartesian product with Cross Join

This Microsoft Access tutorial explains how to produce Cartesian product with Cross Join in Access Query. You may also want to read: Create Cartesian product with Excel Query Microsoft Access Cartesian product with Cross Join In SQL, there are several types of Table Join: Left Join, Right Join, Inner Join and Cross Join. Cross Join is to produce all possible combination of records in joined Tables which are unrelated (without common key), the result is known as Cartesian product. Most...
More

Excel VBA use Inputbox to select options

This Excel tutorial explains how to use VBA Inputbox to select options using SELECT CASE. Excel VBA use Inputbox to select options In my previous post, I demonstrated how to use VBA Inputbox to input a value and create Msgbox to return the value. In this post I will create an Inputbox for users to select options with the help of Select Case. In the below example, I create three options in the Inputbox for users to select: 1. Mary 2. May 3. Susan If users select something else, a...
More

Solution to Access Error 3047 Record is too large

This Microsoft Access tutorial explains how to work around the error 3047 Record is too large when using DoCmd.TransferSpreadSheet Method. Access Error 3047 Record is too large Although Microsoft Access is a database management system, unfortunately it does not allow users to put too much data in the database. According to Access 2016 specifications , Access Table is limited to 4000 records, and the total size of Access is 2GB. If you create a Table that contains more than 4000 records, ...
More

Access replace Crosstab Query with Expression

This Microsoft Access tutorial explains how to replace Crosstab Query with Expression in order to add multiple aggregate values. Access replace Crosstab Query with Expression In previous post, I have introduced how to use Crosstab Query to build a matrix report. However, Crosstab Query has a limitation that it can only add one aggregate value in the report, which is the Sum of amount in the above example. I also don't like the fact that I need to define the order of the month in the Pro...
More

Excel delete all comments in Workbook

This Excel tutorial explains how to delete all Comments in Workbook using VBA and without using VBA. You may also want to read: Excel show all comments in worksheet Excel consolidate all Comments in the workbook Excel - delete all Comments in Worksheet In Excel spreadsheet, if you know where the comments locate, you can delete multiple Comments by selecting multiple Cells, then right click on any selected Cells > select Delete Comment.   If you are not certain where ...
More

Excel compare time or apply condition on timestamps

This Excel tutorial explains how to compare time or apply condition on timestamps in Excel spreadsheet Excel compare time or apply condition on timestamps When we have two timestamps (a date with time) on the same date, we can use simple subtraction on the two timestamps to compare. If the two timestamps have different dates, we can still minus the timestamps to compare. But if we only want to compare the time only without considering the date, we need to extract the time portion of time...
More

Access VBA change Query criteria using QueryDef.SQL Property

This Access VBA tutorial explains how to change Query criteria in VBA using QueryDef.SQL Property. You may also want to read: Create Query using CreateQueryDef Access VBA - change Query criteria It is easy to change Query criteria in Query View, but sometimes your criteria may depend on a variable that is not a constant. In Access VBA, you can change the Query criteria using QueryDef.SQL Property. In fact, QueryDef.SQL does not really update just the update the criteria of the statement,...
More

Excel VBA declare Global Variable and variable scope

This Excel VBA tutorial explains how to declare private variable, global variable / public variable in VBA, and explain the scope of variable. You may also want to read: Function, Sub, Method, Property Difference between Dim and Set Scope of local variable / global variable / private variable Procedure Level When you declare a variable inside a Sub Procedure or Function (local variable), the variable can only be used within them. For example, in Sub Procedure1, you have declared...
More