How to find Minimum of four fields

[galsaba]

I have a table with four field (column) what wil be the way to find the
minimum?
does access provide a function to do so?
does MS VBS provides?
thanks

galsaba

 

[Jeff Boyce]

If you are trying to find a minimum value across four fields, it sounds like
you have a spreadsheet. Have you tried using Excel?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[galsaba]

If I tried Excel? I did not becuase I know it will work...
I need it in access as part of big application with table relations and
other features that Excel does not have.

I need it in Access

Thanks

galsaba

 

[John Spencer]

If you need to do this in a query and cannot fix your data structure you can
use the following function. Paste this into a VBA module and you can call
it in a query (or anywhere else).

Field: Smallest: fRowMin([TableName].[FieldA], [TableName].[Fieldb],
[TableName].[FieldC], [TableName].[FieldD])

That will return the smallest value in the four fields. In a query, you
could check up to 39 values. You can check many more than that if you
aren't doing this within a query.

(Even in a query, you can do more than 39, by nesting the function-
fRowMin(fRowMin(....),fRowMin(...)) would allow you to compare 77 fields -
if you didn't run into the max length for an expression in SQL.

Public Function fRowMin(ParamArray Values()) As Variant
'John Spencer UMBC CHPDM
'Last Update: April 5, 2000
'Returns the minimum Number of a group of values passed to it.
'Sample call: myMin = GetMinNumber("-21","TEST","2","3",4,5,6,"7",0)
' returns -21
'Ignores values that cannot be treated as numbers.
Dim i As Integer, vMin As Variant
Dim tfFound As Boolean, dblCompare As Double

vMin = 1E+308 'very large positive number
For i = LBound(Values) To UBound(Values)

If IsNumeric(Values(i)) Then
dblCompare = CDbl(Values(i))
If dblCompare < vMin Then
vMin = dblCompare
tfFound = True
End If
End If
Next

If tfFound Then
fRowMin = vMin
Else
fRowMin = Null
End If

End Function

 

[David F Cox]

When people start talking about minimums of four fields it is often an
indication that the design is not normalised. This is often called
"commiting spreadsheet". In a properly designed database the function Min()
would make this task easy.
One way:
lowest: IIF([a]<=, IIF([a]<=[c], IIF([a]<=[d], [a],[d]),IIF([c] <= [d],
[c], [d]) ......etc

another is a VB function

 

[Jeff Boyce]

As others have pointed out, your data design is not optimized for what
Access can do. If you feed Access 'sheet data, you won't get the best of
its features and functions.

If you provide a bit more detail about what you are storing in those four
fields, the newsgroup readers may be able to offer suggestions about
improving the table design (and hence be better able to use Access'
strengths).

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[galsaba]

As others have pointed out, your data design is not optimized for what
Access can do. If you feed Access 'sheet data, you won't get the best of
its features and functions.

If you provide a bit more detail about what you are storing in those four
fields, the newsgroup readers may be able to offer suggestions about
improving the table design (and hence be better able to use Access'
strengths).

Regards

Jeff Boyce
Microsoft Office/Access MVP

say i have a store , and I have 4 option of how to seel an item. web,
phone, store, show.
each one will have adifferent cost of sale for me, based on many
inputs. there is a list of items, that's ehy i think the "my cost
selling by web", "cost selling by phone", etc should be columns, and
the items are the rows.

galsaba

 

[Douglas J. Steele]

What happens if you suddenly have a 5th option? You'd have to redo your
table, all the forms that use that table, and so on.

Having 4 separate rows is definitely the correct approach. It represents the
resolution of the many-to-many relationship between the Item table and the
MethodOfSale table.

 

[galsaba]

What happens if you suddenly have a 5th option? You'd have to redo your
table, all the forms that use that table, and so on.

Having 4 separate rows is definitely the correct approach. It represents the
resolution of the many-to-many relationship between the Item table and the
MethodOfSale table.

Sounds good, but I really dont know how to do it. How should I convert
columns to rows?
galsaba

 

[Douglas J. Steele]

Let's assume you currently have a table with Id, Field1, Field2, WebCost,
PhoneCost, StoreCost, and ShowCost.

Create a UNION query along the following lines:

SELECT Id, Field1, Field2, "WebCost" AS CostType, WebCost AS CostAmount
FROM MyTable
UNION
SELECT Id, Field1, Field2, "PhoneCost" AS CostType, PhoneCost AS CostAmount
FROM MyTable
UNION
SELECT Id, Field1, Field2, "StoreCost" AS CostType, StoreCost AS CostAmount
FROM MyTable
UNION
SELECT Id, Field1, Field2, "ShowCost" AS CostType, ShowCost AS CostAmount
FROM MyTable

Use that query to populate the properly normalized table.

 

[galsaba]

Let's assume you currently have a table with Id, Field1, Field2, WebCost,
PhoneCost, StoreCost, and ShowCost.

Create a UNION query along the following lines:

SELECT Id, Field1, Field2, "WebCost" AS CostType, WebCost AS CostAmount
FROM MyTable
UNION
SELECT Id, Field1, Field2, "PhoneCost" AS CostType, PhoneCost AS CostAmount
FROM MyTable
UNION
SELECT Id, Field1, Field2, "StoreCost" AS CostType, StoreCost AS CostAmount
FROM MyTable
UNION
SELECT Id, Field1, Field2, "ShowCost" AS CostType, ShowCost AS CostAmount
FROM MyTable

Use that query to populate the properly normalized table.

This is BEATIFUL! It works! (although I dont exactly understand what I
did...)
is there a way to do it with "Design View"? or just "SQL View"?

Thanks!

galsaba

 

[Douglas J. Steele]

Union queries can only be made in the SQL view.