Similar with DLookup function

[Mihail]

Hi all !

I have a table with this fields: (ID,a,b) where a,b - Double, b <> 0
Is there any way (without using additional query) to find the value in ID field where a/b has a maximum/minimum value ?

Thank you !

[Rabbit]

I'm not exactly sure what it is you're looking for. Your question is a little unclear. A more concise description along with sample data and results would help. Also, I don't see what this has to do with DLookup. It may have something to do with where you're trying to do this but you left out that information. Also, I don't understand why you can't use a query.

[Mihail]

Hello Rabbit, and thank you for the answer.

I can use a query. Actually I solve this by using query: (ID,DivideOpera tion:[a]/[b]). This query is sorted A-Z or Z-A as I need. Then I extract the first record ID.

I can do that, but I need a lot of queries to accomplish entire job.
Of course I can use the SQL instead a query, but from my view point is the same thing. More: I prefer the query not the SQL.

So, I am wonder if I can manage this problem using other way (a single function IF exist).

A single intersection point with DLookup: must return a value from a field (ID in my example) using the values from other fields (a,b).
"Bytes" ask me for a TITLE for my question and I can't find a better one. Sorry for that !

[Mihail]

Sorry, I forget your request for simple data:

ID   a   b
1   20   1.5
2   10   1.5
3   30   1.5
4   50   1.5
5   40   1.5

I am looking for a function that return
2 if I select MIN, because min(a/b) occur for 10/1.5 => ID=2
or
4 if I select MAX, because max(a/b) occur for 50/1.5 => ID=4

Yes: is as simple as you see, but I am looking for an Access function (if exist) to do that.

Thank you again.

[Rabbit]

Well, you could use a DLookup combined with a DMin or DMax to accomplish a similar thing. But that would be much slower than a query. And a query and SQL are the same thing, a query in the query editor is just a different way of looking at the SQL. In a query, you could do something like this:

SELECT *
FROM tableName
WHERE (a/b) = (
   SELECT MIN(a/b)
   FROM tableName
)

[Mihail]

I'll try.
Thank you for tip !
The speed is not a request for my database but the maintenance is.

[NeoPa]

DMin() and DMax() are Domain Aggregate functions, just as DLookup() is Mihail. It seems they are what you're looking for. There is a whole set of such functions that provide various ways to aggregate across a dataset.