Find Lowest Value Per Record

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

Guest

What kind of logic or function can I use to get a query to show me the last
sampling that occured for all zones (A-D) as a group? I have tried
subtracting the sample date from Now() and used the Dmin function but it will
only show me Zones A through C. Because 4 is the min from this data set but
I need it to tell me A =4 B=4 C=4 and D=6. Make sense? I am probably
missing something simple so please forgive me if this is a dumb question.

ZoneId ZoneName DaysSinceLastSamp SampDate
17 D 76 7/22/2007
13 C 76 7/22/2007
12 B 76 7/22/2007
11 A 76 7/22/2007
17 D 6 9/30/2007
13 C 6 9/30/2007
12 B 6 9/30/2007
11 A 6 9/30/2007
13 C 4 10/2/2007
12 B 4 10/2/2007
11 A 4 10/2/2007

Thanks for your help.
 
I finally found the answer in the posts here:
FYI for everyone who finds this post.
LastSampDate:DMin("[DaysSinceLastSamp]","qry1","[qry1]![ZoneId] =" &
[qry1]![ZoneId]) assuming ZoneId is a number if you use a string add a ' to
either side of the last [qry1]![ZoneId].

I really appreciate all who contribute to this site. It helps with what I
do immensley.
 
something like this?

SELECT Testdata.ZoneName, Last(Testdata.DaysSinceLastSamp) AS
LastOfDaysSinceLastSamp
FROM Testdata
GROUP BY Testdata.ZoneName;
 
something like this?

SELECT Testdata.ZoneName, Last(Testdata.DaysSinceLastSamp) AS
LastOfDaysSinceLastSamp
FROM Testdata
GROUP BY Testdata.ZoneName;
Click to expand...

Last() does not do what you think it does. It just returns what you expect
often enough to fool people into using it. It is mostly a useless function.
Max() is the proper substitute in most cases.

If you search these groups you will find numerous thread discussing the issue.
 
Back
Top