Query to find minimum of two fields

[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

 

[Jerry Whittle]

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.

 

[Marshall Barton]

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.

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.

 

[Boon]

thanks for all responses. I will try.

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.