Conditional Sum Field
I have a table called product, and another table called orders. It's a one-to-many relationship.
I have a field product::TotalSales that is the Sum(Orders::TotalAmount). This works great.
I want to creat a field (product::Sales2009) that is the Sum(Orders::TotalAmount), if the Orders::Year = "2009"
I tried using a if clause in my calculation, but that does not work. I know that I can write a script to populate the Sales2009 field, but it would be more convenient to have a calculation field. Can this be done?
All ideas are appreciated.