Need advice on date calc

[TWillson]

I am currently working on a database for the documentation of athlete's injuries and their playing status. I would like to run a report which shows the number of days an athlete has been able, limited or unable. I have a main database with athlete demographic information and a separate related database for athlete status (i.e. able, limited, unable). A new record is created in the athlete status database each time their status changes. An athlete's status may change daily, weekly or monthly.

I was planning on setting up three text fields in the athlete status database. One each to keep a running total of the number of days an athlete was able, limited or unable. Each time a new record was created in the athlete's status database the number of days they were able, limited or unable would be calculated and appended to the running total fields.

Does anybody have any suggestions on a better way to set this up. It would be nice to calculate the number of days an athlete was at a particular status "on the fly" with a looping script, however this seems like it would be too complicated.

Thanks,

Tom

[Vaughan]

This is just a guess at a solution...

To make it easier you could make each related record represent one or more whole days of the athelete being on a particular status. Each related record needs a Status field, a Start date, and an End date. The Status and Start dates are entered when the status is changed. The end date gets processed by a script which sorts the related records by Start date, then inserts the start date of record 2 into the end date of record 1 etc. The duration of weach record will therefor be the End - Start dates.

Getting a report out will be as simple as creating a summary report, with sub-summary parts for athelete and status.

Somebody else will probably come up with an automated way (or better yet a calulation) for the end date that does not require the looping script.

[TWillson]

Thanks for the feedback Vaughan.

I think your suggestion of having each related record represent the number of days of the athlete being on a particular status would make things a lot more flexible as far as reporting (rather than keeping a running total of the number of days an athlete is on each status).

So than my script to record the end date would look something like:

Go to Related Record [AthleteDemographics::ID=AthleteStatus::AtheteID] # Sorted by Start Date, descending

Set Field [AthelteDemographics::ID=AthleteStatus::AthleteID], Status(CurrentDate)

The script would than continue to create a new status record, setting the start date to the current date and leaving the end date open.

Sound right?

Thanks

Tom

[Jim McKee]

Hi Tom ...

I'm attaching a system I did for a similar type situation, and tweaked it for your needs to give you some ideas about how to proceed. The system is completely calculation-based (no looping scripts or SetFields for running totals). Since it's unlikely that your record count will ever be huge, this will probably work fine for your purposes.

If you have any questions, ask away! That's why we're here

Good luck!

Athlete Availability.zip

[TWillson]

Jim

Thank you for the example, it seems to be along the same lines as what Vaughan had suggested and is exactly what I am looking for (although I may automate the closing of a status event when a user tries to create a new one). I'm spending some time now reading through the scripts.

Thanks again for the advice, it is greatly appreciated.

Tom