Need suggestions - I have a "duration" issue

[Guest]

I'm trying to construct a track meet database. I have 5 tables: tblMeet
(fields are MeetID, MeetDate, Event); tblEventDetail (fields are
EventDetailID, fkParticipant, fkMeetID, Minutes, Seconds, Feet, Inches);
tblEventLookup (fields are Event, Type, Type2); tblParticipants (fields are
Name, School, Gender); and tblSchoolLookup (field is School).

Field events will have "distance" results (feet, inches) and Track events
will have "time" results (minutes, seconds). Currently I'm converting Track
times as such: Time: (Nz([Minutes]*60)*100)+Nz([Seconds]*100). Fields
events are converted as: Distance: (Nz([Feet]*12)*10)+Nz([Inches]*10).


First question: do you see any problem in my structure so far? Any
suggestions?

Second question: should I keep re-entering these formulas on each form,
report, query, etc, or can I store calculations like this in a module?

Third question: I'll want to rank these records and assign a point value
based on the rank. I set up 2 queries to do this because in Track events the
lowest result wins, and in Field events the highest wins. I can get the
ranking to work, but I can't get a report to work based on either query.
Here's an example:

SELECT qryDistance1.EventDetailID, qryDistance1.MeetDate,
qryDistance1.fkParticipant, qryDistance1.Type, qryDistance1.School,
qryDistance1.Event, qryDistance1.Distance, (Select Count(*) from qryDistance
Where [Distance]+1 > qryDistance1.[Distance] AS DistanceRank
FROM qryDistance AS qryDistance1
ORDER BY qryDistance1.Distance DESC;

I get the "multi level group by clause is not allowed in a subquery" error,
because I'm trying to group the results on MeetDate and then Event.

If I can get this to work, then how can I combine my Time and Distance
queries as the basis for a report without the dreaded cartesian product?

Thanks for your time.

 

[Edward Reid]

EventDetailID, fkParticipant, fkMeetID, Minutes, Seconds, Feet, Inches);

Instead of minutes and seconds, use a single Date/Time field. There's
plenty of precision; in this context you won't have any round-off
problems. The alternative is to store the entire time as the number of
seconds. If you store separate minutes and seconds, you'll find
yourself doing extra work at every step. Store feet and inches in a
single integer which is the number of inches.

BTW, what do you do if the 100-yard dash is run twice in one meet? It
doesn't look like your tables can handle that. I'm not sure I'd say
that's a problem, as long as this is such a rare occurrence that you
are willing to add a new event type for a second event of the same
"real" type in a single meet.

Field events will have "distance" results (feet, inches) and Track events
will have "time" results (minutes, seconds). Currently I'm converting Track
times as such: Time: (Nz([Minutes]*60)*100)+Nz([Seconds]*100). Fields
events are converted as: Distance: (Nz([Feet]*12)*10)+Nz([Inches]*10).

Bad idea. As mentioned above, use a single Date/Time or integer field
for time, and a single integer field for distance. Do NOT make it look
like minutes and seconds in the stored data. For example, 2:30 should
be stored as 150 (or in a Date/Time item). 18' 11" should be stored as
227 (18*12+11).

Use built-in functions for the time conversion. The distance formula
becomes just nz(feet)*12+nz(inches).

I've seen lots of applications written to make the raw stored value
look like we write it in real life. Believe me, it makes everything a
lot harder. Look at it another way. The issue is the time and distance
(in the English system) are stored in mixed radix: 24,60,60 for time
and 10,12 for distance. But they are going to stored internally in
radix 2 anyway, so why try to represent a mixture of radices? Just
store one number and do the conversions on input and output.

Also, even if you don't anticipate ever recording a marathon, at least
make sure the input form can take times greater than 60 minutes, in
case an event you didn't anticipate needs it.

I don't understand your tblEventLookup. I assume one of the fields is
the name of the event. Type and Type2 confuse me.

Second question: should I keep re-entering these formulas on each form,
report, query, etc, or can I store calculations like this in a module?

As a rule, never duplicate code if you can help it. However, I have to
ask why you are even concerned. I'd anticipate two input forms, one for
time-based events and one for distance-based events. User selects event
from a menu. Each has a subform with recurring lines for participants.
The formula would be in the subform. Looks like one occurrence of each
formula to me. Why do you anticipate more?

Third question: I'll want to rank these records and assign a point value
based on the rank. I set up 2 queries to do this because in Track events the
lowest result wins, and in Field events the highest wins. I can get the
ranking to work, but I can't get a report to work based on either query.

Technically speaking, ranking is calculated and wouldn't be stored. In
this case, I'd probably make an exception. Partly this is because the
ranking of each participant is based on multiple records (all the
participants in the meet-event). It's also because once rankings are
announced and used for other permanent records, they probably shouldn't
change even if the underlying data turn out to have been entered
incorrectly.

Finally, it simplifies your "two-version" problem. Run two update
queries to compute and store all the ranks. Then run a report ordered
by rank. (But in computing ranking, make sure to take ties into
account.) Even if you always want ranking to reflect the current data,
this simplification is probably helpful enough to warrant doing it this
way.

Edward