rows deleting from VB

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
I have products in a table with quantity per year:
Product Territory Y1 Y2 Y3 ...
TTT 1 10 0 0
Click to expand...

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:
Click to expand...

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
 
Back
Top