Re: Combining records from two queries

Thanks for this Vincent. I appreaciate your help plus the good advice about
combining UNION and SELECT queries to best effect. I will certainly do that
when I get this to work. But I'm still having problems - mySQL is now:

SELECT *
qrySubjectMentors UNION ALL
SELECT *
FROM qryProfessionalMentors

I've tried all sorts of combinations with SELECT and * and I'm obviously not
getting this 'cos I get every conceivable error message. Also, when I start a
new query in design view ithe SQL says "SELECT;". Should the ";" be in there
somewhere? Using my query names, can you say exactly what my SQL should be
please? Then I'll cut and paste it in and see what happens.Thanks, JohnB

"Vincent Johns" wrote:

> You needed another "SELECT *" at the beginning.
>
> But maybe you don't need the "*"; some of the fields may not be useful
> to you. Just list the ones that you need, such as [Name] and [Address];
> maybe you don't care about the value of [Grange School Student]. The
> list in each case has to have the same number & types of fields (for
> example, if the first Query begins with a Date/Time field, the second
> one should do so as well).
>
> Also, you may want to omit the "ALL", as that might give you unwanted
> duplicate records. You want to send your letter only once to each
> person, I assume.
>
> Having defined your Union Query, you can use that as the basis for a
> Select Query that could sort or filter the records. Or your Union Query
> could list only the key values of the people on your list, and the
> Select Query could then match these with their names, addresses, phone
> numbers, etc., from other Tables; you could use the Select Query as the
> data source for your Mail Merge.
>
> The reason I suggest using a separate Select Query is that you could
> edit it in Query Design View, unlike a Union Query. So, I like to keep
> my Union Queries short and simple and do the rest of the work elsewhere.
>
> -- Vincent Johns <vjohns@xxxxxxxxxxxxxxxxxx>
> Please feel free to quote anything I say here.
>
> JohnB wrote:
>
> > 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]
> >>
>
> > JohnB wrote:
> >> Hi.
> >>
> >> I fear that this is going to have an incredibly easy answer but I can't see
> >> it. Sorry if it's long winded.
> >>
> >> I have a a number of related tables: tblStudents, tblMentors, tblSchools and
> >> tbl Placements. tblPlacements acts as a cross connection table. A Placement
> >> subform allows Placement records to be created for each Student and then
> >> combos are used in each Placement subform record to allocate a School and a
> >> Mentor.
> >>
> >> tblMentors comprises Subject Mentors and Professional Mentors and the
> >> Placement subform has a combo for each - the Subject Mentor combo puts the
> >> chosen MentorID in the SubjectMentorID field and the Professional Mentor
> >> combo puts the chosen MentorID in the ProfessionalMentorID field.
> >>
> >> The problem I have is in producing a query that shows a listing of all
> >> Subject and Professional Mentors that are allocated to placements. I can
> >> produce a query that shows all Subject Mentors (by linking
> >> tblMentors.MentorID to tblPlacements.SubjectMentorID) and another that shows
> >> all Professional Mentors (by linking tblMentors.MentorID to
> >> tblPlacements.ProfessionalMentorID) but I can't produce a query that lists
> >> them all in terms of seperate records. i.e.
> >>
> >> Joe Bloggs English Mentor Grange School Student: Sarah Jones
> >> Fred Jones Professional Mentor Grange School Student: Sarah Jones
> >>
> >> The reason I want this is to feed a MailMerge. A letter has to go to each
> >> Subject and Professional Mentor.
> >>
> >> 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 bit like using an append query to add records to a table.
> >>
> >> JohnB
> >>
> >>
> >
>
.

Relevant Pages

  • Re: Combining records from two queries
    ... combining UNION and SELECT queries to best effect. ... The list in each case has to have the same number & types of fields (for example, if the first Query begins with a Date/Time field, the second one should do so as well). ... I like to keep my Union Queries short and simple and do the rest of the work elsewhere. ... tblMentors comprises Subject Mentors and Professional Mentors and the Placement subform has a combo for each - the Subject Mentor combo puts the chosen MentorID in the SubjectMentorID field and the Professional Mentor combo puts the chosen MentorID in the ProfessionalMentorID field. ...
    (microsoft.public.access.queries)
  • Re: Combining records from two queries
    ... I saved that Union query and then reopened it and it was changed to the ... > my Union Queries short and simple and do the rest of the work elsewhere. ... >>> Subject and Professional Mentors that are allocated to placements. ...
    (microsoft.public.access.queries)
  • Re: Question about Queries with Subqueries
    ... performance reasons to use UNION ALL instead of UNION. ... i have a query i am working on that draws data from 3 different data ... i have a question about queries like this. ... to try "flattening" my queries by using subqueries. ...
    (microsoft.public.access.queries)
  • Re: Help! Union Query has started crashing!
    ... It's tblSupport on the RLR_SUPPORT_INFOTERRA.mdb database. ... I think I have tracked the problem down to the query ... I also tried a UNION ALL, ... the actual structure of the queries as they have been running fine for weeks. ...
    (microsoft.public.access.queries)
  • Re: Change of field name causes application to crash
    ... this all kind of got me searching through all the underlying queries ... have a six-way union query separated from the query in question by an ... Here is the six-way union query: ... I'm assuming that LookupInventoryCombinedNet is the UNION query you ...
    (microsoft.public.access.modulesdaovba)