Populate Calculated field in query

[June7]

My suggestion was to do a GROUP BY (aggregate) query that uses MLS or ID as the grouping criteria. Actually, group on LoanID of tblCashFlow and it is not necessary to include tblLoanGeneral in the query.

[Mpike926]

June,
when you do a group by (aggregate)query, you cannot have the expression to sum up all of the fields in the same query. it tells you cannot have an aggregate function in the group by clause Expr1: Nz(Sum([Principal]+[Interest]+[fees]+[payoff]+[notesale]+[reonoi]))-Nz(Sum([priorliens]+[backtaxes]+[assetmgtfee]+[legalfc]+[loanadvances]+[reotaxins]+[reoothercost]+[brokerfees]+[ProfFeesOtherExp]))

[Mpike926]

Ok I think I might have figured it out... I just baskspaced out 'group By' below the expression. Im going to spot check and see what I come up with.
Ill be back
-Matt

[Mpike926]

Ok it works Thank you June and Rod for the help.
Im sure we will run into each other again... Until that day, take care
-Matt

[June7]

Don't put the Sum function in the expression. Instead of 'group by' below the expression, choose Sum. Group By should be under LoanID.

I don't think the Nz as you are using will give desired results. Might need the Nz used on each term.

[Mpike926]

June,
The sum function produces the same error. I just put in the Total: expression, and it works. If you feel so inclined, I have another thread with a question that can be found here
https://www.accessforums.net/access/...ort-27637.html
If you want to help out, id appreciate it =]
-Matt

[June7]

Glad it is working but seems odd to me. I will check out the other thread.

You might want to remove the file from post if the data is real info.

[Rod]

Expr1: Nz(Sum([Principal]+[Interest]+[fees]+[payoff]+[notesale]+[reonoi]))-Nz(Sum([priorliens]+[backtaxes]+[assetmgtfee]+[legalfc]+[loanadvances]+[reotaxins]+[reoothercost]+[brokerfees]+[ProfFeesOtherExp]))

June7 has pointed out that you should not put the Sum function in the expression. I however have a concern with the use of the Nz function. Should the expression not be

NetIncome: Nz([Principal])+Nz([Interest])+Nz([fees])+Nz([payoff])+Nz([notesale])+Nz([reonoi])-Nz([priorliens])+Nz([backtaxes])-Nz([assetmgtfee])-Nz([legalfc])-Nz([loanadvances])-Nz([reotaxins])-Nz([reoothercost])-Nz([brokerfees])-Nz([ProfFeesOtherExp])

I don't know what Access SQL does with 3+Null. Access mainstream certainly objects.

Just noticed that June7 has made this point as well - sorry.

[Mpike926]

Hi all,
So I removed the sum from the front and then made the group by sum. However I have one question. One of my assets shows 555,000 as the net income from the cashflow, however when it is ran in the query, it shows (119,000). Every other asset in our pool is correct except for this one. Any ideas?

[June7]

No, I would have to examine the data and report.

[Mpike926]

Ive narrowed it down to this.
The cashflow is a continuous form limited at 60 and for some reason, the income for the one month isnt picked up and the -119k is actually expenses from the loan. however if i move the income of what ever it is, up or down a month it gets picked up and reflected. I find this strange because the query adds and subtacts the sum of all income and expenses, so you would think that whatever month the income was in it really wouldnt matter