'Sum' ignoring minus figures

[Matt Greatorex]

I've been using the following query:

SELECT 
[qry_1].Month, 
B_Division_Group.Grouping_Name,
Sum([qry_1].Month_Client_Count_from_B) AS Monthly_Count, 
Sum([qry_1].Month_Assets_from_B) AS Monthly_Total, 
Sum([qry_1].YTD_Client_Count_from_B) AS Yearly_Count, 
Sum([qry_1].YTD_Assets_from_B) AS Yearly_Total
FROM 
([qry_1] INNER JOIN tbl_branch 
ON [qry_1].BranchCode = tbl_branch.BranchID) 
INNER JOIN B_Division_Group 
ON tbl_branch.BranchName = BDivision_Group.N_Br
GROUP BY 
[qry_1].Month, 
B_Division_Group.Grouping_Name;

While all monthly and yearly values were positive, the query produced exactly the results expected (i.e. one row of data for each Grouping Name/Month combination, containing the overall totals for each field).

Now, however, some Grouping Names have minus values and the query is showing an extra row (one for positive values, one for negative). It's as if having one or more minus values is being treated as a new Grouping Name/Month combination.

Is Sum() the correct method to use, when dealing with negative values, or should I be using some other function?

 

[Moniker]

If I read your post correctly, it's because you're grouping on the Grouping_Name field. Each unique field entry becomes it's own group, as it were. Therefore, if you have a set of Grouping_Names like this:

Group1
Group2
-Group2

Then you are going to get three rows back. SUM() is the correct function to use for adding up all the numbers (negatives, positives, whatever), but placing a unique identifier like the minus symbol in the Grouping_Names field will always break those apart. You would need to have a higher level field that is consistently named to get the exact results you want.

 

[Matt Greatorex]

Thanks for the reply. Unfortunately, the grouping names are aren't differing.

I end up with:

Group A -$66.00
Group A $100.00
Group B $77.00

and so on.

Anyone else with any suggestions? getting desperate here, folks.

 

[Moniker]

The only thing I can see is the JOINS that might cause it, but I don't know your table structure. Can you post a stripped-down version of it?