Ms-access – Assistance with part of a join

ms-access-2010

I'm a college student and the database i'm working with is purely fictional but part of it requires me to make a query that is a join.

What I have are 3 tables each with part of the data but also needing to use two of those tables as conditions based off the main table. What I mean is I have an employee table, order table and customer table and the only thing any two of them have in common is the ID of either the employee or the customer is part of the order table. Now what i am trying to do is create a join statement that will get certain information from the employee and customer tables and only those that both the employee and the customer are also on the same line in the order table.

How should i make this type of conditional statement? Any example using the same basic scenario will work I can use that to help me build my own query.

This is what i have right now:

SELECT [Customer/Vendor_Info_local].Name_of_customer,
       [Customer/Vendor_Info_local].[Customer/VendorID],
       Employee_Info_local.Employee_Name, 
       Employee_Info_local.EmployeeID
FROM Employee_Info_local 
RIGHT JOIN ([Customer/Vendor_Info_local] 
RIGHT JOIN Order_Information_local 
   ON [Customer/Vendor_Info_local].[Customer/VendorID] = Order_Information_local.[Cusrtomer/VendorID]) 
   ON Employee_Info_local.EmployeeID = Order_Information_local.EmployeeID;

Best Answer

SELECT C.Name_of_customer,
       C.[Customer/VendorID],
       E.Employee_Name, 
       E.EmployeeID 
from Order_Information_local as O
JOIN Employee_Info_local as E
JOIN [Customer/Vendor_Info_local] as C
       ON C.[Customer/VendorID] = O.[Cusrtomer/VendorID]) 
      AND E.EmployeeID = O.EmployeeID;
Related Question