Query Problems-> Summing Fields with different criteria???

[Rick Brandt]

I am trying to create a query that includes several columns, of which each
of these columns is a sum of an amount field, however each column has
different selection criteria.

How do i get this to work? as my current query works fine with one field but
as soon as you add the second it doesn't return anything because the having
clause is combined into 1 instead for each individual column.. for example..

Sum1: Amount Sum2: Sum(Amount) Sum3: Sum(Amount)
Total: Sum Total: Sum Total: Sum
Criteria Class = "A" Criteria Class = "B" Criteria Class = "C"

This is not working?? when i look at my SQL it's been combined into one huge
select statement with 1 having clause, instead if individual having clauses
for each column name.

What am i doing wrong?

You can't have different criteria per column, but you can fake it with
expressions like this...

Sum1: Sum(IIf([Criteria Class]="A",[Amount],0))
Sum2: Sum(IIf([Criteria Class]="B",[Amount],0))
Sum3: Sum(IIf([Criteria Class]="C",[Amount],0))

 

[Darryn Ross]

Hi

I am trying to create a query that includes several columns, of which each
of these columns is a sum of an amount field, however each column has
different selection criteria.

How do i get this to work? as my current query works fine with one field but
as soon as you add the second it doesn't return anything because the having
clause is combined into 1 instead for each individual column.. for example..

Sum1: Amount Sum2: Sum(Amount) Sum3: Sum(Amount)
Total: Sum Total: Sum Total: Sum
Criteria Class = "A" Criteria Class = "B" Criteria Class = "C"

This is not working?? when i look at my SQL it's been combined into one huge
select statement with 1 having clause, instead if individual having clauses
for each column name.

What am i doing wrong?

Regards

Darryn

 

[Darryn Ross]

Hi Rick,

The thing is, there is more than one criteria for each column, so i can't
use the IIF(). There must be a way of combining multiple sum fields with
separate criteria! unless i just do a query for each criteria and join them
all!! but there is going to be like 20 of them and that seems a little
annoying.

Regards

Darryn

I am trying to create a query that includes several columns, of which each
of these columns is a sum of an amount field, however each column has
different selection criteria.

How do i get this to work? as my current query works fine with one field but
as soon as you add the second it doesn't return anything because the having
clause is combined into 1 instead for each individual column.. for example..

Sum1: Amount Sum2: Sum(Amount) Sum3: Sum(Amount)
Total: Sum Total: Sum Total: Sum
Criteria Class = "A" Criteria Class = "B" Criteria Class = "C"

This is not working?? when i look at my SQL it's been combined into one huge
select statement with 1 having clause, instead if individual having clauses
for each column name.

What am i doing wrong?

You can't have different criteria per column, but you can fake it with
expressions like this...

Sum1: Sum(IIf([Criteria Class]="A",[Amount],0))
Sum2: Sum(IIf([Criteria Class]="B",[Amount],0))
Sum3: Sum(IIf([Criteria Class]="C",[Amount],0))

 

[Rick Brandt]

Hi Rick,

The thing is, there is more than one criteria for each column, so i can't
use the IIF(). There must be a way of combining multiple sum fields with
separate criteria! unless i just do a query for each criteria and join them
all!! but there is going to be like 20 of them and that seems a little
annoying.

IIf() can have multiple criteria.

IIf([SomeField]="A" And [SomeOtherField]=123,"ReturnThis", "ReturnThat")

Also look at the Switch function which has multiple tests, and of course
you can always write a custom function.

You have to understand that queries are "set oriented". Criteria is always
applied to the entire set to determine the rows returned.

 

[John Spencer (MVP)]

Can you just group by the Criteria Class and then get the sum of the amount?
This will sum for each group of Criteria Class

FIELD: [Criteria Class] Amount
TOTAL: Group By Sum

This will return three (or more) Rows.

A 122
B 5
C 85