billb1057
asked on
Syntax for Left Join with query aliases in Access
This question was helpful in showing how to create Joins.
https://www.experts-exchange.com/questions/20950529/JOIN-INNER-JOIN-OUTER-JOIN-RIGHT-JOIN-LEFT-JOIN.html
I'm not sure I'm asking this correctly, but how would the syntax work with "alias queries" (I'm not sure if that's the right term)? So, three queries on one table (as a, as b, as c) for example, then with a Left Outer on each one from a reference table.
The goal is to get All of the organizations (in the reference table) and any of the results in the queries by various criteria. This is a survey -- so the reference table = "Operations". The other table is "Surveys".
I tried something like
select a.OperName, b.CountS, c.CountD, d.CountN
From
(select OperName
from Operations) a,
(Select count(Category) As CountS
From Surveys
Where Category = "Satisfied") b,
(Select count(Category) As CountS
From Surveys
Where Category = "Dissatisfied") c,
(Select count(Category) As CountN
From Surveys) d
where a.OperName = b.OperName = c.OperName = d.OperName
But that doesn't work at all -- and it's not a Left Join ON ... b.OperName, etc.
putting that term in at the end doesn't work either.
Thanks in advance for your help.
https://www.experts-exchange.com/questions/20950529/JOIN-INNER-JOIN-OUTER-JOIN-RIGHT-JOIN-LEFT-JOIN.html
I'm not sure I'm asking this correctly, but how would the syntax work with "alias queries" (I'm not sure if that's the right term)? So, three queries on one table (as a, as b, as c) for example, then with a Left Outer on each one from a reference table.
The goal is to get All of the organizations (in the reference table) and any of the results in the queries by various criteria. This is a survey -- so the reference table = "Operations". The other table is "Surveys".
I tried something like
select a.OperName, b.CountS, c.CountD, d.CountN
From
(select OperName
from Operations) a,
(Select count(Category) As CountS
From Surveys
Where Category = "Satisfied") b,
(Select count(Category) As CountS
From Surveys
Where Category = "Dissatisfied") c,
(Select count(Category) As CountN
From Surveys) d
where a.OperName = b.OperName = c.OperName = d.OperName
But that doesn't work at all -- and it's not a Left Join ON ... b.OperName, etc.
putting that term in at the end doesn't work either.
Thanks in advance for your help.
-- Reference table
Select Distinct OperName
From Operations
-- Satisfied Surveys
Select count(Category) As CountS
From Surveys
Where Category = "Satisfied"
-- Dissatisfied Surveys
Select count(Category) As CountS
From Surveys
Where Category = "Dissatisfied"
--- Sample Size
Select count(Category) As CountN
From Surveys
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Aaronroach -- that's definitely a good point - since the end result is the same. But I have to go through this process for 6 organizations and 2 questions. So, that's 4 queries times 12 -- 48 separate queries. By building all the calculations into one query, it cuts down on all of that clutter.
I'm also trying to automate this so I just run a couple of queries and I don't have to change parameters each month (that's kind of a different issue).
I originally started with crosstab queries, but then I had to manually change code every time a new month of data was added.
I guess I'm looking for more elegant solutions which are easier to troubleshoot. The old way has a huge number of queries and it was very hard to troubleshoot problems.
I'm also trying to automate this so I just run a couple of queries and I don't have to change parameters each month (that's kind of a different issue).
I originally started with crosstab queries, but then I had to manually change code every time a new month of data was added.
I guess I'm looking for more elegant solutions which are easier to troubleshoot. The old way has a huge number of queries and it was very hard to troubleshoot problems.
ASKER
That worked perfectly -- thanks for a very quick and accurate reply.
ASKER