Finding the FIRST local Minimum

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am tracking a variable for different subjects over time. I want to find the
value and date of the FIRST LOCAL MINIMUM for each subject.

Here are some sample data:

ID --- Value ---- Date
=========================
1 ----- 9.0 ----- 1/10/03
1 ----- 5.0 ----- 1/10/04
1 ----- 1.0 ----- 1/10/05
1 ----- 0.5 ----- 1/10/06
1 ----- 2.0 ----- 3/10/06
1 ----- 0.5 ----- 3/12/06
1 ----- 0.1 ----- 3/20/06

2 ----- 3.0 ----- 1/10/01
2 ----- 1.0 ----- 1/10/02
2 ----- 1.0 ----- 3/10/02
2 ----- 2.0 ----- 6/10/02
2 ----- 0.5 ----- 6/10/03

I want a query that will give the following results:

ID --- Value ---- Date
==========================
1 ----- 0.5 ----- 1/10/06
2 ----- 1.0 ----- 1/10/02

Can this be done with SQL alone, or is a recursive or array script needed?
I could write a script in PHP, but I'm trying to do this in a stand-alone
desktop Access database and need some help getting started with VBA scripting
if that's what needs to be done. Thanks!
 
LStegman said:
I am tracking a variable for different subjects over time. I want to find
the
value and date of the FIRST LOCAL MINIMUM for each subject.

Here are some sample data:

ID --- Value ---- Date
=========================
1 ----- 9.0 ----- 1/10/03
1 ----- 5.0 ----- 1/10/04
1 ----- 1.0 ----- 1/10/05
1 ----- 0.5 ----- 1/10/06
1 ----- 2.0 ----- 3/10/06
1 ----- 0.5 ----- 3/12/06
1 ----- 0.1 ----- 3/20/06

2 ----- 3.0 ----- 1/10/01
2 ----- 1.0 ----- 1/10/02
2 ----- 1.0 ----- 3/10/02
2 ----- 2.0 ----- 6/10/02
2 ----- 0.5 ----- 6/10/03

I want a query that will give the following results:

ID --- Value ---- Date
==========================
1 ----- 0.5 ----- 1/10/06
2 ----- 1.0 ----- 1/10/02

Can this be done with SQL alone, or is a recursive or array script needed?
I could write a script in PHP, but I'm trying to do this in a stand-alone
desktop Access database and need some help getting started with VBA
scripting
if that's what needs to be done. Thanks!
Click to expand...

Create a Totals Query from the table, group on ID and in the Value column's
Total row
use the Min function instead of the default Group By

Tom Lake
 
L Stegman,
Please don't post the same question in multiple news groups. Find one news
group and provide enough information.

Tom,
Your solution would return the minimum of all values by ID and doesn't
address the Date field. Apparently L wants some value other than the
minumum.
 
Sorry about the double-post. There is such a delay in posting to this board,
I thought that it hadn't gone through.

D.H. is right. Just the Min function doesn't work. I want the value and
date of the FIRST lowest value before a rise, even if there is a LOWER value
at a later time.

-LDS
 
Back
Top