rows deleting from VB

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Alex - 04 Mar 2005 18:02 GMT

I have products in a table with quantity per year:
Product Territory Y1 Y2 Y3 ...
TTT 1 10 0 0
TTT 2 0 0 0
TTT 3 20 10 0
PPP 1 0 0 0
PPP 2 15 0 5
SSS 4 0 0 0

I was deleting a product in territory where the qty was 0 for all years by
using the following code in VB:

CurrentDb.Execute ("DELETE * FROM tblTable WHERE " _
& "tblTable.Y4=0 AND tblTable.Y3=0 AND " _
& "tblTable.Y2=0 AND tblTable.Y1=0 AND " _
.......")
So, TTT for territory 2, PPP for territory 1, and SSS would be deleted.
But, now I need to don't delete the product if for at least for one
territory the qty for all years is not equal 0.
I think, it should be something associated with Sum:
DELETE [Product] FROM tblTable WHERE " _
& "tblTable.Sum([Y4])=0 AND tblTable.Sum([Y3])=0 AND " _
& "tblTable.Sum([Y2])=0 AND tblTable.Sum([Y1]=0 AND ... "
But, it looks like it won't work.

Could anybody advise anything?

Thanks

Reply to this Message

Tim Ferguson - 04 Mar 2005 18:34 GMT

> I have products in a table with quantity per year:
> Product Territory Y1 Y2 Y3 ...
> TTT 1 10 0 0

Now you know why normalising this thing would have been such a good
idea. Do you still have an opportunity for redesigning this thing
correctly?

> So, TTT for territory 2, PPP for territory 1, and SSS would be
> deleted. But, now I need to don't delete the product if for at least
> for one territory the qty for all years is not equal 0.
> I think, it should be something associated with Sum:

You can get the list of products that need to be deleted thus:

SELECT Product
FROM MyTable
GROUP BY Product
HAVING SUM(Y1)=0 AND SUM(Y2)=0 AND SUM(Y3)=0 AND SUM(Y4)=0

So you can delete them like this:

DELETE FROM MyTable
WHERE Product IN
( SELECT Product
FROM MyTable
GROUP BY Product
HAVING SUM(Y1)=0
AND SUM(Y2)=0
AND SUM(Y3)=0
AND SUM(Y4)=0
)

But I would seriously urge you to think hard about remodelling the
whole design.

HTH

Tim F

Reply to this Message