getting minimum of calculated value

[jcook4now]

All,
I would like to have a query with a correlated subquery that will
give me the records associated with the minimum of a calculated value.
I am calculating the difference between the depth in one table
(Actual.Depth) and the depth in another (New.AbsDepth) by joining the
tables and using the calculation ABS(Actual.Depth-New.AbsDepth). My
goal is to get all the records in Actual associated with the minimum of
this calculation for a given EventID and AbsDepth in the table New.

The following query returns ALL the differences:
SELECT actual.eventID, new.absdepth, Actual.depth,
abs(Actual.depth-new.absdepth) AS Difference, actual.temp, actual.ph
FROM new, actual
WHERE new.EventID=actual.EventID;

I can then use a second query to get the records associated with the
minimum Difference. However, the query above becomes extremely slow
with large datasets and I'm assuming there is a way to use a
correllated subquery to accomplish the same goal.

Any ideas?

Jeff

 

[Michel Walsh]

Hi,

SELECT actual.eventID, MIN(ABS(actual.depth-new.absdepth)) as MinAbs
FROM actual INNER JOIN new ON new.EventID=actual.EventID
GROUP BY actual.eventID


use an inner join, rather than a where clause after a cross join, which
*may* be faster, mainly if the EventID columns are indexed. It also uses a
GROUP and aggregate to reach the minimum value.


Hoping it may help,
Vanderghast, Access MVP

 

[jcook4now]

Thanks - I'll give it a shot when I get back to my computer on
Wednesday. . .

 

[jcook4now]

Well, this would probably work if eventID were a key. Unfortunately,
the unique key for table actual is eventID+depth and the unique key for
table new is eventID+absdepth. . .

 

[Michel Walsh]

Hi,


so, it does not work because of... ?


Having eventID and MinAbs, you can then add MinAbs to actualDepth to find
the depth you are looking for as a match, if that is required.

As example, if eventID = 1010, MinAbs=10 and ActualDepth is 100, you are
looking for evenIT=1010 with a depth of either 100-10, either 100+10.

Sure, if the problem is somewhere else, that does not answer to your
(unformulated ?) question ...


Hoping it may help,
Vanderghast, Access MVP