Calculate minimum of fields

[asu81]

Hi!

It's possible to calculate fieldA*fieldB in a new query field. It's also possible to compare these fields with IIF().

But why is it not possible to calculate the min/max of a set of fields by using a similar min() expression?

Thx

 

[plog]

Could you post some sample data from your table along with what you would like returned from your query based on that sample data? Actually, let's use mine:

TableName
a, b
1, 10
2, 9
3, 8
4, 7
5, 6

When I run this query: SELECT Min([a])*Min() AS Tot FROM TableName;

I get 6.

when I run this query: SELECT Min([a]*) AS Tot From TableName;

I get 10.

What are you trying to get exactly?

 

[asu81]

Could you post some sample data from your table along with what you would like returned from your query based on that sample data? Actually, let's use mine:

TableName
a, b
1, 10
2, 9
3, 8
4, 7
5, 6

When I run this query: SELECT Min([a])*Min() AS Tot FROM TableName;

I get 6.

when I run this query: SELECT Min([a]*) AS Tot From TableName;

I get 10.

What are you trying to get exactly?

Thanks for your answer!

I want an expression that calculates the minumum of a and b. In your example the result would be
1
2
3
4
5

I'm aware that it's possible to achieve this with nested IIF() expressions, but wouldn't a simple min() function been easier?

If the MIN keyword is restricted for comparison among values in the same field they could name it minfield() or whatever?

 

[plog]

nested IIF() expressions

If its only 2 fields it wouldn't be nested IIF() statements it would be just one. If its more than 3 fields, I'd write my own function:

MinVal: getMinimum(a, b, c, d, ...)

However, this question is making me start to hear a faint whisper of an improperly structured table. Why would you need to compare data within a record? Let's quit talking general and talk specific--what's this data exactly? Field names/examples.

 

[asu81]

If its only 2 fields it wouldn't be nested IIF() statements it would be just one. If its more than 3 fields, I'd write my own function:

MinVal: getMinimum(a, b, c, d, ...)

However, this question is making me start to hear a faint whisper of an improperly structured table. Why would you need to compare data within a record? Let's quit talking general and talk specific--what's this data exactly? Field names/examples.

I've got a project database where each project is a record. These projects have many parts (fields) that may be in different stages (A1, B2 etc). I want a field that calculates the least progressed stage in each project (record).