Drew Wutka
DWUTKA at Marlow.com
Thu Jan 3 10:09:35 CST 2008
Yes, a query is typically going to be faster then code, if the query is doing the same thing. Quite simply, you are letting Jet do all the work. What you are trying to do should be feasible with just one query though. Drew -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Kaup, Chester Sent: Thursday, January 03, 2008 9:09 AM To: Access Developers discussion and problem solving Subject: Re: [AccessD] I need a better way to do this - looping Here is what I came up with. The master table contains pressure records for many different wells over a long time period. CODE METHOD: I first select the wells with a variance over 100 on the most recent record. Next I extract all the records for each of these wells one at a time looping through the master table. Next I move down through this subset of records until I find the first one with a variance of less than 100. Lastly I calculate the number of days between the record found in the last step and the first record. I continue this process for each of the selected wells. This takes about 5 minutes to run. QUERY METHOD 1. Run a select query to find wells with a variance over 100 on most recent record. 2. Run a query to find the max date with a variance less than 101 using the above subset of wells 3. Run a query to calculate the date difference between the most recent record date for the wells in the subset determined in step 1 and the date determined in step 2 Run time is about 11 seconds. Why the code is slow I don't know. Maybe something to do with extracting a subset from the master table 149 times. -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Dettman Sent: Wednesday, January 02, 2008 9:14 PM To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] I need a better way to do this - looping Drew, If he's got a hefty table though, that sub select is going to be slow unless it can run against indexes. The loop would most likely be faster. He needs to test it both ways. Jim. -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Drew Wutka Sent: Wednesday, January 02, 2008 4:37 PM To: Access Developers discussion and problem solving Subject: Re: [AccessD] I need a better way to do this - looping I made a table with the data below. Fields: ID (Just an Autonumber), RecordDate (The date of the record), PressureVariance (the number you want to find the first one less then 100. This Query produces what you are looking for: SELECT Count(ID) AS CountOfID FROM tblData WHERE RecordDate>(SELECT IIF(IsNull(First(RecordDate)),#01/01/1800#,First(RecordDate)) AS FirstOfRecordDate FROM tblData WHERE PressureVariance<100 ORDER BY First(RecordDate)) Basically, we are getting the count of ID (you can use any field you want), FROM the table (I called mine tblData), WHERE RecordDate is greater then a sub query. The subquery gets the FIRST RecordDate field value FROM tblData WHERE PressureVariance is less then 100, ORDERed BY the RecordDate field (since it's a totals query, the ORDER BY has to have First(RecordDate), not just RecordDate, otherwise you get an error about RecordDate not being part of an aggregate function.) The IIF statement in the subquery will return January 1st of 1800 if there are no records with a Pressure Variance of less then 100, which allows the main query to return the total count of records. So Replace (ID) with (WhateverFieldYouWant), RecordDate with the name of your date field, tblData with the name of your table, and PressureVariance with the name of your Pressure Variance field. One shot query, no code required. Drew -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Kaup, Chester Sent: Wednesday, January 02, 2008 1:31 PM To: Access Developers discussion and problem solving Subject: Re: [AccessD] I need a better way to do this - looping I don't think this will work because I only need to count the number of records between the last record and the first record (date descending) where the measurement is greater than 100. There may records further down in the table that are also above 100 but there are records below 100 between these and the first group. For example 12/31/2007 145 12/30/2007 175 12/29/2007 207 12/28/2007 123 12/27/2007 114 12/26/2007 90 12/25/2007 45 12/24/2007 73 12/23/2007 304 The correct answer in this case would be 5 -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Hale, Jim Sent: Wednesday, January 02, 2008 1:19 PM To: Access Developers discussion and problem solving Subject: Re: [AccessD] I need a better way to do this - looping Couldn't you use a groupby query- something like SELECT Count(tblWellData.[fldMeasure date]) AS [CountOffldMeasure date] FROM tblWellData WHERE (((tblWellData.fldWellNo)=1) AND ((tblWellData.fldMeasure)<100)); For well 1 this gives you the number of records where the measurement is <100 HTH Jim Hale -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Kaup, Chester Sent: Wednesday, January 02, 2008 12:35 PM To: Access Developers discussion and problem solving Subject: Re: [AccessD] I need a better way to do this - looping Let me try to explain what I am trying to calculate. Myds.Fields(4) is a pressure variance measurement on an oilfield injection well. It should always be below 100. The goal is to count the number of records (date descending) from the most recent to the first one where Myds1.Fields(4) is less than 100 for each well. The number of records per well varies. If all the records for a well have Myds.fields(4) with a value above 100 the correct answer is the number of records for the well. If record 11 descending from the most recent is less than 100 then the correct answer would be 10. Hope this makes sense. *********************************************************************** The information transmitted is intended solely for the individual or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of or taking action in reliance upon this information by persons or entities other than the intended recipient is prohibited. If you have received this email in error please contact the sender and delete the material from any computer. As a recipient of this email, you are responsible for screening its contents and the contents of any attachments for the presence of viruses. No liability is accepted for any damages caused by any virus transmitted by this email. -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com