Solved

How do I create a select statement with an outerjoin on access queries?

Posted on 2008-06-25
3
195 Views
Last Modified: 2013-11-27
I have an asp page that queries an access query.  I now need to join the query results from one query with those from another query in the same db. My question is how do I outer join vwResults8.Team on vwResults2.Team.  My existing select statement is:

SELECT Team,  SUM(EffectiveRevenueReal) AS Rev From vwResults2 GROUP BY Team Order By SUM(EffectiveRevenueReal) DESC

I'd like to select all fields from vwResults8 and join on vwResults8.Team
0
Comment
Question by:hologosesh
[X]
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
  • 2
3 Comments
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 21866257
I'm writing this as though you want ALL records from vwResults2 and matching from vwResults8.  If the other way around, change LEFT to RIGHT.  If all from both, change LEFT to FULL
SELECT Team,  SUM(EffectiveRevenueReal) AS Rev,
  vwResults8.*
From vwResults2 LEFT Outer Join vwResults8 on vwResults2.Team = vwResults8.Team
GROUP BY Team Order By SUM(EffectiveRevenueReal) DESC

Open in new window

0
 
LVL 32

Accepted Solution

by:
Daniel Wilson earned 125 total points
ID: 21866284
Hold on ... messed up some of the GROUP BY stuff:

Replace the Field1 - FieldN with real field names
SELECT Team,  SUM(EffectiveRevenueReal) AS Rev,
  vwResults8.*
From vwResults2 LEFT Outer Join vwResults8 on vwResults2.Team = vwResults8.Team
GROUP BY vwResults2.Team ,
vwResults8.Field1, vwResults8.Field2, ... vwResults8.FieldN
Order By SUM(EffectiveRevenueReal) DESC

Open in new window

0
 

Author Closing Comment

by:hologosesh
ID: 31470564
Works great, thanks.
0

Featured Post

Enroll in June's Course of the Month

June's Course of the Month is now available! Every 10 seconds, a consumer gets hit with ransomware. Refresh your knowledge of ransomware best practices by enrolling in this month's complimentary course for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

707 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