counting and checking n levels in a recordset (1 Viewer)

[rsmonkey]

Hi,
I need to work out how many components we have in stock that make up a Part - identified by being the PrimaryPart. Each part has a component, which in turn has another component, but different part have a different number fo sub-components, a simple display of the table I am using is shown below:

ID PartName ComopnentID No. in Stock PrimaryPart
1 Part X 2 12 Yes
2 Part Y 3 14 No
3 Part Z 15 No
4 Part T 5 1 Yes
5 Part U 6 11 No
6 Part V 7 25 No
7 Part W 20 No

The problem I have is that I need my vba code to check if there is a sub-compnent for the current part and if so make sure that is counted. So in the instance above Part X has 2 sub-compnents (Part Y, Part Z) and there are 41 bits in stock, whereas Part T has 3 sub-compnents (PartU, PartV, PartW) and there are 57 bits in stock.

How do I get a script to recurse X number of times to check.

Thanks

 

[WayneRyan]

rs,

I think you need to define your table further.

Where in the table does it show that x is made up of Y, Z?
Or that T is made up of U, V, W?

And, using your example, could part U be made up of other parts?

Need more info,
Wayne

 

[rsmonkey]

well it was nice to see the forum didnt keep my table format.. grrr! right well part of this post was a brainstorming idea, but this is my idea let me give you the updated table structure.

ID | PartName | ComopnentID | No. in Stock | PrimaryPart | Installs |
1 | Part X | 2 | 12 | Yes | 6 |
2 | Part Y | 3 | 14 | No | 6 |
3 | Part Z | - | 0 | No | 6 |
4 | Part T | 5 | 1 | Yes | 0 |
5 | Part U | 6 | 11 | No | 5 |
6 | Part V | 7 | 25 | No | 6 |
7 | Part W | - | - | No | 5 |

So Component ID refers to its Sub-Component & Primary Part obviously signifies a primary part. So what i need to do is to create a loop which will create these results & follow this sort of order:

Create an additional column on my table called Availability:
the problem is the aggregate function to do this is different for a Primary part & a sub component -

Primary Part Availability = No.In Stock - Installs

Sub-Components Availability = (No.In Stock + (Availaility of Component Above)) - Installs

So the order of the script would follow this procedure -

1. Select & create recordsets of Primary Parts (Part X, Part T)
2. Workout Availability (so as above: Part X = (12 - 6) 6
3. Does it have a component id? (Yes = 2)
4. Is Availability of Part X >= 1? (Yes = 6)
5. Workout Availability of ComponentID (Part Y):
(No. In Stock + (Part X) Availability) - Installs = (14 + 6) - 6 = 14
6. Does it have a component id? (Yes = 3)
7. Is Availability of Part Y >= 1? (Yes = 14)
8. Workout Availability of ComponentID (Part Z):
(No. In Stock + (Part Y) Availability) - Installs = (0 + 14) - 6 = 8
9. Does it have a component id? (No = -)
10. rs.movenext

So as you can see thats the procedure i need but I'm just not sure how to do it b/c its a loop within a loop really...

anyhelp would be much appreciated!

 

[rsmonkey]

ideas anyone?