Minimum value from 4 fields

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

Guest

I have two tables, with a one to many relationship. I would like to find the
lowest value from four fields within the "many" table for one record of the
"one" table. i.e. the lowest value of the following four values:
dmin(field1), dmin(field2), dmin(field3), dmin(field4). Any ideas? Thanks.
 
Dave said:
I have two tables, with a one to many relationship. I would like to find the
lowest value from four fields within the "many" table for one record of the
"one" table. i.e. the lowest value of the following four values:
dmin(field1), dmin(field2), dmin(field3), dmin(field4).
Click to expand...


SELECT onetable.PK,
Min(manytable.field1) As Min1,
Min(manytable.field2) As Min2,
Min(manytable.field3) As Min3,
Min(manytable.field4) As Min4
FROM onetable INNER JOIN manytable
ON onetable.PK = manytable.FK
GROUP BY onetable.PK
 
Thanks. I forgot to mention the fields can contain null values...will this
code still work?
 
I am a little confused...is "pk" the primary key field? How about "fk"? How
will I assign the result (minimum value) to a variable or control. I only
need the minimum from the four fields for the one record of the primary table
I am currently looking at. Thanks for the help.

Marshall Barton said:
All of the Aggregate functions,except Count(*), ignore Null
values.
--
Marsh
MVP [MS Access]


Thanks. I forgot to mention the fields can contain null values...will this
code still work?
Click to expand...
Click to expand...
 
Yes, PK ist the one table's Primary Key. FK is the many
table's Foreign Key field that links back to the one table.

Since you only want to retrieve the minimums for a single PK
value, then the query will be simpler or you could use
another approach.

You can open a recordset on the SQL statement to retrieve
the set of minimums in one operation.

Dim rs As Recordset
strSQL="SELECT Min(field1) As Min1,Min(field2) As Min2," _
& "Min(field3) As Min3,Min(field4) As Min4 " _
& "FROM manytable " _
& "WHERE FK=" & me.txtPK
Set rs = CurrentDb.OpenRecordset(strSQL)
Me.txtMin1 = rs!Min1
Me.txtMin2 = rs!Min2
Me.txtMin3 = rs!Min3
Me.txtMin4 = rs!Min4
Set rs = Nothing

Alternatively, you could use the slower approach of four
operations:

Me.txtMin1 = DMin("field1", "manytable", "FK=" & me.txtPK)
Me.txtMin2 = DMin("field2", "manytable", "FK=" & me.txtPK)
Me.txtMin3 = DMin("field3", "manytable", "FK=" & me.txtPK)
Me.txtMin4 = DMin("field4", "manytable", "FK=" & me.txtPK)
 
I understand now. Thanks again for the help!

Marshall Barton said:
Yes, PK ist the one table's Primary Key. FK is the many
table's Foreign Key field that links back to the one table.

Since you only want to retrieve the minimums for a single PK
value, then the query will be simpler or you could use
another approach.

You can open a recordset on the SQL statement to retrieve
the set of minimums in one operation.

Dim rs As Recordset
strSQL="SELECT Min(field1) As Min1,Min(field2) As Min2," _
& "Min(field3) As Min3,Min(field4) As Min4 " _
& "FROM manytable " _
& "WHERE FK=" & me.txtPK
Set rs = CurrentDb.OpenRecordset(strSQL)
Me.txtMin1 = rs!Min1
Me.txtMin2 = rs!Min2
Me.txtMin3 = rs!Min3
Me.txtMin4 = rs!Min4
Set rs = Nothing

Alternatively, you could use the slower approach of four
operations:

Me.txtMin1 = DMin("field1", "manytable", "FK=" & me.txtPK)
Me.txtMin2 = DMin("field2", "manytable", "FK=" & me.txtPK)
Me.txtMin3 = DMin("field3", "manytable", "FK=" & me.txtPK)
Me.txtMin4 = DMin("field4", "manytable", "FK=" & me.txtPK)
--
Marsh
MVP [MS Access]


I am a little confused...is "pk" the primary key field? How about "fk"? How
will I assign the result (minimum value) to a variable or control. I only
need the minimum from the four fields for the one record of the primary table
I am currently looking at.
Click to expand...
Click to expand...
 
I am doing almost the same thing, but only have one table. How do I find the
lowest value of 4 fields in the same record?

Thanks

Marshall Barton said:
Yes, PK ist the one table's Primary Key. FK is the many
table's Foreign Key field that links back to the one table.

Since you only want to retrieve the minimums for a single PK
value, then the query will be simpler or you could use
another approach.

You can open a recordset on the SQL statement to retrieve
the set of minimums in one operation.

Dim rs As Recordset
strSQL="SELECT Min(field1) As Min1,Min(field2) As Min2," _
& "Min(field3) As Min3,Min(field4) As Min4 " _
& "FROM manytable " _
& "WHERE FK=" & me.txtPK
Set rs = CurrentDb.OpenRecordset(strSQL)
Me.txtMin1 = rs!Min1
Me.txtMin2 = rs!Min2
Me.txtMin3 = rs!Min3
Me.txtMin4 = rs!Min4
Set rs = Nothing

Alternatively, you could use the slower approach of four
operations:

Me.txtMin1 = DMin("field1", "manytable", "FK=" & me.txtPK)
Me.txtMin2 = DMin("field2", "manytable", "FK=" & me.txtPK)
Me.txtMin3 = DMin("field3", "manytable", "FK=" & me.txtPK)
Me.txtMin4 = DMin("field4", "manytable", "FK=" & me.txtPK)
--
Marsh
MVP [MS Access]


I am a little confused...is "pk" the primary key field? How about "fk"? How
will I assign the result (minimum value) to a variable or control. I only
need the minimum from the four fields for the one record of the primary table
I am currently looking at.
Click to expand...
Click to expand...
 
I am doing almost the same thing, but only have one table. How do I find the
lowest value of 4 fields in the same record?
Click to expand...

Preferably, you redesign your tables so that you don't have repeating
fields in a single record.

Failing that, you'll need either a narsty complex nested IIF, or some
VBA code: here's some untested air code:

Public Function Least(curIn() As Currency) As Currency
Dim iPos As Integer
Least = 1000000000000000
For iPos = 0 to UBound curIn
If curIn(iPos) < Least Then
Least = curIn(iPos)
End If
Next iPos
End Function

Call it using

Least([field1], [field2], [field3], [field4])

in a query.

This assumes currency datatype and that you'll not have all your
values greater than a trillion. You'll need to change the datatype ant
the largest value of other datatypes.

John W. Vinson[MVP]
 
Back
Top