Different results with calculations

[Gina Maylone]

Why would these calculations have different results?

[dbamount]-[cramount]=73,591.28 (amount is totaled at the bottom of the query)
sum([dbamount]-[cramount])=73,291.28 (ditto)

Data comes from the same place. Losing much hair over this. TIA!

HAPPY FRIDAY!!!

[JoeM]

Why would these calculations have different results?

You are showing them to return the same amount.

In your second formula, if you just want to subtract two fields, why do you have the SUM function in there? Is this part of an aggregate query.
I think we need more details/information regarding where there calculations are located. If they are part of queries, please post the SQL code of the queries.

[June7]

@JoeM, there is a $300 difference in the results.

Also confused as to why using Sum(), regardless, I would also expect results to be same. So doubly confused.

I would have to review db.

[JoeM]

JoeM, there is a $300 difference in the results.

Wow, I looked at the 3 times and didn't notice!
I must have been experiencing "2-5" dyslexia!

[June7]

Me too, caught it on third or fourth review.

[Gina Maylone]

Me too, caught it on third or fourth review.

So we are in agreement that both calculations SHOULD return the same result?

[JoeM]

So we are in agreement that both calculations SHOULD return the same result?

Not necessarily (at least not for me), until I have a clear understanding of where/how you are using this calculations, and why you are surrounding a subtraction formula in a sum formula.
Please explain in more detail.

[Minty]

Although, in theory, it shouldn't make a difference try;

Sum([dbamount])-Sum([cramount])

If that doesn't work dump the raw data into excel and compare the values there, as something isn't what it appears.

[Gina Maylone]

This is inherited from the old database:

sum([dbamount]-[cramount]). The goal of the query is to total the debits and credits and return the balance due. I kept a report from the old db and created my own as well (for comparison). That's when I noticed the totals were different. The original SQL:

SELECT OWNERS.OwnerKey, First(OWNERS.LASTNAME) AS LN, First(OWNERS.FIRSTNAME) AS FN, Sum(Account.DBAmt) AS DBSum, Sum(Account.CRAmt) AS CRSum, Sum([DBAmt]-[CRAmt]) AS Bal
FROM OWNERS INNER JOIN Account ON OWNERS.OwnerKey = Account.Owner
GROUP BY OWNERS.OwnerKey
ORDER BY First(OWNERS.LASTNAME);

My SQL:

SELECT ACCOUNTS.MemberID_FK, [dbamount]-[cramount] AS bal, ACCOUNTS.DBAmount, ACCOUNTS.CRAmount, IIf([cramount]<>0,[crdate],[dbdate]) AS UseDate, IIf([cramount]<>0,[accountnotes],[details]) AS Notes, IIf([SpouseLastName] Is Null And Not ([Spouse] Is Null),[firSTNAME] & " " & [laSTNAME] & " and " & [spouse] & " " & [LASTNAME],IIf([spouselastname]<>[lastname],[spouse] & " " & [spouselastname] & " and " & [firstname] & " " & [lastname],[firstname] & " " & [lastname])) AS ComboName, ACCOUNTS.LotNumber
FROM ACCOUNTS INNER JOIN ActiveMembers ON ACCOUNTS.MemberID_FK = ActiveMembers.MemberID_PK
GROUP BY ACCOUNTS.MemberID_FK, [dbamount]-[cramount], ACCOUNTS.DBAmount, ACCOUNTS.CRAmount, IIf([cramount]<>0,[crdate],[dbdate]), IIf([cramount]<>0,[accountnotes],[details]), IIf([SpouseLastName] Is Null And Not ([Spouse] Is Null),[firSTNAME] & " " & [laSTNAME] & " and " & [spouse] & " " & [LASTNAME],IIf([spouselastname]<>[lastname],[spouse] & " " & [spouselastname] & " and " & [firstname] & " " & [lastname],[firstname] & " " & [lastname])), ACCOUNTS.LotNumber, ACCOUNTS.asmttype
HAVING (((ACCOUNTS.asmttype) Like "*" & "roads" & "*"));

Thank you all for looking at this!

[isladogs]

You have two very different queries.
Grouping is different. The second query is filtered. Why would you expect to get the same result?

[Gina Maylone]

The second query is filtered because all assessment billings are stored in the same table now, where before they were stored in separate tables (roads, monthly, dam), so the filter distinguishes which billing is in question. Grouping is on MemberID_PK (OwnerKey in old db) not sure what to do with the rest of the grouping in the "new" sql. It's just general as far as I can tell.

[isladogs]

The point I'm making is that as your queries are not checking the same things, there is no reason why the values should be the same.
For a start, one filters for all records containing roads. The other doesn't filter.

[Gina Maylone]

The other one doesn't need to filter, as it is from the Roads table, it is only roads data.

[JoeM]

If possible, it might be best if you can upload your database so we can see what the data looks like and analyze it ourselves.
If you can, just be sure to remove any sensitive data from the database first.

Note that I cannot download it from my current location, but can tonight when I am home (and others may download it and take a look at it in the meantime).

[isladogs]

Gina
Back in post #1 you asked why two different expressions gave different results without supplying the queries used.
When you did so, the queries appeared very different as I pointed out.
When I focused on the filter, only then did you point out that the other was already based on filtered data.

If you had provided a clear description at the start, we could have given more focused answers rather than wasting time guessing.
Unless you supply the data (amended as necessary), there is little chance anyone can give you an answer...other than by just hitting lucky