Access Links

Access Links

Access Queries - Tips and Tricks

  • Hits: 2482
    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?
  • Hits: 1980
    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.
  • Hits: 2273
    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.
  • Hits: 1938
    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.
  • Hits: 2793
    Subquery basics
    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:
  • Hits: 1694
    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
  • Hits: 1900
    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.
  • Hits: 1425
    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.
  • Hits: 1624
    Create a query that returns Random records
    How can I return Random records from a table?
  • Hits: 272
    Access error: "Query is corrupt"

    ISSUE

    When attempting to run an Update query, it may not run and displays the error: "Query 'query name' is corrupt". 

    Error indicating query is corrupt

    This will occur for an Update query that:

    • Updates a single table (i.e. it updates a table, rather than the output of a Select query, or join

    • Specifies a WHERE clause (i.e. has entries in the Criteria row in the query designer)

    This issue impacts all supported builds of Access.

    The issue was introduced on November 12, 2019 via the following patch updates for MSI builds:

    Office 2010: Description of the security update for Office 2010: November 12, 2019 (KB4484127)
    Office 2013: Description of the security update for Office 2013: November 12, 2019 (KB4484119)
    Office 2016: Description of the security update for Office 2016: November 12, 2019 (KB4484113)
    Office 2016: November 12, 2019, update for Office 2016 (KB3085368)

    The table below lists the dates/build numbers for the earliest available update, but for MSI builds, the patch referred to is not a required patch and must be manually applied.  There is a December 10 security update for the MSI builds, that will be available via WSUS and will be automatically applied that fixes the issue:

    2010: KB4484193 – build 14.0.7243.5000
    2013: KB4484186 – build 15.0.5197.1000
    2016: KB4484180 – build 16.0.4939.1000

  • Hits: 291
    Access SQL: basic concepts, vocabulary, and syntax

    When you want to retrieve data from a database, you ask for the data by using Structured Query Language, or SQL. SQL is a computer language that closely resembles English, but that database programs understand. Every query that you run uses SQL behind the scenes.

    Understanding how SQL works can help you create better queries, and can make it easier for you to understand how to fix a query that is not returning the results that you want.

    This is one of a set of articles about Access SQL. This article describes the basic use of SQL to select data, and uses examples to illustrate SQL syntax.

  • Hits: 94
    Use a union query to combine multiple queries into a single result

    Sometimes you might want to list the records from one table or query with those from one or more other tables to form one set of records - a list with all the records from the two or more tables. This is the purpose of a union query in Access.

    To effectively understand union queries, you should first be familiar with designing basic select queries in Access. To learn more about designing select queries, see Create a simple select query.

     

    Note: The content in this article is intended for use with Access desktop databases. You can't create or use a union query in Access web databases or Access web apps.

Latest News