Help on IF Expression

[Guest]

Hi,

I have a purchase order table, which shows duplicate purchase orders with a
status of Active (A), Closed (C) and Cancelled (X). See example below

PO No. Status Amount
CIP23 A 4,500
CIP23 C 1,000
CIP23 X 2,000

How do I create an expression that sums the amount of the purchase order no.
CIP23 with a status of A or C? In the example above, the result for CIP23
would be 5,500 under this criteria.

Thanks.

 

[John Spencer]

SELECT [PO No.]
, Sum(amount) as TheAmount
FROM YourTable
WHERE Status in ("A","C")
GROUP BY [Po No.]

Another way if you want to show all PO numbers even if they only have a
Status of X

SELECT [PO No.]
, Sum(IIF [Status] in ("A","C"),Amount,Null) as TheAmount
FROM YourTable
GROUP BY [Po No.]

If you can only work with the Design View (query grid) then post back for
instructions on building this query that way.

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

 

[Guest]

SELECT [PO No.], Sum(Amount) AS SumOfAmt
FROM YourTable
WHERE Status In ("A","C")
GROUP BY [PO No.] ;