Union Query, forcing order of rows

[Guest]

Hiya,

I have a Union Query, where the last select of the union is being used to
calculate a "total" for weach column. However, Access seems to automatically
sort my rows alphabetically, so the "total" row doesnt appear at the end.
Anyone know how to force it to appear at the end?

Thanks very much for your help
Barbara

SELECT Type, Count(Type) AS [Count for that type],
Count(Vendors) AS [No. Vendor],
Count(IIf(Vendors Like '*IBM*',Null,1)) AS [No Vendors not IBM],
Sum(Cost) AS [Sum Cost]
FROM [myTable]
GROUP BY Type

UNION SELECT "Type All" AS Expr1, Count(Type) AS [Count for that type],
Count(Vendors) AS [No. Vendor],
Count(IIf(Vendors Like '*IBM*',Null,1)) AS [No Vendors not IBM],
Sum(Cost) AS [Sum Cost]
FROM [myTable]
GROUP BY "Type All"

 

[John Spencer (MVP)]

Answered in your earlier posting.

To summarize:
Add a special sort order field to both the queries and then use that as your
sort item

SELECT "Details" as LineIdentifier, Type, ...
....
UNION

SELECT "Total", "Type All", ...
....
ORDER BY LineIdentifier, ...