Re: Combining records from two queries

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: How do I verify a valid sql statement programatically ??
    ... recordset, ASP, and javascript. ... the ASP session to minimize generating the query from the DB with different ... parts of the system that generated record lists would use the same list ... Many places in our system generate dynamic queries based on various ...
    (microsoft.public.sqlserver.programming)
  • 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
    ... This produces a set of records, so I now will see if they're what I expect and build up the SELECT query as you advise. ... combining UNION and SELECT queries to best effect. ... 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: totals
    ... >> Piggybacking on John Spencer's reply, I would define a set of Queries to ... >> This Query lists any time slot that is used on a given date. ... >> This one lists how many time slots are scheduled in at least one room on ...
    (microsoft.public.access.queries)
  • Re: Multiple Record Query
    ... The query you have can be improved slightly with this form: ... It would also definitely be a "single user" solution, ... Two users creating two distinct lists simultaneously ... > but this will not work because I need to save these queries. ...
    (microsoft.public.access.queries)