Sub Query Consolidation, Syntax Question

From: Quizarate (Quizarate_at_discussions.microsoft.com)
Date: 09/09/04 

Date: Thu, 9 Sep 2004 08:47:12 -0700

I am new to writing free hand SQL, and have a two part question. #1 relates
to sub query consolidation and #2 relates to SQL syntax and why it changes
automatically. There is a lot here, so if anyone has suggestions on how I
could better post the question, that would be helpful as well. If anyone
likes, I could even email them a notepad or word file with formatting to
highlight my questions. Thanks for any help.

#1 I'm trying to consolidate a number of queries into one query. Below is
what I have so far. If you look at the SQL, you'll see that what I am
calling "c" appears twice and is the exact same SQL in each case. It seems
to me that there should be a way to consolidate this into one place, then get
"a" and "b" from this single "c".

SELECT a.PilName, a.FiscYear, a.MonthName, a.PilCrdDollars AS PilTotDol,
b.TotCrdDollars AS TotDol, (a.PilCrdDollars/b.TotCrdDollars) AS PctOfTot
     
FROM (Select c.[Pillar Name] as PilName, c.[nFiscal Year] as FiscYear,
c.MonthName, sum(c.[Credit Dollars]) as PilCrdDollars
          
>From (SELECT [Date Table].[nFiscal Year], [Style Information].[Pillar
Name], [Date Table].MonthName, Abs(Sum([NetShipPlusOpen]![Discounted])) AS
[Credit Dollars]
          
FROM [Date Table] RIGHT JOIN ([Style Information] RIGHT JOIN ([Customer
Information] RIGHT JOIN NetShipPlusOpen ON [Customer Information].[Sold To
Number] = NetShipPlusOpen.[Sold To]) ON [Style Information].Style =
NetShipPlusOpen.Style) ON [Date Table].YYYYMMDD = NetShipPlusOpen.[Shipped
Date]
          
WHERE ((Not ([Style Information].Pillar) Is Null) AND (([Date
Table].[nFiscal Year])>=2004) AND ((NetShipPlusOpen.DataType)="Credit") AND
((NetShipPlusOpen.Company)="011"))
          
GROUP BY [Date Table].[nFiscal Year], [Style Information].[Pillar Name],
[Date Table].MonthName) as c
    
Group By c.[Pillar Name], c.[nFiscal Year], c.MonthName) AS a

INNER JOIN

(Select c.[nFiscal Year] as FiscYear, c.MonthName, sum(c.[Credit Dollars])
as TotCrdDollars
         
>From (SELECT [Date Table].[nFiscal Year], [Style Information].[Pillar Name],
[Date Table].MonthName, Abs(Sum([NetShipPlusOpen]![Discounted])) AS [Credit
Dollars]
          
FROM [Date Table] RIGHT JOIN ([Style Information] RIGHT JOIN ([Customer
Information] RIGHT JOIN NetShipPlusOpen ON [Customer Information].[Sold To
Number] = NetShipPlusOpen.[Sold To]) ON [Style Information].Style =
NetShipPlusOpen.Style) ON [Date Table].YYYYMMDD = NetShipPlusOpen.[Shipped
Date]
          
WHERE ((Not ([Style Information].Pillar) Is Null) AND (([Date
Table].[nFiscal Year])>=2004) AND ((NetShipPlusOpen.DataType)="Credit") AND
((NetShipPlusOpen.Company)="011"))
          
GROUP BY [Date Table].[nFiscal Year], [Style Information].[Pillar Name],
[Date Table].MonthName) as c
     
Group by c.[nFiscal Year], c.MonthName) AS b

ON (a.FiscYear = b.FiscYear) AND (a.MonthName = b.MonthName);

#2 When I type to SQL into the Query panel, I type it as it appears above.
When I close and then re-open the query, the syntax seems to have changed.
In some places the ( are replaced with [, and a "." is added in others. Any
info on this would be much appreciated. I'm curious as to why this happens,
and how I can learn more about it. Below is the SQL I have after the query
is closed and re-opened:

SELECT a.PilName, a.FiscYear, a.MonthName, a.PilCrdDollars AS PilTotDol,
b.TotCrdDollars AS TotDol, (a.PilCrdDollars/b.TotCrdDollars) AS PctOfTot
FROM [Select c.[Pillar Name] as PilName, c.[nFiscal Year] as FiscYear,
c.MonthName, sum(c.[Credit Dollars]) as PilCrdDollars
          From (SELECT [Date Table].[nFiscal Year], [Style
Information].[Pillar Name], [Date Table].MonthName,
Abs(Sum([NetShipPlusOpen]![Discounted])) AS [Credit Dollars]
          FROM [Date Table] RIGHT JOIN ([Style Information] RIGHT JOIN
([Customer Information] RIGHT JOIN NetShipPlusOpen ON [Customer
Information].[Sold To Number] =
          NetShipPlusOpen.[Sold To]) ON [Style Information].Style =
NetShipPlusOpen.Style) ON [Date Table].YYYYMMDD = NetShipPlusOpen.[Shipped
Date]
          WHERE ((Not ([Style Information].Pillar) Is Null) AND (([Date
Table].[nFiscal Year])>=2004) AND ((NetShipPlusOpen.DataType)="Credit") AND
((NetShipPlusOpen.Company)="011"))
          GROUP BY [Date Table].[nFiscal Year], [Style Information].[Pillar
Name], [Date Table].MonthName) as c
    Group By c.[Pillar Name], c.[nFiscal Year], c.MonthName]. AS a INNER
JOIN [Select c.[nFiscal Year] as FiscYear, c.MonthName, sum(c.[Credit
Dollars]) as TotCrdDollars
         From (SELECT [Date Table].[nFiscal Year], [Style
Information].[Pillar Name], [Date Table].MonthName,
Abs(Sum([NetShipPlusOpen]![Discounted])) AS [Credit Dollars]
          FROM [Date Table] RIGHT JOIN ([Style Information] RIGHT JOIN
([Customer Information] RIGHT JOIN NetShipPlusOpen ON [Customer
Information].[Sold To Number] =
          NetShipPlusOpen.[Sold To]) ON [Style Information].Style =
NetShipPlusOpen.Style) ON [Date Table].YYYYMMDD = NetShipPlusOpen.[Shipped
Date]
          WHERE ((Not ([Style Information].Pillar) Is Null) AND (([Date
Table].[nFiscal Year])>=2004) AND ((NetShipPlusOpen.DataType)="Credit") AND
((NetShipPlusOpen.Company)="011"))
          GROUP BY [Date Table].[nFiscal Year], [Style Information].[Pillar
Name], [Date Table].MonthName) as c
     Group by c.[nFiscal Year], c.MonthName]. AS b ON (a.MonthName =
b.MonthName) AND (a.FiscYear = b.FiscYear);

Relevant Pages

  • Re: [PHP] OOP slow -- am I an idiot?
    ... OOP has overhead. ... That saves you putting wasteful SQL queries in your ... If you need to do a complex query with a couple of joins and such, ... I want to create a "customer" class which fetches its attributes from a ...
    (php.general)
  • Efficient coordinated queries??
    ... A large chunk of it can be brought in with a single query and each row represents an item to process. ... For a hypothetical example consider a customer order database where we need to process through all customers and can bring in a row of customer data as our main query but there are 0..n1 rows of customer order data, 0..n2 rows of customer feedback information, 0..n3 rows of customer discounts available, etc. ... Instead I want to bring in the data as individual DataSets representing an item to process. ... Fully denormalize in a gi-normous SQL query and go back to just a single row of data per item. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: comboBox & northwinds sample orders form
    ... True, but OFTEN to display a customer name when you have a customer id, we ... dlookup) are suggest. ... the problem is that you don't need dlookup in a query. ... You can always just shove in the sql in place of the ...
    (microsoft.public.access.formscoding)
  • Re: Query going wrong
    ... statement from the Query. ... I am not a big programmer so I have no clue what it means when it's in SQL ... >> When I goto the Report Query Report it prints all the same information for ... >> Then when I am in a query where one customer has had the same service ...
    (microsoft.public.access.queries)
  • Re: Sub Query Consolidation, Syntax Question
    ... a separate saved query which you can then reference as such, ... Keep a copy of your queries in SQL ... option to turn of their formatting. ... >to me that there should be a way to consolidate this into one place, ...
    (microsoft.public.access.queries)