DSum function not returning any results

[dougdrex]

Using Microsoft Access 2010.

I'm using the Expression Builder to create a query. It is supposed to calculate the transfers in and transfers out of each account. The bank account is the first field, and the second field is TransfersIn. Following is the syntax from that field: TransfersIn: CCur(Nz(DSum("TransferAmount","TBL_TransactionDeta ils"),0)).



Below is a screen shot if that will help with any feedback on what I am doing wrong.




Thanks!
Doug

[June7]

The REF_BankAccounts table needs to be included in the query twice so there are two copies of the table in the query. Join one of each copy to FromAccount and ToAccount.

Why are you using DSum to summarize data of table that is in the query?

Build an aggregate query or a report.

[dougdrex]

I get another error message when I build an aggregate query. Below is my screen and error message that comes up:

[Bob Fitz]

Don't understand the need for the criteria. What happens if you take it out.

[dougdrex]

Bob, that was it. I removed the criteria and the query worked exactly as intended.

Thanks a LOT!!

[dougdrex]

Okay, I may have jumped the gun a bit on my enthusiastic response to what I believed to be the resolution to my issue. After removing the criteria and attempting to finish the query I remembered there was a purpose behind the query.

Because TBL_TransactionDetails houses the transfers, I need to calculate both the transfers IN as well as the transfers OUT. Consequently, I need a seperate field in the query for transfers IN and transfers OUT. In order to calculate that correctly, I need the criteria of when the ToAccount field matches the BankAccountID field, and conversely, when the FromAccount matches the BankAccountID field for the TransfersOut field I had not yet created when I originally submitted this question.

Any thoughts on how best to handle that?

Thanks!
Doug

[June7]

Maybe you need to do two aggregate queries. One to summarize the amounts for the ToAccounts, one to summarize the amounts for the FromAccounts. Then join those two queries to master table of accounts.

BTW, wonder if BeginningBalance field is appropriate. The starting balance for an account should be the first transaction entry.