If you know anything about what a parameter query is then the only added benefit and knowledge you need is knowing what and how to build a union query and apply adding a parameter to the Access query.

As a reminder a parameter is a prompt for the user to enter a value which is then passed (as a variable – varying value) into the query as it’s criteria. You can have more than one prompt but you can imagine the frustration if you have too many where the end-user will simply not bother to use it at all!

The convention for adding a parameter in Access query is to enclose what caption the user will be prompted for within square brackets; i.e. [Enter your name:]

Adding A Parameter In Access Query: Using A Union Query

The next aspect to this article is to know what a union query is all about and how to build it as this is not a normal standard query where you would typically use the QBE (query by example) grid interface that Microsoft Access kindly provides.

This type of query combines two or more tables (or data sets) together that has no relationship but has a similar structure with the type of information and fields.

The union simply appends the records into one ultimate list using the keyword UNION in the SQL statement. Yep, we need to learn some basic SQL language, check out my free SQL reference guide for more details.

Here’s an example piece of SQL statement that joins a table called ‘Customers’ and a table called ‘Suppliers’ having similar fields and values but is not related to each other at all. In SQL View of a new query add the following code making sure you have tables and fields that relate (if not adjust it):

SELECT City, CompanyName, ContactName, "Customers" AS [Relationship]
FROM Customers
UNION SELECT City, CompanyName, ContactName, "Suppliers"
FROM Suppliers
ORDER BY City, CompanyName;

Do not confuse the [Relationship] notation above which is not a parameter but is instead an alias placing a real value of either ‘Customers’ or ‘Suppliers’ as literal values. I’m coming onto adding a parameter in Access query next.

Now see how the parameter looks like asking for a city from the end-user:

SELECT City, CompanyName, ContactName, "Customers" AS [Relationship]
FROM Customers
WHERE City = [Enter City:]
UNION SELECT City, CompanyName, ContactName, "Suppliers"
FROM Suppliers
WHERE City = [Enter City:]
ORDER BY City, CompanyName;

When you run the query, a prompt appears:

and you will see (as for example)…

So there it is combining a parameter with a union query which is just another flexible way to creating a report. Take a look at some of my eBook offers about queries and other aspects of this database application.

Tags: access query, adding a parameter query, adding a parameter to a union query, criteria access query, how to build access queries, ms access query parameter