Another simple expression question!

[brownk]

Hello everyone. I have a table and i need to know the sum of the "collateral" field. I have an applID (somtimes 2 or 3 of them each with different amounts). I need to know the sum of the collateral. I may have 3 different amounts for each applID. Thanks!

[TheShabz]

DSum("collateral", "tableName", "applID = whatever")

http://www.techonthenet.com/access/f...omain/dsum.php

[brownk]

My query is set to run this expression for all entries in a given month so i wont really be stating any values/criteria for the applID. I just need all the values for "collateral" that have matching applID's to sum up.

[TheShabz]

Oh.
SELECT applID, Sum(collateral) As SumOfCollateral
FROM tableName
GROUP BY applID

That should give you one record for applID and the sum of all the collaterals per applID

[brownk]

okay. Should i make a table query for that SQL statement?

[orange]

What exactly do you want to do with the Collateral Amounts per AppID?

Unless we know what you are wanting to do, it is difficult to advise.

And NO you should not put these Calculated values in a table. You can always calculate them in a query, as TheShabz has shown.

[TheShabz]

What Orange said. You should not store calculated values (with rare exceptions) in a table. They can always be done on the fly.

[brownk]

I figured this one out. Here is the SQL statement.

SELECT dbo_tblCollateral.applID, Sum(dbo_tblCollateral.value) AS SumOfvalue
FROM dbo_tblLoan RIGHT JOIN dbo_tblCollateral ON dbo_tblLoan.applID = dbo_tblCollateral.applID
GROUP BY dbo_tblCollateral.applID;

This is a seperate query, and its working fine. Im going to link this query with another to make a report. What i now need to do is take the SUM from this statement, and divide it by another field in a separate table. The only problem is sometimes the values are 0, so i get a divide by zero error. I have tried a couple expressions such as

IIf([Divisor]=0,Null,[Dividend]/[Divisor])
and
IIf([Divisor]=0,0,[Dividend]/[Divisor])

I cant get any to work. They say its not a part of an aggregate function, or when it does work it completley undoes my grouping that the SQL statement above preforms. Instead of having unique applID with the sum like i need, i get multiple applID's.

[brownk]

Attachment 7807Attachment 7808


Maybe this will help in explaining my problem

[brownk]

@Orange - What i said in my original post. i needed to know the SUM.

[orange]

?? Where/When did the RIGHT JOIN come into this?

I think if you tell us in plain English what exactly is your situation and WHAT exactly you are wanting to do, the readers can offer advice or suggestions. When pieces get added, or show up in your code without any context for the rest
of us, it seems we are only guessing at what you are trying to do.

If any values in a DIVIDE operation are null or 0, you will get an error. Of course you could ignore the values that are NULL and/or 0, but since we don't have any idea of what they represent, we can not offer anything.

As you are finding, it is difficult to get Access or any software to evaluate an expression, when you are not clear in What you are doing in business terms, and not clear on the commands in Access.

Go back and read your post #1 -- you don't see any Right Joins or Division etc. We're trying to be helpful, so maybe you can help us by filling in some of the gaps.

[brownk]

The null values represent dollars. There will ocasionally be 0 values, thats somthing thats out of my control. Im building this query as i go along, but FIRST i had to get the SUM of a value stored in the COLLATERAL field (this is now done and working). We add up all the collateral, and compare that to the loan (AMOUNT) to find a loan to value percentage. There were no other tables during my first post because i had to get the SUM of the COLLATERAL field. Now ive added a LOAN table to finish my query. I now have a total of 2 tables. I believe that i am giving clear commands to access. Divide AMOUNT by LOAN, theres nothing complicated in my queries structure. Theres 2 tables, pulling from 3 fields. Sorry if it wasent plain enough english.