469,125 Members | 1,780 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,125 developers. It's quick & easy.

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

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 "concatanate" 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
Jan 3 '11 #1
2 1349
1,258 Expert 1GB
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.

Jan 3 '11 #2
983 Expert 512MB

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:
Expand|Select|Wrap|Line Numbers
  1. SELECT Count(*) AS [CWR Fail],
  2.              0 AS [H&S Fail],
  3.              0 AS [Liability Fail]
  4.   FROM CompanyData 
  5.   WHERE ([CWR Received]=No) 
  6. UNION
  7. SELECT 0 AS [CWR Fail],
  8.              COUNT(*) AS [H&S Fail],
  9.              0 AS [Liability Fail]
  10.   FROM CompanyData 
  11.   WHERE ([H&S Approved]=No) 
  12. UNION SELECT 0 AS [CWR Fail],
  13.              0 AS [H&S Fail],
  14.              COUNT(*) AS [Liability Fail]
  15.   FROM CompanyData 
  16.   WHERE ([Liability Approved]=No)
  17. ORDER BY [Liability Fail] DESC;
Jan 3 '11 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by Sim Zacks | last post: by
22 posts views Thread by tricard | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by Mortomer39 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.