I'm attempting to create a report connecting to SQL backend tables. I have good confidence that I am linking the correct fields between tables. The application tables relate to box entries in a process related to authorizing box destruction. The BOX table has information about the box and is tied to the destruction batch items table by the box number which is duplicated in the Destruction Batch Items table only for the boxes which contain a Destruction batch number (i.e. the destruction batch number is not a field in the Box table, but the box numbers which have been added to destruction batches are duplicated in the Destruction Batch table. Boxes qualify based on a calcualted destruction date in the Box table. Boxes are only added to a destruction batch if fully approved for destruction so not all boxes which qualify are in a destruction batch. I can easily report all boxes which appear in a destruction batch so I can report the rule. I'm having trouble reporting the exception which would be a report showing the boxes which qualify (based on a calculated destruction date), but where there is no Box Number (duplicated) in the Batch Items table meaning it has not been approved.
I think the problem is I don't know how to tell the report designer to show a result where there is a null for a field. I'm sure there is a way to do it and have tried ISNULL as a Select Expert formula, but it only returns no results. I also think I'm having trouble conceptualizing the design process when there is a Box table with Box Number, but without the Destruction Batch number. Instead there is a Destruction Batch Item field in the Destruction Batch table which contains a duplicate of the box numbers which have been fully authorized.
I hope the explanation helps. I can go into better or more detail on request. I'm just stuck at this point. The end product would be a report which contains only boxes (and Box table information for these boxes) which are not showing as items in a destruciton batch.
Tom
IP Logged
