Rick Wannall wrote:
Could you post the where clauses? How can anyone analyze this without
seeing the SQL that's executing?
QUERY 1:
SELECT ProductOrder.SubOrderNum AS [Abs Volume],
(DateDiff("h",[VendorServiceOrder].[ActualStartDateTime],[VendorServiceOrder].[ActualEndDateTime])/24)
AS [Abs TAT], VendorServiceOrder.Cost AS [Abs Cost], "NA" AS [Abs
Quality], VendorServiceOrder.ActualEndDateTime
FROM ((Service INNER JOIN (VendorServiceOrder INNER JOIN ((OrderMaster
INNER JOIN Customer ON OrderMaster.CustomerID = Customer.CustomerID)
INNER JOIN ProductOrder ON OrderMaster.OrderMasterID =
ProductOrder.OrderMasterID) ON VendorServiceOrder.ProductOrderID =
ProductOrder.ProductOrderID) ON Service.ServiceID =
VendorServiceOrder.ServiceID) INNER JOIN Product ON
ProductOrder.ProductID = Product.ProductID) INNER JOIN (ProductGroup
INNER JOIN ProductCategory ON ProductGroup.ProductGroupID =
ProductCategory.ProductGroupID) ON Product.ProductCategoryID =
ProductCategory.ProductCategoryID
WHERE (((VendorServiceOrder.Cost)>0) AND
((VendorServiceOrder.ActualEndDateTime)>#6/1/2005#) AND ((Service.Name)
Not Like "att*" And (Service.Name) Not Like "Title PU") AND
((ProductGroup.Name) Like "titl*") AND
((VendorServiceOrder.IsPaid)=True));
QUERY 2:
SELECT ProductOrder.SubOrderNum AS [Onl Volume],
(DateDiff("h",[VendorServiceOrder].[ActualStartDateTime],[VendorServiceOrder].[ActualEndDateTime])/24)
AS [Onl TAT], VendorServiceOrder.Cost AS [Onl Cost], "NA" AS [Onl
Quality], VendorServiceOrder.ActualEndDateTime
FROM ((Service INNER JOIN (VendorServiceOrder INNER JOIN ((OrderMaster
INNER JOIN Customer ON OrderMaster.CustomerID = Customer.CustomerID)
INNER JOIN ProductOrder ON OrderMaster.OrderMasterID =
ProductOrder.OrderMasterID) ON VendorServiceOrder.ProductOrderID =
ProductOrder.ProductOrderID) ON Service.ServiceID =
VendorServiceOrder.ServiceID) INNER JOIN Product ON
ProductOrder.ProductID = Product.ProductID) INNER JOIN (ProductGroup
INNER JOIN ProductCategory ON ProductGroup.ProductGroupID =
ProductCategory.ProductGroupID) ON Product.ProductCategoryID =
ProductCategory.ProductCategoryID
WHERE (((VendorServiceOrder.Cost)=0) AND
((VendorServiceOrder.ActualEndDateTime)>#6/1/2005#) AND ((Service.Name)
Not Like "att*" And (Service.Name) Not Like "Title PU") AND
((ProductGroup.Name) Like "titl*") AND
((VendorServiceOrder.IsPaid)=True));
QUERY 3:
SELECT "Atlas" AS System, Customer.CustAccountNum AS CustCode,
Customer.Name AS CustName, OrderMaster.PropState AS ST, Service.Name AS
Product, ProductOrder.SubOrderNum AS Volume,
(DateDiff("h",[VendorServiceOrder].[ActualStartDateTime],[VendorServiceOrder].[ActualEndDateTime])/24)
AS TAT, "N/A" AS Quality, ProductOrder.Price AS [Customer Cost],
VendorServiceOrder.Cost AS [Vendor Cost],
([ProductOrder].[Price]-[VendorServiceOrder].[Cost]) AS Margin,
IIf(([ProductOrder].[Price])=0,0,1-([VendorServiceOrder].[Cost])/([ProductOrder].[Price]))
AS [% Margin], VendorServiceOrder.ActualEndDateTime
FROM ((Service INNER JOIN (VendorServiceOrder INNER JOIN ((OrderMaster
INNER JOIN Customer ON OrderMaster.CustomerID = Customer.CustomerID)
INNER JOIN ProductOrder ON OrderMaster.OrderMasterID =
ProductOrder.OrderMasterID) ON VendorServiceOrder.ProductOrderID =
ProductOrder.ProductOrderID) ON Service.ServiceID =
VendorServiceOrder.ServiceID) INNER JOIN Product ON
ProductOrder.ProductID = Product.ProductID) INNER JOIN (ProductGroup
INNER JOIN ProductCategory ON ProductGroup.ProductGroupID =
ProductCategory.ProductGroupID) ON Product.ProductCategoryID =
ProductCategory.ProductCategoryID
WHERE (((Service.Name) Not Like "att*" And (Service.Name) Not Like
"Title PU") AND ((ProductOrder.Price)=0) AND
((VendorServiceOrder.ActualEndDateTime)>#6/1/2005#) AND
((ProductGroup.Name) Like "titl*") AND
((VendorServiceOrder.IsPaid)=True));
COMBINING QUERY: (Where the trouble hits)
SELECT Customer profit search sub customer overall.*,
IIf(IsNull([Customer profit search sub customer abstractor].[Abs
Volume]),0,1) AS IsAbstractor, [Customer profit search sub customer
abstractor].[Abs TAT], [Customer profit search sub customer
abstractor].[Abs Cost], [Customer profit search sub customer
abstractor].[Abs Quality], IIf(IsNull([Customer profit search sub
customer online].[Onl Volume]),0,1) AS IsOnline, [Customer profit
search sub customer online].[Onl TAT], [Customer profit search sub
customer online].[Onl Cost], [Customer profit search sub customer
online].[Onl Quality]
FROM ([Customer profit search sub customer overall] LEFT JOIN [Customer
profit search sub customer online] ON [Customer profit search sub
customer overall].[Volume]=[Customer profit search sub customer
online].[Onl Volume]) LEFT JOIN [Customer profit search sub customer
abstractor] ON [Customer profit search sub customer
overall].[Volume]=[Customer profit search sub customer abstractor].[Abs
Volume];