If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Combining records from two queries
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 |
#2
|
|||
|
|||
Combining records from two queries
On Tue, 24 Jan 2006 09:30:05 -0800, "JohnB"
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] |
#3
|
|||
|
|||
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" 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] |
#4
|
|||
|
|||
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 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" 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 |
#5
|
|||
|
|||
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 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" 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 |
#6
|
|||
|
|||
Combining records from two queries
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 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 "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 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" 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 |
#7
|
|||
|
|||
Combining records from two queries
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; "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 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" 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 |
#8
|
|||
|
|||
Combining records from two queries
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 "Vincent Johns" wrote: 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 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 "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 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" 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 |
#9
|
|||
|
|||
Combining records from two queries
Looks good (but leave a space before the "*").
I still think you might not want the "ALL". -- Vincent Johns Please feel free to quote anything I say here. JohnB wrote: 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 "Vincent Johns" wrote: 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 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 "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 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" 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 |
#10
|
|||
|
|||
Combining records from two queries
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 "Vincent Johns" wrote: Looks good (but leave a space before the "*"). I still think you might not want the "ALL". -- Vincent Johns Please feel free to quote anything I say here. JohnB wrote: 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 "Vincent Johns" wrote: 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 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 "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 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" 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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Combining Parameter Queries with Results in a Form | Staci | Running & Setting Up Queries | 3 | October 11th, 2004 05:43 PM |
Problem combining SELECT queries | Laura McKittrick | Running & Setting Up Queries | 2 | September 29th, 2004 11:46 PM |
combining tables and linking records with queries | Dale Peart | Running & Setting Up Queries | 2 | July 9th, 2004 11:36 PM |
Combining Records | Ross Erickson | Running & Setting Up Queries | 1 | June 15th, 2004 12:45 AM |
How to get only records that are in results of two seperate queries | Terri Jackman | Running & Setting Up Queries | 1 | June 9th, 2004 03:10 PM |