This seems easy but I just can't seem to figure it out. What follows is sample data but the premise is exactly what I'm trying to accomplish.
I have two tables, Contracts and Animals. On the Contracts table I track all the contracts that I have to use my collection of animals, which animal is used, the total cost of food for the animal during the contract, and the date range of the contract. On the Animals table, I track the percentage of each type of food that each animal eats.
I'm trying to find total food costs by food type and Animal. So I'd like a bar graph that sums up the total amount spent for each of the food categories (Meat, Berries, Milk, Honey, Dry Food) by Animal, by year
So, for instance, I'd like to see that Bears had the following breakdown:
I am able to sum by Animal, but I can't then break it down by the percentage of food type that the animal consumes.