I'm new to the community but have referenced it many times in the past. I have an issue I'm trying to overcome in Access, specifically with a SORT BY issue in SQL.
Long story short, I need to create a report based on the results of several different queries. I used a Union query to skirt the "Query is too complex" issue. The results of the query aren't in the order I'd like them, though.
Since this UNION query is not based on one specific table, rather the results of many queries, I'm not able to sort by a specific column header.
I want to sort the results by the way they are written in the SQL statement. Can anyone provide some insight to how to do this? I've attempted several different ways but always end up with an error message. Here's the code, and any help is greatly appreciated.
UNION SELECT [Aqua-Reslin_Total].Expr1
UNION SELECT [Aqua_Zenivex_Total].Expr1
UNION SELECT [Aqualuer_20-20_Total].Expr1
UNION SELECT [Avalon_Total].Expr1
UNION SELECT [BVA_13_Total].Expr1
UNION SELECT [Deltagard_Total].Expr1
UNION SELECT [Envion_Total].Expr1
UNION SELECT [Scourge_18-54_Total].Expr1
UNION SELECT [Zenivex_E20_Total].Expr1
union all instead of
union, so if you are using
union to remove duplicates, there would be more work to do after this.
select Expr1 from ( select [Aqua-Anvil_Total].Expr1, 0 as sort from [Aqua-Anvil_Total] union all select [Aqua-Reslin_Total].Expr1, 1 as sort from [Aqua-Reslin_Total] union all select [Aqua_Zenivex_Total].Expr1, 2 as sort from [Aqua_Zenivex_Total] union all select [Aqualuer_20-20_Total].Expr1, 3 as sort from [Aqualuer_20-20_Total] union all select [Avalon_Total].Expr1, 4 as sort from [Avalon_Total] union all select [bva_13_Total].Expr1, 5 as sort from [bva_13_Total] union all select [Deltagard_Total].Expr1, 6 as sort from [Deltagard_Total] union all select [Envion_Total].Expr1, 7 as sort from [Envion_Total] union all select [Scourge_18-54_Total].Expr1, 8 as sort from [Scourge_18-54_Total] union all select [Zenivex_E20_Total].Expr1, 9 as sort from [Zenivex_E20_Total] ) as u order by u.sort