[AccessD] Query is to complex

The problem seems to be that Access does not think ProductionDate which
is derived from a field named date in a SQL Server view by the formula
Cdate(Fix(Date)) is really a date. By changing the source query to a
make table query and then running the attached query against the table
everything works great.

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of O'Connor,
Patricia (OTDA)
Sent: Tuesday, September 18, 2007 9:00 AM
To: Access Developers discussion and problem solving
Subject: Re: [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/>


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