Output all results from query

[Ali29J]

Hi All

I have a query which i wnat to output all results even if count is null

SELECT Sum((RFQTracker.PriceToHigh)*(-1)) AS SumOfPriceToHigh, Sum((RFQTracker.NoBid)*(-1)) AS SumOfNoBid, Sum((RFQTracker.ProjectCancelled)*(-1)) AS SumOfProjectCancelled, Sum((RFQTracker.OnHold)*(-1)) AS SumOfOnHold, Sum((RFQTracker.NoCapability)*(-1)) AS SumOfNoCapability, Sum((RFQTracker.Other)*(-1)) AS SumOfOther, Sum((RFQTracker.Existing)*(-1)) AS SumOfExisting, Sum((RFQTracker.Requote)*(-1)) AS SumOfRequote, Count(RFQTracker.FollowupStatus) AS CountOfFollowupStatus, StatusLookup.Order, StatusLookup.FollowupStatus, RFQTracker.FollowupStatus, StatusLookup.Description
FROM RFQTracker RIGHT JOIN StatusLookup ON RFQTracker.FollowupStatus = StatusLookup.FollowupStatus
WHERE (((RFQTracker.RaiseRFQStart) Between [Forms]![globalreports]![text143] And [Forms]![globalreports]![text145]))
GROUP BY StatusLookup.Order, StatusLookup.FollowupStatus, RFQTracker.FollowupStatus, StatusLookup.Description
ORDER BY StatusLookup.Order;

at present if none at particular status then it is omitted, how do i get it to show all stages wether value present or not???

THanks

Ali

 

[rac2]

Recode the NULLs to 0. SUM the recoded values.

For example-

SELECT StatusLookup.Order, 
       Sum( IIf( IsNull(RFQTracker.PriceToHigh), 0, RFQTracker.PriceToHigh )  *  (-1)  ) 

FROM RFQTracker 
RIGHT JOIN StatusLookup ON RFQTracker.FollowupStatus = StatusLookup.FollowupStatus 
GROUP BY StatusLookup.Order

 

[Ali29J]

Hi Thanks for your advice, but it did nt work, main reason being as follows:

it seems that its if the statement

Count(RFQTracker.FollowupStatus) AS CountOfFollowupStatus

returns a null record then it is not shown..i tried similar technique to that suggested as below:

Count(IIf(isnull(RFQTracker.FollowupStatus),0,RFQTracker.FollowupStatus)*(-1)) AS CountOfFollowupStatus

however it returned that this was to complex statement, etc

any ideas how i could return all for count even is null??

 

[rac2]

I think that Count(expression) never returns a NULL value, if the expression evaluates to NULL in all of the rows then Count(expression) will be 0 (zero). There is no need to writing an expression in any case because the Count adds 1 for a non-null value and 0 for a null value. Just use

Count(RFQTracker.FollowupStatus) AS CountOfFollowupStatus

I could be wrong here but that is pretty much how I use Count().


Possibly, the issue is with the WHERE condition.

WHERE (((RFQTracker.RaiseRFQStart) Between [Forms]![globalreports]![text143] And [Forms]![globalreports]![text145]))

Think about it this way. For a row in the StatusLookup lookup table which does not have any associated row in the RFQTracker table. The JOIN produces the value NULL for RFQTracker.RaiseRFQStart. So the condition evaluates to

WHERE NULL Between etc. This is FALSE. so that row of the StatusLookup table will not be included in the set of rows to be counted. Consequently it will not provide a group to count.


I would guess that you would like to have a value zero in that case. One approach would be to add another condition.

WHERE (((RFQTracker.RaiseRFQStart) Between [Forms]![globalreports]![text143] And [Forms]![globalreports]![text145]))
OR RFQTracker.RaiseRFQStart IS NULL

Another approach would be to move the condition from the WHERE clause into an IIf(condition, 1, 0) and use SUM instead of COUNT.

As the query is written now it says give me SUMs and COUNTs where there is an RFQTracker value between the globalreports values. And that is exactly what it is doing.

Re-write the query to mean give me SUMs and COUNTs for all of the StatusLookup values; add and count 1 if the RFQTracker row is in the range of interest, otherwise add and count 0.

HTH

 

[Ali29J]

thanks rac2, i tried the statement without the where clause and you were right all records were outputted (inc the if 0) by simple addition of the OR statment returned exactly what i was looking for, great thanks!