DLookup in Query Field (1 Viewer)

JahJr

Andy
Local time
Today, 04:29
Joined
Dec 3, 2008
Messages
93
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.
 

Attachments

  • Table Layout New.JPG
    Table Layout New.JPG
    94.5 KB · Views: 80
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 02:29
Joined
Oct 29, 2018
Messages
21,357
Hi. For the first part of your question, look into the IIf() statement.

Sent from phone...
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:29
Joined
May 7, 2009
Messages
19,169
how do you compute the AvailableCash?
 

JahJr

Andy
Local time
Today, 04:29
Joined
Dec 3, 2008
Messages
93
AvailableCash = CashAccountValue from tblAccountValues unless there is a BillToAccountNumber listed. Then AvailableCash = CashAccountValue from tblAccountValues for the BillToAccountNumber
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:29
Joined
May 7, 2009
Messages
19,169
it's not that easy since tblFees has effective date on them.
 

Attachments

  • sampleAcct.zip
    28.9 KB · Views: 80

JahJr

Andy
Local time
Today, 04:29
Joined
Dec 3, 2008
Messages
93
I thought that was going to be it for a second. I added some more dates to the Account Values table and it is not pulling the correct amount for the cash. It does appear to be pulling the correct PercentFee.

I attached the sample file back to this post with the added dates and values.

Thank you for your help.
 

Attachments

  • sampleAcct.zip
    41.8 KB · Views: 82

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:29
Joined
May 7, 2009
Messages
19,169
test it again.
 

Attachments

  • sampleAcct.zip
    33.9 KB · Views: 89

JahJr

Andy
Local time
Today, 04:29
Joined
Dec 3, 2008
Messages
93
As I've been testing this, I've run into a couple of issues that I missed initially. Good news is I've done away with the available cash field. It is no longer needed.
If an account has a fee change during the billing period then the query needs to return a fee calculation for both of them. I've attached an image of what the query for the attached DB should return. I'm lost as on how to get there.

If you open frmReport and select 9/30/2019 i would like the query to return what i have in the image.

Account Values are only reported for 1 of the following 3 reasons.
1. Qtr End
2. New Account Value
3. Deposit made, this is not the whole account value just the Deposit.

In the example account A2 has a 9/30/19 value and a deposit made on 11/10/19. To further complicate things the fee charged for this account changed on 11/1/2019.

I need to calculate the billing for A2 on the 9/30/19 value up until the fee change on 11/1/19. Then i need to calculate the billing from 11/1/19 through the end of the Qtr. I also need to calculate the billing on the deposit made on 11/10/19 through the end of the Qtr.

My goal is to get all of this into a single report.
 

Attachments

  • Query Sample.jpg
    Query Sample.jpg
    100.3 KB · Views: 79
  • sampleAcctNew.zip
    109.7 KB · Views: 78

Users who are viewing this thread

Top Bottom