Delete items without a parent

[rockies1]

I have a table that looks like this:

Item	Parent
1	0
2	0
3	0
4	3
5	3
6	5
7	3
8	0

I want to be able to do the following:
Delete a record and any records that would be under it. For example, if I delete Item 3, I need to delete items 4, 5, & 7 because their Parent is 3. In addition, Item 6 must go because Item 5 was deleted.

I need this to occur for any number of levels of data. Item 3 could have any number of generations of children.

I hope this all makes snese.

Anyone know how I can accomplish this?

Thanks you!

 

[simongallop]

The way that I would approach it, is create a new table which only has one field, being Parent (primary key the field). Then make an append query where you have item and parent as fields, but make item Not visible. Type in 3 into the criteria of item and make the query a totals query. This gives you all the parent numbers.

For your second query, make it a delete query and type in the item number to delete.

Then take the first number in your new table, rerun the append query with that number, so you get any grand children and delete that number.

As append queries add to the base of a table, you ought to be able to automate the process, until you get to the bottom of the new table.

HTH

 

[rockies1]

How, though, can I do this via code?

 

[rockies1]

Because I'm not doing it manually...

I'm doing it from a VB program...