Syntax for Left Join with query aliases in Access

This question was helpful in showing how to create Joins.
http://www.experts-exchange.com/Databases/MS_Access/Q_20950529.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

Open in new window

LVL 2
billb1057Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

AaronroachCommented:
why dont you just use access's query builder it would make this so simple, drag all the tables on the the workspace, drag the connections you want to make (field to field), then drag the fields you want to view in to the field list below. and if you need to do groupbys or counts, hit the equation button and change to which ever you require.
0
datAdrenalineCommented:
Sorry to be so quick ... but I *think* this will work out for you ... its AIR CODE and done quickly ... so you may have to tweak a bit ...

SELECT a.OperName, b.CountS, c.CountD, d.CountN
FROM
    ((Operations a
    LEFT JOIN (SELECT OperName, count(Category) As CountS
               FROM Surveys
               WHERE Category = "Satisfied"
               GROUP BY OperName) b
    ON a.OperName = b.OperName)
    LEFT JOIN (SELECT OperName, count(Category) As CountS
               FROM Surveys
               WHERE Category = "Dissatisfied"
               GROUP BY OperName) c
    ON a.OperName = c.OperName)
    LEFT JOIN (SELECT OperName, count(Category) As CountN
               FROM Surveys
               GROUP BY OperName) d
    ON a.OperName = d.OperName
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
billb1057Author Commented:
datAdrenaline -- that does look very good.  I'll give it a try tomorrow morning.  I was wondering how to structure it and that seems to fit very well -- I'll give it a shot and let you know.  Thanks.
0
billb1057Author Commented:
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.
0
billb1057Author Commented:
That worked perfectly -- thanks for a very quick and accurate reply.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.