473,326 Members | 2,104 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Access 2000

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
30 3501
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
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
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
Looking forward to your answer
John

Jan 25 '06 #5
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
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
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
I have converted the data from a DBF file format, at the moment there
is no index.
Any recommendations.
John

Jan 26 '06 #9
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
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
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
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
User-defined type not defined, Compile Error for "Dim MyDB As Database"
John

Jan 30 '06 #14
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
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
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
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
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
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
Sorry, I do not understand what is DAO and ADO
John

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

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
Thanks for ypur help

Jan 31 '06 #24
It complies! Thanks. The only issue I have now is how do I call the
routine.
John

Jan 31 '06 #25
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
Run Time Error '13' Type Mismatch at
Set NewTableRS = MyDB.OpenRecordset(strSQL, dbOpenSnapshot)

Any idea
JOHN

Feb 1 '06 #27
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
It works, thank you. You have been a great.
Thanks
John

Feb 1 '06 #29
have you recieved an answer yet?

BG.


*** Sent via Developersdex http://www.developersdex.com ***
Feb 2 '06 #30
It works. I don't have a problem any more
JM

Feb 3 '06 #31

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

17
by: chicha | last post by:
Hey people, I have to convert MS Access 2000 database into mysql database, the whole thing being part of this project I'm doing for one of my faculty classes. My professor somehow presumed I...
2
by: Scott | last post by:
Any help would be grateful :-) Problem: When I test my installation of Access 2000 MDE, I get the following error: "Unrecognised Format".. This means that Access 97 cannot read Access 2000. ...
3
by: Pat | last post by:
We have a database with Access 97 as the front-end and SQL7 as the back end. It is on a network server and accessed by joining a MS Access Workgroup. We are trying to add a new user and have...
3
by: Scott | last post by:
Hi, If we want to compile an Access 2002 database and distribute it to others, will the compiled software run on any PC, like Windows 98, Windows 2000, etc. Also, you don't have to have...
6
by: Serious_Practitioner | last post by:
....or lack thereof... I use Access 2000 which I own as part of Office 2000 Professional. I've had enough trouble with glitches to ask if these are common occurrences - 1. Sometimes the "Help"...
2
by: Jeff | last post by:
Does anyone know of any potential problems running a 2000 database with 2003? Also, what about installing all other Office products as 2003 versions but leaving Access as 2002 running a 2000...
47
by: ship | last post by:
Hi We need some advice: We are thinking of upgrading our Access database from Access 2000 to Access 2004. How stable is MS Office 2003? (particularly Access 2003). We are just a small...
0
by: com | last post by:
MS Access 2000 Password Recoverer 4.2 Screenshot - Soft30.com MS Access 2000 Password Recoverer will display the password to a MS Access database (*.mdb). This program works for MS Access files...
5
by: Mo | last post by:
Hello all, I'm trying to set up an access 2000 .adp project connecting to a SQL server 2005 database. I can set up the connection ok, but once I have completed the setup process, I get the...
3
by: NEWSGROUPS | last post by:
I am in the midst of trying to convert about 25 Access 2000 to Access 2003. The new environment consists of Office/Access 2003 and Outlook 2003. When converting the back ends I have no problems....
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.