Category Archives: Query SQL

Mar 20

Microsoft Access Query Sorting on Multiple Date or Numeric Fields with Blank Values

Posted on by

Read our new paper, Microsoft Access Sorting on Multiple Date (or Numeric) Fields with Blank Values.

Learn how to sort on multiple date fields when null values exist by using a calculated field. By using the IIf function with Is Null, you can easily sort to see the most recent (or oldest) records.

Microsoft Access QueriesWe’ve included information on avoiding the IsNull function to maintain SQL Server compatibility. Additional information and examples are provided to show why using the NZ function (NullToZero) is not equivalent and returns the wrong results.

This new paper is part of our Microsoft Access Query Help Center.

May 24

Microsoft Access Crosstab Queries in Reports on the Microsoft Access Developer Blog

Posted on by

FMS developer Molly Pell is featured as a guest blogger on the Microsoft Access developer blog.

This post demonstrates how to use the Pivot statement to control column names returned by crosstab queries, allowing crosstabs to be used on reports.

Check out the full post, Using crosstab queries in reports.

For another paper with an example of Creating an Annual 12 Month Summary Report without VBA Code by Creatively Using a Microsoft Access Crosstab Query

Nov 09

Microsoft Access Append Query Examples and SQL Syntax

Posted on by

We've enhanced our Microsoft Access Query Help Center with a new paper describing Microsoft Access Append (Insert) Queries, and how to use them to add multiple records or a single record to a table.


We also discuss why rather than using a Make Table query, it's preferable to use an existing table, empty it, then fill it with an Append query.

Jun 07

Microsoft Access and SQL Server Union vs. Union All Query Syntax

Posted on by

Microsoft AccessMicrosoft Access Union QueryMicrosoft SQL ServerThe purpose of the SQL UNION query is to combine the results of two or more queries into a single result set. The list contains all the rows belonging to all the queries in the union. This applies to queries in SQL Server or Microsoft Access. A common question is whether to use the UNION or UNION ALL syntax.

The main difference between UNION ALL and UNION is that, UNION only selects distinct values and sorts the results, while UNION ALL selects all values (including duplicates) without sorting. Read our revised paper on UNION versus UNION ALL SQL Syntax to learn more about the differences, see some examples, and understand why you would use one versus the other.

For more tips and using queries, visit our Microsoft Access Query Help Center.

May 31

New Microsoft Access Query Help Center

Posted on by

Microsoft AccessMicrosoft Access QueriesMicrosoft Access Queries are among the most powerful features of MS Access. We have created a new resource center to make it easy to review all our papers related to Microsoft Access Queries. These original works offer tips and techniques aren’t found anywhere else. They will help you maximize your use of Microsoft Access queries to better analyze and understand your data.

In the Microsoft Access Query Help Center you will find papers covering:

Retrieving Records

Modifying Data

Let us know what additional related topics you’d like us to add in the future.

Mar 24

Paper: Microsoft Access Delete Query and SQL Syntax

Posted on by

Delete Query in Microsoft Access Could not delete from the specified tables.JPG

In our continuing coverage of query tips and techniques, we've create a paper focused on the DELETE query in Microsoft Access. It covers the basics of DELETE query SQL Syntax. It's a very powerful feature and a fundamental part of relational databases since you can remove multiple records at one time, and can specify which records to delete by linking to data in another table.

The paper also covers these more advanced topics:

  • Query fails: "Could not delete from the specified tables"
  • Use DISTINCTROW with Delete Queries for Non-Key Field Joins

Understanding and using Delete Queries improves the performance of your applications and makes them easier to maintain versus doing the same changes manually or in code.

Dec 12

Microsoft Access/SQL Server Query Tip: Finding Records in One Table but Not Another with “Not In” Queries

Posted on by

Here’s another resource in our ongoing coverage of query techniques:

Learn how to create queries to find all the records in one table that don’t have corresponding records in another table. If you’re not familiar with the difference between Inner Join, Outer Join, Left Join and Right Join, check out our paper on Microsoft Access Outer Join Query: Finding All Records in One Table but Not Another and Creating “Not In” Queries on these important query feature. It’ll save you tons of time trying to code this yourself and will surely give you new ideas on how to better retrieve and analyze your data. The techniques apply to both Microsoft Access and SQL Server queries.

Dec 04

Microsoft Access Delete Query SQL Syntax

Posted on by

Delete Query in Microsoft Access Could not delete from the specified tables.JPG

Here’s an additional paper related to our ongoing coverage of queries. This time we’re covering DELETE query syntax in Microsoft Access. In addition to the basics of deleting data and the SQL for DELETE queries, we also cover an interesting situation when DELETE queries fail during multi-table links on non-keyed fields.

If your query fails to delete any records with this message: “Could not delete from the specified tables”, learn why and how to fix it with the DISTINCTROW syntax or setting the Unique Records property to Yes.

For more information on queries in general, read our paper on Microsoft Access Query Tips and Techniques.

Oct 23

Update Query and SQL Syntax in Microsoft Access

Posted on by

Related to our detailed paper on Query Tips and Techniques, we added extra information on the basics and syntax of an Update Query in Microsoft Access.

We also have a related article on dealing with non-updatable queries: Error 3073: Operation must use an updatable query: Dealing with Non-Updateable Queries and the Use of Temporary Tables in Microsoft Access.

Oct 06

FMS Papers Featured on MSDN

Posted on by

MSDNMicrosoft recently added three of our technical papers to their MSDN (Microsoft Developer Network) web site:

We're proud Microsoft recognized our work and look forward to sharing more resources with the developer community.