A great start to my bloggin career is to share something I genuinely didn't know until 5 minutes ago. Access (2002) has a menu selection for SQL specific queries, although doesn't have a design interface. This basically means that if you want to create either Union, Pass-through or data definition queries, you have no user-friendly interface to design them in, but need to enter SQL yourself.
Today I am creating a Union query, as I have several tables with data I want to combine on 1. All three tables are linked, so I don't really want to regularly create a new table every time I wish to query this data. There are helpful instructions on how to do this. The key word in your SQL statement here is of course 'union'. All you really need is to select all your tables indicating a union between. We can also include distinct records by using the the word ALL in our statement, depending on how many fields are in the query.
This will then appear with the linked rings icon.
For example:
SELECT tblClientIDsperPolicy.policy_no, tblClientIDsperPolicy.assured1 AS assuredID
FROM tblClientIDsperPolicy
UNION SELECT tblClientIDsperPolicy.policy_no, tblClientIDsperPolicy.assured2 AS assuredID
FROM tblClientIDsperPolicy;
