Union Query Ruins Numbered Order

[Reese]

[Solved] Union Query Ruins Numbered Order

I have a union query with 2 fields: Order and Row_Heading_Full.

SELECT DummyClientType.Order, DummyClientType.Client_Type AS Row_Heading_Full
FROM DummyClientType
UNION SELECT ("25") AS [Order], ("Totals") AS Row_Heading_Full
FROM DummyClientType;

The purpose is to pull the Order and Row_Heading_Full fields from a table and add another entry that with "25" as the Order and "Totals" as the Row_Heading_Full at the end of the list.

I want it to be ordered sequentially based on the Order field. But instead it is ordered like this: 1, 10, 11, 12... 20, 21, 22... 3, 4, 5... In other words it is ordering only by the first digit and not by the number as a whole.

When I remove the union aspect it is ordered properly:

SELECT DummyClientType.Order, DummyClientType.Client_Type AS Row_Heading_Full
FROM DummyClientType;

But that defeats the purpose because I am not adding a final entry of "25" and "Totals".

Does anyone have any ideas?

 

[Reese]

In case anyone is wondering why go through with this: The table is a "dummy" table, used as a record source for several combo boxes and crosstab queries (forcing the query to display all the Row_Heading_Full fields, even if there are no values associated with them).

The Order field ensures that the entries are ordered in a specific way. For one crosstab query I have to add "Totals" to the end of the list, but it is not applied to any other combo box or crosstab query. I thought that using a union query would let me do that, but I have encountered the above problem.

I'd appreciate any help. Thanks!

 

[plog]

In other words it is ordering only by the first digit and not by the number as a whole.

You are using the wrong terms. [Order] is not a number in that query, its text. And because of that it is ordering it like text comparing characters.

What you need to do is make [Order] a number so that it will treat (and order) it like a number. Remove the quotes around 25.

 

[Reese]

What you need to do is make [Order] a number so that it will treat (and order) it like a number. Remove the quotes around 25.

I tried that before but it didn't work so I didn't bother saving the change. I have also tried removing/adding brackets and parenthesis, nothing so far has worked.

 

[plog]

Is DummyClientType.Order a text field?

 

[MarkK]

Don't enclose your "25" in quotes. Quotes mean the item is a string, not a number . . .

SELECT Order, Client_Type AS RowHeading
FROM DummyClientType
UNION 
SELECT [COLOR="Blue"]25[/COLOR], "Totals"
FROM DummyClientType;

. . . and I don't think you need the field names in the second SELECT clause. They are determined by the names in the first SELECT clause.

 

[Galaxiom]

. . . and I don't think you need the field names in the second SELECT clause. They are determined by the names in the first SELECT clause.

Correct.

Nor is the second FROM clause required since none of the fields comes from the table.

 

[Reese]

Is DummyClientType.Order a text field?

*Smacks head* I thought it was a number field but I was apparently wrong. Changing it to a number field solved the issue right there.

. . . and I don't think you need the field names in the second SELECT clause. They are determined by the names in the first SELECT clause.

Apparently you are right. The query works either way, so it doesn't impact it but it does simplify things.

Nor is the second FROM clause required since none of the fields comes from the table.

While you're right that none of the info comes from a table in the second part of the query, Access apparently doesn't like it if I remove the FROM clause. *shrug* Thanks for the suggestion, though.

 

[namliam]

Just want to add, any select statement without an EXPLICIT order by is never guaranteed to return any sort of order....

If you need a certain order, make sure to add "ORDER BY ..." to your select statement !

 

[MarkK]

And, it's easy enough to sub-query the union query, and then assert an explicit sort, like . . .

SELECT * FROM 
   (
   SELECT Order, Client_Type AS RowHeading
   FROM DummyClientType
   UNION 
   SELECT 25, "Totals"
   FROM DummyClientType
   )
ORDER BY Order;

 

[namliam]

Not a 100% in access, and too lazy to test it right now...
SELECT Order, Client_Type AS RowHeading
FROM DummyClientType
UNION
SELECT 25, "Totals"
FROM DummyClientType
ORDER BY Order;

Should work too, atleast it does in most databases

 

[MarkK]

SELECT Order, Client_Type AS RowHeading
FROM DummyClientType
UNION
SELECT 25, "Totals"
FROM DummyClientType
ORDER BY Order;

In this example I believe the ORDER BY clause applies only to the second SELECT.

 

[Reese]

Thanks everyone for the continued suggestions and responses. Everything is working now so I'm all set. Thanks again!

 

[spikepl]

SELECT Order, Client_Type AS RowHeading
FROM DummyClientType
UNION
SELECT 25, "Totals"
FROM DummyClientType
ORDER BY 1;

Should work

 

[namliam]

SELECT Order, Client_Type AS RowHeading
FROM DummyClientType
UNION
SELECT 25, "Totals"
FROM DummyClientType
ORDER BY Order;

In this example I believe the ORDER BY clause applies only to the second SELECT.

it shouldnt... like I said atleast not in other databases....

Select...
from
order by
Union
select...

Is actually an invalid statement, you cant order one part and not order the next...
The data retrieved by the union isnt guaranteed to be 1st query/2nd query...
Much like any query wont guarantee any fixed order (unless there is an order by)