Updating a query in ms access

Posted by / 18-Mar-2019 23:15

In the query below, we first get all the distinct records in the sub-query (inner query) in the FROM clause for Reports To column.This should give us a unique list of all the managers in Northwind company.It turns out that Access Jet Engine does not support count(distinct xxx) syntax, which is supported by all major DBMS systems such as SQL Server, Oracle, My SQL, etc.The solution to this problem is to write a sub-query in the FROM clause to retrieve all the qualified distinct records, then in the outer query in the SELECT statement, do a count for the number of records returned by the sub-query.You can think of update queries as a powerful form of the Find and Replace dialog box.You cannot use an update query to add new records to a database, or to delete records from a database.Should any right be infringed, it is totally unintentional.

First, because the Orders table does not have year column, we need to retrieve the year part from the Order Date column.

The cost will be 0 for a fat half hour and there will be no charge if we can't help.

Our Access database tutorials come from nearly 500 existing Access databases we have created over the last several years.

Then the outer query aggregates the result set from the sub-query by using GROUP BY on the YR column and puts a count on the Customer ID column.

So the end result is that we get a distinct count of Customer IDs for each year. Please note that, when I was working on the examples, I used the SQL View of MS Access.

updating a query in ms access-29updating a query in ms access-36updating a query in ms access-79

Note that when we run this query alone, NULL is displayed in the result set, but this NULL value is not counted as a distinct value in the example query shown above when we count the number of distinct values. Lesson learned here is that NULL values are not counted when doing distinct count in Access.

One thought on “updating a query in ms access”