Query to find minimum of two fields

  • Thread starter Thread starter Boon
  • Start date Start date
B

Boon

Hello,

I have a table with 3 fields - Name, Lap1, Lap2.

I want to create a query that will shows the result with 2 fields. The 2
fields are Name and Best_Lap. The Best_Lap is the minimum between Lap1 and
Lap2. How can I do this?

I know I can use iif(). But if I have more fields (Lap1,...Lap10), it might
be too complex to use IIf. I am thinking about using Min function but it
didn't work.

Your help is very much appreciated.

Thanks,
Boon
 
See the responses to the "Create query against multiple date fields" post
just below. You have the same problem with the structure of your table. In a
nutshell instead of going across, like Lap1, Lap2, etc., you need to go down
like:

NameID TheLap TheTime
1 1 53
1 2 55
1 3 52

Actually you'll probably need a table of Racers, a table of Races, and a
table of Laps all joined together properly unless this database is just for a
one time event.
 
Boon said:
Hello,

I have a table with 3 fields - Name, Lap1, Lap2.

I want to create a query that will shows the result with 2 fields. The 2
fields are Name and Best_Lap. The Best_Lap is the minimum between Lap1 and
Lap2. How can I do this?

I know I can use iif(). But if I have more fields (Lap1,...Lap10), it might
be too complex to use IIf. I am thinking about using Min function but it
didn't work.
Click to expand...


I use a custom function to get the lowest value in a list:

Public Function MinOfList(ParamArray vValues() As Variant)
As Variant
Dim vX As Variant

MinOfList = vValues(0)
For Each vX In vValues
If vX <= Nz(MinOfList, vX) Then MinOfList = vX
Next vX

End Function

BUT, I have to warn you that your table violates the Rules
of Relational Database Normalization (Google it) and that's
why you have the problem you posted. If you had a separate
table for Laps with a foreign key to the table with the name
field and one lap time per record, then you could join the
two tables in a query and use the Min aggregate function to
get the result far more efficiently with less hassle.
 
thanks for all responses. I will try.
Marshall Barton said:
I use a custom function to get the lowest value in a list:

Public Function MinOfList(ParamArray vValues() As Variant)
As Variant
Dim vX As Variant

MinOfList = vValues(0)
For Each vX In vValues
If vX <= Nz(MinOfList, vX) Then MinOfList = vX
Next vX

End Function

BUT, I have to warn you that your table violates the Rules
of Relational Database Normalization (Google it) and that's
why you have the problem you posted. If you had a separate
table for Laps with a foreign key to the table with the name
field and one lap time per record, then you could join the
two tables in a query and use the Min aggregate function to
get the result far more efficiently with less hassle.
Click to expand...
 
Back
Top