Does not exist statement?

[vbahelp07]

I have the following query:

SELECT Bill.BillNumber, Bill.BillDescription1, Last(Bill.CurrentRevision) AS LastOfCurrentRevision, Bill.DefaultWhse, Bill.ProductLine, Bill.BinLocation, Bill.ComponentItemCode, Bill.ItemDescription, Bill.ComponentRevision, Bill.QtyPerBill INTO CurrentBill
FROM Bill
GROUP BY Bill.BillNumber, Bill.BillDescription1, Bill.DefaultWhse, Bill.ProductLine, Bill.BinLocation, Bill.ComponentItemCode, Bill.ItemDescription, Bill.ComponentRevision, Bill.QtyPerBill
HAVING (((Bill.ComponentItemCode)<>[BillNumber]))
ORDER BY Bill.BillNumber;

there are item numbers in the BillNumber that are the same in the ComponentItemCode field.

I don't want the BillNumbers that also exist in the ComponentItemCode.

The above query isn't quite working.

example of result:

BillNumber	ComponentItemCode
80-42500            	03-00220
BillNumber	ComponentItemCode
BX1200              	80-42500

Notice that item "80-42500" exist as a BillNumber as well as a ComponentItemCode.

How do I NOT show the "80-42500" from the BillNumber because this item exists as a ComponentItemCode?

 

[lameid]

SELECT Bill.BillNumber, Bill.BillDescription1, Last(Bill.CurrentRevision) AS LastOfCurrentRevision, Bill.DefaultWhse, Bill.ProductLine, Bill.BinLocation, Bill.ComponentItemCode, Bill.ItemDescription, Bill.ComponentRevision, Bill.QtyPerBill INTO CurrentBill
FROM Bill

[i]WHERE Bill.BillNumber Not IN (Select B.ComponentItemCode From Bill as B)[/i]

GROUP BY Bill.BillNumber, Bill.BillDescription1, Bill.DefaultWhse, Bill.ProductLine, Bill.BinLocation, Bill.ComponentItemCode, Bill.ItemDescription, Bill.ComponentRevision, Bill.QtyPerBill
ORDER BY Bill.BillNumber;

The above will eliminate records where the value in Billnumber can be found in ComponentItemCode.

I put it in the Where clause but because you are grouping, I am wondering if you want it in the having clause.

The where clause happens before the group by and other aggregate functions where as the having clause happens after.

I hope all that makes sense to you.

 

[vbahelp07]

hi lameid,
thanks for answering this toO!

tried to run and it's been running for awhile and will not finish?

 

[lameid]

Ctrl+Break will halt it but you may not want to.

Indexing both Billnumber and ComponentItemcCode is my best suggestion.

You can also do this another way. You may get faster results...

SELECT Bill.BillNumber, Bill.BillDescription1, Last(Bill.CurrentRevision) AS LastOfCurrentRevision, Bill.DefaultWhse, Bill.ProductLine, Bill.BinLocation, Bill.ComponentItemCode, Bill.ItemDescription, Bill.ComponentRevision, Bill.QtyPerBill INTO CurrentBill
FROM Bill [i]Left Join Bill As B On Bill.BillNumber = B.ComponentItemCode
Where B.ComponentItemCode Is Null[/i]

GROUP BY Bill.BillNumber, Bill.BillDescription1, Bill.DefaultWhse, Bill.ProductLine, Bill.BinLocation, Bill.ComponentItemCode, Bill.ItemDescription, Bill.ComponentRevision, Bill.QtyPerBill
ORDER BY Bill.BillNumber;

 

[vbahelp07]

thanks.

THANK YOU THANK YOU!

this is giving me results.
just have to check/verify the results now

 

[vbahelp07]

ok, this is WEIRD too.

i don't get the 80-42500 in the billnumber BUT now the BX1200 in billnumber with the 80-42500 as componentitemcode is NOT showing :-(

i don't get it.

 

[lameid]

Does this help you see why it does not show?

Select B.* 
From Bill as B 
Where B.ComponentItemCode = "BX1200"

Or are we back to the corrupt database theory?