Sum only specific records and group by

[Silvio]

I have a query called qryCharges. One of the colums is called ChargeHours and
another is called TradeID

The query is using a GroupBy O would like to create a third column that will
sum ChargeHours only for the record where the TradeID is = 1. I have tried
several syntax without success (rememebr that I am using GroupBy as well) How
can I accomplish this?
This is what I ave but the total are wrong:

SELECT DISTINCTROW qryCharges.ProjNumber, qryCharges.ChargeHours,
DSum("ChargeHours","qrycharges",[TradeID]=1) AS Admin
FROM qryCharges
GROUP BY qryCharges.ProjNumber, qryCharges.ChargeHours,
DSum("ChargeHours","qrycharges",[TradeID]=1);

 

[John Spencer]

IF I understand what you want then the following should work.

SELECT qryCharges.ProjNumber
, qryCharges.ChargeHours
, SUM(IIF(TradeID=1,ChargeHours,null)) AS Admin
FROM qryCharges
GROUP BY qryCharges.ProjNumber, qryCharges.ChargeHours

However I strongly suspect that this is not what you want, even if it
appears to be what you asked for.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Silvio]

John, this is EXACTLY what I was looking for. Thank you a million!

IF I understand what you want then the following should work.

SELECT qryCharges.ProjNumber
, qryCharges.ChargeHours
, SUM(IIF(TradeID=1,ChargeHours,null)) AS Admin
FROM qryCharges
GROUP BY qryCharges.ProjNumber, qryCharges.ChargeHours

However I strongly suspect that this is not what you want, even if it
appears to be what you asked for.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

I have a query called qryCharges. One of the colums is called ChargeHours and
another is called TradeID

The query is using a GroupBy O would like to create a third column that will
sum ChargeHours only for the record where the TradeID is = 1. I have tried
several syntax without success (rememebr that I am using GroupBy as well) How
can I accomplish this?
This is what I ave but the total are wrong:

SELECT DISTINCTROW qryCharges.ProjNumber, qryCharges.ChargeHours,
DSum("ChargeHours","qrycharges",[TradeID]=1) AS Admin
FROM qryCharges
GROUP BY qryCharges.ProjNumber, qryCharges.ChargeHours,
DSum("ChargeHours","qrycharges",[TradeID]=1);