How complex is this query?

[grnzbra]

I've had two Access jobs and both had queries like this. My question is this: Is this a particularly complex query or does it just seem that way to me?

SELECT [Sales Closing Details].Date_Entered, IIf([Commission Calcs].[Role_Played]=&quot;Selling&quot; And [Type_of_Sale]=&quot;Straight-Line&quot;,IIf([SIRRole]<>&quot;Both&quot;,([GOC_Alloc_Rate]*[AdjustedSellingCommission])/[AdjustedSellingCommission],(([GOC_Alloc_Rate]*[AdjustedSellingCommission])/[AdjustedSellingCommission])*0.5),IIf([Commission Calcs].[Role_Played]=&quot;Selling&quot; And [Type_of_Sale]<>&quot;Straight-Line&quot;,([SellingGrossRate]/[Gross_Ofc_Rate])*[GOC_Alloc_Rate])) AS [SellShare%], IIf([Commission Calcs].[Role_Played]=&quot;Listing&quot; And [Type_of_Sale]=&quot;Straight-Line&quot;,IIf([SIRRole]<>&quot;Both&quot;,([GOC_Alloc_Rate]*[AdjustedNetCommission])/[AdjustedNetCommission],(([GOC_Alloc_Rate]*[AdjustedNetCommission])/[AdjustedNetCommission])*0.5),IIf([Commission Calcs].[Role_Played]=&quot;Listing&quot; And [Type_of_Sale]<>&quot;Straight-Line&quot;,([ListingGrossRate]/[Gross_Ofc_Rate])*[GOC_Alloc_Rate])) AS [ListShare%], [Branch Offices].Office_No, IIf([Type_Of_Sale]=&quot;Straight-Line&quot;,[Reporting_Branch_No],[Office_No]) AS [WhatOffice#GetsIt], Sales.Reporting_Branch_No, [Branch Offices].Office_Name AS BranchName, [Commission Calcs].Select_Flag, Sales.Source_of_Sale, Sales.Type_of_Sale, [Commission Calcs].Role_Played, [Commission Calcs].Portioned_Commission, [Commission Calcs].Portion_Rate, Sales.Sale_No, [Commission Calcs].Attached_to_Sale_Number, [Street_Number] & &quot; &quot; & [Street_Name] & &quot; &quot; & [Street_Type] & IIf(IsNull([Unit_or_Suite_Number]),&quot;&quot;,&quot;, Apt. &quot; & [Unit_or_Suite_Number]) AS PropLine1, [Listing_City_Name] & IIf(IsNull([Listing_State]),&quot;&quot;,&quot;, &quot; & [Listing_State] & &quot; &quot; & [Postal_Zip_Code]) AS PropLine2, IIf(IsNull([Sales].[OwnerID]),&quot;&quot;,Left([Clients].[Contact_1_Last_Name],10)) AS Seller, IIf(IsNull([Sales].[BuyerID]),&quot;&quot;,Left(Clients_1.Contact_1_Last_Name,10)) AS Buyer, IIf([Type_of_Sale]=&quot;Our List and Sale&quot;,&quot;Both&quot;,IIf([Type_of_Sale]=&quot;O/B List Our Sale&quot;,&quot;Selling&quot;,IIf([Type_of_Sale]=&quot;Our List O/B Sale&quot;,&quot;Listing&quot;,IIf([Type_of_Sale]=&quot;Referral&quot;,&quot;Ref.&quot;,IIf([Type_of_Sale]=&quot;Lease&quot; And [No_of_Listing_Assocs]>0 And [No_of_Selling_Assocs]>0,&quot;Both&quot;,IIf([Type_of_Sale]=&quot;Lease&quot; And [No_of_Listing_Assocs]=0 Or IsNull([No_of_Listing_Assocs]) And [No_of_Selling_Assocs]>0,&quot;Selling&quot;,IIf([Type_of_Sale]=&quot;Lease&quot; And [No_of_Selling_Assocs]=0 Or IsNull([No_of_Selling_Assocs]) And [No_of_Listing_Assocs]>0,&quot;Listing&quot;,IIf([Type_of_Sale]=&quot;Affiliate&quot;,&quot;Aff.&quot;,IIf([Type_of_Sale]=&quot;Straight-Line&quot; And [No_of_Listing_Assocs]=0 Or IsNull([No_of_Listing_Assocs]) And [No_of_Selling_Assocs]>0,&quot;Selling&quot;,IIf([Type_of_Sale]=&quot;Straight-Line&quot; And [No_of_Selling_Assocs]=0 Or IsNull([No_of_Selling_Assocs]) And [No_of_Listing_Assocs]>0,&quot;Listing&quot;,IIf([Type_of_Sale]=&quot;Straight-Line&quot; And [No_of_Listing_Assocs]>0 And [No_of_Selling_Assocs]>0,&quot;Both&quot)))))))))) AS SIRRole, Mid([Property Listings].[LISTING_FILE_NO],6,3) & Mid([Property Listings].[LISTING_FILE_NO],15,8) AS [Prop#], Format([Acceptance_Date],&quot;mmm d&quot;&quot;, &quot;&quot;yyyy&quot AS [U/CDate], [Commission Calcs].Deal_Completion_Date, IIf([Deal_Status]=&quot;Closed&quot;,Format([Deal_Completion_Date],&quot;mmm d&quot;&quot;, &quot;&quot;yyyy&quot,IIf([Deal_Status]=&quot;Pending&quot;,Format([Estimated_Closing_Date],&quot;mmm d&quot;&quot;, &quot;&quot;yyyy&quot)) AS CloseDate, IIf([Commission Calcs].[Role_Played]=&quot;Listing&quot;,[ListShare%],IIf([Commission Calcs].[Role_Played]=&quot;Selling&quot;,[SellShare%])) AS SidesShare, IIf([Commission Calcs].[Role_Played]=&quot;Listing&quot;,([ListShare%]*[Sale_Price]),IIf([Commission Calcs].[Role_Played]=&quot;Selling&quot;,([SellShare%]*[Sale_Price]))) AS PriceShare, Sales.Sale_Price AS SalesPrice, IIf([Role_Played]=&quot;Listing&quot;,([ListShare%]*[Gross_Commission_Amount]),IIf([Role_Played]=&quot;Selling&quot;,([SellShare%]*[Gross_Commission_Amount]))) AS GrossCommShare, Format([Gross_Commission_Rate],&quot;#.00%&quot AS [CommIncome%], IIf([Commission Calcs].[Role_Played]=&quot;Listing&quot;,([ListShare%]*[3rdPartyAmt]),IIf([Commission Calcs].[Role_Played]=&quot;Selling&quot;,([SellShare%]*[3rdPartyAmt]))) AS 3rdPartyShare, IIf(IsNull([3rdPartyShare]) Or [3rdPartyShare]=0 Or IsNull([Brokers].[Office_Name]),&quot;&quot;,[Brokers].[Office_Name]) AS 3rdPartyName, Sales.Amount_Paid_tther_Broker AS AffiliateComm, Format([Gross_Commission_Amount],&quot;$#,###.00&quot AS CommIncome, [Commission Calcs].GOC_Alloc_Rate, ([CoExclusive1Comm]+[Amount_Paid_tther_Broker]+[CoExclusive2Comm]) AS 3rdPartyAmt, IIf([Type_Of_Sale]=&quot;Straight-Line&quot;,([SidesShare]*[SecondCoopBrokerAmount]),IIf([ReferralBrokerSide]=&quot;Selling&quot; And [Commission Calcs].[Role_Played]=&quot;Selling&quot;,([GOC_Alloc_Rate]*[SecondCoopBrokerAmount]),IIf([ReferralBrokerSide]=&quot;Listing&quot; And [Commission Calcs].[Role_Played]=&quot;Listing&quot;,([GOC_Alloc_Rate]*[SecondCoopBrokerAmount]),0))) AS ExtReferral1Amount, IIf([ExtReferral1Amount]>0,Brokers_2.Office_Name,&quot;&quot AS ExtReferral1Name, IIf([Type_Of_Sale]=&quot;Straight-Line&quot;,([SidesShare]*[ReferralBroker2Amount]),IIf([ReferralBroker2Side]=&quot;Selling&quot; And [Commission Calcs].[Role_Played]=&quot;Selling&quot;,([GOC_Alloc_Rate]*[ReferralBroker2Amount]),IIf([ReferralBroker2Side]=&quot;Listing&quot; And [Commission Calcs].[Role_Played]=&quot;Listing&quot;,([GOC_Alloc_Rate]*[ReferralBroker2Amount]),0))) AS ExtReferral2Amount, IIf([ExtReferral2Amount]>0,Brokers_3.Office_Name,&quot;&quot AS ExtReferral2Name, [ExtReferral1Amount]+[ExtReferral2Amount] AS TotalExtReferralAmt, [GrossCommShare]-[3rdPartyShare]-[TotalExtReferralAmt]-[IntReferralAmount] AS GrossToCoShare, IIf([Commission Calcs].[Role_Played]=&quot;Listing&quot; And [Type_Of_Sale]=&quot;Straight-Line&quot;,([TotalListReportingGrossComm]*[GOC_Alloc_Rate]),IIf([Commission Calcs].[Role_Played]=&quot;Listing&quot; And [Type_Of_Sale]<>&quot;Straight-Line&quot;,([ListCommLessRefs]*[GOC_Alloc_Rate]*[Portion_Rate]))) AS ListingAssocShare, IIf([Commission Calcs].[Role_Played]=&quot;Selling&quot; And [Type_Of_Sale]=&quot;Straight-Line&quot;,([TotalSellReportingGrossComm]*[GOC_Alloc_Rate]),IIf([Commission Calcs].[Role_Played]=&quot;Selling&quot; And [Type_Of_Sale]<>&quot;Straight-Line&quot;,([SellCommLessRefs]*[GOC_Alloc_Rate]*[Portion_Rate]))) AS SellingAssocShare, IIf([Commission Calcs].[Role_Played]=&quot;Listing&quot;,[GrossToCoShare]-[ListingAssocShare],IIf([Commission Calcs].[Role_Played]=&quot;Selling&quot;,[GrossToCoShare]-[SellingAssocShare])) AS CompanyDollarShare, IIf([Commission Calcs].[Role_Played]=&quot;Listing&quot;,([CompanyDollarShare]/[PriceShare])*[GOC_Alloc_Rate],IIf([Commission Calcs].[Role_Played]=&quot;Selling&quot;,([CompanyDollarShare]/[PriceShare]*[GOC_Alloc_Rate]))) AS [Gross%Share], Sales.TotalSellReportingGrossComm AS SellingAssoc, Sales.Gross_Commission_Amount AS GrossComm, Sales.Gross_Commission_Rate AS [Gross%], IIf([Type_Of_Sale]=&quot;Straight-Line&quot; And [Commission Calcs].[Role_Played]=&quot;Selling&quot;,([SellShare%]*([TotalSellRefAssocComms]+[TotalListRefAssocComms])),IIf([Type_Of_Sale]=&quot;Straight-Line&quot; And [Commission Calcs].[Role_Played]=&quot;Listing&quot;,([ListShare%]*([TotalSellRefAssocComms]+[TotalListRefAssocComms])),IIf([Commission Calcs].[Role_Played]=&quot;Selling&quot;,([GOC_Alloc_Rate]*[TotalSellRefAssocComms]),IIf([Commission Calcs].[Role_Played]=&quot;Listing&quot;,([GOC_Alloc_Rate]*[TotalListRefAssocComms]))))) AS IntReferralAmount, Sales.TotalReferralAssocPayouts AS InternalReferral, ([Gross_Commission_Amount]-[CoExclusiveAgencyFees]-[Amount_Paid_tther_Broker]-[GrandTotalReferralsPaid]) AS GrossToCo, ([Gross_Commission_Amount]-[CoExclusiveAgencyFees]-[Amount_Paid_tther_Broker]-[GrandTotalReferralsPaid]-[TotalListReportingGrossComm]-[TotalSellReportingGrossComm]) AS CompanyDollar, Sales.Gross_Commission_Amount, Sales.Total_Listing_Referral_Payouts, Sales.Total_Selling_Referral_Payouts, [Commission Calcs].Associate_ID, [Commission Calcs].AssociateCompany, Sales.Reporting_Company_No, [Branch Offices].ReportingDivisionNo, [Commission Calcs].Office_Applied_To, Brokers.Office_Name AS Office, Sales.Acceptance_Date, Sales.Close_Date, Format([Close_Date],&quot;mmm dd yyyy&quot AS ToClosePeriod, Sales.TotalListReportingGrossComm, Sales.TotalSellReportingGrossComm, Sales.Role_of_Other_Broker, [Branch Offices].Company_No, Companies.Company_Name, Companies.Address1 AS CoAddline1, IIf(IsNull([Companies].[Address2]),[Companies].[City] & IIf(IsNull([Companies].[State]),&quot;&quot;,&quot;, &quot; & [Companies].[State] & &quot; &quot; & [Companies].[Zip_Code]),[Companies].[Address2]) AS CoAddLine2, IIf(IsNull([Companies].[Address2]),&quot;&quot;,[Companies].[City] & IIf(IsNull([Companies].[State]),&quot;&quot;,&quot;, &quot; & [Companies].[State] & &quot; &quot; & [Companies].[Zip_Code])) AS CoAddLine3, [Companies].[City] & IIf(IsNull([Companies].[State]),&quot;&quot;,&quot;, &quot; & [Companies].[State] & &quot; &quot; & [Companies].[Zip_Code]) AS CoCity, IIf(IsNull([Phone_Number]),&quot;&quot;,&quot;Phone#: (&quot; & Left([Phone_Number],3) & &quot &quot; & Mid([Phone_Number],4,3) & &quot;-&quot; & Right([Phone_Number],4)) AS [CoPhone#], [Commission Calcs].Office_Applied_To, Sales.Reporting_Branch_No, [Property Listings].STREET_NUMBER, [Property Listings].STREET_NAME, [Property Listings].Listing_State
FROM [Sales Closing Details] RIGHT JOIN (((([Commission Calcs] LEFT JOIN ((((((Sales LEFT JOIN Brokers ON Sales.Other_Broker_Number = Brokers.Office_ID) LEFT JOIN [Property Listings] ON Sales.Sale_No = [Property Listings].Attached_to_Sale_Number) LEFT JOIN Clients ON Sales.OwnerID = Clients.ClientNo) LEFT JOIN Clients AS Clients_1 ON Sales.BuyerID = Clients_1.ClientNo) LEFT JOIN Brokers AS Brokers_1 ON Sales.CoExclusiveID1 = Brokers_1.Office_ID) LEFT JOIN Brokers AS Brokers_2 ON Sales.SecondCoopBroker = Brokers_2.Office_ID) ON [Commission Calcs].Attached_to_Sale_Number = Sales.Sale_No) LEFT JOIN [Branch Offices] ON ([Commission Calcs].Office_Applied_To = [Branch Offices].Office_No) AND ([Commission Calcs].AssociateCompany = [Branch Offices].Company_No)) LEFT JOIN Brokers AS Brokers_3 ON Sales.ReferralBroker2 = Brokers_3.Office_ID) INNER JOIN Companies ON [Commission Calcs].AssociateCompany = Companies.Company_No) ON [Sales Closing Details].Sale_Number = [Commission Calcs].Attached_to_Sale_Number
WHERE ((([Sales Closing Details].Date_Entered) Between #1/1/2002# And #12/31/2002#) AND ((Sales.Source_of_Sale)=&quot;Sale&quot AND ((Sales.Type_of_Sale)<>&quot;Affiliate&quot AND (([Commission Calcs].Role_Played)<>&quot;Allocation&quot; And ([Commission Calcs].Role_Played)<>&quot;HIstory&quot; And ([Commission Calcs].Role_Played)<>&quot;MtdHistory&quot AND ((([Gross_Commission_Amount]-[CoExclusiveAgencyFees]-[Amount_Paid_tther_Broker]-[GrandTotalReferralsPaid]-[TotalListReportingGrossComm]-[TotalSellReportingGrossComm]))>=50000) AND ((Sales.Deal_Status)=&quot;Closed&quot) OR ((([Sales Closing Details].Date_Entered) Between #1/1/2002# And #12/31/2002#) AND ((Sales.Source_of_Sale)=&quot;Sale&quot AND ((Sales.Type_of_Sale)<>&quot;Affiliate&quot AND (([Commission Calcs].Role_Played)<>&quot;Allocation&quot; And ([Commission Calcs].Role_Played)<>&quot;HIstory&quot; And ([Commission Calcs].Role_Played)<>&quot;MtdHistory&quot AND ((Sales.Sale_Price)>=10000000) AND ((Sales.Deal_Status)=&quot;Closed&quot);

 

[KornGeek]

Without even going through it, I can say that it definitely appears to be a very complex query. Have you considered grouping data in other queries first, and then consolidating it in your final query? This would probably result in a performance hit, but make it easier to maintain.

 

[grnzbra]

Thanks, I was getting severe feelings of inadequacy.

This is typical of what was here when I got here. (And when I got to my previous job, also.) Unfortunately, the performance is pretty bad as it is; by the time the report runs' I've forgotten why I ran it (I don't run it for the information it contains; only in an effort to modify the report or correct an error in it.)

 

[JoyInOK]

grnzbra, you just gave me a great laugh. I actually started to read your query, when I accidentally scrolled down with my mouse and realized it went on for 4 screens!
I think KornGeek is right on the money. Figure out what you need in the results, and break this down into managable chunks. Then pull all the results together in one query.

 

[JeremyNYC]

This is ludicrous, actually. Do you use all 85 fields that get pulled across? Is there a need for all of this information to be flattened like this? Why not use sub reports or sub forms? I would think that converting some of these things into functions and useing those in the query would be a lot easier to understand, and a bunch faster, as Jet wouldn't have to evaluate both sides of so many immediate if statements.

Here's My favorite single field:
IntReferralAmount: IIf([Type_Of_Sale]=&quot;Straight-Line&quot; And [Commission Calcs].[Role_Played]=&quot;Selling&quot;,([SellShare%]*([TotalSellRefAssocComms]+[TotalListRefAssocComms])),IIf([Type_Of_Sale]=&quot;Straight-Line&quot; And [Commission Calcs].[Role_Played]=&quot;Listing&quot;,([ListShare%]*([TotalSellRefAssocComms]+[TotalListRefAssocComms])),IIf([Commission Calcs].[Role_Played]=&quot;Selling&quot;,([GOC_Alloc_Rate]*[TotalSellRefAssocComms]),IIf([Commission Calcs].[Role_Played]=&quot;Listing&quot;,([GOC_Alloc_Rate]*[TotalListRefAssocComms])))))

Jeremy ==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Affordable Development, Professionally Done

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[grnzbra]

You counted them? I have no idea if they're all used. I think some of the calculated fiedls are then used in other calculated fields. Actually you missed the big one which has 10 closing parenthesis.

I've thought that using functions would be better, but I wasn't sure if these were really that complex or if it was my limited experience. (As I said, I've only had 2 Access jobs, and this type of thing was the norm in both of them.)

What do you mean by &quot;flattened&quot;?

 

[GDGarth]

Who would ever set something like that up? Can you imagine trying to trouble-shoot it?

You'd be way better off to break it up into smaller pieces. Even if the performance is worse (which I doubt, by the way), you'd have some chance of trouble shooting it, should the need arise.

 

[grnzbra]

Actually, I do know what it's like to troubleshoot it; it's my job. That along with trying to modify reports driven by it.

If one tries to run this query, there is an error message saying it is too complex. However, if one opens the report driven by this thing, it runs fine. Also, removing the first calculated field (the second field), it will open ok.

 

[MrMcFestoe]

Ouch ! Its a big one, Must be another way like grouping, it needs a real tech guy this one