Total of Sum

[key]

Hi Access-Friends,

I'm stuckt with the following query: I'd like to build a total sum for the whole year. In table1 is Amount in table2 is the changedAmount. Unfortunately, I got a wrong Sum. I assume it's because of the 'group by' function. Am I on the right track?

Thanx for any suggestions,

Key

SELECT Sum(Table1.Amount) AS SumOfAmount, Sum(Table2.ChangedAmount) AS SumOfChangedAmount, Table1.AccNo, Table1.Year, Sum(([Amount]+[ChangedAmount])) AS Expr1
FROM Table1 LEFT JOIN Table2 ON (Table1.Year = Table2.Year) AND (Table1.Month = Table2.Month) AND (Table1.AccNo = Table2.AccNo)
GROUP BY Table1.AccNo, Table1.Year
HAVING (((Table1.AccNo)=100) AND ((Table1.Year)=2003));

 

[RichMorrison]

You wrote
<<
Unfortunately, I got a wrong Sum. I assume it's because of the 'group by' function. Am I on the right track?
>>

Yes.

You are grouping (and therefore "summing") by Account Number then Year.

If you want a grand total for all Account Number for a Year, you should do this with a form or report based on your query.

RichM

 

[Cosmos75]

Is this what you are trying to do?

Key,

Is this what you want to do? Hopefully this'll give you a stating point.

There are two queries (One that shows Sums by Year and one By Account for all years)

You can add parameters to filter for whatever records you want.

 

[Pat Hartman]

One other point about the query. Access has a tendency to create Having clauses when you add criteria to a queyr that uses Group By. In this case, using a Where clause will be more efficient. The Where criteria is applied to the records to be selected for grouping. The Having criteria is applied to the query AFTER all the grouping has been done. Since you are selecting one account for one year, there is no need to sort and sum all accounts for all years only to discard the data later.

The Having clause is necessary when you want the selection criteria to apply to a value after the aggregation has been done. For example if you only wanted accounts where the sum of the changed amount is greater than a certain value.