Combining records from two queries

[JohnB]

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

 

[John Vinson]

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]

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

query1 UNION [ALL]

query2 [UNION [ALL]

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."

 

[Vincent Johns]

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 <[email protected]>
Please feel free to quote anything I say here.

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

query1 UNION [ALL]

query2 [UNION [ALL]

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."



:

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]

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

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 <[email protected]>
Please feel free to quote anything I say here.

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

query1 UNION [ALL]

query2 [UNION [ALL]

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."



:

On Tue, 24 Jan 2006 09:30:05 -0800, "JohnB"


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]

 

[Vincent Johns]

Oops -- my mistake. I meant "SELECT * FROM". The "FROM" is important.
Basically, the "UNION" or "UNION ALL" just goes between two "SELECT"
statements.

Also, you may need a semicolon, ";", at the end, though that may be
optional.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

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

:

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 <[email protected]>
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

query1 UNION [ALL]

query2 [UNION [ALL]

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."



:



On Tue, 24 Jan 2006 09:30:05 -0800, "JohnB"



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

 

[JohnB]

Hi again Vincent.

I saved that Union query and then reopened it and it was changed to the
following. But when I run it I get the message "The SELECT statement contains
a reserved word or an argument name that is misspelt or missing, or the
punctuation is incorrect". Can you see what's wrong? Thanks, JohnB

SELECT;
qryProfessionalMentors UNION ALL FROM qrySubjectMentors;

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 <[email protected]>
Please feel free to quote anything I say here.

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

query1 UNION [ALL]

query2 [UNION [ALL]

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."



:

On Tue, 24 Jan 2006 09:30:05 -0800, "JohnB"


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]

Sorry Vincent. Our posts are crossing.

I think I now have it. The code is:

SELECT* FROM
qryProfessionalMentors UNION ALL SELECT * FROM qrySubjectMentors;

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 . Many thanks for the help. Will
post on a new thread if I need further help.
Cheers, JohnB

Oops -- my mistake. I meant "SELECT * FROM". The "FROM" is important.
Basically, the "UNION" or "UNION ALL" just goes between two "SELECT"
statements.

Also, you may need a semicolon, ";", at the end, though that may be
optional.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

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

:

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 <[email protected]>
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

query1 UNION [ALL]

query2 [UNION [ALL]

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."



:



On Tue, 24 Jan 2006 09:30:05 -0800, "JohnB"



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

 

[Vincent Johns]

Looks good (but leave a space before the "*").

I still think you might not want the "ALL".

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

Sorry Vincent. Our posts are crossing.

I think I now have it. The code is:

SELECT* FROM
qryProfessionalMentors UNION ALL SELECT * FROM qrySubjectMentors;

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 . Many thanks for the help. Will
post on a new thread if I need further help.
Cheers, JohnB

:

Oops -- my mistake. I meant "SELECT * FROM". The "FROM" is important.
Basically, the "UNION" or "UNION ALL" just goes between two "SELECT"
statements.

Also, you may need a semicolon, ";", at the end, though that may be
optional.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.


JohnB wrote:

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

:



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 <[email protected]>
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

query1 UNION [ALL]

query2 [UNION [ALL]

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."



:




On Tue, 24 Jan 2006 09:30:05 -0800, "JohnB"




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

 

[JohnB]

Thanks Vincent. All working great now. I've added an ORDER BY statement too.
Many thanks again for the help and to John for the first reply.

Cheers, JohnB

Looks good (but leave a space before the "*").

I still think you might not want the "ALL".

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

Sorry Vincent. Our posts are crossing.

I think I now have it. The code is:

SELECT* FROM
qryProfessionalMentors UNION ALL SELECT * FROM qrySubjectMentors;

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 . Many thanks for the help. Will
post on a new thread if I need further help.
Cheers, JohnB

:

Oops -- my mistake. I meant "SELECT * FROM". The "FROM" is important.
Basically, the "UNION" or "UNION ALL" just goes between two "SELECT"
statements.

Also, you may need a semicolon, ";", at the end, though that may be
optional.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.


JohnB wrote:


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

:



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 <[email protected]>
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

query1 UNION [ALL]

query2 [UNION [ALL]

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."



:




On Tue, 24 Jan 2006 09:30:05 -0800, "JohnB"




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