[Select] the results of 2 queries

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have two queries that produce lists of "Course Speakers" and "Course
Registrations". I want to combine the lists to produce a list of all
attending my course whether they are an speaker OR paying participant
(registrations)

The tables & important fields i am using are:

[Everyone] = with "Everyone ID" pk, "First name" , "Last name"
[Training Course] = "Course ID" pk (My criteria for the on both select
queries)
[Registrations] = "Reg ID" pk, "Everyone ID" fk, "Course ID" fk
[Speakers] = "Speaker ID" pk, "Everyone ID" fk, "Course ID" fk

The two queries naturally are:
Course_Speakers (which speakers on which course ie.. Everyone, Training
Course, Speakers)
Course_Registrations (the registered participants on which course ie..
Everyone, Training Course, Registrations)

How do i write a query to produce a list of Speakers OR Registrations with
the criteria being the Training Course
 
You need a UNION query e.g.

(SELECT * FROM Course_Speakers) UNION (SELECT * FROM Course_Registrations)

Note that, although the two queries don't need return identical field names,
they do need to return the same number of fields, and in the same positions
in the SELECT list with respect to data types.

Also, you can't do this in query design view, you'll need to type it into
the SQL window.
 
That works great thanks.
I'm surprised that the query deigner can't create some types of query.. i
didn't know that, although it makes sense that it can't do a Union query

Chris

Baz said:
You need a UNION query e.g.

(SELECT * FROM Course_Speakers) UNION (SELECT * FROM Course_Registrations)

Note that, although the two queries don't need return identical field names,
they do need to return the same number of fields, and in the same positions
in the SELECT list with respect to data types.

Also, you can't do this in query design view, you'll need to type it into
the SQL window.

Engels said:
I have two queries that produce lists of "Course Speakers" and "Course
Registrations". I want to combine the lists to produce a list of all
attending my course whether they are an speaker OR paying participant
(registrations)

The tables & important fields i am using are:

[Everyone] = with "Everyone ID" pk, "First name" , "Last name"
[Training Course] = "Course ID" pk (My criteria for the on both select
queries)
[Registrations] = "Reg ID" pk, "Everyone ID" fk, "Course ID" fk
[Speakers] = "Speaker ID" pk, "Everyone ID" fk, "Course ID" fk

The two queries naturally are:
Course_Speakers (which speakers on which course ie.. Everyone, Training
Course, Speakers)
Course_Registrations (the registered participants on which course ie..
Everyone, Training Course, Registrations)

How do i write a query to produce a list of Speakers OR Registrations with
the criteria being the Training Course
Click to expand...
Click to expand...
 
Back
Top