Union Query Sort Problem

[LadyDi]

I have a database that tracks service calls on my company's products. I need to export the problem field and the solution field veritcally to Excel instead of horizontally (how they are currently stored in the database). I thought I could use a Union Query to accomplish this, but it isn't working for me. It does list the fields vertically, but it is sorting by fields that I don't want sorted. In each of the subqueries, I have a sort for the completion date and completion time. Then it sorts the events in alphabetical order even though I don't want it to. How can I stop it from doing that? I want the results listed in the order that the subqueries are listed (the first subquery is the type of call:first line or second line, the second subquery is the problem, and the third subquery is the solution)

I want to display the type of call first, then what the problem was, and then what the solution was. However, if the problem was a Jam and the solution was just to Clear it, my query is showing Clear first and then Jam. If the type for this call was first line, my results would show Clear, First Line, Jam. I want it to show First Line, Jam, Clear. Do you have any suggestions?

 

[JHB]

I think the only way to solve this problem is to use VBA.
Create a form, put a button on it an place the code in the button's _Click event.

  Dim dbs As Database, rst As Recordset, rstInsert As Recordset, tblLoop
  
  Set dbs = CurrentDb
  For Each tblLoop In dbs.TableDefs
    If tblLoop.Name = "Veritcally" Then
      dbs.TableDefs.Delete ("Veritcally")
      Exit For
    End If
  Next tblLoop
  
  dbs.Execute ("CREATE TABLE Veritcally (Veritcally TEXT)")
  Set rst = dbs.OpenRecordset("SELECT Problem, Solution FROM YourTableName")
  If Not rst.EOF Then
    Set rstInsert = dbs.OpenRecordset("Veritcally")
    Do
      With rstInsert
        .AddNew
        ![Veritcally] = rst![Problem]
        .Update
        .AddNew
        ![Veritcally] = rst![Solution]
        .Update
      End With
      rst.MoveNext
    Loop Until rst.EOF
  End If

 

[pbaldy]

For starters, a UNION query only uses the ORDER BY clause in the last query. You can add a field to each of the pieces:

SELECT..., 1 As SortField

and sort on that field at the end.