Maybe a running sum problem, maybe not...

[Clddleopard]

Ok, here's the scenario:
I'm trying to count calories consumed by an animal each day.
I have a table FoodTypes with the following fields:
FoodID (Autonumber)
FoodName (Text)
Calories (Number)

I have a table "AnimalIDinfo". The only relevant field is AnimalID (Long
Integer)

I have a table "DietInfo" with the following fields.
AnimalID
FoodID
AmountOffered (Number)
DefaultDiet (yes/no)
DietDate

What I had envisioned was that a default diet would be entered at the
beginning of the record keeping cycle and would be indicated by checking yes
in the DefaultDiet field. This would allow me to calculate the base number of
calories the animal received. As the days went on, the keeper would put in
only the deviations from the default diet. Some days, an animal might get 2
pounds extra, some days it might get 3 pounds less. On days when nothing was
different, I didn't want them to have to record anything at all. When the
default diet changed, the keeper would indicate that by a checkmark in the
defaultdiet field. I would then write a fancy schmancy query and be able to
tell how many calories the animal consumed in a given time period.
Sample Data from DietInfo Table:
AnimalID FoodID AmountOffered DefaultDiet DietDate
32 5 3 yes 2/1/08
32 6 2 yes
2/1/08
32 7 1 no
2/1/08
32 5 4 yes
2/5/08

From Food Type Table we would know that
FoodID Calories
5 10
6 15
7 20

So for 2/1 through 2/4, the default diet would be 5 lbs, resulting in 60
calories. However, on 2/1 the animal would have received 80 calories because
of receiving 1 pound of foodID 7.
For 2/5 until the next diet change, the default diet would be 6 lbs,
resulting in 70 calories.
Date Calories Animal ID
2/1 80 32
2/2 60 32
2/3 60 32
2/4 60 32
2/5 70 32
Now I need help with the fancy shmancy query. I know it's going to involve
subqueries, but my basic confusion is how to tell it to check what the
default diet is for every day, even days which aren't represented in the
DietInfo Table. And how do I tell it to take the latest default diet that is
less than or equal to each day? Is this a running sum problem?
Any help at all is appreciated!

 

[KARL DEWEY]

Your food calores are not related to an amount of food.
ERROR here --
Sample Data from DietInfo Table:
AnimalID FoodID AmountOffered DefaultDiet DietDate
32 5 3 yes 2/1/08
32 6 2 yes
Amount offered different but says default diet.
I would recommend not using the default. How about offered vs eaten?

 

[Clddleopard]

Food Calories are entered as they are per pound, in the foodtype table. So it
is easy to get how many calories were consumed by multiplying amountoffered
(which is actually the amount the animal eats, the name is admittedly a bit
unclear) by Calories.
An animal receives a combination of food items for its diet. So the
defaultdiet will be made up of two or three different foodIDs and weights.
For any given day, the defaultdiet is the combination of FoodIDs and weights
with the highest date.

 

[Clddleopard]

Correction:
The defaultdiet on any given day is the combination of foodIDs and weights
with the most recent date AND marked defaultdiet