Nulls in median calculation 1

[lcfc]

I am using this code to obtain the median from a field:

SELECT Min([Time Arrive to Dr]) FROM [All]
WHERE [Time Arrive to Dr] IN
(SELECT TOP 50 PERCENT [Time Arrive to Dr] FROM [All] ORDER BY [Time Arrive to Dr] DESC)
UNION SELECT Max([Time Arrive to Dr]) FROM [All]
WHERE [Time Arrive to Dr] IN (SELECT TOP 50 PERCENT [Time Arrive to Dr] FROM [All] ORDER BY [Time Arrive to Dr] ASC);

This produces either 1 number or 2, from which i can find the average and thus get the median.

Unfortunatley there are some null cells in the field which i don't want to include in the calculation. How can i filter these out, i.e, where do i put "is not null" in the above code. Thanks

 

[PHV]

In each of the top 50 percent subqueries

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[lcfc]

Could you show me, in this query, I have tried but cannot seem to work it out?

 

[PHV]

Unfortunatley there are some null cells in the field
What is(are) the column(s) that may be null ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[lcfc]

In this field - [Time Arrive to Dr]

Thanks

 

[PHV]

Have you tried this ?[tt]
SELECT Min([Time Arrive to Dr])
FROM [All]
WHERE [Time Arrive to Dr] IN
(SELECT TOP 50 PERCENT [Time Arrive to Dr] FROM [All]
WHERE [Time Arrive to Dr] IS NOT NULL
ORDER BY [Time Arrive to Dr] DESC)
UNION
SELECT Max([Time Arrive to Dr])
FROM [All]
WHERE [Time Arrive to Dr] IN
(SELECT TOP 50 PERCENT [Time Arrive to Dr] FROM [All]
WHERE [Time Arrive to Dr] IS NOT NULL
ORDER BY [Time Arrive to Dr] ASC);[/tt]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[lcfc]

Hey that worked a treat, many thanks