databasedev.co.uk - database solutions and downloads for microsoft access

Microsoft Access Queries

Record Comparisons Between Tables

When would this be needed? It's probably most used with Joins, when you need to find missing information connected to records in different tables. Consider the following query, which is a combination of a Customers table and Orders table using an INNER JOIN:

Let's say that a customer, Aaron, comes into my store and wants to order a product right away, and I only have time to enter the orders, but not his personal information. He places 2 orders, one for apples and one for oranges. I enter that information into my orders table so I can send it off to the vendor:

This is a situation where a comparison of the two tables would be useful. The built-in tool for displaying these inconsistencies is called the "Unmatched Query". It is available through the query wizard in Access, but it's actually quite confusing to use. In this simple order example, below is the basic progression of the wizard:

We select our orders table to be displayed, because we're looking for orders that have been entered where there is no customer records associated with them:

On the next screen we're asked for the table where the related records are supposed to be kept. For us, it is the customers table:

The next screen is asking us what the common field between the two tables is. This creates the JOIN statement in the query:

And finally, it asks us what fields we want to see in the result:

<image>unmatched4.jpg</image>

And it gives us just what we ask for, which is every record from the orders table that is not linked to a customer record in the customers table:

This is probably the easiest way to find inconsistencies in your data, but to many people's surprise it's also the most unproductive. Why? Because as you can see in the result, you can't make the necessary changes! It is simply an informative output, which, for people that are running businesses, does not mean too much! There is something to be learned from this tool though. If we open the query and look at the sql, here's what we see:

SELECT orders.orderid, orders.customer, orders.item, orders.unitprice, 
orders.quantity, orders.orderdate
FROM orders LEFT JOIN customers ON orders.[customer] = customers.[customer]
WHERE (((customers.customer) Is Null));

That's very useful, because we can start to construct our own queries by using this template. As I mentioned above, when Aaron came into my store and ordered the two products I didn't have time to enter information into a customer record for him. But, if we modify this query that the wizard already gave us, we can do just that:

SELECT customers.* 
FROM customers RIGHT JOIN orders ON orders.[customer] = customers.[customer]
WHERE (((customers.customer) Is Null));

And it shows us just what we expect. Two blank records from the customers table, which both correspond to Aaron's orders:

Even though this gives us useful information, it is a great example of why the wizard can be misleading. The query result in the above image cannot be updated. I will explain the reasons for this later. This basic example illustrates a simple case of "programming oversight", as I call it, because during the data entry process for the user, there was obviously nothing required on the left side (parent side) of the relationship between the two tables. In reality, this would probably never happen, simply because most database programmers know better than to leave vulnerability like this open to users. A more realistic scenario would be one where the customer, Aaron, came into the store and I was required to enter his name (at the very least) into the user form, which then would create a record for him in the customers table with the absolute minimum amount of information. If we look at this scenario, and execute the same RIGHT JOIN query again with slightly different criteria (the Address field), we can update the information as soon as it is presented to us.

The concept that is basically being presented here is the fact that JOIN queries cannot be updated (e.g. - additional info cannot be entered into them) when there is no common field value. In our first effort, there was no customer name in one of the tables and that simply makes data authentication impossible. For more information on the reasons why, I would recommend reading articles about database normalization.

The Author

Adam Evanovich lives in Iowa in the United States and frequently works on contract in various industries. He started using Access in 1997 to record notes in a small database for a marketing program. Since then he has continued to explore the models that are available in Access and often uses them to streamline various small business operations.

Adam's database skills also include MySQL, Oracle, and SQL Server implementations. He also enjoys spending some of his free time understanding the history of technology. In his off hours he enjoys exercising, spending time friends and family and exploring the mountains and forestry.

If you'd like to contact Adam, you can reach him through his email address: the.net.2.0.0.0@gmail.com