I don't know if you can do it in MS Access, but it almost sounds like a CTE would work the best for you. You could have had with TableA(a,b,c,d,dollarAmt)(do math), etc. Then, at the end, you would have your final query that works in everything and only brings in the columns you want by doing a left outer join. then you would have select vendor,tableAsum,TableBSum etc.

I just don't know if you can do that in MS Access but that's what it sounds like it would solve your issue.