MS Access : Looping and Recordset

Hello all,

I am pretty new at VBA programming. I just simplify the following table to show what needs to be done.

ID LEVEL ITEM NUMBER Contract Unit Cost
20 .001
21 ..002 277439-1
22 ...003 A605/6063-T5 CMMN $0.03
22 ...003 A605/6063-T5 CMMN $0.02
23 ..002 277449-1
24 ...003 MS20257C4-3230 2100 $16.00
24 ...003 MS20257C4-3230 2100 $16.00
25 ..002 277719-1
26 ..002 277719-1
27 ...003 FSCU7900-10C7 CMMN $6.25
27 ...003 FSCU7900-10C7 CMMN $8.70
27 ...003 FSCU7900-10C7 CMMN $10.90
28 ...003 277719-11
29 ....004 277719-11RS 2100 $50.84
29 ....004 277719-11RS 2100 $50.84
30 ..002 278786-1
31 ..002 278786-1
32 ..002 278855-1
33 ..002 278855-1
34 ...003 A806/6063-T52 CMMN $0.52
34 ...003 A806/6063-T52 CMMN $0.56
34 ...003 A806/6063-T52 CMMN $0.86
34 ...003 A806/6063-T52 CMMN $0.78
34 ...003 A806/6063-T52 CMMN $0.21
34 ...003 A806/6063-T52 CMMN $4.28

The actual data is formatted the way I represented in the table listed above. The first part of the query will grab the structure(assembly) from the AS/400 as shown with the level in place, at the right position. Basically, level one is the top level and all part underneath must roll into it(this is the easy part). Another example would be, if I am on (level ..002, item # 277439-1), I want all the next level to roll into (level ..002, item # 277439-1)and stop when it get to the next level ..002

I figure the logic is to test the level of the original part to the next row and if its has a greater level then add it if not stop but I am not sure which function to use

All I need to do is make sure I roll it up correctly which I am having a difficult time. I can't seem to make the rollup stop at the right level.

Please help!!!
Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories