Query Problem

[harrisw]

Designed a query SQL below:

SELECT tblNewOrders.[Date taken], Sum(tblNewOrders.Upsell) AS [Total Upsell], Sum(tblNewOrders.Price) AS [Total Sales], Sum([tblNewOrders]![BI]=True) AS [BI Schs], Sum([tblNewOrders]![CS]=True) AS [CS Schs], Sum(DLookUp(" [tblNewOrders]![Upsell] ","tblNewOrders"," [tblNewOrders]![Product Code] = 1180 or 1177 or 1181 or 1182 or 1183 or 1184 or 5644")) AS [Int Supps]
FROM tblOrders LEFT JOIN tblNewOrders ON tblOrders.[Invoice Code] = tblNewOrders.[Invoice Number]
GROUP BY tblNewOrders.[Date taken]
HAVING (((tblNewOrders.[Date taken]) Is Not Null));

When I run the query the BI Schs and CS schs columns have the correct numbers but are negative instead of positive.

What am I doing wrong?

 

[Rich@ITTC]

Hi Harrisw

Are the fields/columns BI Schs and CS schs Yes/No (True/False) fields? I would guess so ... and in that case it is understandable why you get a negative value, once you are aware of the fact that Access stores :

Yes as -1

No as 0

So if you sum 10 rows that have a value of -1 you get -10.

You therefore need to convert these fields from a negative to a positive value. You could multiply the sum by -1 or you could use the Abs function:

---------------------------------------------------------
This example uses the Abs function to compute the absolute value of a number.

Dim MyNumber
MyNumber = Abs(50.3) ' Returns 50.3.
MyNumber = Abs(-50.3) ' Returns 50.3.
------------------------------------------------------------

(Taken from Access 97 Help File).

HTH

Rich Gorvin

[This message has been edited by Rich@ITTC (edited 09-13-2001).]

 

[harrisw]

That explains it.

Cheers