finding lowest value

A

Allan Jackson

Hello
I hope that someone may be able to assist.
I have a table that contains numerous fields, including 30 fields labelled
time1, time2, time3.........time30.
Each field can contain a time value (hh:nn:ss) but it is not necessary for
each field to have a value. That is, each record can have various times
entered. eg record 1 can have a time in field 1 up to 20, record 2 can have
a value in field 1 up to 15 and so on. The only rule that applies is that
there can only be a maximum of 30 time entries per record.
My problem is, I must be able to locate the lowest time value in each
record. I have tried nested "iff" statements but as you can gather this
become extremely cumbersome and with the limitations in access, it becomes
to complex for access to handle. Does anyone know of another way to achieve
a way of finding the lowest time value.Bearing in mind, I have limited
knowledge of SQL.
I hope that this makes sense.
Many Thanks
Regards
Allan
 
D

Douglas J. Steele

Unfortunately, it sounds as though your database hasn't been property
normalized. Having fields named time1, time2, time3, etc. is usually a sign
of having a repeating field. In general, you should have a second table, so
that you'd have up to 30 rows in the 2nd table linked to your one row in
your existing table. Then it becomes a simple SELECT Min(TimeField) FROM
My2ndTable

If you're stuck with that design, you could try writing a function. However,
I can't remember whether you can have 30 fields as a parameter. Try
something like the following untested air code:

Function LowestValue(TimeArray As Variant) As Date

Dim dtmLowestTime As Date
Dim intLoop As Integer

dtmLowestTime = TimeArray(1)
For intLoop = 2 To 30
If IsNull(TimeArray(intLoop)) = False Then
If TimeArray(intLoop) < dtmLowestTime Then
dtmLowestTime = TimeArray(intLoop)
End If
Else
Exit For
End If
Next intLoop

End Function

You'd call this in your query as

SELECT Field1, Field2,
LowestValue(Array([Time1], [Time2], ..., [Time30])) AS EarliestDate
FROM MyTable
 
B

Brian

Allan Jackson said:
Hello
I hope that someone may be able to assist.
I have a table that contains numerous fields, including 30 fields labelled
time1, time2, time3.........time30.
Each field can contain a time value (hh:nn:ss) but it is not necessary for
each field to have a value. That is, each record can have various times
entered. eg record 1 can have a time in field 1 up to 20, record 2 can have
a value in field 1 up to 15 and so on. The only rule that applies is that
there can only be a maximum of 30 time entries per record.
My problem is, I must be able to locate the lowest time value in each
record. I have tried nested "iff" statements but as you can gather this
become extremely cumbersome and with the limitations in access, it becomes
to complex for access to handle. Does anyone know of another way to achieve
a way of finding the lowest time value.Bearing in mind, I have limited
knowledge of SQL.
I hope that this makes sense.
Many Thanks
Regards
Allan
Click to expand...

This sounds like a moderately disastrous design. Assuming that your table
is called MyTable, with a primary key of MyKey, then if you had the
following tables:

MyTable
======
MyKey


Times
====
TimeID
MyKey
TimeValue


where each time had it's own row in the Times table, then the following
simple query would give you the lowest one for each MyKey:

SELECT MyKey, Min(TimeValue) FROM Times GROUP BY MyKey
 
A

Allan Jackson

Thanks Douglas,
Will a second table assist or am I missing something? Each time is
associated to one date, one track and one session. On each day, it is
possible to complete 7 sessions with a total of 30 times per session. My
main table structure is
Track...text
Date...date format
Session....text format
lap1 time...time format
lap 2 time...time format
..
..
..
lap 30 time...time format
plus numerous other fields that need to be filled in.

How could I remove the lap times from the main table and put them into a
second table and still have it linked to the other data?

I hope that this explains the problem.

Furthermore, with your suggested code, where would I place this code?

Many Thanks
Allan



Douglas J. Steele said:
Unfortunately, it sounds as though your database hasn't been property
normalized. Having fields named time1, time2, time3, etc. is usually a
sign of having a repeating field. In general, you should have a second
table, so that you'd have up to 30 rows in the 2nd table linked to your
one row in your existing table. Then it becomes a simple SELECT
Min(TimeField) FROM My2ndTable

If you're stuck with that design, you could try writing a function.
However, I can't remember whether you can have 30 fields as a parameter.
Try something like the following untested air code:

Function LowestValue(TimeArray As Variant) As Date

Dim dtmLowestTime As Date
Dim intLoop As Integer

dtmLowestTime = TimeArray(1)
For intLoop = 2 To 30
If IsNull(TimeArray(intLoop)) = False Then
If TimeArray(intLoop) < dtmLowestTime Then
dtmLowestTime = TimeArray(intLoop)
End If
Else
Exit For
End If
Next intLoop

End Function

You'd call this in your query as

SELECT Field1, Field2,
LowestValue(Array([Time1], [Time2], ..., [Time30])) AS EarliestDate
FROM MyTable

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Allan Jackson said:
Hello
I hope that someone may be able to assist.
I have a table that contains numerous fields, including 30 fields
labelled time1, time2, time3.........time30.
Each field can contain a time value (hh:nn:ss) but it is not necessary
for each field to have a value. That is, each record can have various
times entered. eg record 1 can have a time in field 1 up to 20, record 2
can have a value in field 1 up to 15 and so on. The only rule that
applies is that there can only be a maximum of 30 time entries per
record.
My problem is, I must be able to locate the lowest time value in each
record. I have tried nested "iff" statements but as you can gather this
become extremely cumbersome and with the limitations in access, it
becomes to complex for access to handle. Does anyone know of another way
to achieve a way of finding the lowest time value.Bearing in mind, I have
limited knowledge of SQL.
I hope that this makes sense.
Many Thanks
Regards
Allan
Click to expand...
Click to expand...
 
D

Douglas J. Steele

Yes, a second table will make it much simpler. See Brian's response for one
way to restructure your tables. In general, the primary key for your second
table would be all of the fields that make up the primary key plus a time
number, so that if you're currently using Track, Date and Session as the
Primary Key, your second table would use Track Date, Session and TimeNumber
as its Primary Key. (BTW, using Date as a field name is not a good idea:
it's a reserved word, and using reserved words can lead to all sorts of
trouble.)

In terms of the function I provided, you'd copy that code into a module in
your application. Make sure you do not name the module the same as the
function.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Allan Jackson said:
Thanks Douglas,
Will a second table assist or am I missing something? Each time is
associated to one date, one track and one session. On each day, it is
possible to complete 7 sessions with a total of 30 times per session. My
main table structure is
Track...text
Date...date format
Session....text format
lap1 time...time format
lap 2 time...time format
.
.
.
lap 30 time...time format
plus numerous other fields that need to be filled in.

How could I remove the lap times from the main table and put them into a
second table and still have it linked to the other data?

I hope that this explains the problem.

Furthermore, with your suggested code, where would I place this code?

Many Thanks
Allan



Douglas J. Steele said:
Unfortunately, it sounds as though your database hasn't been property
normalized. Having fields named time1, time2, time3, etc. is usually a
sign of having a repeating field. In general, you should have a second
table, so that you'd have up to 30 rows in the 2nd table linked to your
one row in your existing table. Then it becomes a simple SELECT
Min(TimeField) FROM My2ndTable

If you're stuck with that design, you could try writing a function.
However, I can't remember whether you can have 30 fields as a parameter.
Try something like the following untested air code:

Function LowestValue(TimeArray As Variant) As Date

Dim dtmLowestTime As Date
Dim intLoop As Integer

dtmLowestTime = TimeArray(1)
For intLoop = 2 To 30
If IsNull(TimeArray(intLoop)) = False Then
If TimeArray(intLoop) < dtmLowestTime Then
dtmLowestTime = TimeArray(intLoop)
End If
Else
Exit For
End If
Next intLoop

End Function

You'd call this in your query as

SELECT Field1, Field2,
LowestValue(Array([Time1], [Time2], ..., [Time30])) AS EarliestDate
FROM MyTable

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Allan Jackson said:
Hello
I hope that someone may be able to assist.
I have a table that contains numerous fields, including 30 fields
labelled time1, time2, time3.........time30.
Each field can contain a time value (hh:nn:ss) but it is not necessary
for each field to have a value. That is, each record can have various
times entered. eg record 1 can have a time in field 1 up to 20, record 2
can have a value in field 1 up to 15 and so on. The only rule that
applies is that there can only be a maximum of 30 time entries per
record.
My problem is, I must be able to locate the lowest time value in each
record. I have tried nested "iff" statements but as you can gather this
become extremely cumbersome and with the limitations in access, it
becomes to complex for access to handle. Does anyone know of another way
to achieve a way of finding the lowest time value.Bearing in mind, I
have limited knowledge of SQL.
I hope that this makes sense.
Many Thanks
Regards
Allan
Click to expand...
Click to expand...
Click to expand...
 
A

Allan Jackson

Brian,
Many thanks for your suggestion.
Does this mean that
for every time entry I must cross-reference it to the main table by entering
the
unique ID "MyKey" which auto generated for each new race/session.
I thought that it was easier to enter data in one table via a form as on
each day it is possible to complete 7 sessions with a total of 30 times per
session. Will a
second table assist with data entry as for every time I will need to enter
the "MyKey" value, or am I missing something.
Please excuse my ignorance with "access" as I am self taught and only
relatively new to working with databases.

Kind Regards
Allan
 
B

Brian

Allan Jackson said:
Brian,
Many thanks for your suggestion.
Does this mean that
for every time entry I must cross-reference it to the main table by entering
the
unique ID "MyKey" which auto generated for each new race/session.
I thought that it was easier to enter data in one table via a form as on
each day it is possible to complete 7 sessions with a total of 30 times per
session. Will a
second table assist with data entry as for every time I will need to enter
the "MyKey" value, or am I missing something.
Please excuse my ignorance with "access" as I am self taught and only
relatively new to working with databases.

Kind Regards
Allan
Click to expand...

If you have a form for "MyTable", which has on it a linked subform for
"Times", then Access will handle the common field MyKey for you (the
technical term for this field in the "Times" table is "foreign key")
 
A

Allan Jackson

Brian,
Thanks for the information. It's late here so I will attempt what you have
suggested in the morning.
Your time is very much appreciated.
Regards
Allan
 
Top