I have a one table of logged data, Time and Value (two fields)

TIME VALUE
01/01/05 11:00:08 1.33763
01/01/05 11:00:16 1.33238
01/01/05 11:00:24 1.32988
01/01/05 11:00:32 1.33125

I want to filter out all records above a certain value X and then only
the first record when the value goes from below to above X. Can you do
this in SQL / Sub query Help Please

Regards John

Mamilovic wrote:[color=blue]
> I have a one table of logged data, Time and Value (two fields)
>
> TIME VALUE
> 01/01/05 11:00:08 1.33763
> 01/01/05 11:00:16 1.33238
> 01/01/05 11:00:24 1.32988
> 01/01/05 11:00:32 1.33125
>
> I want to filter out all records above a certain value X and then only
> the first record when the value goes from below to above X. Can you do
> this in SQL / Sub query Help Please
>
> Regards John[/color]

John,

This looks like an interesting problem that I feel confident someone
here can help you with, but it is not yet clear to me what you are
trying to do. I don't want to spend time on a wild guess chase.
Please provide the name of the table of logged data, change the name of
the fields TIME and VALUE to names that will not confuse SQL and, most
importantly, provide a more detailed example of what you are trying to
do, including a sample of the output you want to obtain.

I hope this helps,

James A. Fortune
CDMAPoster@FortuneJames.com

Thanks for replying
Table name is DataLogged and sample data is shown below.[color=blue]
> TIMESTAMP READING
> 01/01/05 11:00:08 1.4
> 01/01/05 11:00:16 1.5
> 01/01/05 11:00:24 1.6
> 01/01/05 11:00:32 1.3
> 01/01/05 11:00:40 1.4
> 01/01/05 11:00:48 1.5
> 01/01/05 11:00:56 1.5
> 01/01/05 11:01:04 1.5
> 01/01/05 11:01:12 1.5[/color]

The reords which I want to see, if X was 1.45 would be[color=blue]
> 01/01/05 11:00:16 1.5
> 01/01/05 11:00:48 1.5[/color]
John

Mamilovic wrote:[color=blue]
> Thanks for replying
> Table name is DataLogged and sample data is shown below.[color=green]
> > TIMESTAMP READING
> > 01/01/05 11:00:08 1.4
> > 01/01/05 11:00:16 1.5
> > 01/01/05 11:00:24 1.6
> > 01/01/05 11:00:32 1.3
> > 01/01/05 11:00:40 1.4
> > 01/01/05 11:00:48 1.5
> > 01/01/05 11:00:56 1.5
> > 01/01/05 11:01:04 1.5
> > 01/01/05 11:01:12 1.5[/color]
>
> The reords which I want to see, if X was 1.45 would be[color=green]
> > 01/01/05 11:00:16 1.5
> > 01/01/05 11:00:48 1.5[/color]
> John[/color]

I see what you are trying to do now. If no one else posts a solution I
should have one for you tomorrow afternoon. It should only take about
ten minutes to do and test but I have to leave right now.

James A. Fortune
CDMAPoster@FortuneJames.com

Looking forward to your answer
John

Mamilovic wrote:[color=blue]
> Looking forward to your answer
> John[/color]

See if this does what you're looking for:

SELECT TimeStamp, Reading FROM DataLogged WHERE Reading > 1.45 AND
(SELECT A.Reading FROM DataLogged AS A WHERE A.TimeStamp = (SELECT
MAX(B.TimeStamp) FROM DataLogged AS B WHERE B.TimeStamp <
DataLogged.TimeStamp)) < 1.45 ORDER BY TimeStamp;

The subquery grabs the previous TimeStamp then gets the Reading
corresponding to that TimeStamp.

James A. Fortune
CDMAPoster@FortuneJames.com

Thanks you for your reply this google group stuff is great.

It works on a small table.

I tried it on the actual data 107000 records and my computer went ga ga
for over an hour (spat out nothing) I had to kill Access any
suggestions

John
Thanks again for help to date

Mamilovic wrote:[color=blue]
> Thanks you for your reply this google group stuff is great.
>
> It works on a small table.
>
> I tried it on the actual data 107000 records and my computer went ga ga
> for over an hour (spat out nothing) I had to kill Access any
> suggestions
>
> John
> Thanks again for help to date[/color]

In cases like yours I usually convert at least one subquery into a
temporary table; but since I'm actually a code jockey that got
converted into solving things with SQL I'm thinking about solving this
with some VBA code. If written well the VBA version is usually quite
fast. I can't really say what method I'll end up using. Stay tuned.
BTW, are your TimeStamp and Reading fields both indexed?

James A. Fortune
CDMAPoster@FortuneJames.com

I have converted the data from a DBF file format, at the moment there
is no index.
Any recommendations.
John

Mamilovic wrote:[color=blue]
> I have converted the data from a DBF file format, at the moment there
> is no index.
> Any recommendations.
> John[/color]

With the number of records you have I don't think putting an index on
those two fields will be enough, but the indexes will help tremendously
for either a VBA solution or a SQL solution.

James A. Fortune
CDMAPoster@FortuneJames.com

My house in Bradenton has gone up at least $150,000 in the last six
months and they haven't even finished building it. -- D. Smith

CDMAPoster@FortuneJames.com wrote:[color=blue]
> Mamilovic wrote:[color=green]
> > I have converted the data from a DBF file format, at the moment there
> > is no index.
> > Any recommendations.
> > John[/color]
>
> With the number of records you have I don't think putting an index on
> those two fields will be enough, but the indexes will help tremendously
> for either a VBA solution or a SQL solution.
>
> James A. Fortune
> CDMAPoster@FortuneJames.com[/color]

Make sure the indexes are in DataLogged. After copying the structure
of DataLogged to NewTable and deleting any fields beyond TimeStamp and
Reading:

Public Sub FillNewTable(dblBoundary As Double)
Dim MyDB As Database
Dim DataLoggedRS As Recordset
Dim NewTableRS As Recordset
Dim strSQL As String
Dim lngCount As Long
Dim lngI As Long
Dim boolCount As Boolean
Dim dblPrevValue As Double

Set MyDB = CurrentDb
strSQL = "DELETE NewTable FROM NewTable;"
MyDB.Execute strSQL, dbFailOnError
strSQL = "SELECT TimeStamp, Reading FROM NewTable;"
'Confirm that NewTable has no records
boolCount = True
Do While boolCount
Set NewTableRS = MyDB.OpenRecordset(strSQL, dbOpenSnapshot)
If NewTableRS.RecordCount = 0 Then boolCount = False
NewTableRS.Close
Set NewTableRS = Nothing
Loop
Set NewTableRS = MyDB.OpenRecordset(strSQL, dbOpenDynaset)
strSQL = "SELECT TimeStamp, Reading FROM DataLogged ORDER BY
TimeStamp;"
Set DataLoggedRS = MyDB.OpenRecordset(strSQL, dbOpenSnapshot)
If DataLoggedRS.RecordCount > 0 Then
DataLoggedRS.MoveLast
lngCount = DataLoggedRS.RecordCount
DataLoggedRS.MoveFirst
For lngI = 1 To lngCount
If lngI <> 1 Then
If dblPrevValue < dblBoundary And DataLoggedRS("Reading") >
dblBoundary Then
NewTableRS.AddNew
NewTableRS("TimeStamp") = DataLoggedRS("TimeStamp")
NewTableRS("Reading") = DataLoggedRS("Reading")
NewTableRS.Update
End If
End If
dblPrevValue = DataLoggedRS("Reading")
If lngI <> lngCount Then DataLoggedRS.MoveNext
Next lngI
End If
DataLoggedRS.Close
Set DataLoggedRS = Nothing
NewTableRS.Close
Set NewTableRS = Nothing
Set MyDB = Nothing
End Sub

This code is completely untested but it does compile. I.e., to be safe
I'm calling it air code but I suspect that it will work as is. If it
works it should be much faster than the SQL I posted earlier. The
method for confirming the delete query is one I've never tried before.
One of the reasons for the huge difference in speed that I suspect will
occur is due to the code storing the previous value rather than having
SQL search the entire table for it. To run it use: Call
FillNewTable(1.45) Let me know how this turns out.

James A. Fortune
CDMAPoster@FortuneJames.com

One more thing. Perhaps put a MsgBox("Done.") before the End Sub so
that you'll know when the code has finished.

James A. Fortune
CDMAPoster@FortuneJames.com

Hi Mamilovic

Using two queries should solve your problem

SELECT Reading, TimeStamp FROM Table2 WHERE (((Reading)>[Target])) ORDER
BY Reading, TimeStamp;

SELECT TOP 2 Reading, TimeStamp FROM Query1;

MS Access - A legend in it's own time.

*** Sent via Developersdex http://www.developersdex.com ***

User-defined type not defined, Compile Error for "Dim MyDB As Database"
John

What is "table2" is it DataLogged?

"Select Top 2" there is more than 2 records that do this. Actual data
have 100,000 records with possibly 100's of them that meet my criteria.
John

Maybe I'm misunderstanding your problem.

You asked for:
I want to filter out all records above a certain value X and then only
the first record when the value goes from below to above X. Can you do
this in SQL / Sub query Help Please

You then gave the example below where the first two records ordered by
TIMESTAMP and where READING exceeds X are those you want to see.
[color=blue]
> TIMESTAMP READING
> 01/01/05 11:00:08 1.4
> 01/01/05 11:00:16 1.5
> 01/01/05 11:00:24 1.6
> 01/01/05 11:00:32 1.3
> 01/01/05 11:00:40 1.4
> 01/01/05 11:00:48 1.5
> 01/01/05 11:00:56 1.5
> 01/01/05 11:01:04 1.5
> 01/01/05 11:01:12 1.5[/color]

The reords which I want to see, if X was 1.45 would be[color=blue]
> 01/01/05 11:00:16 1.5
> 01/01/05 11:00:48 1.5[/color]

I then gave 2 queries which would produce exactly that. I've changed
Query1 slightly to be more clear.

1st Query (Query1, do not run this, save it as Query1)

SELECT Reading, TimeStamp FROM DataLogged WHERE (((Reading)>[X])) ORDER
BY Reading, TimeStamp;


2nd Query (when this query is run it will prompt you for X.)

SELECT TOP 2 Reading, TimeStamp FROM Query1;

If you only require the first record where READING exceeds X then change
SELECT TOP 2 to SELECT TOP 1

If you require something different then please give a more complete
example and I'll do what I can to help you.

MS Access - A legend in it's own time.

*** Sent via Developersdex http://www.developersdex.com ***

Mamilovic wrote:[color=blue]
> User-defined type not defined, Compile Error for "Dim MyDB As Database"
> John[/color]

This was written in A97 so perhaps move a DAO reference above the ADO
reference.

Also, google this group for "disambiguate" in case that's needed also.
I.e., Dim MyRS As DAO.Recordset.

James A. Fortune
CDMAPoster@FortuneJames.com

CDMAPos...@FortuneJames.com wrote:[color=blue]
> Mamilovic wrote:[color=green]
> > User-defined type not defined, Compile Error for "Dim MyDB As Database"
> > John[/color]
>
> This was written in A97 so perhaps move a DAO reference above the ADO
> reference.
>
> Also, google this group for "disambiguate" in case that's needed also.
> I.e., Dim MyRS As DAO.Recordset.
>
> James A. Fortune
> CDMAPoster@FortuneJames.com[/color]

If you use disambiguation you can leave the ADO reference above a DAO
reference. That appears to be safer. I don't have that problem since
the multi-version Access environment I have handles the references
correctly when the new versions perform the conversion.

James A. Fortune
CDMAPoster@FortuneJames.com

I have a logged value "reading", which goes up and down all the time.
I want to see the first instance it goes above a value X every times
the goes above X.

The sample I have presented is only a very small portion of the total
data.
I have 100,000 samples

Mamilovic wrote:[color=blue]
> I have a logged value "reading", which goes up and down all the time.
> I want to see the first instance it goes above a value X every times
> the goes above X.
>
> The sample I have presented is only a very small portion of the total
> data.
> I have 100,000 samples[/color]

I just created a table with 100,000 samples with two records meeting
the condition out of every 10. I ran the code I posted using A97. The
function finished creating the 20,000 records in under two seconds.
You can decide if that is sufficient motivation to pursue a VBA
solution after testing the solution Steve comes up with.

James A. Fortune
CDMAPoster@FortuneJames.com

Sorry, I do not understand what is DAO and ADO
John

Mamilovic wrote:[color=blue]
> Sorry, I do not understand what is DAO and ADO
> John
>[/color]

In the VBA editor, goto Tools->References, and in the list, find and
select "Microsoft Data Access Objects 3.60 Library".

Then, instead of this:

dim db as database

do this:

dim db as dao.database

....because when you reference some of the other DAO classes that have
the same class name as the ADO class name, you'll run into some name
collisions unless you (unwisely) unreference ADO.

The solution from James A. Fortune is perfect. I unfortunately did not
understand your problem initially. A solution using SQL is near
impossible since SQL provides no way to view the previous record.

To compile the code which he supplied you need the following references
in Acc97:
Visual Basic For Applications
Microsoft Access 8.0 Object Library
Microsoft DAO 3.51 Object Library
usually located in C:\Program Files\Common Files\Microsoft
Shared\DAO\DAO350.dll

In Acc2K you need the following:
Visual Basic For Applications
Microsoft Access 9.0 Object Library
Microsoft DAO 3.6 Object Library
usually located in C:\Program Files\Common Files\Microsoft
Shared\DAO\DAO360.dll

To view / select references design any module and choose Tools,
References

MS Access - A legend in it's own time.

*** Sent via Developersdex http://www.developersdex.com ***

Thanks for ypur help

It complies! Thanks. The only issue I have now is how do I call the
routine.
John

Mamilovic wrote:[color=blue]
> It complies! Thanks. The only issue I have now is how do I call the
> routine.
> John[/color]

Here's what I used:

Private Sub cmdGetResults_Click()
Call FillNewTable(1.45)
End Sub

Note that I cheated a little to get it to run on 100,000 records in
under two seconds. I used local tables on the hard drive instead of
linking to a backend.

James A. Fortune
CDMAPoster@FortuneJames.com

Count every "F" in the following text:

FINISHED FILES ARE THE RE
SULT OF YEARS OF SCIENTI
FIC STUDY COMBINED WITH
THE EXPERIENCE OF YEARS...

(ANSWER BELOW)


















6

Most people pass over the F's contained in "OF" in this example. --
David Smith

Run Time Error '13' Type Mismatch at
Set NewTableRS = MyDB.OpenRecordset(strSQL, dbOpenSnapshot)

Any idea
JOHN

I used:

TimeStamp Date/Time Format: mm/dd/yy hh:nn:ss Indexed, Yes (No
Duplicates)
Reading Number Double No Default Value Indexed, Yes
(Duplicates OK)

for both tables.

Do you have:

Dim NewTableRS As DAO.Recordset

James A. Fortune
CDMAPoster@FortuneJames.com

It works, thank you. You have been a great.
Thanks
John

have you recieved an answer yet?

BG.




*** Sent via Developersdex http://www.developersdex.com ***

It works. I don't have a problem any more
JM