Help with a query

[Ashley]

THIS IS THE SAMPLE DATE
I want to see supplierID, sum of amount shipped and sum of amount not
shipped, and group them by suppier, total no of orders accpted by
supplier,
total no of orders declined


ORDERS_SHIPPED

OrderID int, [Shipped Date] datetime


1 '1/2/05'
2 '2/3/05'
6 5/6/06'


ORDERS (1st 2 columns)


supplierID int OrderID int
111 1
111 2
111 8
222 3
222 4
333 5
333 6


ORDERS (3rd and 4th cloumn)


status char, amount int
'accepted' 1000
'accepted' 2500
'declined' 200
'accepted' 2000
'accepted' 3000
'declined' 2000
'accepted' 500


SUPPLIER


supplierid supplier
111 ABC
222 ROCKWAY
333 CASTLE


THIS IS THE EXPECTED RESULT


supplier OrdersShipped OrdersPending OrdersAccepted
ABC 2 1 2



ROCKWAY 0 2 1


CASTLE 1 0 1


NEED TO ADD ANOTHER COLUMNs WITH


OrdersDeclined AmtShipped
1 3500
0 0
1 500


AND


AMTDECLINED AMTPENDINGSHIPPING
200 0
0 5000
2000 0






PENDING IS WHICH IS ACCEPTED BUT NOT SHIPPED


I am trying something like this. I AM NOT AN ACCESS PROGRAMMER AND
WOULD
APPRECIATE HELP WITH THIS. I AM DOING SOMETHING WRONG HERE.


SELECT s.supplier,


sum (IIf((o.orderstatusStatus ="ACCEPTED")
AND ( EXISTS (select os.ordersshipped from ORDERS_SHIPPED OS where
o.orderid = os.orderid group by os.orderid)
) ,1,0)) AS [ORDERSSHIPPED]


sum (IIf((o.Status ="ACCEPTED")
AND ( NOT EXISTS (select os.ordersshipped from ORDERS_SHIPPED OS
where o.orderid = os.orderid group by os.orderid)
) ,1,0)) AS [ORDERSPENDING],


sum (IIf((o.Status ="ACCEPTED") ) ,1,0)) AS [ORDERSACCEPTED],


sum (IIf((o.Status ="DECLINED") ) ,1,0)) AS [ORDERSDECLINED],


sum (IIf((o.orderstatusStatus ="ACCEPTED")
AND ( EXISTS (select os.ordersshipped from ORDERS_SHIPPED OS where
o.orderid = os.orderid group by os.orderid)
) ,o.amount ,0)) AS [AMOUNTSHIPPED]


sum (IIf((o.Status ="DECLINED") ) ,o.orderamount,0)) AS [AMTDECLINED],



sum (IIf((o.Status ="ACCEPTED")
AND ( NOT EXISTS (select os.ordersshipped from ORDERS_SHIPPED OS
where o.orderid = os.orderid group by os.orderid)
) ,o.amount,0)) AS [AMTPENDINGSHIPPING],


FROM


supplier as s,
orders o,
ORDERS_SHIPPED os


WHERE


o.orderid = os.orderid
and o.supplierid = s.supplierid


GROUP BY


o.supplierid