Using a JOIN on Two Different SQL statements

  • Thread starter Thread starter R Tanner
  • Start date Start date
R

R Tanner

Hi,

I have the following code which would run beautifully in SSMS but I
can't get it to run in Access 2007. What do I need to change to make
it run?


SELECT *

FROM

(
SELECT (Employee_List.LName & ", " & Employee_List.FName) AS
["Employee"],
SUM(Transactions.Amount) as "Pips Received"
FROM (Employee_List INNER JOIN Transactions ON Transactions.ReceiverId
= Employee_List.ID)
GROUP BY (Employee_List.LName & ", " & Employee_List.FName)

) t1

JOIN

(

SELECT (Employee_List.LName & ", " & Employee_List.FName) AS
["Employee"],
SUM(Expenses.Amount) as "Expenses"
FROM (Employee_List INNER JOIN Expenses ON Expenses.EmployeeID =
Employee_List.ID)
GROUP BY (Employee_List.LName & ", " & Employee_List.FName)) t2

ON t1.Employee = t2.Employee
 
You need to use a specific type of join depending on what
you are trying to do.

There are LERT JOIN, RIGHT JOIN and INNER JOIN. I will
guess that you want to use INNER JOIN
 
You need to use a specific type of join depending on what
you are trying to do.

There are LERT JOIN, RIGHT JOIN and INNER JOIN.  I will
guess that you want to use INNER JOIN
--
Marsh
MVP [MS Access]



R said:
I have the following code which would run beautifully in SSMS but I
can't get it to run in Access 2007.  What do I need to change to make
it run?
Click to expand...
SELECT *
FROM
Click to expand...

(
SELECT (Employee_List.LName & ", " & Employee_List.FName) AS
["Employee"],
            SUM(Transactions.Amount) as "Pips Received"
FROM (Employee_List INNER JOIN Transactions ON Transactions.ReceiverId
= Employee_List.ID)
GROUP BY (Employee_List.LName & ", " & Employee_List.FName)
Click to expand...
)  t1
JOIN
Click to expand...
(
Click to expand...

SELECT (Employee_List.LName & ", " & Employee_List.FName) AS
["Employee"],
            SUM(Expenses.Amount) as "Expenses"
FROM (Employee_List INNER JOIN Expenses ON Expenses.EmployeeID =
Employee_List.ID)
GROUP BY (Employee_List.LName & ", " & Employee_List.FName)) t2
Click to expand...
ON t1.Employee = t2.Employee- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

ahh...perfect. You actually have to specify that in Access huh? In
SSMS it is the default join type so you don't have to. So now it's
asking me for t1.Employee. Any thoughts?
 
R said:
ahh...perfect. You actually have to specify that in Access huh? In
SSMS it is the default join type so you don't have to. So now it's
asking me for t1.Employee.
Click to expand...


Sheesh, I missed that earlier. When you enclose a name
in [ ], all the characters inside the [ ] are part of the
name.

Remove the quotes in ["Employee"]
 
Back
Top