Re: Combining records from two queries

Thanks John.

I've never used a Union query before. I've found a Microsoft help page which
says the following but I can't understand how to use it in a query. If I
understand this, I should be able to take the results of my two queries and
'stack' the two sets of records one on top of the other, yes? My two queries
are called qrySubjectMentors and qryProfessionalMentors. I've tried entering
the following as a new queries SQL, but it's obviously wrong and I get error
messages. Can you help me a bit more please? Thanks, JohnB

My (pathetic) attempt at SQL:

qrySubjectMentors UNION ALL

SELECT *

FROM qryProfessionalMentors

Help site text:

"Creates a union query , which combines the results of two or more
independent queries or tables.

Syntax
[TABLE] query1 UNION [ALL] [TABLE] query2 [UNION [ALL] [TABLE] queryn [ ... ]]

The UNION operation has these parts:

Part Description
query1-n A SELECT statement , the name of a stored query, or the name of a
stored table preceded by the TABLE keyword.


Remarks
You can merge the results of two or more queries, tables, and SELECT
statements, in any combination, in a single UNION operation. The following
example merges an existing table named New Accounts and a SELECT statement:

TABLE [New Accounts] UNION ALL

SELECT *

FROM Customers

WHERE OrderAmount > 1000;

By default, no duplicate records are returned when you use a UNION
operation; however, you can include the ALL predicate to ensure that all
records are returned. This also makes the query run faster.

All queries in a UNION operation must request the same number of fields;
however, the fields do not have to be of the same size or data type .

Use aliases only in the first SELECT statement because they are ignored in
any others. In the ORDER BY clause, refer to fields by what they are called
in the first SELECT statement."



"John Vinson" wrote:

> On Tue, 24 Jan 2006 09:30:05 -0800, "JohnB"
> <JohnB@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
>
> >Any ideas on this please? I can supply SQL for the two seperate queries I've
> >produced, if it will help. The way I envisage this is that I want to be able
> >to take the first queries list of records and stack them on top of the second
> >queries list.
>
> A UNION query does precisely this. See the online help for UNION, and
> post back if it's not clear how to use it in your case.
>
> John W. Vinson[MVP]
>
.

Relevant Pages

  • RE: Argument Error in Union Query
    ... The only other thing I didn't mention was the union queries that are in all ... "KARL DEWEY" wrote: ... My other queries were working fine, then why i try to run the union query ...
    (microsoft.public.access.queries)
  • Re: Help! Union Query has started crashing!
    ... Does it work if you use UNION ALL instead of UNION? ... queries, it is probably too big a task to debug this for you here. ... basically you need to track down where the data types could be ... I can open each of the Queries the Union Query draws on separatley ...
    (microsoft.public.access.queries)
  • RE: Union Query
    ... You can use the Formatfunction within each of the select queries to return ... "Brook" wrote: ... well it sounds like you want the union query to pull together the fields ...
    (microsoft.public.access.queries)
  • Re: creteria macros
    ... Having written the Union Query, if you have control over the Tables, I suggest basing a Make-Table Query on the Union Query and creating a Table containing all the records from the separate Tables. ... Class1_ID STATUSFIELD DATEFIELD ... So let's enter the new creteria once for Date and Status field and have it forwarded to each of the queries for CLASS1 through to CLASS8. ...
    (microsoft.public.access.queries)
  • Re: Union Query
    ... I definitely understand you comment about normalization. ... What happens with the "union" table is that it get's linked to ... here's the essentials of how to build a Union query. ... Each SELECT subquery may have other clauses (eg. ...
    (microsoft.public.access.queries)
Loading