I've attached a picture showing a basic view of the 2 tables and how I would like the query results.
I have tblAccountValues and tblFees
I want a query to return all the AccountValue info and Fee info for all the AccountNumbers on a given date. This part i can do, where I'm having problems is some accounts have a BillToAccountNumber. If the account has a BillToAccountNumber then I need the Query to return the CashAccountValue for the BillToAccountNumber.
My guess is the Query would have a Calculated Column, AvailableCash. If BillToAccountNumber is Null then AvailableCash = CashAccountValue for AccountNumber. If BillToAccountNumber is Not Null then AvailableCash = CashAccountValue for BillToAccountNumber.
One last wrinkle is the query needs to pull the Information from tblFees with the largest effective date that is <= the date the Query is run for.
BillToAccountNumber is not part of any relationships.
I have tblAccountValues and tblFees
I want a query to return all the AccountValue info and Fee info for all the AccountNumbers on a given date. This part i can do, where I'm having problems is some accounts have a BillToAccountNumber. If the account has a BillToAccountNumber then I need the Query to return the CashAccountValue for the BillToAccountNumber.
My guess is the Query would have a Calculated Column, AvailableCash. If BillToAccountNumber is Null then AvailableCash = CashAccountValue for AccountNumber. If BillToAccountNumber is Not Null then AvailableCash = CashAccountValue for BillToAccountNumber.
One last wrinkle is the query needs to pull the Information from tblFees with the largest effective date that is <= the date the Query is run for.
BillToAccountNumber is not part of any relationships.
Attachments
Last edited: