[AccessD] Query is to complex

I have had this happen quite a few times. Would have to see more to test out. 
 
It actually could be that you are using a field as numeric but it is actually a text field.  
Is the MAnifoldList a single selection or multiple choice?  
How do you want to handle the GAS1 if there is no record for GAS?
 
The other could be the number of characters in the sql text but that is iffy. 
 
 
I have a very very strong rule here that NO table, field, query name has spaces or special characters. I also keep the length of the query names down.  Makes it easier to read and check 
 
SELECT qryManfldOilProdLst90Days.Manifold, 
       qryManfldOilProdLst90Days.ProductionDate, 
       qryManfldOilProdLst90Days.Oil1, 
       qryManfldGasProdLst90Days.Gas1, 
       qryManfldWaterProdLst90Days.Water1, 
       IIf([Oil1]=0,Null,[Gas1]*1000/[Oil1]) AS GOR1
FROM (qryManfldOilProdLst90Days LEFT JOIN qryManfldWaterProdLst90Days 
     ON (qryManfldOilProdLst90Days.Manifold = qryManfldWaterProdLst90Days.Manifold) 
  AND (qryManfldOilProdLst90Days.ProductionDate = qryManfldWaterProdLst90Days.ProductionDate)) 
 LEFT JOIN qryManfldGasProdLst90Days 
    ON ((qryManfldOilProdLst90Days.Manifold = qryManfldGasProdLst90Days.Manifold) 
    AND (qryManfldOilProdLst90Days.ProductionDate = qry ManfldGasProdLst90Days.ProductionDate))
WHERE (qryManfldOilProdLst90Days.Manifold =[Forms]![frmSelManifold]![ManifoldList]) 
AND (qryManfldOilProdLst90Days.ProductionDate Between [Forms]![frmTimeInterval]![StartDate] And [Forms]![frmTimeInterval]![EndDate])
GROUP BY qryManfldOilProdLst90Days.Manifold, 
         qryManfldOilProdLst90Days.ProductionDate, 
         qryManfldOilProdLst90Days.Oil1, 
         qryManfldGasProdLst90Days.Gas1, 
         qryManfldWaterProdLst90Days.Water1, 
         IIf([Oil1]=0,Null,[Gas1]*1000/[Oil1]);
 
*************************************************************
* Patricia E. O'Connor
* Associate Computer Programmer/Analyst
* OTDA - BDMA
* (W) mailto:Patricia.O'Connor at otda.state.ny.us
* (W) mailto:aa1160 at otda.state.ny.us
***********************************************************


--------------------------------------------------------
This e-mail, including any attachments, may be confidential, privileged or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy or otherwise use this e-mail or its attachments.  Please notify the sender immediately by reply e-mail and delete the e-mail from your system. 


________________________________


From: accessd-bounces at databaseadvisors.com on behalf of Kaup, Chester
Sent: Mon 09/17/2007 9:10 AM
To: Access Developers discussion and problem solving
Subject: [AccessD] Query is to complex



Access says this query is to complex and of course cannot run it. I need some suggestions on how to do this better. Several queries,  VBA? 

Thanks 

SELECT [qry Manifold Oil Production last 90 Days].Manifold, [qry Manifold Oil Production last 90 Days].ProductionDate, [qry Manifold Oil Production last 90 Days].Oil1, [qry Manifold Gas Production Last 90 Days].Gas1, [qry Manifold Water Production Last 90 Days].Water1, IIf([Oil1]=0,Null,[Gas1]*1000/[Oil1]) AS GOR1

FROM ([qry Manifold Oil Production last 90 Days] LEFT JOIN [qry Manifold Water Production Last 90 Days] ON ([qry Manifold Oil Production last 90 Days].Manifold = [qry Manifold Water Production Last 90 Days].Manifold) AND ([qry Manifold Oil Production last 90 Days].ProductionDate = [qry Manifold Water Production Last 90 Days].ProductionDate)) LEFT JOIN [qry Manifold Gas Production Last 90 Days] ON ([qry Manifold Oil Production last 90 Days].Manifold = [qry Manifold Gas Production Last 90 Days].Manifold) AND ([qry Manifold Oil Production last 90 Days].ProductionDate = [qry Manifold Gas Production Last 90 Days].[Production Date])

WHERE ((([qry Manifold Oil Production last 90 Days].Manifold)=[Forms]![frm Select Manifold]![ManifoldList]) AND (([qry Manifold Oil Production last 90 Days].ProductionDate) Between [Forms]![frm Time Interval]![StartDate] And [Forms]![frm Time Interval]![EndDate]))

GROUP BY [qry Manifold Oil Production last 90 Days].Manifold, [qry Manifold Oil Production last 90 Days].ProductionDate, [qry Manifold Oil Production last 90 Days].Oil1, [qry Manifold Gas Production Last 90 Days].Gas1, [qry Manifold Water Production Last 90 Days].Water1, IIf([Oil1]=0,Null,[Gas1]*1000/[Oil1]);

Chester Kaup 
Engineering Technician 
Kinder Morgan CO2 Company, LLP 
Office (432) 688-3797 
FAX (432) 688-3799 


No trees were killed in the sending of this message. However a large number of electrons were terribly inconvenienced. 


-- 
AccessD mailing list 
AccessD at databaseadvisors.com 
http://databaseadvisors.com/mailman/listinfo/accessd 
Website: http://www.databaseadvisors.com <http://www.databaseadvisors.com/>