Help with Frustrating Query

[Todd]

This one is giving me fits.

I have two tables.
The first table is called BatchHist.
It has fields: BatchNum, ManuDate

The second table is called Status.
It has fields: Equip, Status, Status Date, BatchNumInEquip

What I'm trying to do is to identify the "in-process"
batches. As a batch moves through the equipment, its
status is updated at each step. The last step is
called "Returned". So in simple terms, what I want to do
is return all the BatchNums from the BatchHist table,
except I want to exclude any where BatchNum =
BatchNumInEquip where Status = Returned.

Here is some sample data and results.

BatchHist
BatchNum, Manu Date
1A,1/1/03
1B,1/1/03
12A,1/6/03
3A, 1/28/03
3B, 1/28/03

Status
Equip, Status, Status Date, BatchNumInEquip
V1,Filled,1/1/03,1A
V2,Filled,1/1/03,1B
V1,Shipped,1/12/03,1A
V1,Returned,1/18/03,1A
V2,Shipped,1/13/03,1B
V3,Filled,1/6/03,12A
V1,Filled,1/28/03,3A

The query I want would return

1B
12A
3A
3B

Can someone help with this?

Thanks in advance.

Note: both of my tables have numerous other fields, I've
only listed the fields that I think are relevant in the
query.

 

[JohnFol]

Try this (Cut down version)

SELECT BatchHist.BatchNum
FROM BatchHist
WHERE (((BatchHist.BatchNum) Not In (select [BatchNumInEquip] from status
where status ="returned")));

 

[Brian Camire]

You might try a query whose SQL looks something like this:

SELECT
[BatchHist].[BatchNum]
FROM
[BatchHist]
WHERE
[BatchHist].[BatchNum]
NOT IN
(SELECT
[Status].[BatchNumInEquip]
FROM
[Status]
WHERE
[Status].[Status] = "Returned")

 

[Todd]

Thanks a lot. This worked perfectly. Its actually pretty
simple, I was really trying to overcomplicate things.
This method will be useful for me to apply elsewhere also.

-----Original Message-----
You might try a query whose SQL looks something like this:

SELECT
[BatchHist].[BatchNum]
FROM
[BatchHist]
WHERE
[BatchHist].[BatchNum]
NOT IN
(SELECT
[Status].[BatchNumInEquip]
FROM
[Status]
WHERE
[Status].[Status] = "Returned")

This one is giving me fits.

I have two tables.
The first table is called BatchHist.
It has fields: BatchNum, ManuDate

The second table is called Status.
It has fields: Equip, Status, Status Date, BatchNumInEquip

What I'm trying to do is to identify the "in-process"
batches. As a batch moves through the equipment, its
status is updated at each step. The last step is
called "Returned". So in simple terms, what I want to do
is return all the BatchNums from the BatchHist table,
except I want to exclude any where BatchNum =
BatchNumInEquip where Status = Returned.

Here is some sample data and results.

BatchHist
BatchNum, Manu Date
1A,1/1/03
1B,1/1/03
12A,1/6/03
3A, 1/28/03
3B, 1/28/03

Status
Equip, Status, Status Date, BatchNumInEquip
V1,Filled,1/1/03,1A
V2,Filled,1/1/03,1B
V1,Shipped,1/12/03,1A
V1,Returned,1/18/03,1A
V2,Shipped,1/13/03,1B
V3,Filled,1/6/03,12A
V1,Filled,1/28/03,3A

The query I want would return

1B
12A
3A
3B

Can someone help with this?

Thanks in advance.

Note: both of my tables have numerous other fields, I've
only listed the fields that I think are relevant in the
query.

.