Problem with Union Query for a ComboBox

D

David C. Holley

I'm upsizing an Access DB to SQL Server and updating the queries as needed.
The only thing that's changed in the query below are the new names in SQL
Server, yet instead of returning

* <Show All>
Dispatched Dispatched
Not Ready Not Ready
Ready for Dispatch Ready for Dispatch

I'm getting...

<Show All> <Show All>
Dispatched Dispatched
Not Ready Not Ready
Ready for Dispatch Ready for Dispatch

Its a query for a combo box which places <Show All> as the first record and
allows all records to be selected using the * wildcard. And yes, I know that
I'll need to switch over to % for it to work.

What the hell am I missing?

SELECT USys_dbo_DispatchStatusCodes.StatusCode,
USys_dbo_DispatchStatusCodes.StatusCode AS [Dispatch Status] FROM
USys_dbo_DispatchStatusCodes UNION SELECT "*" Expr1, "<Show All>" AS Expr2
FROM USys_dbo_DispatchStatusCodes;
 
J

John Spencer

Perhaps just the AS before Expr1

SELECT USys_dbo_DispatchStatusCodes.StatusCode,
USys_dbo_DispatchStatusCodes.StatusCode AS [Dispatch Status]
FROM USys_dbo_DispatchStatusCodes
UNION SELECT "*" AS Expr1
, "<Show All>" AS Expr2
FROM USys_dbo_DispatchStatusCodes;


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
D

David C. Holley

Nope not the problem. It was a typo in the original post.

SELECT USys_dbo_DispatchStatusCodes.StatusCode,
USys_dbo_DispatchStatusCodes.StatusCode AS [Dispatch Status] FROM
USys_dbo_DispatchStatusCodes UNION SELECT "*" AS StatusCode, "<Show All>" AS
[Dispatch Status] FROM USys_dbo_DispatchStatusCodes;


John Spencer said:
Perhaps just the AS before Expr1

SELECT USys_dbo_DispatchStatusCodes.StatusCode,
USys_dbo_DispatchStatusCodes.StatusCode AS [Dispatch Status]
FROM USys_dbo_DispatchStatusCodes
UNION SELECT "*" AS Expr1
, "<Show All>" AS Expr2
FROM USys_dbo_DispatchStatusCodes;


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
I'm upsizing an Access DB to SQL Server and updating the queries as
needed. The only thing that's changed in the query below are the new
names in SQL Server, yet instead of returning

* <Show All>
Dispatched Dispatched
Not Ready Not Ready
Ready for Dispatch Ready for Dispatch

I'm getting...

<Show All> <Show All>
Dispatched Dispatched
Not Ready Not Ready
Ready for Dispatch Ready for Dispatch

Its a query for a combo box which places <Show All> as the first record
and allows all records to be selected using the * wildcard. And yes, I
know that I'll need to switch over to % for it to work.

What the hell am I missing?

SELECT USys_dbo_DispatchStatusCodes.StatusCode,
USys_dbo_DispatchStatusCodes.StatusCode AS [Dispatch Status] FROM
USys_dbo_DispatchStatusCodes UNION SELECT "*" Expr1, "<Show All>" AS
Expr2 FROM USys_dbo_DispatchStatusCodes;
Click to expand...
Click to expand...
 
J

John Spencer

Strange. What happens if you just run this query.

SELECT DISTINCT "*" AS StatusCode
, "<Show All>" AS [Dispatch Status]
FROM USys_dbo_DispatchStatusCodes;

OR how about using single parentheses

SELECT DISTINCT '*' AS StatusCode
, '<Show All>' AS [Dispatch Status]
FROM USys_dbo_DispatchStatusCodes;


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Nope not the problem. It was a typo in the original post.

SELECT USys_dbo_DispatchStatusCodes.StatusCode,
USys_dbo_DispatchStatusCodes.StatusCode AS [Dispatch Status] FROM
USys_dbo_DispatchStatusCodes UNION SELECT "*" AS StatusCode, "<Show All>" AS
[Dispatch Status] FROM USys_dbo_DispatchStatusCodes;

David said:
I'm upsizing an Access DB to SQL Server and updating the queries as
needed. The only thing that's changed in the query below are the new
names in SQL Server, yet instead of returning

* <Show All>
Dispatched Dispatched
Not Ready Not Ready
Ready for Dispatch Ready for Dispatch

I'm getting...

<Show All> <Show All>
Dispatched Dispatched
Not Ready Not Ready
Ready for Dispatch Ready for Dispatch

Its a query for a combo box which places <Show All> as the first record
and allows all records to be selected using the * wildcard. And yes, I
know that I'll need to switch over to % for it to work.

What the hell am I missing?

SELECT USys_dbo_DispatchStatusCodes.StatusCode,
USys_dbo_DispatchStatusCodes.StatusCode AS [Dispatch Status] FROM
USys_dbo_DispatchStatusCodes UNION SELECT "*" Expr1, "<Show All>" AS
Expr2 FROM USys_dbo_DispatchStatusCodes;
Click to expand...
Click to expand...
Click to expand...
 
D

David W. Fenton

Perhaps just the AS before Expr1

SELECT USys_dbo_DispatchStatusCodes.StatusCode,
USys_dbo_DispatchStatusCodes.StatusCode AS [Dispatch Status]
FROM USys_dbo_DispatchStatusCodes
UNION SELECT "*" AS Expr1
, "<Show All>" AS Expr2
FROM USys_dbo_DispatchStatusCodes;
Click to expand...

If your combo box is not displaying column headings, there's no gain
at all in even bothering to name the columns in the second SELECT.

SELECT USys_dbo_DispatchStatusCodes.StatusCode,
USys_dbo_DispatchStatusCodes.StatusCode
FROM USys_dbo_DispatchStatusCodes
UNION SELECT "*", "<Show All>"
FROM USys_dbo_DispatchStatusCodes;

....is going to populate the combo box just as successfully as
assigning column names that are never going to be seen anywhere.
And if you need the column headings, you only have to define them in
the FIRST of the unioned SELECT statements.

Also, you're implicitly depending on the UNION, which eliminates
duplicates, to sort your resultset. If your first field were
something other than "*", which sorts before all text values, you'd
need to define a sort order, or you'd need to use UNION ALL and
DISTINCT:

SELECT DISTINCT "*", "<Show All>"
FROM USys_dbo_DispatchStatusCodes
UNION ALL
SELECT DISTINCT USys_dbo_DispatchStatusCodes.StatusCode,
USys_dbo_DispatchStatusCodes.StatusCode AS [Dispatch Status]
FROM USys_dbo_DispatchStatusCodes;

Alternatively:

SELECT TOP 1 "*", "<Show All>"
FROM USys_dbo_DispatchStatusCodes
UNION ALL
SELECT DISTINCT USys_dbo_DispatchStatusCodes.StatusCode,
USys_dbo_DispatchStatusCodes.StatusCode AS [Dispatch Status]
FROM USys_dbo_DispatchStatusCodes;

I don't know which Jet/ACE will optimize more efficiently, but if
the source table had a lot of records in it, I think I'd tend to use
some other table that has only a few rows.
 
D

David C. Holley

Its just for a combo box that pulls record status codes, so I'm not
concerned in the list bit with efficiency. We're only talking 3 records at
the moment and I can't see anymore than that happening.

David W. Fenton said:
Perhaps just the AS before Expr1

SELECT USys_dbo_DispatchStatusCodes.StatusCode,
USys_dbo_DispatchStatusCodes.StatusCode AS [Dispatch Status]
FROM USys_dbo_DispatchStatusCodes
UNION SELECT "*" AS Expr1
, "<Show All>" AS Expr2
FROM USys_dbo_DispatchStatusCodes;
Click to expand...

If your combo box is not displaying column headings, there's no gain
at all in even bothering to name the columns in the second SELECT.

SELECT USys_dbo_DispatchStatusCodes.StatusCode,
USys_dbo_DispatchStatusCodes.StatusCode
FROM USys_dbo_DispatchStatusCodes
UNION SELECT "*", "<Show All>"
FROM USys_dbo_DispatchStatusCodes;

...is going to populate the combo box just as successfully as
assigning column names that are never going to be seen anywhere.
And if you need the column headings, you only have to define them in
the FIRST of the unioned SELECT statements.

Also, you're implicitly depending on the UNION, which eliminates
duplicates, to sort your resultset. If your first field were
something other than "*", which sorts before all text values, you'd
need to define a sort order, or you'd need to use UNION ALL and
DISTINCT:

SELECT DISTINCT "*", "<Show All>"
FROM USys_dbo_DispatchStatusCodes
UNION ALL
SELECT DISTINCT USys_dbo_DispatchStatusCodes.StatusCode,
USys_dbo_DispatchStatusCodes.StatusCode AS [Dispatch Status]
FROM USys_dbo_DispatchStatusCodes;

Alternatively:

SELECT TOP 1 "*", "<Show All>"
FROM USys_dbo_DispatchStatusCodes
UNION ALL
SELECT DISTINCT USys_dbo_DispatchStatusCodes.StatusCode,
USys_dbo_DispatchStatusCodes.StatusCode AS [Dispatch Status]
FROM USys_dbo_DispatchStatusCodes;

I don't know which Jet/ACE will optimize more efficiently, but if
the source table had a lot of records in it, I think I'd tend to use
some other table that has only a few rows.
Click to expand...
 
D

David W. Fenton

Its just for a combo box that pulls record status codes, so I'm
not concerned in the list bit with efficiency. We're only talking
3 records at the moment and I can't see anymore than that
happening.
Click to expand...

I wasn't necessarily posting for your situation alone, but to cover
some of the issues involved with this kind of rowsource. Even in
your case, I see no reason whatsoever to define column names in both
SELECT statements, and the only reason to do it in the first SELECT
is if you're displaying column headers in your combo box. Otherwise,
you don't need to bother aliasing the fields at all.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Field is being truncated after simple query is run, even though nogrouping is taking place 1
Using a SELECT query as the rowsoruce for another SELECT 2
Update query. Within the same table: populating field 2 from field 6
Filter specific records in subform 24
Data type for nulls in UNION query 7
Union Query question 2
Assigning trucks to deliver loads 9
Access SQL question 1

Top