Query in a query? (MS Access)

Question

I have a Tasks table for example:

TaskTitle DueDate Person Manager
Report     3/28/15  John   Dave
Inspection 4/10/15  Brian  Shane

and a Contacts Table:

ID   Contact  Email                Manager
1    John     [email protected]     False
2    Dave     [email protected]     True
3    Brian    [email protected]    False
4    Shane    [email protected]    True

And what I want to do is write a query like this:

PEmail            MEmail             TaskTitle 
[email protected]  [email protected]   Report
[email protected] [email protected]  Inspection

I can can get the query to select the PEmail or the MEmail, but not both together?

SELECT [Contacts].[Email], [Tasks].[TaskTitle]
FROM tasks
LEFT JOIN [Contacts] 
ON [Tasks].[Person] = [Contacts].[Contact]

and

SELECT [Contacts].[Email], [Tasks].[TaskTitle]
FROM tasks
LEFT JOIN [Contacts] 
ON [tasks].[Manager] = [Contacts].[Contact]

Is there a specific thing this is called? A multiple join or multiple select? I've been really stuck on this.

You can make joins over more than two tables in a query. You actually can join a single table more than once. — arkascha, Mar 19 '15 at 19:39

score 0 · Accepted Answer · answered Mar 19 '15 at 19:35

0

SELECT [ManagerContacts].[Email] MEmail, 
       [PersonContacts].[Email] PEmail, 
       [Tasks].[TaskTitle]
FROM tasks
    LEFT JOIN [Contacts] ManagerContacts
ON [tasks].[Manager] = [ManagerContacts].[Contact]
    LEFT JOIN [Contacts] PersonContacts
ON [tasks].[Person] = [PersonContacts].[Contact]

answered Mar 19 '15 at 19:35

stuartd

70,509
14
132
163

It wasn't exactly it, but I understand what was going on and got mine to work too! Thanks! – arsarc Mar 19 '15 at 20:03

Query in a query? (MS Access)

1 Answers1