[AccessD] I need a better way to do this - looping

Drew Wutka DWUTKA at Marlow.com
Thu Jan 3 10:07:39 CST 2008


That's a pretty easy fix.  Wouldn't it be better to get the 'results' in
one query?  I mean, it sounds like you are doing this for each well.  If
you could give me the field names (and table name) for :

The ID for each well
The pressure variance
The date field

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 8:54 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] I need a better way to do this - looping

Upon study I don't believe this method will work. The problem being
there is not a record for every day. I thus have to calculate the number
of days between the most recent record for a particular well if the
pressure variance on that record is over 100 and next record for the
same well with a pressure variance below 100. 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Drew Wutka
Sent: Wednesday, January 02, 2008 3: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




More information about the AccessD mailing list