DLOOKUP Help

[UTLee]

I have very minimal experience with Access and desperately need some help.

I have a query in Access called qryCashBalances that, among other things, displays certain client info: accountID (basically the account number), billToAccount (one the client’s accounts which our fees are pulled from), and cashbalance (the current cash in every account). If a client has multiple accounts we generally will only pull fees from one of our clients accounts to make it easier for our billing process. I would like to add a new field in the qryCashBalances query that pulls data from a different query, qryFeeCalc. qryFeeCalc shows the client’s aggregated fee amount (SumOfFee field) and also displays the billToAccount field mentioned earlier. I want to enter the DLOOKUP expression as a field on the CashBalances query to grab each accountID from this query, look at qryFeeCalc to see if there is a matching billToAccount, then display the SumOfFee amount associated with that billToAccount on the same row so that now I can see accountID, billToAccount, cashbalance, and the new field SumOfFee. If an account number isn’t found, I’d like it to display 0. I can’t figure out how to specify the criteria in the expression. Here’s what I’ve got so far entered into qryCashBalances:

DLOOKUP(“SumOfFee”,”qryFeeCalc”, _________)

Sorry for the somewhat lengthy and confusing description. Thanks in advance.
-Lee

[pbaldy]

It would be far more efficient to join the second query to the first on the account field. Editing the join will handle the instances where there's no record in the second query.

[UTLee]

Thanks for your quick response Paul. So, do you mean for me to open the first query and add the second query within it?

[pbaldy]

Yes, add the query to the top section and the field from it below.

[UTLee]

Did that but it is showing me the individual fee for every single account now. the point of the second query was to aggregate all fees on a per client basis rather than showing fees for each account. Then the aggregated fee should be pulled from the billToAccount.

[UTLee]

Just for reference, the first query shows roughly 1100 accounts and the second query only shows about 500 because we usually only bill 1 account for each client.

[pbaldy]

Can you post the db here? Done correctly, you should get the same data you got with the first query, plus the aggregated data from the second on those records with a matching account.

[UTLee]

Paul, sorry for the delayed response. I finally figured it out thanks to your advice. I added the second query like you mentioned, changed which fields were linked between the two, and changed the join to the second option under the Join Properties. Thanks again for your help, I'm sure I'll be back with more questions in the near future!

[pbaldy]

Happy to help Lee!