I have two tables orderHeader and orderDetails that have been imported from our ERP system. I have joined these on the orderNumber. orderHeader has the order number as the primary key. orderDetails is tied to orderHeader on the ordernumber field. There is a unique record in orderDetails for each order line item base on orderNumber. Therefore I have joined them on the order number. Pretty standard.:)

The problem is that not all orderHeader records have orderDetail records. For some reason, there are orders that were generated in our ERP system that do not have any line items. When I add additional fields to the query, I get errors (#Error - Invalid use of Null) in the result records.

How do I complete this query so that I only return orderHeader records that have at least 1 orderDeatil record? That would fix this issue.

Thanks.

You seem to have a Left Join going on here where you are showing all records from OrderHeader whether they have an OrderDetails entry or not. You can change this Join to an Inner Join to remove the Null OrderDetails values, or in the criteria line of the query designer, just add "Is Not Null". This will suppress OrderHeader records that do not have OrderDetails entries.

Did that help?


mmcdonal

Look it up at: http://wrox.books24x7.com

Sorry, add Is Not Null to the OrderDetails column.

mmcdonal

Look it up at: http://wrox.books24x7.com

I added "is not null" to the orderDetails OrderNumber criteria field, and it worked like a charm!

Thank you.