Do you know how to do a for loop?

[TivoUser]

Hello all,

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

ROW LEVEL PART COST
1 1 a $2
2 2 b $1
3 3 f $3
4 4 g $8
5 2 o $2
6 3 x $2
7 2 c $5
8 2 r $5

Basically, level one is the top level and all part
underneath must roll into it(this is the easy part). The
problem I am having is when I want to roll up PART B(row
2), I want row(2,3,4) only or if I want PART O (row 5) I
want row(5,6).

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

Please help.

 

[JeremyNYC]

Tivo,

Is there a field in your table that allows you to sort things in this order? (ie, does that "Row" field really exist?) If not, you'll need to add one, as rows are not stored in any particular order in tables.

Yes, your thought of how to do the loop is correct.

Look into help for "recordset".

You'll have to be a lot more specific to get more help though. It's not clear what you want to do with the rows.

First, though, I'd check out the help files and start into the process yourself so that you can describe the problem a little more fully.

Jeremy =============
Jeremy Wallace

http://www.AlphaBetCityDataworks.com

Designing, Developing, and Deploying Access databases since 1995.

 

[cmmrfrds]

Just as a pointer.
It looks like you have the classical problem of a recursive relationship in the table. A parent child relationship with indeterminate levels, other examples are Org Chart and Bill of Materials.

There has been discussions in the past on the Access and SQL Server Forums. Check or do a search on the Forum for recursive or other keywords. There are a couple of ways to handle the design of these tables. Joe Celko had an article on this subject, but I don't remember the name right now. It is better to consider this problem at the design stage of your project and come up with the best table design for the problem.

 

[TivoUser]

JeremyNYC

ID LEVEL ITEM NUMBER Contract Unit Cost
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. 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.

Example:
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 level ..002.


Thanks for replying.