Solved

Syntax for Left Join with query aliases in Access

Posted on 2009-07-13
5
2,115 Views
Last Modified: 2012-06-27
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

0
Comment
Question by:billb1057
  • 3
5 Comments
 
LVL 5

Assisted Solution

by:Aaronroach
Aaronroach earned 20 total points
ID: 24844216
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
 
LVL 11

Accepted Solution

by:
datAdrenaline earned 480 total points
ID: 24844303
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
 
LVL 2

Author Comment

by:billb1057
ID: 24845528
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
 
LVL 2

Author Comment

by:billb1057
ID: 24845595
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
 
LVL 2

Author Closing Comment

by:billb1057
ID: 31603034
That worked perfectly -- thanks for a very quick and accurate reply.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

740 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question