Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Syntax for Left Join with query aliases in Access

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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

828 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