Microsoft Access allows you to create various query types using the QBE (Query By Example) pane. There are also various other queries that you can create in Microsoft Access, however you will need to create these by typing in the appropriate SQL (Structured Query Language) directly into the SQL view window when in query design.
The SQL specific queries available in Microsoft Access are:
If we look at the Union Query, we will see how this lets us quickly combine fields from more than one table or query. You will see that the resultant snapshot of data is not updatable.
To perform a Union query, you must ensure that each SQL SELECT statement contains exactly the same number of fields, in the same order.
As an example, you might want to create a single set of contact details from your Customers table and your Suppliers table to send out a newsletter or Christmas card. You could create a union query to combine the data from both tables. If our tables look like the following examples:
CustomerName | CustomerAddress | CustomerTown | CustomerPostcode |
---|---|---|---|
Elaine Jones | 1 Old Road | Manchester | M27 1SN |
David Thomas | 245 Lane End | Lincoln | LN34 2TH |
Simon Lee | 9 Swallow Lane | Wigan | WN3 0NR |
SupplierName | SupplierAddress | SupplierTown | SupplierPostcode |
---|---|---|---|
Office Suppliers Ltd | 1002 Trinity Road Sth | Swindon | SN2 1JH |
SM Electricals | 3 North End Road | Nottingham | NG8 2LJ |
We use the following SQL to create the union query:
SELECT tblCustomers.CustomerName, tblCustomers.CustomerAddress, tblCustomers.CustomerTown, tblCustomers.CustomerPostcode FROM tblCustomers UNION SELECT tblSuppliers.SupplierName, tblSuppliers.SupplierAddress, tblSuppliers.SupplierTown, tblSuppliers.SupplierPostcode FROM tblSuppliers;
This will create the following recordset:
Showing the results of the Union Query
When you use Union command in the SQL SELECT statement, it will show only records that are NOT duplicates when it joins the multiple tables together. If you want to see all records regardless of whether there are any duplicates, you will need to use the keyword ALL after the UNION command.
This syntax required for this would be:
UNION ALL SELECT