MS Access SQL Error Adding a static value via UNION to a SELECT Distinct Query

[ianclaridge]

I have a simple form based on a table for unskilled users to view and filter data using a combo box. Hence I simply want to add the the option "All" to the SELECT distinct query for values in that field but am encountering "unexpected error evaluating complex query". Here is my code....

SELECT "All" As Status
UNION
SELECT Distinct table1.Status As Status
FROM table1

Either part of the code (i.e. above or below the UNION) work fine individually, returning the expected value(s), and the existing table field is a short text one so in my view should not be in conflict with the type of the "All" value.

Can anyone tell me where I am going wrong as am sure this shouldn't be so difficult?

I really don't want to have to create a table that just contains a single field and row with the "All" value in it unless there is no other option!

Thanks in advance

Thanks for any help

 

[GPGeorge]

In the first SELECT you do not specify a FROM ....

I think you do need to include the FROM. You can use the same table, table1 (although I bet in the real world that table has a different name. )

 

[GPGeorge]

In the first SELECT you do not specify a FROM ....

I think you do need to include the FROM. You can use the same table, table1 (although I bet in the real world that table has a different name. )

 

[ianclaridge]

View attachment 114374

Thanks but that part isn't from a table, it's just a value, like this..

... and similarly the other side of the query works fine standalone...

...but if I UNION them..

Do you see what I mean?

 

[ianclaridge]

In the first SELECT you do not specify a FROM ....

I think you do need to include the FROM. You can use the same table, table1 (although I bet in the real world that table has a different name. )

The penny just dropped thanks, I had assumed the static value didn't have a source and that adding one would cause an error!

SELECT "All" As Status FROM table1
UNION
SELECT Distinct table1.Status As Status
FROM table1

works thanks and apologies for missing that within your reply!

 

[The_Doc_Man]

The specific rule that gets you here is that every field name dervies from the first member of the UNION, which means it must be a complete query that could stand on its own. You don't even need the "As Status" in the second leg of the UNION because the first leg defines everything except the data source for the later legs in a UNION.

 

[ebs17]

SELECT "All" As Status FROM table1
UNION
SELECT Distinct table1.Status As Status
FROM table1

This works, but is not a convincing solution.
In the first subquery, "All" is duplicated according to the number of records in table1. UNION without ALL then performs grouping, which reduces the number again. In the second subquery, further grouping is performed using DISTINCT. This process is not really structured.

The following would be convincing:

SELECT "All" As Status FROM T1Only
UNION ALL
SELECT Status FROM table1

T1Only is an auxiliary table with exactly one record for exactly such cases. With exactly one record there is a duplication to 1 - which is what you want.
table1 should not contain any duplicates by default, with a well thought-out database schema this would be guaranteed.
This means you can then use UNION ALL instead of UNION, which is faster without trying to group.

 

[cheekybuddha]

The following would be convincing:

SELECT "All" As Status FROM T1Only
UNION ALL
SELECT Status FROM table1

Wouldn't this show repeated entries from table1 (which I guess the OP is trying to avoid since they used 'DISTINCT')?

Maybe no need for a utility table, but still more efficient:

SELECT TOP 1
  'All' AS Status
FROM table1
UNION
SELECT
  Status
FROM table1
;

 

[ebs17]

TOP 1 of many equal delivers all, since there is no distinction.

 

[cheekybuddha]

TOP 1 of many equal delivers all, since there is no distinction.

Good point!

Perhaps better then:

SELECT
  'All' AS Status
FROM (
  SELECT TOP 1
    ID
  FROM table1
)
UNION
SELECT
  Status
FROM table1
;

 

[ebs17]

Do we now have to discuss all conceivable variants?
The variant in #5 works, even if it is complicated in several ways, as described. Because the data volumes are certainly very small, there will never be any performance problems thanks to the power of the processor, so you can calculate many things twice or three times.

In #7 I described what I consider to be the maximum optimization. Either you really need to optimize for performance in some cases, otherwise you can use the tips to form good habits. Something in between is somehow in between.