Feb 18 2005, 05:27 AM
Hi, I have several "layers" of calculations being performed in queries - i.e. several values need to be calculated in one query before other variables may be obtained from these etc. This was all going according to plan until the 4th/5th layer...upon opening a new query (qryCalc5) in design view and trying to add tables/previous queries, I am able to add the 2 tables and one previous query (tblApps, tblHydros and qryCalc2). However upon trying to add a second and final query (qryCalc4) Access becomes non responsive and the only way to continue is to end the program and restart upon which the same action causes the same response (or lack of as the case may be). I have tried deleting the problem query and redesinging it but to no avail.
What is going wrong?
Feb 18 2005, 06:27 AM
Is qryCalc4 a crosstab query? These can be very slow sometimes because all records have to be processed to determine the column names.
Have you tried a repair/compact?
Feb 18 2005, 06:31 AM
no its a standard select query. The database is set to repair/compact on close and so when I left yesterday evening it did so fine but coming in today to do qryCalc5 and trying to add qryCalc4 to it is the bit thats causing problems. I can't run qryCalc4 either, the ssame Access Crash occurs then too. So its something about that query, though what is beating me at the moment.
Feb 18 2005, 06:32 AM
Can you post the query's text, so we can look for something suspicious?
Feb 18 2005, 06:40 AM
Righto. The data used in the calculations is from 2 tables - tblApps and tblHydros - and 2 previous queries - qryCalc2, qryCalc3 - the calculations are as follows:
Feb 18 2005, 06:53 AM
Just a wild guess, but could it be a division by zero?
To rule that out try adding the following conditions:
WHERE ([tblApps].[keelLPA]+[tblApps].[rudLPA]) <> 0
AND [tblHydros].[LWL] <> 0
Feb 18 2005, 07:03 AM
There probably are some divisions by zero as all the data is not yet in the main base tables, however in the previous queries all that happens is that a #ERROR type text appears. The queries still run fine.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here