Combining 2 queries

union

I would like to combine 2 queries in MS Query.

Query 1:

 Select
      c.Ship_Name,
      a.Voy, 
      a.Start_Pos,
      a.End_Pos,
      a.Comments_Pos
    From Table1 a, Table2 c
    Where a.Ship_Code = c.Ship_Code
      And a.Port_Type = 'W'
    Order by c.Ship_Name, a.Voy, a.Start_Pos

Query 2:

SELECT 
  c.Ship_Name,
  a.Voy,
  b.Start_Act,
  b.End_Act,
  b.Comments_Act,    
  b.Activity
From Table1 a, Table2 c, Table3 b
Where a.Ship_Code = c.Ship_Code
  And a.Key = b.Key 
  And b.Offh = 'W'
Order by c.Ship_Name, a.Voy, b.Start_Act

Output should be something like:

Ship_Name, Voy, Start, End, Activity, Comments.

I think I have to create a new table, filling with data from the 2 queries.
But how?

Best Answer

It sounds like you goal can be met using a UNION. UNION combines two select statements into a single resultset. The order by is applied to the unioned query not the individual select statements. You'll need to order the results after the union is applied.

See: http://technet.microsoft.com/en-us/library/ms180026.aspx

Here is an example using your queries.

Select
      c.Ship_Name,
      a.Voy, 
      a.Start_Pos AS [Start],
      a.End_Pos AS [End],
      '' AS Activity,
      a.Comments_Pos AS Comments
From Table1 a, Table2 c
Where a.Ship_Code = c.Ship_Code
      And a.Port_Type = 'W'

UNION

SELECT 
  c.Ship_Name,
  a.Voy,
  b.Start_Act AS [Start],
  b.End_Act AS [End],
  b.Activity AS Activity,
  b.Comments_Act AS Comments
From Table1 a, Table2 c, Table3 b
Where a.Ship_Code = c.Ship_Code
  And a.Key = b.Key 
  And b.Offh = 'W'
Related Question