I'm trying to create a budget table that is related to a number of other tables representing purchases and payments. In the budget table one record represents one year with 12 individual fields representing the twelve months of the year. A corresponding calculation field representing a total amount for a given month derived from the related purchase and payment amount fields in the purchase and payment tables .

I'm basically trying to bring the purchase and payment totals from a given month and bring it over into the corresponding budget table totals so it can be compared to a budget amount and then calculate the difference in another field. I tried the following calculation but it is not working properly.

If ( MonthName (Payment by Check::payment_date = MonthName (january) ; Sum (Payment by Check::payment_amount) ; "" )

My logic behind this is if my purchase or payment date equals the january date in my budget table, then give me a sum total of those payment or purchase amounts. Otherwise do nothing. The totals are going into the assigned budget field but they continue to go into the field whether or not the date equals January. I did this same calculation but replaced the budget MonthName with February instead of January but the result still goes into the January budget field and seems not to care what the date is.

Does anyone have an idea how to solve this or am I approaching this thing wrong from the word go?

You probably need to base your relationship "Pay By Check" to a stored calculation field to capture the transaction month:

transaction_month = MonthName(payment_date)

Then use this field "transaction_month" for the relationship key.