By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,476 Members | 1,529 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,476 IT Pros & Developers. It's quick & easy.

Access 2000

P: n/a
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

Jan 24 '06 #1
Share this Question
Share on Google+
30 Replies


P: n/a
Mamilovic wrote:
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


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
CD********@FortuneJames.com

Jan 24 '06 #2

P: n/a
Thanks for replying
Table name is DataLogged and sample data is shown below.
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
The reords which I want to see, if X was 1.45 would be 01/01/05 11:00:16 1.5
01/01/05 11:00:48 1.5

John

Jan 24 '06 #3

P: n/a
Mamilovic wrote:
Thanks for replying
Table name is DataLogged and sample data is shown below.
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


The reords which I want to see, if X was 1.45 would be
01/01/05 11:00:16 1.5
01/01/05 11:00:48 1.5

John


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
CD********@FortuneJames.com

Jan 24 '06 #4

P: n/a
Looking forward to your answer
John

Jan 25 '06 #5

P: n/a
Mamilovic wrote:
Looking forward to your answer
John


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
CD********@FortuneJames.com

Jan 25 '06 #6

P: n/a
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

Jan 26 '06 #7

P: n/a
Mamilovic wrote:
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


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
CD********@FortuneJames.com

Jan 26 '06 #8

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

Jan 26 '06 #9

P: n/a
Mamilovic wrote:
I have converted the data from a DBF file format, at the moment there
is no index.
Any recommendations.
John


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
CD********@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

Jan 27 '06 #10

P: n/a
CD********@FortuneJames.com wrote:
Mamilovic wrote:
I have converted the data from a DBF file format, at the moment there
is no index.
Any recommendations.
John


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
CD********@FortuneJames.com


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
CD********@FortuneJames.com

Jan 29 '06 #11

P: n/a
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
CD********@FortuneJames.com

Jan 29 '06 #12

P: n/a
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 ***
Jan 29 '06 #13

P: n/a
User-defined type not defined, Compile Error for "Dim MyDB As Database"
John

Jan 30 '06 #14

P: n/a
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

Jan 30 '06 #15

P: n/a
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.
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
The reords which I want to see, if X was 1.45 would be 01/01/05 11:00:16 1.5
01/01/05 11:00:48 1.5


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 ***
Jan 30 '06 #16

P: n/a
Mamilovic wrote:
User-defined type not defined, Compile Error for "Dim MyDB As Database"
John


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
CD********@FortuneJames.com

Jan 30 '06 #17

P: n/a
CDMAPos...@FortuneJames.com wrote:
Mamilovic wrote:
User-defined type not defined, Compile Error for "Dim MyDB As Database"
John


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
CD********@FortuneJames.com


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
CD********@FortuneJames.com

Jan 30 '06 #18

P: n/a
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

Jan 31 '06 #19

P: n/a
Mamilovic wrote:
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


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
CD********@FortuneJames.com

Jan 31 '06 #20

P: n/a
Sorry, I do not understand what is DAO and ADO
John

Jan 31 '06 #21

P: n/a
Mamilovic wrote:
Sorry, I do not understand what is DAO and ADO
John


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.

Jan 31 '06 #22

P: n/a

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 ***
Jan 31 '06 #23

P: n/a
Thanks for ypur help

Jan 31 '06 #24

P: n/a
It complies! Thanks. The only issue I have now is how do I call the
routine.
John

Jan 31 '06 #25

P: n/a
Mamilovic wrote:
It complies! Thanks. The only issue I have now is how do I call the
routine.
John


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
CD********@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

Jan 31 '06 #26

P: n/a
Run Time Error '13' Type Mismatch at
Set NewTableRS = MyDB.OpenRecordset(strSQL, dbOpenSnapshot)

Any idea
JOHN

Feb 1 '06 #27

P: n/a
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
CD********@FortuneJames.com

Feb 1 '06 #28

P: n/a
It works, thank you. You have been a great.
Thanks
John

Feb 1 '06 #29

P: n/a
have you recieved an answer yet?

BG.


*** Sent via Developersdex http://www.developersdex.com ***
Feb 2 '06 #30

P: n/a
It works. I don't have a problem any more
JM

Feb 3 '06 #31

This discussion thread is closed

Replies have been disabled for this discussion.