M
Marshall Barton
rgrantz said:There are two tables: OrderDetail and OrderTracking
OrderDetail has field OrderNumber (indexed, but duplicates OK, because every
Backorder on a specific order is the original order number preceded by "B";
not my choice). OrderTracking has field StatDetail (related to order by
OrderNumber).
OrderNumber is a one-to-many relationship to StatDetail (one order goes
through several phases/locations, ie. Order Entered, Order Made, Order
Shipped, Backorder Created, etc.)
I am trying to narrow down the relationships of these two tables, and have
found that there are more "Backorder Created" records than there are
OrderNumbers that begin with "B." So, I'd like to do a query that checks
for every OrderNumber that begins with "B" that does NOT have a
"BackorderCreated" value in ANY of its related StatDetail records. How can
I do this? When I query for OrderNumber Like "B*" and StatDetail
<>"BackorderCreated" I get all the OTHER StatDetail records, which doesn't
necessarily mean that one of them WASN'T "BackorderCreated." How do you
search for the nonexistence of a value in ANY of a field's one-to-many
related records?
I didn't follow all that, but maybe you want to use a
non-equi Join in the query:
SELECT OrderDetail.OrderNumber
FROM OrderDetail LEFT JOIN OrderTracking
ON Mid(OrderDetail.OrderNumber,2)=OrderTracking.StatDetail
WHERE Left(OrderDetail.OrderNumber,1) = "B"
AND OrderTracking.StatDetail Is Null
If that doesn't get you going, post back with a few examples
of your data that demonstrate what you need in the query's
result (as well as some data it should exclude).