Help on IF Expression

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Shirley - 08 Nov 2007 15:47 GMT

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.

Signature

Shirley

Reply to this Message

John Spencer - 08 Nov 2007 16:22 GMT

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.

Signature

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

> Hi,
>
[quoted text clipped - 14 lines]
>
> Thanks.

Reply to this Message

Jerry Whittle - 08 Nov 2007 16:27 GMT

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

Signature

Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

> Hi,
>
[quoted text clipped - 11 lines]
>
> Thanks.

Reply to this Message