Solved

Syntax for Left Join with query aliases in Access

Posted on 2009-07-13
5
2,109 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

920 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now