HiTechCoach | Access Tutirials, TIPs, Examples
You are here:
Access Queries - Tips and Tricks
Access Queries - Tips and Tricks
Finding Records in One Table but Not Another with "Not In" Queries
Microsoft Access Query SQL Tip: Finding Records in One Table but Not Another with "Not In" Queries by Molly Pell, Quality Assurance Specialist When querying data from multiple tables in Microsoft Access or SQL Server, we usually use Inner Joins to link records with values that exist in both tables. But what if we need to find records that exist in one table but not the other?
Fixed column name and crosstabs
If someone wants to fix column names produced by a crosstab query, they can use Column Headings property of such a crosstab ( from its property sheet, in design view). You just put in it the name of the columns you want to get, like "JAN", "FEB", "MAC", "APR", ... and if you have typed MAC, you will NOT get a column MAR, for March, and you WILL get an column MAC, probably full of NULLs. Setting the Column Headings will force the XTab query to produce the columns names you give, even if a column is all empty, and it will produce ONLY those.
How to Update and Append Records in One Update Query
In Microsoft Access, you can use an update query to append unique new records to a table, as well as update existing records in the table. For example, by using two tables (Table1 and Table2) in an update query, you can update older records in Table1 by using newer data from Table2, and you can append any unique new records to Table1 from Table2.
Referring to a Field in the Previous Record or Next Record
This article describes two methods that you can use to obtain values from the previous or the next record for use in calculations. The first method uses the DLookup() function in an expression; the second method uses two user-defined functions.
Discovering subqueries is one of those "Eureka!" moments. A new landscape opens in front of you, and you can do really useful things such as:
Tips and Techniques for Queries in Access 2007
Summary: Learn about queries and their uses in Microsoft Office Access 2007. (29 printed pages) Luke Chung, President of FMS Inc August 2009 Apples to: 2007 Microsoft Office System, Microsoft Office Access 2007
Use Multi-Select List boxes as query parameters
(Q) I have a MultiSelect listbox control on my form. I want to pass the selected items to a query as a parameter. How do I do this? (A) Unlike simple listbox controls which can be referenced as a parameter by a query, MultiSelect listboxes cannot be used directly as a parameter. This is because calling the listbox (Forms!frmMyForm!lbMultiSelListBox) from anywhere will not automatically concatenate all the selected items. You need to build the criteria yourself.
Use queries to delete one or more records from a database
You can use two types of queries to delete data from an Access database. The query that you use depends on the type of deletion that you need to perform.
Create a query that returns Random records
How can I return Random records from a table?
ACC2000: How to Build a Visual Basic Module to Print a Report in Landscape Mode on Legal-Size Paper
Office Template Store
What happens to those free Windows 10 upgrades after July 29, 2016?
Preview a Report and move to last page
Deploying Application (Front End) Updates
Microsoft Most Valued Professional MVP - Access Expert
Trust Center in Access 2007, 2010, 2013, and 2016
Why are there not many articles or examples for Access 2007, 2010, and 2013?
Splitting your Access database into application and data
What is the Access Runtime Version?