about UNION query? (very simple to most of you most likely)

[samul]

very simple i would think to those who would know what to do but having a hard time:
i've addresses. street names and addresses on the streets. so those are the two fields. i want to arrange the whole thing according to how the route should be done. so i've done queries for subsections like A means north, and the way the North should be done, B query, means south area, and the way the streets should be arranged when if someone were to do area B only. problem: when I do a union query A+B, the result gets rearranged. I want the query union to actually "concatanat e" not rearrange the way the table is displayed. to be honest, i don't even know what field the union is using to actually arrange the table records (VERY IMPORTANT: there are no repeated records)
thanks again for the help

[jimatqsi]

You need to know what the sort order is. Apparently you have not specified a sort order, and you need to do that. The first part of the sort order should be the indicator for the area, so all of north sort together, all of south sort together, etc....

I think you need to make another query that uses the union query as input. This query is the one that will do the sorting.

Jim

[Oralloy]

samul,

You have a slight problem, in that a UNION query returns unique records. It has to sort the results in order to remove duplicates.

On the other hand, you can impose an "ORDER BY" clause at the end of your "UNION"s to give you the desired ordering. For example:

SELECT Count(*) AS [CWR Fail],
             0 AS [H&S Fail],
             0 AS [Liability Fail]
  FROM CompanyData 
  WHERE ([CWR Received]=No) 
UNION
SELECT 0 AS [CWR Fail],
             COUNT(*) AS [H&S Fail],
             0 AS [Liability Fail]
  FROM CompanyData 
  WHERE ([H&S Approved]=No) 
UNION SELECT 0 AS [CWR Fail],
             0 AS [H&S Fail],
             COUNT(*) AS [Liability Fail]
  FROM CompanyData 
  WHERE ([Liability Approved]=No)
ORDER BY [Liability Fail] DESC;