How to Create a Crosstab Query

How to Create a Crosstab Query for Customer type, Product, and Category A crosstab query is a type of select query. A crosstab query calculates a sum, average, and other aggregate functions, and then groups the results into two sets of values—one down the side of the datasheet (called Row) and the other across the datasheet (called Column). You can also think of it as the two dimensions of a table. Methods For Creating Your Crosstab Query Using the Crosstab Query Wizard The Crosstab Query Wizard is usually the fastest…

Read More

How to Use a Select Query

How to Use a Select Query with Different Type of Row Total A Select query is created from a simple query by adding a Row Total in the query design. There are many types of Row Total that can be selected from the list such as Group By, Sum, Average, Min, Max, etc. By using this query, we can find the sum (total), average, min, or max on that field from a group of data. In this How To, I will show how to find the Sum (total), Average, or…

Read More

How to Find the Unmatched Record

How to Find the Unmatched Records using Query Wizard We can find the unmatched records using a Find Unmatched Query Wizard.  The query will display only the records on the first table, but not on the second table. In this example, I will show how to find a customer that doesn’t have an order. I have a Customer table and Order table that related by the CustomerID. Some customers still don’t have any order in the Order table, so I want to get a list of those customers. Step #1…

Read More

How to Create a Find Duplicate Query

How to Find Duplicate Value using a Find Duplicate Query We use a Find Duplicate Query to get a record from table or query that has same value on certain field. The query will display only records that have duplicate value. The easy way of creating a Find Duplicate Query is using a Query Wizard. In this example, I will show a step by step of creating the Find Duplicate Query from a Product table by using Query Wizard. Step #1 Click a Query Wizard from a Create Menu to…

Read More

How to Create Query from Multiple Tables

How to Create Query from Multiple Tables And Invoke Query Builder The purpose of creating a query from multiple tables is displaying many fields from multiple tables. One table may not contain all fields that you want to display in form or report. Remember: those tables must have at least field related for a relation query. For example below, I have the Orders table which contains five fields; OrderNo, Order Date, CustomerID, Amount, Discount, and Total field. Creating Auto Report You can create auto report if you need all fields…

Read More

How to Split First Name and Last Name from Full Name

How to Split First Name and Last Name from Full Name Sometime we have only one field for a full customer name, do not have a separate field for first name and last name. In the picture below, it is a list of customer full name. If we want to extract a first name and last name from this full name of customer name, we can use the Left Function and Mid Function with InStr function inside the Left and Mid Function. We can create a new field for FirstName…

Read More

Example of using a Criteria in Query: MS Access

Example of using a Criteria in Query 1)  =, >, <, >= , <= , <> (not equal) comparison operator1 These operators can be used for number, currency, date and text of data type field. The meaning is the same meaning for mathematic operator.            2) Using “Is null” It is used as a criteria to get the empty data of that field. For example, you want to get a list of task that has not completed or finished. You can put the “Is null” in…

Read More