Checking for nonexistence of value in ANY related records

[Marshall Barton]

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).

 

[rgrantz]

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?

Thanks for the help, anyone, it is appreciated.

 

[rgrantz]

I appreciate the input, but there would be no nulls. It's like this:

OrderDetail (Table)
- - - - - - - - - - - - - - - -
OrderNum (Field in OrderDetail, text datatype, BTW)
_________
1234
5678
B1234
B5678


OrderTracking (Table)
- - - - - - - - - - - -- - - - - -
OrderNum StatDetail
_________ ________
1234 Order Entered
1234 Order Packaged
1234 Order Shipped
1234 Backorder Created
5678 Order Entered
5678 Order Packaged
5678 Backorder Created
B1234 Order Packaged
B5678 Order Packaged


Every "Backorder Created" SHOULD indicate the presence of a corresponding
order with "B" in front of the original order number. However, I found that
there are a lot more OrderNumbers starting with "B" (indicating a Backorder)
than there are records in the OrderTracking Table with StatusDetail of
"Backorder Created". So, I wanted to run a query that returned all the
Order Numbers beginning with "B" that had NO corresponding "Backorder
Created" record in the original order.

Ex.:

B1234 SHOULD mean that OrderNumber 1234 has a "Backorder Created" record in
the StatDetail table. This is not always the case in the tables, though. I
want to query all the "B" orders (rightmost 4 characters of every order
beginning with "B") that have NO "Backorder Created" records associated in
the StatDetail table. They won't be null, and they can't be just
<>"Backorder Created", because that query will still return all the
StatDetail items per order, just not the "Backorder Created" ones, which
doesn't mean it's not IN there, it just won't show it in the query.

So, I need to find out all the orders for which a Backorder exists (9876
would be B9876), which do not have a "BackorderCreated" in ANY of the
StatDetail records associated with it (B9876 and 9876 both exist in
OrderDetail, but no 9876 in OrderTracking has a "Backorder Created"
StatDetail record; it can have a "entered," "Packaged", "Shipped,", etc.,
just no "Backorder Created" one).

 

[Marshall Barton]

I'm still not sure I'm following this, but try this kind of
thing:

SELECT OrderDetail.OrderNum
FROM OrderDetail
WHERE Left(OrderDetail.OrderNum,1) = "B"
AND NOT EXIST(SELECT OrderTracking.OrderNum
FROM OrderTracking
WHERE Mid(OrderDetail.OrderNum,2)=
OrderTracking.OrderNum
AND OrderTracking.StatDetail = "Backorder Created")