Sum identical numbers only?

A

arran1180

Is it possible to edit/create a query that will sum similar (identical)
numbers?
i have a query that rturns, among other things such as reference etc, two
fields DATE and AMOUNT. where the amounts are the same (or the same but
negative) i really need to be able to sum them, but only if the also occur on
the same DATE.

I;ve been at this for a while and its proving quite tricky, I've used Access
a fair but but am new to SQL and relativly new to using any SUM functions...
Any help greatly appreciated!!
 
A

Arvin Meyer [MVP]

If the data is in a table or you can write a query that will place the
fields in a single row then it's quite easy in Access. First, you'd want to
write a VBA function in a standard module. I've written this simply without
checking for datatypes, but it will work if your data is well formed.

Public Function CheckDateAndAdd(Date1 As Date, Date2 As Date, _
Money1 As Currency, Money2 As Currency) As Boolean

On Error Resume Next

If Date1 = Date2 Then
If Abs(Money1) = Abs(Money2) Then
CheckDateAndAdd = True
Else
CheckDateAndAdd = False
End If
Else
CheckDateAndAdd = False
End If

End Function

Now take this function and apply it to the query you have written:

SELECT
CheckDateAndAdd([DateField1],[DateField2],[MoneyField1],[MoneyField2]) AS
Condition, [MoneyField1]+[MoneyField2] AS Total
FROM MyQuery
WHERE
(((CheckDateAndAdd([DateField1],[DateField2],[MoneyField1],[MoneyField2]))=T
rue));

If your data looked like this in MyQuery:

DateField1 DateField2 MoneyField1 MoneyField2
12/28/2005 12/28/2005 $2.00 $3.00
12/28/2005 12/28/2005 $2.00 ($2.00)
12/27/2005 12/28/2005 $2.00 $2.00
12/27/2005 12/27/2005 $2.00 $2.00

It would look like this after running through the above query:

Condition Total
-1 $0.00
-1 $4.00

Rows 2 and 4 being the only ones returned.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access
 
J

John Vinson

Is it possible to edit/create a query that will sum similar (identical)
numbers?
i have a query that rturns, among other things such as reference etc, two
fields DATE and AMOUNT. where the amounts are the same (or the same but
negative) i really need to be able to sum them, but only if the also occur on
the same DATE.

I;ve been at this for a while and its proving quite tricky, I've used Access
a fair but but am new to SQL and relativly new to using any SUM functions...
Any help greatly appreciated!!
Click to expand...

A Totals query, grouping by [DATE] and Abs([AMOUNT]) and summing
[AMOUNT], should do the job for you. Doesn't it? What do you get when
you do so? Please post the SQL.


John W. Vinson[MVP]
 
Top