Solved

Multiple FROM Clauses in VBA code for a single block of code

Posted on 2014-02-01
5
191 Views
Last Modified: 2014-02-03
Why are three FROM clauses needed below..I assume the second FROM clause is needed to build the first ?  If that is the case does the order of the FROM clause matter ?  

Note the below works..I am just trying to understand it....

Select G.Client, G.Date

FROM(Select GROCERY.Client,GROCERY.DATE
FROM (Select LEFT([FullClientName,40) AS Client,Date
FROM strExcelWSName
WHERE [Product] = 'Pizza') AS GROCERY

INNER JOIN (Select LEFT([FullClientName,40) AS Client,Date
FROM strWSName
WHERE [Product] = 'Soybeans' AS AG

ON GROCERY.CLIENT = AG.CLIENT

GROUP BY GROCERY.CLIENT,GROCERY.DATE AS
0
Comment
Question by:upobDaPlaya
[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
  • 2
5 Comments
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39827092
That code would not work "as is", there are syntax errors
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 350 total points
ID: 39827099
This is a corrected version of the code, and the indentation should help you understand the structure.
SELECT
      G.Client
    , G.DATE
FROM (
            SELECT
                  GROCERY.Client
                , GROCERY.DATE
            FROM (
                        SELECT
                              LEFT([FullClientName], 40) AS Client
                            , [Date]
                        FROM strExcelWSName
                        WHERE [Product] = 'Pizza'
                  ) AS GROCERY
                  INNER JOIN (
                        SELECT
                              LEFT([FullClientName], 40) AS Client
                            , [Date]
                        FROM strWSName
                        WHERE [Product] = 'Soybeans'
                  ) AS AG
                        ON GROCERY.CLIENT = AG.CLIENT
      ) AS G
GROUP BY
      GROCERY.CLIENT
    , GROCERY.[DATE]

Open in new window

I assume the second FROM clause is needed to build the first ?  
YES, they are a series of subqueries.
 If that is the case does the order of the FROM clause matter ?  
Does the order matter? YES
0
 

Author Comment

by:upobDaPlaya
ID: 39827125
OMG ! The indentation makes a huge difference.  So the hierarchy of the sub-queries then is the one that always appears at the top is the one that gets built last..
0
 
LVL 32

Assisted Solution

by:Robberbaron (robr)
Robberbaron (robr) earned 150 total points
ID: 39827298
yes to heiracry..

but i think the last 3 lines should be
GROUP BY
      G.CLIENT
    , G.[DATE]

Open in new window


due to the ) AS G  immediately above.

its possible the outer clause is superfluous as well; the group by could be on the second select, in which case the Group By names would be corrent

eg
      SELECT
                  GROCERY.Client
                , GROCERY.DATE
        FROM (
                        SELECT
                              LEFT([FullClientName], 40) AS Client
                            , [Date]
                        FROM strExcelWSName
                        WHERE [Product] = 'Pizza'
                  ) AS GROCERY
                  INNER JOIN (
                        SELECT
                              LEFT([FullClientName], 40) AS Client
                            , [Date]
                        FROM strWSName
                        WHERE [Product] = 'Soybeans'
                  ) AS AG
                        ON GROCERY.CLIENT = AG.CLIENT
         GROUP BY
              GROCERY.CLIENT
              , GROCERY.[DATE]

Open in new window

0
 

Author Closing Comment

by:upobDaPlaya
ID: 39831483
Thanks all...makes sense
0

Featured Post

Is Your DevOps Pipeline Leaking?

Is your CI/CD pipeline a hodge-podge of randomly connected tools? You’ve likely got a tool to fix one problem & then a different tool to fix another, resulting in a cluster of tools with overlapping functionality. Learn how to optimize your pipeline with Gartner's recommendations

Question has a verified solution.

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

Suggested Solutions

Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

734 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