Sum Urgent

[Max]

Hello All,

I have a data set that look like this

Unique ID Amt1 Amt2 Amt3 Amt4 Amt5 Amt6 Amt7
11111111 -40 0 50 -20 10 20 -10

What I want is to sum the positives together and the negatives together:

Result
Unique ID Negative Positive
11111111 -70 80

Thank you

 

[Steve]

First you need to redesign your table to:
TblAmount
UniqueID
Amt

The totals can then easily be found in a Totals query.

Steve
(e-mail address removed)

 

[KARL DEWEY]

Your table structure is like a spreadsheet - horizontal and should be
vertical --
Unique ID Amt Unit
11111111 -40 1
11111111 0 2
11111111 50 3
11111111 -20 4
11111111 10 5
11111111 20 6
11111111 -10 7

Use a union query --
qryUnionID --
SELECT [Unique ID], Amt1 AS Amt, 1 AS Unit
FROM YourTable
UNION ALL SELECT [Unique ID], Amt2 AS Amt, 2 AS Unit
FROM YourTable
UNION ALL SELECT [Unique ID], Amt3 AS Amt, 3 AS Unit
FROM YourTable
...
UNION ALL SELECT [Unique ID], Amt7 AS Amt, 7 AS Unit
FROM YourTable;

Then a totals query --
SELECT Sum(IIF([Amt] <0, [Amt], 0)) AS Negative, Sum(IIF([Amt] >0, [Amt],
0)) AS Positive
FROM qryUnionID;

 

[John Spencer]

You can use an expression like the following to get the negative sum

IIF(Amt1<0,Amt1,0) +IIF(Amt2<0,Amt2,0) +IIF(Amt3<0,Amt3,0) +IIF(Amt4<0,Amt4,0)
+IIF(Amt5<0,Amt5,0) +IIF(Amt6<0,Amt6,0) +IIF(Amt7<0,Amt7,0)

And the following to get the positive sum
IIF(Amt1>0,Amt1,0) +IIF(Amt2>0,Amt2,0) +IIF(Amt3>0,Amt3,0) +IIF(Amt4>0,Amt4,0)
+IIF(Amt5>0,Amt5,0) +IIF(Amt6>0,Amt6,0) +IIF(Amt7>0,Amt7,0)

It would be a lot easier with normalized data. That is UniqueID and Amount so
you would have up to 7 records per unique id. You could construct that with a
UNION query

SELECT UniqueID, Amt1 as Amount
FROM YourTable
UNION ALL
SELECT UniqueID, Amt2 as Amount
FROM YourTable
....
SELECT UniqueID, Amt7 as Amount
FROM YourTable

Then the query becomes
SELECT UniqueID
, Sum(IIF(Amount<0,Amount,0) as Negative
, Sum(IIF(Amount>0,Amount,0) Positive
FROM TheSavedUnionQuery

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Max]

Thank u all, but thats what i did:

Public Function Appendpositive() As Integer

Dim strSQL As String

strSQL = "INSERT INTO PositiveTable ([document], [Audit#], [CP-1$$])
SELECT [document], [Audit#], [CP-2$$] from ClaimProcessingSetupTable Where
[CP-2$$] > 0"
CurrentDb().Execute strSQL

Appendpositive = 0

End Function

 

[John W. Vinson]

Thank u all, but thats what i did:

Public Function Appendpositive() As Integer

Dim strSQL As String

strSQL = "INSERT INTO PositiveTable ([document], [Audit#], [CP-1$$])
SELECT [document], [Audit#], [CP-2$$] from ClaimProcessingSetupTable Where
[CP-2$$] > 0"
CurrentDb().Execute strSQL

Appendpositive = 0

End Function

Fine... but that solution appears to have no relationship whatsoever to the
question you posted. This query has one field - CP-1$$; your posted question
has multiple non normalized fields.

It really helps if you are clear and accurate when you post.