Scheduling project/conundrum

[Guest]

Ok, here's the bare bones of the idea I've been kicking around in my head.
I'm just going to narrate for you what I need to do, and maybe some of you
will have some brilliant ideas about how to do this.
I have a table of animals
I have a table of enrichments. There are four "types" of enrichments coded
F, T, E, and S.
There are four enrichment frequencies: once a week, twice a week, three
times a week and four times a week.
A specific animal has a specific enrichment frequency that does not change,
so the frequency would probably be stored in the animal table
I would like the days the animal receives the enrichment and the type of
enrichment to be randomized. An example of the type of output I would like to
see:
This week: Sasha (the animal) Monday-F Wednesday-T Thursday-T Sunday-S
Next week: Sasha Tuesday-E Wednesday-S Friday-F Sunday-T

I think it would be pretty easy to do if I just made the frequencies always
happen on the same day, but that diminishes the power of the enrichment. You
don't want it to be rigidly scheduled. Also, I'd like to be able to put a cap
on the number of times the same type of enrichment could be scheduled so that
they don't get a 'T" enrichment every time.

It's kind of a big problem and other than having the tables set up, I
haven't really started in on it yet. Just thought I would share the fun!

Hope this sounds interesting to someone, and I certainly would appreciate
any help I could get!

 

[Guest]

At the end I meant to say that it would probably be easy to do if I just had
the enrichments on the same day-- i.e. always on Sun Tues Thurs Sat. But, you
don't want them to be able to anticipate getting an enrichment so it's good
to mix it up. Also, I'd like to cap the number of times they can receive the
same type of enrichment *each week* so they can only receive T three times or
less in a given week or something like that.

 

[Guest]

In my sad halting way I have been working on this. I have figured out how to
generate a random enrichment type and a random day, then append these to a
table with the animal's name. However, I don't want two enrichments on the
same day. When I get the results: Sasha, Monday, T and Sasha, Monday, S, is
there a way I can force access to change the Monday to another day, any other
day? I've figured out that by setting the combination of Day and Animal name
as the primary key I can prevent duplicates, but that means that when a
record is not appended due to primary key violations, the animal misses out
on a day of enrichment. That's bad.
Any help?