Finding the FIRST local Minimum

[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!

 

[Tom Lake]

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!

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

 

[TC]

Please try not to multi-post

http://tc2.atspace.com/0012-UsingUsenet.htm#MultiPosting

HTH,
TC (MVP Access)
http://tc2.atspace.com

 

[Duane Hookom]

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.

 

[Guest]

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