[AccessD] Query synatx

Scott & Stuart,
 
Your pointers were a great help. It's a SQL server backend so I swaped
the IIF for Case and ended up with the following -
 
SELECT SUM(Quantity) AS TotalQuantity
FROM  (SELECT 'Quantity' = CASE 
    WHEN dbo.Orders.Type = 0 
        THEN dbo.[Order Details].Outers 
            ELSE dbo.[Order Details].Outers * - 1 
        END
     FROM dbo.Orders INNER JOIN
               dbo.[Order Details] ON dbo.Orders.OrderID = dbo.[Order
Details].OrderID
     WHERE (dbo.Orders.Status <> 2) AND (dbo.Orders.StockAdj = 0) 
            AND (dbo.[Order Details].SupplierRef = 'AM040')) DummyAlias
 
Many thanks
Tim 

	-----Original Message-----
	From: Marcus, Scott (GEAE, RHI Consulting)
[mailto:scott.marcus at ae.ge.com] 
	Sent: 24 March 2003 16:34
	To: 'accessd at databaseadvisors.com'
	Subject: RE: [AccessD] Query synatx
	
	
	How about an if statement like the following
	 
	Select OrderID,
	          Price, 
	          IIF(InvoiceType = True, Quantity, Quantity * -1) as
QTY
	FROM OrderHeader, OrderDetails
	WHERE OrderHeader.OrderID = OrderDetails.OrderID
	 
	You get the jist?
	 
	Scott

		-----Original Message-----
		From: Pain, T. (Tim) [mailto:Tim.Pain at e20.akzonobel.com]
		Sent: Monday, March 24, 2003 11:22 AM
		To: accessd at databaseadvisors.com
		Subject: [AccessD] Query synatx
		
		

		Dear List 

		I have a OrderHeader and OrderDetails tables. 
		The OrderHeader contains a InvoiceType field, set to
True for Invoice and False for CreditNotes. 
		The OrderDetails contains a Quantity field. This is
always a positive value, even for CreditNotes. 
		The join between the tables is on OrderID. 

		What I am trying to do is create a query that will
return a single value for all the quantities on order in the
OrderDetails, for a given product number. So where the InvoiceType =
False, the Quantity will have to be Quantity * -1.

		This has got to be possible, but having spent most of
the day going nowhere with this, I need help. 

		Any ideas would be much appreciated. 

		Many thanks 
		Tim Pain 



-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://databaseadvisors.com/pipermail/accessd/attachments/20030325/cc6826ce/attachment-0001.html>