Access gives you the easy to use query design grid interface for combining data from tables that share one or more related fields. It’s simple to use and generally effective. However, there are still some times when the query design grid isn’t enough to get the job done.

Consider the situation where you have two tables that share similar, but unrelated data — for instance a products table and a services table. Both tables have an ID field, a price field, a warranty or guaranty field, and an expiration date field. You’d like to create a dataset that combines the data from these two tables so that you can call customers when their warranties are about to expire-and you’d like to make a single call to discuss both products and services.

A simple query won’t work here, because the key fields ProductID in the Products table and Service ID in the Services table are not related.   A union query can do the trick!  By switching to SQL view while in query design mode and entering the SQL select statements shown below, linked with the UNION keyword, you can create a report based on the query dataset containing both the product and services data.

SELECT productID, price, warranty_available, expiration_date

FROM Products

UNION ALL

SELECT serviceID, price, guarantee_available, expiration_date

FROM Services;

Learn more about the power of SQL statements, and Access queries and database management techniques at Full Circle Computing!