here is the setup:
[AM and OE] table has a field w/ date, USD or CAD, and another field with $
[Currency Conversion] table has 3 fields:
CAD to USD (Example: 1.362)
Effective Date (Example: 1/1/2019)
Effective to (Example: 01/31/2019)
I need a query that looks like this:
if [AM and OE] = CAD and [AM and OE] date is between [currency conversion] effective date and effective to date, return the sum of [Currency Conversion] CAD to USD / [AM and OE] $
end result
The query will update $$ field on [AM and OE] table that as a net value (calculated) USD to USD or CAD to USD