CODEWITHSUNDEEP
sqlms-accessms-access-2010ms-access-2007
adp
adp

Reputation: 311

MS Access : SQL Query "Union All"

I have the Following problem , I have a form for which I created a combobox that I use to Filter. My Row Source SQL Query looks like this :

SELECT Employee_ComboBox.LastName,Employee_ComboBox.FirstName, Employee_ComboBox.ID,Employee_ComboBox.OperatingEntity,Employee_ComboBox.OrganisationNameFull
FROM Employee_ComboBox
ORDER BY Employee_ComboBox.[ID];

I Would like to Add UNION SELECT "(All)" FROM so that I get (All) in my combobox.The Problem is I don't know how to add more than 1 Column to the SQL Query. I tried something like :

SELECT Employee_ComboBox.LastName FROM Employee_ComboBox UNION SELECT "(All)" FROM Employee_ComboBox; This Works ,but when I try to Add other Columns I Do something wrong..Example :

SELECT Employee_ComboBox.LastName,Employee_ComboBox.FirstName FROM Employee_ComboBox UNION SELECT "(All)" FROM Employee_ComboBox;

Any Ideas on how I can Add all the columns and the (All) Value in my Combobox?

Br,

Upvotes: 0

Views: 2268

Answers (2)

Subin Chalil
Subin Chalil

Reputation: 3660

This may work. no of columns should be equal in both candidates of joins

SELECT Employee_ComboBox.LastName, Employee_ComboBox.FirstName
    FROM Employee_ComboBox
    UNION ALL
    SELECT "(All)" as LastName,"" as FirstName FROM Employee_ComboBox;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269463

You need to add values for them so the subqueries for the UNION have the same number of columns. NULL is often a reasonable value:

SELECT Employee_ComboBox.LastName, Employee_ComboBox.FirstName
FROM Employee_ComboBox
UNION ALL
SELECT "(All)", NULL FROM Employee_ComboBox;

You might want the empty string instead. Also, use UNION ALL instead of UNION. UNION incurs the overhead of removing duplicates, which is unnecessary in this case.

Upvotes: 0

Related Questions