<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD><TITLE>Message</TITLE>
<META http-equiv=Content-Type content="text/html; charset=us-ascii">
<META content="MSHTML 6.00.2800.1106" name=GENERATOR></HEAD>
<BODY>
<DIV><SPAN class=868244116-25032003><FONT face=Arial color=#0000ff size=2>Scott
& Stuart,</FONT></SPAN></DIV>
<DIV><SPAN class=868244116-25032003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=868244116-25032003><FONT face=Arial color=#0000ff size=2>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 -</FONT></SPAN></DIV>
<DIV><SPAN class=868244116-25032003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=868244116-25032003><FONT face=Arial color=#0000ff size=2>SELECT
SUM(Quantity) AS TotalQuantity<BR>FROM (SELECT 'Quantity' = CASE
</FONT></SPAN></DIV>
<DIV><SPAN class=868244116-25032003><FONT face=Arial color=#0000ff
size=2> WHEN dbo.Orders.Type = 0 </FONT></SPAN></DIV>
<DIV><SPAN class=868244116-25032003><FONT face=Arial color=#0000ff
size=2> THEN dbo.[Order
Details].Outers </FONT></SPAN></DIV>
<DIV><SPAN class=868244116-25032003><FONT face=Arial color=#0000ff
size=2> ELSE
dbo.[Order Details].Outers * - 1 </FONT></SPAN></DIV>
<DIV><SPAN class=868244116-25032003><FONT face=Arial color=#0000ff
size=2>
END<BR> FROM dbo.Orders INNER
JOIN<BR>
dbo.[Order Details] ON dbo.Orders.OrderID = dbo.[Order
Details].OrderID<BR> WHERE (dbo.Orders.Status
<> 2) AND (dbo.Orders.StockAdj = 0) </FONT></SPAN></DIV>
<DIV><SPAN class=868244116-25032003><FONT face=Arial color=#0000ff
size=2> AND
(dbo.[Order Details].SupplierRef = 'AM040')) DummyAlias</FONT></SPAN></DIV>
<DIV><FONT face=Arial color=#0000ff size=2></FONT> </DIV>
<DIV><SPAN class=868244116-25032003><FONT face=Arial color=#0000ff size=2>Many
thanks</FONT></SPAN></DIV>
<DIV><SPAN class=868244116-25032003></SPAN><SPAN lang=en-gb><B><I><FONT
face=Arial color=#008080 size=2>Tim</FONT></I></B><I></I></SPAN> </DIV>
<BLOCKQUOTE dir=ltr style="MARGIN-RIGHT: 0px">
<DIV></DIV>
<DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left><FONT
face=Tahoma size=2>-----Original Message-----<BR><B>From:</B> Marcus, Scott
(GEAE, RHI Consulting) [mailto:scott.marcus@ae.ge.com] <BR><B>Sent:</B> 24
March 2003 16:34<BR><B>To:</B>
'accessd@databaseadvisors.com'<BR><B>Subject:</B> RE: [AccessD] Query
synatx<BR><BR></FONT></DIV>
<DIV><SPAN class=326133116-24032003><FONT face=Arial color=#0000ff size=2>How
about an if statement like the following</FONT></SPAN></DIV>
<DIV><SPAN class=326133116-24032003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=326133116-24032003><FONT face=Arial color=#0000ff
size=2>Select OrderID,</FONT></SPAN></DIV>
<DIV><SPAN class=326133116-24032003><FONT face=Arial color=#0000ff
size=2> Price, </FONT></SPAN></DIV>
<DIV><SPAN class=326133116-24032003><FONT face=Arial color=#0000ff
size=2> IIF(InvoiceType
= True, Quantity, Quantity * -1) as QTY</FONT></SPAN></DIV>
<DIV><SPAN class=326133116-24032003><FONT face=Arial color=#0000ff size=2>FROM
OrderHeader, OrderDetails</FONT></SPAN></DIV>
<DIV><SPAN class=326133116-24032003><FONT face=Arial color=#0000ff
size=2>WHERE OrderHeader.OrderID = OrderDetails.OrderID</FONT></SPAN></DIV>
<DIV><SPAN class=326133116-24032003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=326133116-24032003><FONT face=Arial color=#0000ff size=2>You
get the jist?</FONT></SPAN></DIV>
<DIV><SPAN class=326133116-24032003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=326133116-24032003><FONT face=Arial color=#0000ff
size=2>Scott</FONT></SPAN></DIV>
<BLOCKQUOTE>
<DIV class=OutlookMessageHeader dir=ltr align=left><FONT face=Tahoma
size=2>-----Original Message-----<BR><B>From:</B> Pain, T. (Tim)
[mailto:Tim.Pain@e20.akzonobel.com]<BR><B>Sent:</B> Monday, March 24, 2003
11:22 AM<BR><B>To:</B> accessd@databaseadvisors.com<BR><B>Subject:</B>
[AccessD] Query synatx<BR><BR></FONT></DIV><!-- Converted from text/rtf format -->
<P><FONT face=Arial size=2>Dear List</FONT> </P>
<P><FONT face=Arial size=2>I have a OrderHeader and OrderDetails tables.
</FONT><BR><FONT face=Arial size=2>The OrderHeader contains a InvoiceType
field, set to True for Invoice and False for CreditNotes.</FONT> <BR><FONT
face=Arial size=2>The OrderDetails contains a Quantity field. This is always
a positive value, even for CreditNotes.</FONT> <BR><FONT face=Arial
size=2>The join between the tables is on OrderID.</FONT> </P>
<P><FONT face=Arial size=2>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.</FONT></P>
<P><FONT face=Arial size=2>This has got to be possible, but having spent
most of the day going nowhere with this, I need help.</FONT> </P>
<P><FONT face=Arial size=2>Any ideas would be much appreciated.</FONT> </P>
<P><FONT face=Arial size=2>Many thanks</FONT> <BR><B><I><FONT
face="Times New Roman" color=#000080>Tim Pain</FONT></I></B>
</P><BR><BR></BLOCKQUOTE></BLOCKQUOTE></BODY></HTML>