remove invalid data

>I need to be able to only show records that don't have any planningjob associated at all

SELECT orderno, planningjob
FROM binding
WHERE planningjob IS NULL
ORDER BY orerno

Hi,

I wish it was that simple. There are some orderno that are duplicated 4 or 5 times. 2 of those records could have a null value and the others would have a proper value. So using your code returns orderno that do in fact have a record associated, if that makes sense?

Ben

Ben,

You need to get a count of records that contain non- null planningJobs.   If that count is zero for any given OrderNo, then all of the planningJobs for that order number are Null (meaning that ordernumber should be selected).

- Create a new query in design view and open the binding table.
- Select OrderNo
- Add the following expressio as a new column:
   JobNull: IIf(IsNull([PlanningJob]),0,1)
   This will select 0 when PlanningJob is Null, and 1 when it is not null.
   You need to sum this column and group by OrderNo to get a "count of non-nulls per order".
- Click the Summation sign on the toolbar of the query builder.
- Select "SUM" for the Total box for the JobNull column
- Select "Group By" for the total box in the OrderNo column
- Add the following criteria for JobNull:
    = 0
  This will select all orders that only have null planning jobs.

The resulting SQL will look something like this:

SELECT Orderno, Sum(IIf(IsNull([PlanningJob]),0,1)) AS JobNull
FROM binding
GROUP BY Orderno
HAVING Sum(IIf(IsNull([PlanningJob]),0,1))=0;

Hi,

I am actually using Query Anlayzer in SQL 2000. Sorry, perhaps I loaded this in the wrong area.

That still brings through jobs that have planningjob associated.

There can be multiple records per orderno but only some have planningjob associated. It's a pickle. I only want to see the orderno that have no planningjob associated at all