[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 203
  • Last Modified:

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

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
hologosesh
Asked:
hologosesh
  • 2
1 Solution
 
Daniel WilsonCommented:
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
 
Daniel WilsonCommented:
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
 
hologoseshAuthor Commented:
Works great, thanks.
0

Featured Post

Industry Leaders: 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!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now