If you have the joins set right on the tables it's just a matter of selecting the right Product, State, and Region in the query and the correct Balance and rate will populate automatically. Although it seem like the table structure needs work, but i don't know which data will repeat and which will not, so i can't be sure about it. In any event this will have to be done in a query in order to gather the data from two different tables. At least in my experience, which is very limited. Someone with more EXP. will chime in soon.
I am working based on the EXCEL files loaded into Access. You are right the table structure is not the best deigned one. The recordsets are gotten from complex queries involving aggregate functions. I presented it as if it is simple. That is the reason I said that I got myself into this mess. I should have concentrated on designing the table structure properly. ( this work was handed over to me!!).