Returning Null Values

[Doozer1979]

Returning Null Values as Zero

Hello

I have three fields in a query all from the same table.

The Fields are:

DateRange, Nf_Orders, Priority

DateRange contains integers from 0 to 11, Nf Orders is a number field, and Priority contains either 'Y' or 'N'.

What i want to do is sum Nf_Orders where (DataRange is >0 AND <=8) AND Priority is equal to 'Y'

For some of theDateRange integers through 1-8 there will not be any orders where priority is equal to 'Y'. In those instances i want the query to return 0.

I've tried messing around with Nz but it still only returns the DateRange Integer where there are orders that exist.

Can anyone help? Do I need a subquery for this?

 

[Brianwarnock]

Nz will not help by itself as if there are no orders there is no datato work on.
The only way I can think of doing this is to have another table with 1 field daterange and the entries 1 - 8, then outerjoin this to the other table selecting all from this new table but only matching entries from the original then you can apply the Nz but as I type this I think hangon , no entry - no priority!!

Brian

 

[Doozer1979]

Thanks for your reply Brian.

Is there anyone else out there that can assist with this problem?

Thanks

 

[neileg]

Brian is on the right lines. Create a query that joins your dataset 1-8 with your totals query and create a caculated field Nz(SumOfNf_Orders,0).

The dataset holding 1-8 could be a query, not a table, driven by your user parameters so it would work with any date range.

 

[Doozer1979]

Cheers Neil, But you might have to teach me how to suck eggs on this one!

Let me See if i've got this right.

I create query A with dataRange and SumOfNf_Orders in it then i use that in another query that has my priority criteria in it. Which query should the calculated field be in?

Thanks

Liam

 

[neileg]

In the query that joins the totals with the priority criteria.

 

[Doozer1979]

Thanks Neil,

I've tried the following SQL but it's giving me results that are quite obviously wrong.


Query 1 SQL

SELECT Orders.DateRange, Sum(Orders.No_Of_Orders) AS SumOfOrders FROM Orders
GROUP BY Orders.DateRange
HAVING (((Orders.DateRange)<=8 And (Orders.DateRange)>0));

Query2 SQL

SELECT SumOfDateRange.DateRange, Nz([SumOfOrders],0) AS SumPriority
FROM SumOfDateRange, Orders
WHERE (((Orders.Priority)="Y"));

I need the result set to return this (Numbers in Sum_Of_Orders are random in this example)


Date Range Sum_Of_Orders
1 5
2 0
3 2
4 0
5 3
6 4
7 8
8 0


But I get:

DateRange Sum_Of_Orders
1 432
1 432
1 432
2 200
2 200
etc
etc