Entire Union query returns no results if part of it returns no results

Question

I've created the folllowing Union query, which works fine most of the time:

SELECT [%$##@_Alias].[Contact ID], [%$##@_Alias].[Mailing Name]
FROM (SELECT [Referrals - Contacts Within Organisations].[Contact ID], [Referrals - Contacts Within Organisations].[Mailing Name],  [Referrals - Contacts Within Organisations].[Surname], [Referrals - Contacts Within Organisations].[First name]
FROM [Referrals - Contacts Within Organisations]
UNION SELECT "0" as [Contact ID], "View all contacts" as [Mailing Name], "0" as [Surname], "0" as [First name]
FROM [Referrals - Contacts Within Organisations])  AS [%$##@_Alias]
ORDER BY [%$##@_Alias].Surname, [%$##@_Alias].[First name];

This adds an initial row of "View all contacts" at the top of whatever the query returns.

However, if the "actual" query part of it returns no results, the entire query returns no results, whereas I'd always want the initial row to appear regardless.

Is this possible, and if so, what am I doing wrong? Thanks.

EDIT: Thanks all for your help. The final working query is below for reference of anyone else who needs this sort of thing:

SELECT A.[Contact ID], A.[Mailing Name]
FROM (SELECT "0" as [Contact ID], "View all contacts" as [Mailing Name], "0" as [Surname], "0" as [First name]
FROM [Dummy] 
UNION
SELECT [Referrals - Contacts Within Organisations].[Contact ID], [Referrals - Contacts Within Organisations].[Mailing Name],  [Referrals - Contacts Within Organisations].[Surname], [Referrals - Contacts Within Organisations].[First name]
FROM [Referrals - Contacts Within Organisations]) AS A
ORDER BY A.Surname, A.[First name];

It's something Access added automatically when I went from SQL mode to Design mode. — Sinister Beard, Nov 30 '12 at 08:55
Yeah, because your SQL code did not include the alias. Other DBMS would have given error, Access allows it by adding an alias of its choice. You can change it into something readable, like `a` or `q1` or `report` — ypercubeᵀᴹ, Nov 30 '12 at 08:57

score 1 · Accepted Answer · answered Nov 30 '12 at 08:46

1

To return always one row with data in SQL, even when you are manually providing the values, you need to be selecting from a table that has at least one row, I suggest using a tblDummy with one field of random information.

e.g:

SELECT "Hello", "Goodbye"
FROM tblDummy

As a side note I would also try and get rid of:

[%$##@_Alias]

This can be accomplished by aliasing your Derived tables by adding:

(SELECT * FROM Blah) AS A

for example to name the derived table as A

answered Nov 30 '12 at 08:46

Matt Donnan

4,933
3
20
32

1

I agree. Here a related question. How to create this dummy table: http://stackoverflow.com/questions/7933518/table-less-union-query-in-ms-access-jet-ace – dani herrera Nov 30 '12 at 08:47
That's worked - what I'd rather stupidly done was use the same query that I'm using for the main data in place of a dummy table - so if the query didn't return data, I got nothing across the board. Makes sense now. Thanks! – Sinister Beard Nov 30 '12 at 09:00

Entire Union query returns no results if part of it returns no results

1 Answers1