Re: Union sort

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

You can treat the UNION query as though it were a table, and SELECT and
ORDER BY
You will have a bit of work with field names, alas.

FWIW this worked:

SELECT pets AS [pets_], name1 as [name1_]
FROM
(SELECT Pets, name1 FROM Table1)
UNION
(SELECT location AS Pets, Name1 FROM Table1)
ORDER BY [Pets_];


"rachael" <rachael@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:802D44DA-600E-4E63-B9A3-774CECE9AFA7@xxxxxxxxxxxxxxxx
I have a union query that is combining the results of several other
queries.
I have a specific sort order that I need to maintain - this sort order is
built into the individual queries, but not the union query that joins them
together. The problem is there is no easy way to use ORDER BY on the union
results to get the order I need. What I need at the end is an order like:

Organization A - [blank field] - [blank field] - Geography A
Organization A - [blank field] - [blank field] - Geography B
Organization A - [blank field] - [blank field] - Geography C
--------------------------------------------------------------------- [end
of 1st query]
Organization O - [blank field] - [blank field] - [blank field]
Organization O - [SubOrg A] - [blank field] - [blank field]
Organization O - [SubOrg A] - [Group A] - [blank field]
Organization O - [SubOrg A] - [Group B] - [blank field]
Organization O - [SubOrg A] - [Group C] - [blank field]
Organization O - [SubOrg B] - [blank field] - [blank field]
Organization O - [SubOrg C] - [blank field] - [blank field]
--------------------------------------------------------------------- [end
of 2nd query]
Organization M - [blank field] - [blank field] - [blank field]
Organization M - [SubOrg A] - [blank field] - [blank field]
Organization M - [SubOrg B] - [blank field] - [blank field]
Organization M - [SubOrg C] - [blank field] - [blank field]
--------------------------------------------------------------------- [end
of 3rd query]
Organization C - [blank field] - [blank field] - [blank field]
Organization D - [blank field] - [blank field] - [blank field]
Organization E - [blank field] - [blank field] - [blank field]
--------------------------------------------------------------------- [end
of 4th query]
etc...

I can't really create a static table (or don't know how) that lists the
proper sort order for these results, which I could then link to the union
results to sort after the fact. The organizations, etc. in this data will
change over time and it would be difficult to maintain a static table. Any
advice?

Thanks
rachael




.

Relevant Pages

  • Re: Transpose Rows to Columns in Access Query
    ... survey as an interim tool to gather succession data before we implement ERP. ... This was my first time writing a code for a union query and I believe this ... If it's JUST as you describe a Normalizing Union query will do the job. ... matter) temporary table, which I'll call tblTemp. ...
    (microsoft.public.access.queries)
  • RE: Union sort
    ... The UNION QUERY loses the desired sort order maintained by its individual ... "rachael" wrote: ...
    (microsoft.public.access.queries)
  • RE: Union sort
    ... the basis for a SELECT query, but the sorting is still a problem. ... If you still need me to post the UNION query, ... The UNION QUERY loses the desired sort order maintained by its individual ... "rachael" wrote: ...
    (microsoft.public.access.queries)
  • Re: Query Question
    ... I suggested a UNION query using the function as the solution. ... Public Function fGetSection(StrIn, iSection as Integer) ... fGetSection = Null ...
    (microsoft.public.access.queries)
  • Re: Need to Normalize (Repost)
    ... I have never built a Union Query. ... I want to normalize the other tables when I run the append query. ... A "Normalizing Union Query" is the ticket here. ... WHERE Recommendation1 IS NOT NULL OR Observation1 IS NOT NULL ...
    (microsoft.public.access.modulesdaovba)